Solved

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

Posted on 2003-11-20
5
1,083 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
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 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