Solved

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

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

11 Experts available now in Live!

Get 1:1 Help Now