?
Solved

Sequence Number Records In Query or Table

Posted on 2008-10-14
8
Medium Priority
?
1,316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 93

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 93

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
 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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