Solved

Running Count of records per emloyees in access query

Posted on 2007-03-23
8
1,367 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:Bonnie_K
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
Would any user have two entries for the same date?
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 200 total points
Comment Utility
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
 

Author Comment

by:Bonnie_K
Comment Utility
Jerryb,

No, the dates are distinct per employee.

I am going to try GrayL's idea now
0
 
LVL 9

Accepted Solution

by:
dbeneit earned 300 total points
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Do you mean 'Group By' or 'Order By'?
0
 
LVL 9

Expert Comment

by:dbeneit
Comment Utility
Grayl,
group by , becouse we need only 1 row for each row of table a.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:Bonnie_K
Comment Utility
Thank you very much,

Bonnie
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now