Solved

SQL-Query: Select Max(...) in an INSERT Statement

Posted on 2003-11-20
5
1,104 Views
Last Modified: 2010-07-27
Hi Experts,

I wonder if there is any possiblity to do a query like that:
INSERT INTO Work_Orders (Order_No, User_ID, Correction_Level) VALUES(1,1,(SELECT MAX(Correction_Level) FROM T_Tool_Work_Orders WHERE Order_No=1)+1)

If I execute this in MS Access 2000 I get: "Reserved error (-3025); there is no message for this error."

I know how to solve that using 2 queries, but I wondered, if I could combine them...

Thanks for your help
Chris
0
Comment
Question by:Chrising
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 6

Accepted Solution

by:
lausz earned 400 total points
ID: 9790371
Try this

INSERT INTO Work_Orders (Order_No, User_ID, Correction_Level)
select 1,1, MAX(Correction_Level) +1
FROM T_Tool_Work_Orders
WHERE Order_No=1
0
 
LVL 1

Author Comment

by:Chrising
ID: 9790408
Ups, forgot to change that: The Second table should be work_orders, too...
But it worked! Thanks a lot!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 9790417
Or:

INSERT INTO Work_Orders (Order_No, User_ID, Correction_Level) VALUES(1, 1, DMAX("Correction_Level)","T_Tool_Work_Orders","Order_No=1)+1);

Nic;o)
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9790420
Hey Chrising!

  This look like a process you will need a function to produce
  The VALUES statement doesn't like anything but constants

 
  Function AppendWorkOrder() as Boolean
    dim tmpOrdNo as Variant
    dim ssql as string
       
    tmpOrdNo = nz(dmax("CorrectionLevel","T_Tool_Work_Orders"),0) + 1

    ssql = INSERT INTO Work_Orders (Order_No, User_ID, Correction_Level) VALUES(1,1," & tmpOrdNo & ");"
    currentdb.execute ssql, dbfailonerror
   
    AppendWorkOrder=True

  AppendWorkOrder_Exit:
    exit function

  AppendWorkOrder_Err:
    select case err
    case else
      msgbox Err.description
      resume   AppendWorkOrder_Exit:
      resume
    end select

  end function

regards
Jack
0
 
LVL 54

Expert Comment

by:nico5038
ID: 9790423
Oops typo, try:

INSERT INTO Work_Orders (Order_No, User_ID, Correction_Level) VALUES(1, 1, DMAX("Correction_Level)","T_Tool_Work_Orders","Order_No=1")+1);

Nic;o)
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

717 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