Solved

Access query to provide a sequence number

Posted on 2014-03-03
4
1,412 Views
Last Modified: 2014-03-06
How do I add a field to a query that is a numerical sequence (1,2,3...) that resets back to 1 each time an item changes

P/N    Seq
1         1
1         2
1         3
2         1
2         2
3         1
0
Comment
Question by:Alphacircuits
  • 2
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39901956
you need a unique id for each record. [ID] in the query could be an autonumber field

select Yourtable.[P/N],(select count(*) from Yourtable as T where T.[P/N]=Yourtable.[P/N] and T.id>=Yourtable.ID) as Seq
from Yourtable
order by Yourtable.[P/N];
0
 

Author Comment

by:Alphacircuits
ID: 39903968
Ok, I have I added the ID to the table, but I am not sure where to insert the select statement you provided?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39904378
In your original post, you were asking for

Access query
0
 

Author Closing Comment

by:Alphacircuits
ID: 39910400
Thanks!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 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

24 Experts available now in Live!

Get 1:1 Help Now