Rows to Columns

  • Thread starter Thread starter Randal
  • Start date Start date
R

Randal

How can I turn this Query ---

ID LEVL Period Count Amt
B86V 1 Q1 175 40.17
B86V 1 Q2 147 25.07
B86V 1 Q3 92 97.91
B86V 1 Q4 113 13.91
B86V 2 Q1 71 14.16
B86V 2 Q2 75 19.39
B86V 2 Q3 58 15.55
B86V 2 Q4 82 37.60

Into this Report layout?

ID = B86V
LEVEL 1
Period Q1 Q2 Q3 Q4
COUNT 175 147 92 113
Amt 40.17 25.07 97.91 13.91

LEVEL 2
Period Q1 Q2 Q3 Q4
COUNT 71 75 58 82
Amt 14.16 19.39 15.55 37.60
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a cross-tab (aka Pivot Table) queries. You'll need 2 queries: 1
for the Count, the other for the Amount. Join them tobether on the ID
& Level columns. Use the final query as the report's RecordSource.
In the report Group by the Level field (with a header section). In
the Level Header section put the ID & Level controls. In the Detail
section put the Count & Amount values.

Example of querys (JET SQL). I changed the name of the Count column to
Tally to avoid use of SQL keyword. I use the name of TallyExample as
the table's name - change to your table's name.

Query_A - gets Amount totals for Qtr:

TRANSFORM Sum(A.Amount) AS Amt
SELECT A.ID, A.Level, Sum(A.Amount) AS Total
FROM TallyExample AS A
GROUP BY A.ID, A.Level
PIVOT A.Period

Query_C - gets Count totals for Qtr:

TRANSFORM Sum(C.Tally) AS SumOfTally
SELECT C.ID, C.Level, Sum(C.Tally) AS Total
FROM TallyExample AS C
GROUP BY C.ID, C.Level
PIVOT C.Period

Final query - combines Query_A & Query_C:

SELECT A.ID, A.Level, A.Total AS TotalAmt, A.Q1, A.Q2, A.Q3, A.Q4,
C.Total AS CountTotal, C.Q1, C.Q2, C.Q3, C.Q4
FROM Query_A AS A INNER JOIN Query_C AS C
ON (A.Level = C.Level) AND (A.ID = C.ID)

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQD0854echKqOuFEgEQJSCwCeLyOoK5B6Jc75s2S5CJAsmbC7Y64AoI+J
j7SU34O0YDD5JD87pDNCfEtU
=uLe8
-----END PGP SIGNATURE-----
 
Back
Top