Solved

Sequence Number Records In Query or Table

Posted on 2008-10-14
8
1,305 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 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 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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
 

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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 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