Solved: number visits

[tkaplan]

I have a table that has visit dates and center numbers. the combination of the 2 fields is the primary key.

i need to write a query that numbers the visits in order so that i can query on all of the 1st visits, or all of the 2nd visits, etc. there is an unlimited number of visits possible.

so in my table if i have:
DATE.......CENTER
1/1/05.....centerA
2/4/05.....centerB
6/5/05.....centerA
4/3/05.....centerA

my query should produce:
DATE.......CENTER....VISIT
1/1/05.....centerA.....1
2/4/05.....centerB.....1
6/5/05.....centerA.....3
4/3/05.....centerA.....2

is there a function that already exists, or can anyone assist me in writing such a function???

Thank you.

[geekgirlau]

You should be able to build this into a totals query. In your query design screen, click on the button that looks like the "Sum" button in Excel. Then you add your fields: Date with the function "Group by", Center with the function "Group by", and Center again with the function "Count".

[Cosmos75]

tkaplan,

You can use a subquery to create a running count.

SELECT a.strCenter, a.dtDate,
(Select Count(*) from [tblData]
where [strCenter] = a.strCenter
and [dtDate] <= a.dtDate) AS RunningCount
FROM tblData AS a
ORDER BY a.strCenter, a.dtDate;

I've got an ariticle that I have been working on that explains it. Wasn't quite ready to add it to my website, but I went ahead and added it anyway.
- http://accessdb.info/index.php?option=com_content&task=view&id=74&Itemid=40

Maybe you can tell me if I did a good job or not!

I've got a sample but am not ready to add that today.

[tkaplan]

This works perfect. Thank you cosmos:)

geekgirl - thank you for taking the time to respond. I was looking for the running sum solution though.