Adding serial number

[Gwilliams57]

I need to change my ERP system to a serialized inventory system, i have list of products and their total quantity. all i am trying to do is take a product and if the total quantity is 10, create 10 records so i can assign a unique number to each product.

Example of data i have:

2018 Catalog

10

<tbody>
</tbody>

can i do this in access, i currently have 2075 products with a 125k total.

i could use some help

thank you


EDIT: Same question posted in Excel forum here: https://www.mrexcel.com/forum/excel-questions/1045372-adding-serial-number.html

 

[xenou]

Hi,
this is a little ugly but I guess will work:


** please make a two copies of the table. One will be used to copy "from", the other will be used to copy "to". This query is destructive (it will alter the original data) so it should not be used on the original table.



Sample Data Used:

Inventory table is called Table8 (we do not use this table!):
(ID autonumber, Product text, Qty number)

Copy of Inventory table is called CopyOfTable_1 (it is as stated a copy of the above):
(ID autonumber, Product text, Qty number)

Another Copy of Inventory table is called CopyOfTable_2 (it is empty initially):
(ID autonumber, Product text, Qty number)

(I would have used your table name, field names, and data types, but you did not provide this information ).



Code to run from a standard module:

Sub DoTheThing()
    
Dim NumberOfRuns As Long
Dim i As Long

    NumberOfRuns = DMax("Qty", "CopyOfTable_1")
    For i = 1 To NumberOfRuns
        DoCmd.SetWarnings False
        DoCmd.RunSQL "insert into CopyOfTable_2 select Product from CopyOfTable_1 where Qty > 0"
        DoCmd.RunSQL "Update CopyOfTable_1 set Qty = (Qty - 1) where Qty > 0"
        DoCmd.SetWarnings True
    Next i

End Sub

Results before running procedure (showing data from CopyOfTable_1):
----------------------
| ID | Product | Qty |
----------------------
|  1 | A       |   5 |
|  2 | B       |   4 |
|  3 | C       |   1 |
----------------------

Results after running procedure (showing data from CopyOfTable_2):
----------------
| ID | Product |
----------------
| 23 | A       |
| 21 | A       |
| 19 | A       |
| 17 | A       |
| 14 | A       |
| 22 | B       |
| 20 | B       |
| 18 | B       |
| 15 | B       |
| 16 | C       |
----------------


Note that my ID fields are irrelevant but are there so that the table have a primary key even if its arbitrary. The CopyOfTable1 table will have 0 for all quantities when this is completed, which is why you must use a copy for running this code - you do not want to change the original data you have.

As always, backup your data first.

 

[Gwilliams57]

that work nicely, thank you