Adding sequencial line number to an insert query (key field)
I have a complicated query that is adding rows to my database. Normally, this is done from the front end and every time a row is added, the system automcatically adds a line number (Sales Order and Line Number are key fields)
How can I add line number (sequencially numbered from 1) in the following query. The field is called [Line Number]
I cannot update the table without the key field.
INSERT INTO tblCustomerOrderEntryTest ( [Part Number], Quantity, [Ship Date], [Sales Order] )
SELECT [qry Update Customer Order Entry with BDMR Forecast].[Part Number], [qry Update Customer Order Entry with BDMR Forecast].Quantity, [qry Update Customer Order Entry with BDMR Forecast].ShipDate, [qry Update Customer Order Entry with BDMR Forecast].SalesOrder
FROM [qry Update Customer Order Entry with BDMR Forecast];
TIA
How can I add line number (sequencially numbered from 1) in the following query. The field is called [Line Number]
I cannot update the table without the key field.
INSERT INTO tblCustomerOrderEntryTest ( [Part Number], Quantity, [Ship Date], [Sales Order] )
SELECT [qry Update Customer Order Entry with BDMR Forecast].[Part Number], [qry Update Customer Order Entry with BDMR Forecast].Quantity, [qry Update Customer Order Entry with BDMR Forecast].ShipDate, [qry Update Customer Order Entry with BDMR Forecast].SalesOrder
FROM [qry Update Customer Order Entry with BDMR Forecast];
TIA
ASKER
ok, I think that will work.
How would I add that to my above query, so that it will insert that record into my table?
How would I add that to my above query, so that it will insert that record into my table?
Can't you simply use an autonumber field type?
As shown in the link, just add the Counter as an alias column:
DCount("id","TestTable","i
if you are using the query grid, you would put the alias in the field row as shown below:
Counter: DCount("id","TestTable","i
DCount("id","TestTable","i
if you are using the query grid, you would put the alias in the field row as shown below:
Counter: DCount("id","TestTable","i
If not interested in modifying the table, as eeshahidt suggested, try this for table t_1, field score
DoCmd.RunSQL "insert into t_1 (score) select (max(score)+1) from t_1"
DoCmd.RunSQL "insert into t_1 (score) select (max(score)+1) from t_1"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.vb123.com/toolshed/07_access/countercolumn.htm