Solved

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vba DCount with 2 criteria 3 33
SQL profiler equivalent in MS-Access 3 40
MS Access Order Smallest to Biggest Query Help 13 39
Export Query data to excel file 14 26
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

943 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

10 Experts available now in Live!

Get 1:1 Help Now