• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 712
  • Last Modified:

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];

1 Solution
see this link for an easy way:
faunnabAuthor Commented:
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?

Shahid ThaikaSole ProprietorCommented:
Can't you simply use an autonumber field type?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

As shown in the link, just add the Counter as an alias column:
             DCount("id","TestTable","id <= " & [id]) AS Counter,                 <<<<sql view

if you are using the query grid, you would put the alias in the field row as shown below:
                Counter: DCount("id","TestTable","id <= " & [id])
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"
If you were asking how to do the insert once you have generated the sequential numbers, that is also contained in the downloadable database available from the link:  
           Note: Look at qryNumericalSequence then qryAppendCounters in download database

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now