SQL Syntax for an update query

[Joe]

Hi

The following query is throwing the error message that

"I have written a query that can return more than one field without
using the EXISTS reserve word in the queries main FROM clause. Revise
the Select statement of the subqueries FROM clause to request only one
field."

My query is looking to compare 2 values from 2 different tables and
update one table with the corresponding value.

Can anyone help? I am going blind. Thanks!

UPDATE [Job Inventory]
SET [Job Inventory].Quantity_Inve = (SELECT Physical_Job_Number_To,
Part_Number FROM [Temporary Transactions]
WHERE [Job Inventory].SF_Job_Number=[Temporary Transactions]!
Physical_Job_Number_To And [Job Inventory].Part_Number = [Temporary
Transactions]!Part_Number)

WHERE EXISTS (SELECT Physical_Job_Number_To, Part_Number FROM
[Temporary Transactions]
WHERE [Job Inventory].SF_Job_Number=[Temporary Transactions]!
Physical_Job_Number_To And [Job Inventory].Part_Number = [Temporary
Transactions]!Part_Number);

 

[vanderghast]

It seems that it is your subquery that returns more than one record.

SELECT Physical_Job_Number_To,
Part_Number
FROM [Temporary Transactions]
WHERE [Job Inventory].SF_Job_Number=[Temporary
Transactions].Physical_Job_Number_To
AND [Job Inventory].Part_Number = [Temporary Transactions].Part_Number


which is your subquery, probably return MORE than one record, for a given
SF_job_number and a given Part_number. It is an error to try to store all
these records... into one field. You have to define which of these multiple
records you really want, by adding the appropriate criteria.



Vanderghast, Access MVP

 

[John Spencer]

PERHAPS you are looking for something like the following query.

UPDATE [Job Inventory] AS J INNER JOIN [Temporary Transactions] As TT
ON J.SF_Job_Number = TT.Physical_Job_Number_To
AND J.Part_Number = TT.Part_Number
SET [Job Inventory].Quantity_Inve = ???? SOME VALUE GOES HERE ????

Can you tell us what you are attempting to do in words. That is something
like - I want to get the quantity from Temporary Transactions and subtract
that number from the quantity in Job Inventory. By the way, that is probably
not a good idea as it can too easily introduce errors into your data.

An exists clause should return only one field or all values either * or a
specific field.

The Subquery you are using where you want to set Quantity_INVE = to something
lists two fields to set the one field equal to and there is the possibility
that your sub-query could return multiple rows. Either of those will make the
query fail. You may be forced to use one of the VBA aggregate functions
(DLookup, DMax, DMin, etc. )to get the value you want.

UPDATE [Job Inventory]
SET [Job Inventory].Quantity_Inve =
DLookup("SomeField","[Temporary Transactions]",<<<Some where clause
equivalent>>> )

WHERE EXISTS (SELECT *
FROM [Temporary Transactions]
WHERE [Job Inventory].SF_Job_Number=
[Temporary Transactions]!Physical_Job_Number_To
And [Job Inventory].Part_Number = [Temporary Transactions]!Part_Number);

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County