SQL statement to insert a value generated by a SELECT statement?

  • Thread starter Thread starter Rob Richardson
  • Start date Start date
R

Rob Richardson

Greetings!

I want to write an insert query for which one of the values will come from
another query. I have a query that generates a number to use for a new
order. The query is called "GetNewOrderNumber", and its single field is
named "NewOrderNumber". (I forget why I didn't make it an autonumber, but I
didn't.) I want to add a row to my Orders table. The simplest case is to
set the number field only. The following query gives me a wierd error
message:

INSERT INTO Orders (Order_number)
VALUES ((SELECT NewOrderNumber FROM GetNewOrderNumber));

The error message is "Reserved error (-3025); There is no message for this
error."

What is the correct SQL statement to do what I want to do?

Thanks very much!

Rob, once again being hit over the head with how little I truly know about
SQL.
 
Thanks! I had managed to stumble across it using the automatic query
creation tool, which I have never understood.

In my real-world case, the query ends up looking like:

PARAMETERS a string, b string, c string, d string;
INSERT INTO Orders
SELECT a, b, c, d, NewOrderNumber FROM GetNewOrderNumber

This looks weird to me, since a, b, c and d don't come from the query, but
it works...

Is it just me, or does anybody else think that the Access 2000 help system
absolutely sucks? There's no way to get a direct answer to anything! In
Access 97, I could go to the help files, type "INSERT query", and get good
descriptions with examples of writing INSERT queries. In Access 2000, no
matter what I do, I get a search instead of an index. So, if I type "INSERT
query", I get a list of all 274 articles that have the words "insert" and
"query" in them, and none of them give me the information I need!

Rob
 
Back
Top