mwilson87a avatar image
mwilson87a asked

How to properly assign sequential numbers to parts list

I have a table in a database which represents parts that must be ordered for a project. There are MULTIPLE instances of some part numbers and they obviously have a manufacturing company field. I'm adding an ItemNo_ID field that will initially be blank. It will be filled in, sequentially, among the part numbers with the following guidelines. 1) the records will be sorted by the manufacturing company field. 2) ItemNo's will always start with a value of 1 and increase with each different part number It must first check to see if the number has been used in the table. If So, it much check to see if there are any NEW records using the same part, and then assign the number to it. (We often add records to these tables, reprocessing is a must) If the number has not been used, it will find the next part in the list that does not have an ItemNo and assign the number to it. It must then check for all other records with the same part number. ********** ALL IDENTICAL PART NUMBERS MUST GET THE SAME ItemNo *********** There MAY be another issue... looks like the table the part numbers can not have the actual ItemNo in it, the ItemNo will be stored in another table... there is a relational reference ID for this though. Any suggestions? One thought I had was to just create a joined temp table with just the fields needed, sorted of course, then run through and do the numbering check, then, run through the relation reference ID's and and copy the new ItemNo field to the original target table. SQL level is pretty low, understand the basics of what's going on.... this is a tool I'm trying to create to apply to the database of an existing application. Simple, but can be tricky. :) Any help/suggestions is greatly appreciated. Marc
ms sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
It may be better to give some examples of the data and example output - then we can understand the problem a little better.
1 Like 1 ·

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.