Crosstab sum doubles

R

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;
 
K

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;
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query error 1
Crosstab qry not adding up 1
"Invalid scale" error during Crosstab 2
Problems with Crosstab Query 4
insert a crosstab in to temp table query 2
Multiple expressions (Sum, Count, Average) of same value in crosstab query 3
Duplicate values in Crosstab 2
Crosstab, Quarter Sum isn't working 6

Top