Sequence Number Records In Query or Table

I have a table and or query with a list of bill #'s these  and transaction dates
1. The bill # will duplicate with every transdate that is different
2. Every Tranaction date is not unique there are several other bill #'s with the same transaction date.
I need to have either VB or sql that will place a numbered sequence in the query or table to count the number of time the Bill # appears and place it in the table or query in the same row with the bill# it is counting. I have looked at several fuctions to complete this but have found nothing that complete what I am asking for, Dcount kind of worked but it did not count the records grouped but instead counted the grouped records. If anyone has any ideas please let me know, basically I need to do a running sum on a query or table to show the count sequence of grouped records.


This is what it looks like now:
K8018JD      3/10/2008
K8018JD      3/24/2008
K8018JD      4/01/2008
K8018JD      6/10/2008
K8018JD      9/10/2008
K8018JD      11/01/2008
K80705G      3/10/2008
K80705G      3/24/2008
K80705G      4/01/2008
K80705G      6/10/2008
 
This is what I need it to look like:
1  K8018JD      3/10/2008 
2  K8018JD      3/24/2008
3  K8018JD      4/01/2008
4  K8018JD      6/10/2008
5  K8018JD      9/10/2008
6  K8018JD      11/01/2008
1  K80705G      3/10/2008
2  K80705G      3/24/2008
3  K80705G      4/01/2008
4  K80705G      6/10/2008

Open in new window

nelsonjeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
copy and paste this

SELECT (select count(*) from [Weekly Comments] as WC where bill=[Weekly Comments].bill and [TRANS DATE]<=[Weekly Comments].[TRANS DATE]) AS sequence, [Weekly Comments].bill, [Weekly Comments].[TRANS DATE]
FROM [Weekly Comments]
ORDER BY [Weekly Comments].bill, [Weekly Comments].[TRANS DATE];
0
 
Patrick MatthewsCommented:
SELECT t1.ID, t1.TranDate,
    (SELECT Count(t2.ID) FROM SomeTable t2 WHERE t2.ID = t1.ID And t2.TranDate <= t1.TranDate) AS BillNum
FROM SomeTable t1
ORDER BY t1.ID, t1.TranDate
0
 
Rey Obrero (Capricorn1)Commented:
SELECT (select count(*) from A as B where billno=A.billno and [date]<=A.[date]) AS sequence, A.billno, A.date
FROM A
ORDER BY A.billno, A.date;
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
nelsonjeAuthor Commented:
Where would I put this information into access?? Grid in Query or SQL macro??
0
 
Patrick MatthewsCommented:
nelsonje,

Go to the SQL view and paste it there, and be sure to update to match your actual table and column names.

Regards,

Patrick
0
 
nelsonjeAuthor Commented:
I can not get this SQL to work at all, I dont know what I am doing wrong,
Here is the SQL you gave me:
SELECT (select count(*) from A as B where billno=A.billno and [date]<=A.[date]) AS sequence, A.billno, A.date
FROM A
ORDER BY A.billno, A.date;

Here is what my Current QUERY SQL looks like:
SELECT [Weekly Comments].BILL, [Weekly Comments].[TRANS DATE]
FROM [Weekly Comments];

What am I doing wrong ???
0
 
nelsonjeAuthor Commented:
Oh my god thank you soooo soooo much I dont know how many different ways I have tried that, you are the man!
0
 
nelsonjeAuthor Commented:
Did a great job, excellent all the why thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.