Incrementing A Value For Every Return In Select Statement (1 Viewer)

rexcision

New member
Local time
Today, 08:44
Joined
May 10, 2021
Messages
7
Trying to increment a value in an SQL SELECT statement. Started with the following:

SQL:
INSERT INTO Table1 (PROJECT, SET_NO) SELECT tblImport.[Project Number], (SELECT MAX(SET_NO) + 1 FROM Table1 INNER JOIN tblImport ON Table1.PROJECT = tblImport.[Project Number])
FROM tblImport
INNER JOIN tblSubImport ON tblImport.[Submission Id] = tblSubImport.[Submission Id];

Which allows me to do:

Table1 before Query
PROJECTSET_NO
5670797.345234.235​
13
5670797.345234.23514

Table1 after Query
PROJECTSET_NO
5670797.345234.23513
5670797.345234.23514
5670797.345234.23515
5670797.345234.23515
5670797.345234.23515

How I'd like it to look like after the Query
PROJECTSET_NO
5670797.345234.23513
5670797.345234.23514
5670797.345234.23515
5670797.345234.23516
5670797.345234.23517

tblSubImport is a table that has field sample information and is linked to tblImport through their [Submission Id] column. tblImport then has other info for the project as well as the actual project number that can be used to relate to Table1.PROJECT. There is also a chance that tblImport.[Project Number] wont be in Table1.PROJECT yet in case that changes anything.

So each field sample I would like to have their own "SET_NO" which are just incrementally labeled for samples within each project and I'm not entirely sure how to do this as the tblImport and tblSubImport tables can have information on multiple projects at the same time. I have seen some examples using ROW_NUMBER() but I haven't quite understood how I can transfer the examples for my needs.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:44
Joined
Oct 29, 2018
Messages
15,542
Hi. Welcome to AWF!

Since you mentioned ROW_NUMBER(), I just had to ask. Is you data stored in SQL Server or Access?
 

plog

Banishment Pending
Local time
Today, 06:44
Joined
May 11, 2011
Messages
10,479
You can't do this with just SQL unless you have a field to uniquely identify data in tblImport. Perhaps a date/time value, or an incremented key. Perhaps a combination of fields. But without that you cannot do it just with SQL.

If you have that you can build a "running total query" (search the forum for that term) which will allow you to numerate your records in the manner you want and then INSERT them into Table1.

If you do not, then you need to use VBA. You would refashion your existing query and run for just 1 record at a time for however many times you need. That way it INSERTS a record with SET_NO=15, then the next time it runs it sees that 15 is the max number so it inserts the next record with SET_NO=16, then the next with SET_NO=17, etc. until it INSERTS all the records.
 

rexcision

New member
Local time
Today, 08:44
Joined
May 10, 2021
Messages
7
Hi. Welcome to AWF!

Since you mentioned ROW_NUMBER(), I just had to ask. Is you data stored in SQL Server or Access?
Access. I am pretty new to SQL in general so based on your question I'm assuming ROW_NUMBER() is not an Access function?

You can't do this with just SQL unless you have a field to uniquely identify data in tblImport. Perhaps a date/time value, or an incremented key. Perhaps a combination of fields. But without that you cannot do it just with SQL.

If you have that you can build a "running total query" (search the forum for that term) which will allow you to numerate your records in the manner you want and then INSERT them into Table1.

If you do not, then you need to use VBA. You would refashion your existing query and run for just 1 record at a time for however many times you need. That way it INSERTS a record with SET_NO=15, then the next time it runs it sees that 15 is the max number so it inserts the next record with SET_NO=16, then the next with SET_NO=17, etc. until it INSERTS all the records.
tblImport has a date field and tblSubImport also has a [Sampled] column that has a time value which ideally I'd like it Ordered By but not super necessary. I'll look into the "running total query" though thanks!
 

Users who are viewing this thread

Top Bottom