Re: Group By Sequential Records
- From: John Nurick <j.mapSoN.nurick@xxxxxxxxxxxxxx>
- Date: Fri, 29 Dec 2006 09:05:46 +0000
Ted,
There's a lot of work going on and I'd expect it to take hundreds or
thousands of times longer than a simple SELECT from the same table. All
I can suggest is that you make sure that the following fields are
indexed:
ID
AltID
Group
and that your network (if any) is in A1 condition.
But I know very little about optimising queries. If the query is
"extremely slow" with the fields indexed, consider starting a new thread
here with a subject something like "Optimize query/subquery". Include
the SQL of the two queries.
On Thu, 28 Dec 2006 14:01:00 -0800, teddyb777
<teddyb777@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
John,
It seems to be extremely slow. Is there any way to speed up the query?
Thanks for your help in this. It does do what I need it to.
Ted
"John Nurick" wrote:
Hi Teddy,
I think you can do it this way, but I haven't tested it comprehensively.
Start with a query that uses a subquery to return the number of records
in each "sequence" where Group and AltID are the same and ID numbers are
sequential. By subtracting the number returned by the subquery from the
ID number you get a calculated field - I've called it GroupedID - which
remains the same throughout each "sequence". If you omit the ID field
from the query, the rows in each "sequence" will be identical, so you
can use the DISTINCT keyword to omit the duplicates and return just one
row per "sequence". In my test database the query looks like this (the
table name is AddrCent).
SELECT DISTINCT
Group,
AltID,
( ID - (
SELECT COUNT(ID)
FROM AddrCent AS Sub
WHERE (Sub.Group = Main.Group)
AND (Sub.AltID = Main.AltID)
AND (Sub.ID < Main.ID)
)
) AS GroupedID
FROM AddrCent AS Main
;
Then base an ordinary totals query on the first query:
SELECT Group, COUNT(AltID) AS AltIDs
FROM qryXXX
GROUP BY Group
ORDER BY Group
;
On Thu, 21 Dec 2006 12:11:00 -0800, teddyb777
<teddyb777@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I have a table that contains:
Group, AltID, ID
The ID field is a sequential autonumber. I need the query to count AltId's
in each group but I need it to count like this:
If 2 instances of the same AltID are in sequential ID's then it needs to
count as 1. If the same AltID is in another instance further down in the
group then it needs to count each separated instance separately. I have
tried DLOOKUP but couldn't get the criteria right to say "Sequential ID."
Thanks in advance.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
- References:
- Re: Group By Sequential Records
- From: John Nurick
- Re: Group By Sequential Records
- From: teddyb777
- Re: Group By Sequential Records
- Prev by Date: Re: Square Peg in a Round Hole
- Next by Date: Re: delet query password
- Previous by thread: Re: Group By Sequential Records
- Next by thread: Merging rows
- Index(es):
Relevant Pages
|
Loading
