Solved

Creating an item sequence by account number

Posted on 2013-11-27
6
300 Views
Last Modified: 2013-11-27
I have a table that contains the detail records of an account. Each account can have up to 99 pieces of equipment associated with it. The table contain 125,000 records. I need to create a sequence number of reach item within the account. If I have 83 items and my account number is 123456, then I need to number the items from 1 to 83. If the next account, 123457, has 28 items, I need to number them 1 to 28. And so on. . .

What is the best method to create such sequencing in an existing data table?
0
Comment
Question by:ergenbgr
  • 4
  • 2
6 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Are you doing this to display item number/sequence on forms and reports?

I'd do this by query rather than storing the item number in the table.

If you have an autonumber field in your table (assuming here that it is called ID), you can query the data with a sequence number like this:


Select a.*, (SELECT Count(b.ID) FROM YourTable b WHERE b.ID <= a.ID AND b.[AccountNumber] = a.[AccountNumber]) AS [SequenceByAccountNumber]
FROM YourTable a
ORDER BY a.[AccountNumber],  a.ID

Open in new window



You can also convert that to an UPDATE query by right-clicking the query window, selecting Query Type --> Update Query if you really need to store the sequence... but I would simply get it in a query like this on the fly, and display it when needed.
0
 

Author Comment

by:ergenbgr
Comment Utility
Sounds like a great way to do it. I will try it an see how it works. One additional caveat is that I need to start the sequence at 900. I presume I can just factor that in the query by adding 900 to the result. Would you agree?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Exactly...  and also change the <= to simply <.  My example above starts at 1 (so adding 900 would be 901 as a starting point).  If you use  < for the comparison, the count will start at 0 (900 if you add 900)

Try this:

Select a.*, (SELECT Count(b.ID) + 900 FROM YourTable b WHERE b.ID < a.ID AND b.[AccountNumber] = a.[AccountNumber]) AS [SequenceByAccountNumber]
FROM YourTable a
ORDER BY a.[AccountNumber],  a.ID

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:ergenbgr
Comment Utility
Thanks mbizuo! Great solution.
0
 

Author Comment

by:ergenbgr
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for ergenbgr's comment #a39680796

for the following reason:

Simple, yet effective, solution.
0
 

Author Closing Comment

by:ergenbgr
Comment Utility
Simple, but effective. My kind of solution.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

17 Experts available now in Live!

Get 1:1 Help Now