Sequential Numbering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I'm sure I've done this before, but I can't remember how. I have a table, tblAttendance which keeps track of when clients in the database came in for services. It has many fields, but these are the important ones: ClientID, DateOf, GroupID

I want to create a query which with a running sum of the displayed records based on client ID. For instance, I would want the following output

ClientID DateOf GroupID GroupN
-----------------------------------------
22121 10/5/05 12345
22121 10/7/05 12345
22121 10/15/05 12345
23545 9/7/05 12212
23545 9/7/05 35353
23545 10/5/05 12345
 
Dear John:

The technique is know as a correlated subquery, and also employs
aliasing:

SELECT ClientID, DateOf, GroupID,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.ClientID = T.ClientID
AND T1.DateOf < T.DateOf) AS GroupNo
FROM YourTable T
Order By ClientID, DateOf

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks Tom, but I'm guessing I didn't do this in a query after all. Using your sample code, I'm not sure what I'm supposed to put in for T or T1. I tried using tblAttendance and tblAttendance_1, but ended up getting a syntax error.

If you can help me out more I'd be greatful.

Thanks Again

Joh

----- Tom Ellison wrote: ----

Dear John

The technique is know as a correlated subquery, and also employ
aliasing

SELECT ClientID, DateOf, GroupID
(SELECT COUNT(*) + 1 FROM YourTable T
WHERE T1.ClientID = T.ClientI
AND T1.DateOf < T.DateOf) AS GroupN
FROM YourTable
Order By ClientID, DateO

Tom Elliso
Microsoft Access MV
Ellison Enterprises - Your One Stop IT Expert


On Fri, 5 Mar 2004 13:11:09 -0800, "John
 
Dear John:

Just leave T and T1 alone. Substitute the name of your table each
place it says "YourTable".

Read up on "alias" in online help to understand this better.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top