Increment non unique field during SQL insert

Question

I'm not sure how to word this cause I am a little confused at the moment, so bare with me while I attempt to explain, I have a table with the following fields:

OrderLineID, OrderID, OrderLine, and a few other unimportant ones.

OrderLineID is the primary key and is always unique(which isn't a problem), OrderID is a foreign key that isn't unique(also not a problem), and OrderLine is a value that is not unique in the table, but should be unique for any OrderIDs that are the same...so if that didn't make sense, perhaps a picture

OrderLineID, OrderID, OrderLine
     1          1         1
     2          1         2
     3          1         3
     4          2         1
     5          2         2

For all OrderIDs there is a unique OrderLine. I am trying to create an insert statement that gets the max OrderLine value for a specific OrderId so I can increment it, but it's not working so well and I could use a little help. What I have right now is below, I build the sql statement in a program and replace OrderID # with an actual value. I am pretty sure the problem is with the nested select statement, and incrementing the result, but I can't find any examples that do this since my google skills are weak apparently....

INSERT INTO tblOrderLine (OrderID, OrderLine) VALUES 
       (<OrderID #>, (SELECT MAX(OrderLine) 
                       FROM tblOrderLine WHERE orderID = <same OrderID #>)+1)

any help would be nice.

Answer 1

Are you getting some type of error? Your SQL code seems to work fine for me.

Answer 2

Don't use a combination of VALUES and SELECT. Try:

INSERT INTO tblOrderLine (OrderID, OrderLine)
SELECT <OrderID #>, MAX(OrderLine)  
FROM tblOrderLine 
WHERE orderID = <same OrderID #>)+1
;

Answer 3

Adding a scalar to the result of a query isn't generally kosher. Try moving the "+1":

INSERT INTO tblOrderLine (OrderID, OrderLine) VALUES
(
<OrderID #>,
(SELECT MAX(OrderLine)+1 FROM tblOrderLine WHERE orderID = <OrderID #>)
)

Answer 4

This statement works in Access 2003. You would have to substitute your OrderID value in the WHERE clause.

INSERT INTO tblOrderLine (OrderID, OrderLine)
SELECT
    s.OrderID, 
    s.MaxOrderLine + 1 AS NewOrderLine
FROM (
    SELECT
        OrderID, 
        Max(OrderLine) AS MaxOrderLine
    FROM
        tblOrderLine
    WHERE
        OrderID=1
    GROUP BY
        OrderID
    ) AS s;

I read the others' misgivings, and will leave the wisdom of this approach to you. It could get more interesting if you can have multiple users updating tblOrderLine at the same time.