Trying to increment a value in an SQL SELECT statement. Started with the following:
Which allows me to do:
Table1 before Query
Table1 after Query
How I'd like it to look like after the Query
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.
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
PROJECT | SET_NO |
---|---|
5670797.345234.235 | 13 |
5670797.345234.235 | 14 |
Table1 after Query
PROJECT | SET_NO |
---|---|
5670797.345234.235 | 13 |
5670797.345234.235 | 14 |
5670797.345234.235 | 15 |
5670797.345234.235 | 15 |
5670797.345234.235 | 15 |
How I'd like it to look like after the Query
PROJECT | SET_NO |
---|---|
5670797.345234.235 | 13 |
5670797.345234.235 | 14 |
5670797.345234.235 | 15 |
5670797.345234.235 | 16 |
5670797.345234.235 | 17 |
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.