UPDATE Statement to Add Values Incrementally

[mpkimler]

Hello Everyone,

I have an Access 2016 DB with a table called Parts with the following structure. ID is the Primary Key field.

ID	PART_DESCRIPTION	PART_NUMBER	PART_CATEGORY
1	PART 1	1001	CATEGORY 1
2	PART 2		CATEGORY 1
3	PART 3		CATEGORY 2
4	PART 4		CATEGORY 3
5	PART 5		CATEGORY 4

This table currently has several thousand records. The PART_DESCRIPTION and PART_CATEGORY values already exist. No PART_NUMBER values currently exist. I want the database to generate them as they are smart numbers that will mean something. I have been able to generate the part number using VBA and a recordset/do loop but it is slow and I would prefer to do this in SQL if possible with an UPDATE statement. I know that some people would suggest that I just use the Primary Key ID to dynamically generate the PART_NUMBER but I have good reason not to do that. What i'm really trying to do is just generate the seed numbers. I will do more with it after the update statement, using the Category to make the final smart number. That is why I really need this type of solution.



So my question is this: If the first part number exists (1001) is there a way to use an UPDATE statement inside VBA that will find the last part number used (DMAX function?) and then increment it for every existing record in the table? If the table above were the starting table, the table below would be the final output of the query. It would find that last number used is 1001 and then start updating all the NULL part numbers by sequentially adding 1 to the last part number.

ID	PART_DESCRIPTION	PART_NUMBER	PART_CATEGORY
1	PART 1	1001	CATEGORY 1
2	PART 2	1002	CATEGORY 1
3	PART 3	1003	CATEGORY 2
4	PART 4	1004	CATEGORY 3
5	PART 5	1005	CATEGORY 4

I have tried the below but it doesnt work. I now understand why it doesnt work, but i'm stumped on how to execute a SQL statement that does something different on a per record basis. In this case, incrementing by 1.

Dim strSQL As String
strSQL = "UPDATE PARTS SET PART_NUMBER = " & DMax("PART_NUMBER", "PARTS") + 1
CurrentDb.Execute strSQL

Hope my question is clear and that someone can help me out!

[pbaldy]

I can't think of a way offhand with just SQL. Why not just add 1000 to the ID field?

[davegri]

I think you'll need to create a recordset and loop thru the table, updating the Part_number where it is null

[pbaldy]

davegri, you didn't read this:

I have been able to generate the part number using VBA and a recordset/do loop but it is slow and I would prefer to do this in SQL if possible with an UPDATE statement.

and I didn't read this:

I know that some people would suggest that I just use the Primary Key ID to dynamically generate the PART_NUMBER but I have good reason not to do that.

It now occurs to me you might be able to use a DCount() in the update SQL, counting ID's less than or equal to the current ID and add 1000 to that, but that's untested and might be fairly slow itself.

[mpkimler]

I appreciate all the responses. The reason I thought it might be possible to do this with SQL was this link: https://stackoverflow.com/questions/...menting-values

I'm not a professional programmer so i'm probably just confused and I think that was a different platform from Access.

Iterating through the records is still my current working solution. Using an UPDATE statement including the ID would also work per the recommendation above, however it doesn't achieve quite what i'm looking for. I could explain why that isnt what i'm looking for but it would be pretty long winded lol. I'm still ok with my current solution if there isn't a slick way to do this with SQL. Thanks again all.

[davegri]

You could restrict the number of records to iterate through by using a query that includes only records with null Part_Number as the recordset.
You could also test speed of two methods:
1. Doing the entire update via the recordset with .update in the iteration loop.
2. Having the code in the iteration loop fire off a query for each update.

[CJ_London]

you could try a bit of VBA

put this in a general module

Function RowNumber(Optional r As Variant = -1) As Variant
Static X As Long


    If r = -1 Then
        X = 0
    Else
        X = X + 1
    End If
    RowNumber = X
End Function

and in sql something like this

UPDATE parts SET part_number = 10000+rownumber([ID])
WHERE (((RowNumber())=False))

This does assume your partnumber field is unpopulated - otherwise it will be over written

You can probably change the order of assignment if required as well by sorting on a field.

Edit: This does not work - change the 10000 to 20000 in the sql
If you stop/start then in the function change X=0 to say X=20000 to give you a different number range - or you could include it as another parameter to the function