How to design a query for grouping totals by months?

[hkgary33 via AccessMonster.com]

Dear all,
I’ve built a table named tblProductInfo, in each record, it consists of 1
date field (product_date), 2 numeric fields for product costs of two separate
parts (cost_A; cost_B); and another 2 numeric fields for product prices of
two separate parts (price_A; price_B).
Now I would like to design a query such that it will basically have 12
columns x 2 rows in the result, in which each column is actually indicates
each month. The first row is for the costs and prices of part A (sum of
(cost_A + price_A) for all products with that particular month indicated by
the date field); and the second row is for the costs and prices of part B,
using similar calculation. (It’s just like a statistics table)
But if the products dates are range larger than one year, then additional
columns will be added.

I’ve tried to write the SQL statement for query but I still can’t
successfully make such query, can anybody helps me?

Thanks so much!!!
Gary

 

[Michel Walsh]

Hi,


Since you generate fields, extra fields, based on actual values, that is the
signature of a problem requiring a crosstab.

A crosstab generally generates just one sequence of data, so, I will focus
on the costA+priceA



TRANSFORM SUM(cost_A+price_A) As theValue
SELECT "A"
FROM myTable
GROUP BY 1
PIVOT Format(dateField, "mmyyyy")




should do.


If you have some table, for illustration, here, Iotas, with one field, iota,
with 2 records, with values 0 and 1, then:


TRANSFORM SUM( iif(iota=0, cost_A+price_A, cost_B+price_B)) AS theValue
SELECT iif(iota=0,"A", "B")
FROM myTable INNER JOIN (SELECT iota FROM iotas WHERE iota IN(0, 1)) As
whatever
GROUP BY iif(iota=0,"A", "B")
PIVOT Format(dateField, "mmyyyy")


should do the two groups at once.




Hoping it may help,
Vanderghast, Access MVP