Transpose/ Crosstab query

[Guest]

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

 

[Wolfgang Kais]

Hello 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

In a crosstab query, the only value you need is the percentage.
Use Depart as row heading and Month as column heading.
This works fine in a query, but in a report...
The problem is that the month names and therefore the column names
of the query change, but the field names in the report don't.

 

[James A. Fortune]

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

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
(e-mail address removed)

 

[Guest]

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 ....

 

[James A. Fortune]

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 ....

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
(e-mail address removed)