Update Query

  • Thread starter Thread starter andrew
  • Start date Start date
A

andrew

I need to update values in a table based on aggregate
values from other records. I have an update query that
nearly does what I want:-

UPDATE tblEquipment AS a
SET a.curCostPrice = [THIS IS THE PROBLEM BIT]
WHERE a.lngEquipmentID IN
(SELECT d.lngEquipmentID
FROM tblEquipment AS c INNER JOIN tblEquipmentDetails
AS d ON c.lngEquipmentID = d.lngEquipmentID2
WHERE d.ysnDataType=Yes
AND d.lngEquipmentID2=Forms!
frmEquipment.lngEquipmentID);

The above SQL updates the right records with simple data,
but I can't seem to get it to update with the values I
need.

The query below selects the records to be updated and
calculates the values to update - I need to update my
table with the curCostTotal values from below:-

SELECT b.lngEquipmentID, Sum(a.curCostPrice*b.sngQty) AS
curCostTotal
FROM tblEquipment AS a
INNER JOIN tblEquipmentDetails AS b
ON a.lngEquipmentID = b.lngEquipmentID2
GROUP BY b.lngEquipmentID
HAVING b.lngEquipmentID In
(SELECT d.lngEquipmentID
FROM tblEquipment AS c
INNER JOIN tblEquipmentDetails AS d
ON c.lngEquipmentID = d.lngEquipmentID2
WHERE d.ysnDataType=Yes
AND d.lngEquipmentID2=Forms!
frmEquipment.lngEquipmentID);

Any help would be much appreciated.
 
Problem Solved. My mistake was to use aggregate
functions (Sum, Group By) in an update query. I have
created a query (qryComponentCosts) which lists
components and prices but doesn't aggregate them:-

SELECT b.lngEquipmentID, a.curCostPrice, b.sngQty
FROM tblEquipment AS a INNER JOIN tblEquipmentDetails AS
b ON a.lngEquipmentID = b.lngEquipmentID2;

I then use the DSum Domain Aggregate Function to get my
aggregate totals in the update query:-

UPDATE tblEquipment AS a SET a.curCostPrice = DSum
("curCostPrice*sngQty", "qryComponentCosts", "lngEquipment
ID = " & lngEquipmentID)
WHERE a.lngEquipmentID IN
(SELECT d.lngEquipmentID
FROM tblEquipment AS c INNER JOIN tblEquipmentDetails
AS d ON c.lngEquipmentID = d.lngEquipmentID2
WHERE d.ysnDataType=Yes
AND d.lngEquipmentID2=Forms!
frmEquipment.lngEquipmentID);
 
Problem Solved. My mistake was to use aggregate
functions (Sum, Group By) in an update query. I have
created a query (qryComponentCosts) which lists
components and prices but doesn't aggregate them:-

SELECT b.lngEquipmentID, a.curCostPrice, b.sngQty
FROM tblEquipment AS a INNER JOIN tblEquipmentDetails AS
b ON a.lngEquipmentID = b.lngEquipmentID2;

I then use the DSum Domain Aggregate Function to get my
aggregate totals in the update query:-

UPDATE tblEquipment AS a SET a.curCostPrice = DSum
("curCostPrice*sngQty", "qryComponentCosts", "lngEquipment
ID = " & lngEquipmentID)
WHERE a.lngEquipmentID IN
(SELECT d.lngEquipmentID
FROM tblEquipment AS c INNER JOIN tblEquipmentDetails
AS d ON c.lngEquipmentID = d.lngEquipmentID2
WHERE d.ysnDataType=Yes
AND d.lngEquipmentID2=Forms!
frmEquipment.lngEquipmentID);
 
Back
Top