?
Solved

Access query to provide a sequence number

Posted on 2014-03-03
4
Medium Priority
?
1,782 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
[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
  • 2
  • 2
4 Comments
 
LVL 120

Accepted Solution

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

Expert Comment

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

Access query
0
 

Author Closing Comment

by:Alphacircuits
ID: 39910400
Thanks!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…
Suggested Courses

800 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