Running Count of records per emloyees in access query

I am creating an access database query and I need to add a field that will have a running count for each employee.

This is what I have
Gary  1-5-07
Gary  1-12-07
Gary  1-19-07
Jeff    1-12-07
Jeff    1-19-07
Bob   1-5-07
Bob   1-12-07
Bob   1-19-07

and this is what I need:

Gary  1-5-07    1
Gary  1-12-07  2
Gary  1-19-07  3
Jeff    1-12-07  1
Jeff    1-19-07  2
Bob   1-5-07    1
Bob   1-12-07  2
Bob   1-19-07  3

I can't figure out what to do here...

Thanks a lot,
Bonnie
LVL 1
Bonnie_KAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerryb30Commented:
Would any user have two entries for the same date?
0
GRayLCommented:
SELECT a.Name, a.MyDate, (Select Count(b.Name) FROM myTable AS b WHERE b.Name=a.Name and b.MyDate<=a.MyDate) as Ctr FROM myTable AS a;
0
Bonnie_KAuthor Commented:
Jerryb,

No, the dates are distinct per employee.

I am going to try GrayL's idea now
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dbeneitCommented:
GRayL solution is ok, but with big tables, are slow because he use fields of external query into subquery.

If yours tables are bigs try this
SELECT a.Name, a.MyDate, count(b.Name) from FROM myTable AS a inner join myTable AS b on
a.Name = b.Name and b.MyDate<=a.MyDate group by a.Name, a.MyDate;
It is the same but converting to join isntead subquery.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
Do you mean 'Group By' or 'Order By'?
0
dbeneitCommented:
Grayl,
group by , becouse we need only 1 row for each row of table a.
0
Jeffrey CoachmanMIS LiasonCommented:
Bonnie_K:,

Why not do this in a Report?

Report TextBoxes have a "Running Total" property, that you can set to Over Group or Over All.

So if you grouped your Report by Name you would select "Over Group"
https://filedb.experts-exchange.com/incoming/ee-stuff/2979-Access--Ruuning-GroupTotals.zip

Is this what you wanted?

Jeff Coachman
0
Bonnie_KAuthor Commented:
Thank you very much,

Bonnie
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.