Adding sequencial line number to an insert query (key field)

Posted on 2009-12-21
Last Modified: 2012-06-27
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];

Question by:faunnab
    LVL 38

    Expert Comment

    see this link for an easy way:

    Author Comment

    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?

    LVL 9

    Expert Comment

    by:Shahid Thaika
    Can't you simply use an autonumber field type?
    LVL 38

    Expert Comment

    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])
    LVL 30

    Expert Comment

    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"
    LVL 38

    Accepted Solution

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now