counting cycles in a table based on a unique field 1

[jeswel77]

Hi, I'm trying to create an automatic feature in my table(or query...wherever it's oppropriate) to count cycles in my table. I have a provider table with dates of service and i want to count each unique providers "cycle" based on their dates of service in descending order. I'm unsure how to do that..and i know autonumber isn't the way to go since i would want the count to start at 1 for each new provider.I'm using Access '97.

Here is an example of how i would like the table to look

Provider - Date - Cycle
smith - 3/1/04 - 2
jones - 2/1/04 - 1
brady - 12/1/04 - 2
brady - 5/1/04 - 1
smith - 2/1/04 - 1
smith - 1/1/05 - 3

thank you!!
Jessi

 

[PHV]

Say you have a table named tblProvider with fields named Provider and ServiceDate.
You may try this query for your ranking:
SELECT A.Provider, A.ServiceDate
, (SELECT Count(*) FROM tblProvider B WHERE B.Provider=A.Provider AND B.ServiceDate<=A.ServiceDate) As Cycle
FROM tblProvider AS A
ORDER BY 1,2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jeswel77]

PH..thank you thank you thank you!! It worked like a charm!!

Jessi