Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sequence Number Records In Query or Table

Posted on 2008-10-14
8
Medium Priority
?
1,324 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

610 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