Transpose/ Crosstab query

G

GILBERT

Hi,
I have a report that needs to look like this.
Depart July-A July-B June-A June-B May-A May-b
abc 34% 89% 55% 89% 45% 77%
def 45% 56% 100% 45% 67% 88%

In the query and report it looks like this
Month Depart Precentage
July-A abc 34%
July-B abc 89%
July-A def 45%
July-B def 56%
I've tried a cross tab but it only allows for 1 value. I tried two crosstab
and then join them in a new query but it doesn't update for new months(E.G.
August) TIA
 
J

James A. Fortune

GILBERT said:
Hi,
I have a report that needs to look like this.
Depart July-A July-B June-A June-B May-A May-b
abc 34% 89% 55% 89% 45% 77%
def 45% 56% 100% 45% 67% 88%

In the query and report it looks like this
Month Depart Precentage
July-A abc 34%
July-B abc 89%
July-A def 45%
July-B def 56%
I've tried a cross tab but it only allows for 1 value. I tried two crosstab
and then join them in a new query but it doesn't update for new months(E.G.
August) TIA
Click to expand...

Greetings GILBERT,

Unless I'm missing something, this turned out not to be very difficult.

tblDepartSummary
ID AutoNumber
TimeFrame Text
Depart Text
Pct Long
ID TimeFrame Depart Pct
1 May-A abc 45
2 May-B abc 77
3 June-A abc 55
4 June-B abc 89
5 July-A abc 34
6 July-B abc 89
7 May-A def 67
8 May-B def 88
9 June-A def 100
10 June-B def 45
11 July-A def 45
12 July-B def 56

qryDepartSummary:
TRANSFORM First(Pct) & "%" AS [The Value]
SELECT Depart
FROM tblDepartSummary
GROUP BY Depart
PIVOT TimeFrame IN ("July-A", "July-B", "June-A", "June-B","May-A",
"May-B" );

!qryDepartSummary:
Depart July-A July-B June-A June-B May-A May-B
abc 34% 89% 55% 89% 45% 77%
def 45% 56% 100% 45% 67% 88%

I hope the -A, -B correspond to something like different years rather
than something else. If they don't you're going to get the wrong
results after a year passes because you will be looking at, say, July-A
across multiple years.

Maybe the crosstab can be generated on the fly in code behind a form so
that a function can produce automatically the list of all relevant
timeframes for the 'IN' part.

James A. Fortune
[email protected]
 
G

GILBERT

Thank you so much for your assistance ... I've another question my statement
looks like this and it works....How can I add a number to the month for
example July if its Overallscore I need it to say July1 and if its AFS then
make it July2
SQL SELECT MAINTABLE.Month, MAINTABLE.AFS AS VALUE1, MAINTABLE.Channe
FROM MAINTABLE
UNION ALL SELECT MAINTABLE.MONTH,MAINTABLE.OVERALLSCORE AS
VALUE1,MAINTABLE.CHANNE
FROM MAINTABLE;
Since the query is tracking both question in the month I need to have SQL
concat a number to month ....
 
J

James A. Fortune

GILBERT said:
Thank you so much for your assistance ... I've another question my statement
looks like this and it works....How can I add a number to the month for
example July if its Overallscore I need it to say July1 and if its AFS then
make it July2
SQL SELECT MAINTABLE.Month, MAINTABLE.AFS AS VALUE1, MAINTABLE.Channe
FROM MAINTABLE
UNION ALL SELECT MAINTABLE.MONTH,MAINTABLE.OVERALLSCORE AS
VALUE1,MAINTABLE.CHANNE
FROM MAINTABLE;
Since the query is tracking both question in the month I need to have SQL
concat a number to month ....
Click to expand...

Is that your -A and -B? If I understand you correctly, try:

SELECT MAINTABLE.Month & "1" AS Month, MAINTABLE.AFS AS VALUE1,
MAINTABLE.Channe
FROM MAINTABLE
UNION ALL SELECT MAINTABLE.MONTH & "2" As Month,MAINTABLE.OVERALLSCORE AS
VALUE1,MAINTABLE.CHANNE
FROM MAINTABLE;

It's probably better to have the query make a new field for that
distinction. Field contents make terrible databases.

SELECT MAINTABLE.Month, MAINTABLE.AFS AS VALUE1, MAINTABLE.Channe, "AFS"
AS Value1Source
FROM MAINTABLE
UNION ALL SELECT MAINTABLE.MONTH,MAINTABLE.OVERALLSCORE AS
VALUE1,MAINTABLE.CHANNE, "OVERALLSCORE" AS Value1Source
FROM MAINTABLE;

James A. Fortune
[email protected]
 
Top