Solved

Sequence Number Records In Query or Table

Posted on 2008-10-14
8
1,297 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:nelsonje
  • 4
  • 2
  • 2
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22714230
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22714271
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
 

Author Comment

by:nelsonje
ID: 22714799
Where would I put this information into access?? Grid in Query or SQL macro??
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22714831
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:nelsonje
ID: 22740932
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 22741025
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
 

Author Comment

by:nelsonje
ID: 22741082
Oh my god thank you soooo soooo much I dont know how many different ways I have tried that, you are the man!
0
 

Author Closing Comment

by:nelsonje
ID: 31506022
Did a great job, excellent all the why thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

760 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

15 Experts available now in Live!

Get 1:1 Help Now