update query help

[bigwillno2 via AccessMonster.com]

Hello, i am trying to run this query bellow and it says that its too complex.
can anyone help please! little of background on the query. i am trying to
update current data in table with data in a query that's made of other
queries. i tried appending the data from the same query to the same table and
it works, but whne i try to update only, it seems to give me a "Query too
Complex" error. can anyone spot my error.

UPDATE qrAllItemsCountFXFab LEFT JOIN tblEditablePO ON qrAllItemsCountFXFab.
OrderNo = tblEditablePO.OrderNo SET tblEditablePO.Vendor =
[qrAllItemsCountFXFab].[Vendor], tblEditablePO.OrderNo =
[qrAllItemsCountFXFab].[OrderNo], tblEditablePO.Type = [qrAllItemsCountFXFab].
[Type], tblEditablePO.qFill = [qrAllItemsCountFXFab].[qFill], tblEditablePO.
OrderQty = [qrAllItemsCountFXFab].[OrderQty], tblEditablePO.Cost = (SELECT
Sum([QfillCost]*[OrderQty]) FROM qrAllItems WHERE [OrderNo] =
[qrAllItemsCountFXFab.[OrderNo]);

 

[John W. Vinson]

Hello, i am trying to run this query bellow and it says that its too complex.
can anyone help please! little of background on the query. i am trying to
update current data in table with data in a query that's made of other
queries. i tried appending the data from the same query to the same table and
it works, but whne i try to update only, it seems to give me a "Query too
Complex" error. can anyone spot my error.

Well, let's parse this out so it's more readable:

UPDATE qrAllItemsCountFXFab
LEFT JOIN tblEditablePO
ON qrAllItemsCountFXFab.OrderNo = tblEditablePO.OrderNo
SET tblEditablePO.Vendor = [qrAllItemsCountFXFab].[Vendor],
tblEditablePO.OrderNo = [qrAllItemsCountFXFab].[OrderNo],
tblEditablePO.Type = [qrAllItemsCountFXFab].[Type],
tblEditablePO.qFill = [qrAllItemsCountFXFab].[qFill],
tblEditablePO.OrderQty = [qrAllItemsCountFXFab].[OrderQty],
tblEditablePO.Cost =
(SELECT Sum([QfillCost]*[OrderQty])
FROM qrAllItems
WHERE [OrderNo] = [qrAllItemsCountFXFab.[OrderNo]);

There's a missing close bracket after qrAllItemsCountFXFab on the last line.

The biggest problem - even if you can get around the QTC error - is that no
query including a Totals operation (like your sum subquery) is going to be
updateable, even if - as in this case - it logically should be updateble. You
may need to use the DSum() function instead of calculating the sum, or - even
more likely - don't store the sum AT ALL, since it can be recalculated as
needed.

Note also that updating tblEditablePO.OrderNo to OrderNo is pointless - it's
already there, since that's the join field. Are you sure you don't want an
Append query to insert a new record, rather than an Update query to update
existing records?

John W. Vinson [MVP]

 

[Jamie Collins]

The biggest problem - even if you can get around the QTC error - is that no
query including a Totals operation (like your sum subquery) is going to be
updateable, even if - as in this case - it logically should be updateble.

See:

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Produc

Jamie.

--

 

[bigwillno2 via AccessMonster.com]

Got it working perfect...thanks guys....great help