sum in a query brings back wrong total

B

Bruce Smith

I have a table that tracks the weight and cost of shipping. The query is
designed to bring back the total weight shiped and the total cost shipped for
a given period of time. The cost comes back correctly but the weight is
almost 2x the actual figure. I think it has to do with the format of the
field?

SELECT tblHazWasteContainer.HazWasteCode,
Sum(tblHazWasteContainer.ContWeight) AS SumOfContWeight,
Sum(tblHazWasteContainer.DISP_COST) AS SumOfDISP_COST
FROM tblProfile RIGHT JOIN tblHazWasteContainer ON tblProfile.Primary =
tblHazWasteContainer.primary2
WHERE (((tblHazWasteContainer.DATE_ENTER) Between #12/2/2002# And
#12/30/2003#) AND ((tblHazWasteContainer.ShipDate)<#12/30/2003#) AND
((tblHazWasteContainer.MANIFESTNO) Not Like "*see*"))
GROUP BY tblHazWasteContainer.HazWasteCode
HAVING (((Sum(tblHazWasteContainer.DISP_COST))>0));

I just got called away so will be back in hour or so.

Thanks Bruce
 
J

John Vinson

I have a table that tracks the weight and cost of shipping. The query is
designed to bring back the total weight shiped and the total cost shipped for
a given period of time. The cost comes back correctly but the weight is
almost 2x the actual figure. I think it has to do with the format of the
field?

SELECT tblHazWasteContainer.HazWasteCode,
Sum(tblHazWasteContainer.ContWeight) AS SumOfContWeight,
Sum(tblHazWasteContainer.DISP_COST) AS SumOfDISP_COST
FROM tblProfile RIGHT JOIN tblHazWasteContainer ON tblProfile.Primary =
tblHazWasteContainer.primary2
WHERE (((tblHazWasteContainer.DATE_ENTER) Between #12/2/2002# And
#12/30/2003#) AND ((tblHazWasteContainer.ShipDate)<#12/30/2003#) AND
((tblHazWasteContainer.MANIFESTNO) Not Like "*see*"))
GROUP BY tblHazWasteContainer.HazWasteCode
HAVING (((Sum(tblHazWasteContainer.DISP_COST))>0));

I just got called away so will be back in hour or so.

Thanks Bruce
Click to expand...

My guess is that there are two records in tblHazWasteContainer which
fit the criterion, and have the same weight. It's adding the weights
from the two records.

Try opening this query in design view; uncheck the Totals icon; and
look at the datasheet view of the query to see which records are being
summed.

John W. Vinson[MVP]
 
Top