Crosstab sum doubles

[Ron P]

I created a crosstab query that appeared to work fine. However after I
looked at the numbers the query is returning double amounts in the sum
columns. I can't seem to figure out why. Any suggestions?

TRANSFORM Sum(PartsUsed.QtyUsed) AS SumOfQtyUsed
SELECT PartsUsed.PartNumber, PartsUsed.Component, Sum(PartsUsed.QtyUsed) AS
[Total Of QtyUsed]
FROM PartsUsed LEFT JOIN [Screen Inv] ON PartsUsed.PartNumber=[Screen
Inv].[Product #]
GROUP BY PartsUsed.PartNumber, PartsUsed.Component
PIVOT PartsUsed.Day;

 

[KARL DEWEY]

Try it like this --
TRANSFORM Sum(PartsUsed.QtyUsed) AS SumOfQtyUsed
SELECT PartsUsed.PartNumber, PartsUsed.Component, Sum(PartsUsed.QtyUsed) AS
[Total Of QtyUsed]
FROM PartsUsed
GROUP BY PartsUsed.PartNumber, PartsUsed.Component
PIVOT PartsUsed.Day;

 

[John Spencer]

If Karl's query works then you probably have duplicate values in the
[Screen Inv].[Product #] field. So you are getting two results returned for
every Product.

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