Re: How to design a query for grouping totals by months?

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


"hkgary33 via AccessMonster.com" <u23950@uwe> wrote in message
news:63df76c23a37d@xxxxxx
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200607/1



.

Relevant Pages

  • How to design a query for grouping totals by months?
    ... 2 numeric fields for product costs of two separate ... two separate parts. ... Now I would like to design a query such that it will basically have 12 ... and the second row is for the costs and prices of part B, ...
    (microsoft.public.access.queries)
  • Re: TWO Crosstab Query Ouput Questions
    ... field very similar to the LOCATE field in the STATES2 that we created. ... I was certain that all I had to do was create a crosstab on RCROSS with RLOC ... do is to modify the main query to change your RFLY_RSTA to LOCATE and the ... locations for each species as rows with the ...
    (microsoft.public.access.queries)
  • Re: TWO Crosstab Query Ouput Questions
    ... we want to see and use those as the input to the crosstab. ... and only 1098 in the final query. ... locations for each species as rows with the ... concerning the Totals Query. ...
    (microsoft.public.access.queries)
  • Re: dynamic crosstab A2002 - Almost there
    ... I would set up the crosstab as ... Query 1: QryMonthlyIncidentsBase_Crosstab ... Base your report on this query. ... >>> Dim ReportLabelAs String ...
    (microsoft.public.access.queries)
  • Re: Cross Tab Query with two value=-[Result] & " - " & [Error] as the
    ... This solution uses a crosstab with a cartesian to create multiple values. ... Anyway, the Union Query now has 6 SELECT parts, not 3, and each one ... SELECT ID,Client,Date, ResultA as Value1, "ResultA" as Task ...
    (microsoft.public.access.queries)