calculating totals in crosstab table

[robojeff]

I have a crosstab table which contains a count of bad parts found every month by part number for 12 months.

I would like to create a query to total the amounts of each month so that I can display the total bad parts in each month.

I have tried to sum the amounts in the table with a query but I get the same results as if I group each items, where I get a list of each part per month...

Here is was the two queries look like and they produce the same results:

SELECT DISTINCTROW [1badparts_tbl].[Part Number], [1badparts_tbl].Desc, [1badparts_tbl].Jan, [1badparts_tbl].Feb, [1badparts_tbl].Mar, [1badparts_tbl].Apr, [1badparts_tbl].May, [1badparts_tbl].Jun, [1badparts_tbl].Jul, [1badparts_tbl].Aug, [1badparts_tbl].Sep, [1badparts_tbl].Oct, [1badparts_tbl].Dec
FROM 1badparts_tbl
GROUP BY [1badparts_tbl].[Part Number], [1badparts_tbl].Desc, [1badparts_tbl].Jan, [1badparts_tbl].Feb, [1badparts_tbl].Mar, [1badparts_tbl].Apr, [1badparts_tbl].May, [1badparts_tbl].Jun, [1badparts_tbl].Jul, [1badparts_tbl].Aug, [1badparts_tbl].Sep, [1badparts_tbl].Oct, [1badparts_tbl].Nov, [1badparts_tbl].Dec;

and

SELECT DISTINCTROW [1badparts_tbl].[Part Number], [1badparts_tbl].Desc, Sum([1badparts_tbl].Jan) AS SumOfJan, Sum([1badparts_tbl].Feb) AS SumOfFeb, Sum([1badparts_tbl].Mar) AS SumOfMar, Sum([1badparts_tbl].Apr) AS SumOfApr, Sum([1badparts_tbl].May) AS SumOfMay, Sum([1badparts_tbl].Jun) AS SumOfJun, Sum([1badparts_tbl].Jul) AS SumOfJul, Sum([1badparts_tbl].Aug) AS SumOfAug, Sum([1badparts_tbl].Sep) AS SumOfSep, Sum([1badparts_tbl].Oct) AS SumOfOct, Sum([1badparts_tbl].Nov) AS SumOfNov, Sum([1badparts_tbl].Dec) AS SumOfDec
FROM 1badparts_tbl
GROUP BY [1badparts_tbl].[Part Number], [1badparts_tbl].Desc;

What am I doing wrong?

Thanks

 

[SkipVought]

Hi,

I have a crosstab table which contains a count of bad parts found every month by part number for 12 months.

Your table design is you mistake. You ought to have a NORMALIZED table to store your statistics.

then use a query each month, like a crosstab, to summarize & report.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[lespaul]

you need to create a query that normalizes your data:

SELECT B.PartNumber, b.Desc, 'Jan' As MonthName, Jan As Qty FROM [1badparts_tbl] B
UNION
SELECT B.PartNumber, b.Desc, 'Feb', Feb FROM [1badparts_tbl] B
UNION
SELECT B.PartNumber, b.Desc, 'Mar', Mar FROM [1badparts_tbl] B
etc...
UNION
SELECT B.PartNumber, b.Desc, 'Dec', Dec FROM [1badparts_tbl] B

Save that as qryNormal. Then to sum you need to do:

SELECT MonthName, Sum(QTY) FROM qryNormal GROUP BY MonthName

this will give you the total number of bad parts for each month.

You may want to read Fundamentals of Relational Database Design for more on normalization.

HTH

Leslie

Have you met Hardy Heron?