?
Solved

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

Posted on 2003-11-20
5
Medium Priority
?
1,110 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 1600 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

765 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