Solved

Running Count of records per emloyees in access query

Posted on 2007-03-23
8
1,408 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
ID: 18783297
Would any user have two entries for the same date?
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 200 total points
ID: 18783304
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
ID: 18783386
Jerryb,

No, the dates are distinct per employee.

I am going to try GrayL's idea now
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Accepted Solution

by:
dbeneit earned 300 total points
ID: 18786388
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
 
LVL 44

Expert Comment

by:GRayL
ID: 18786822
Do you mean 'Group By' or 'Order By'?
0
 
LVL 9

Expert Comment

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

Expert Comment

by:Jeffrey Coachman
ID: 18790910
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
ID: 18793345
Thank you very much,

Bonnie
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

735 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