Complex Crosstab Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Not sure how to do this, so I figure some of you wonderful brainiacs out
there can hook me up.

I get the basic idea of Crosstab queries, and now that my boss sees them
regularly, she wants yet even more.

Here is my 'source' data...the results of my query:
Unit Audits Score Possible Audit Score
CS 114 1225 1228 0.99
OG 479 23373 23397 0.99
PGS 4637 43170 43190 0.99953693

She wants this information to be listed Year-to-date, like so (I've mocked
up the data):
10 11 12
CS Audits 114 4637 479
CS Score 1225 43170 23373
CS Possible 1228 43190 23397
CS Score 0.99 0.99 0.99
OG Audits 479 114 4637
OG Score 23373 1225 43170
OG Possible 23397 1228 43190
OG Score 0.99 0.99 0.99
PGS Audits 4637 479 114
PGS Score 43170 23373 1225
PGS Possible 43190 23397 1228
PGS Score 0.99 0.99 0.99

Now, the order doesn't matter, but the query needs to be laid out as shown
above.

Any ideas?

Thanks in advance for reading and replying!
 
I should elaborate...I already have the method to figure out the month, I
just can't get the 2nd column (type of data - Audits, Score, Possible, etc.)
to show...each type of data appears as it's own column in regular
queries...how can I get those columns to appear as one column and display
what type of data follows?

10 11 12
CS Audits 114 4637 479
CS Score 1225 43170 23373
CS Possible 1228 43190 23397
CS Score% 0.99 0.99 0.99
OG Audits 479 114 4637
OG Score 23373 1225 43170
OG Possible 23397 1228 43190
OG Score% 0.99 0.99 0.99
PGS Audits 4637 479 114
PGS Score 43170 23373 1225
PGS Possible 43190 23397 1228
PGS Score% 0.99 0.99 0.99
 
She wants this information to be listed Year-to-date
You do not have any DATE information to chose from. How do you separate by
date?

Where does the 10, 11, and 12 column labels come from?
 
Karl,

I elaborated at about the same time you posted...
"I should elaborate...I already have the method to figure out the month, I
just can't get the 2nd column (type of data - Audits, Score, Possible, etc.)
to show...each type of data appears as it's own column in regular
queries...how can I get those columns to appear as one column and display
what type of data follows?"

How I got the date was to create an expression that pulled the last 2 digits
of the year, a "/" and a two digit month. The true example would look like
the following:

Unit Field 05/10 05/11 05/12 06/01
CS Audits 114 4637 479 114
CS Score 1225 43170 23373 1225
CS Possible 1228 43190 23397 1228
CS Score% 0.99 0.99 0.99 0.99
OG Audits 479 114 4637 479
OG Score 23373 1225 43170 23373
OG Possible 23397 1228 43190 23397
OG Score% 0.99 0.99 0.99 0.99
PGS Audits 4637 479 114 4637
PGS Score 43170 23373 1225 43170
PGS Possible 43190 23397 1228 43190
PGS Score% 0.99 0.99 0.99 0.99

Hope this helps.
 
Where does your table store the date?

Here is something I put together that you probably can run with. I assumed
that you were using weeks. I used this as table structure.
Name Type Size
Section Text 50
Week Long Integer 4
Audits Single 4
Score Single 4
Possible Single 4
Audit Score Single 4

I built 4 crosstab queries.
Audits_Crosstab1 --
TRANSFORM First(Audits.Audits) AS FirstOfAudits
SELECT [Section] & " Audits" AS Expr1
FROM Audits
GROUP BY [Section] & " Audits"
PIVOT Audits.Week;

Audits_Crosstab2 --
TRANSFORM First(Audits.Score) AS FirstOfScore
SELECT [Section] & " Score" AS Expr1
FROM Audits
GROUP BY [Section] & " Score"
PIVOT Audits.Week;

Audits_Crosstab3 --
TRANSFORM First(Audits.Possible) AS FirstOfPossible
SELECT [Section] & " Possible" AS Expr1
FROM Audits
GROUP BY [Section] & " Possible"
PIVOT Audits.Week;

Audits_Crosstab4 --
TRANSFORM First(Audits.[Audit Score]) AS [FirstOfAudit Score]
SELECT [Section] & " Audit Score" AS Expr1
FROM Audits
GROUP BY [Section] & " Audit Score"
PIVOT Audits.Week;

Then a union query --
SELECT Audits_Crosstab1.Expr1, Audits_Crosstab1.[11], Audits_Crosstab1.[12],
Audits_Crosstab1.[13]
FROM Audits_Crosstab1
UNION SELECT Audits_Crosstab2.Expr1, Audits_Crosstab2.[11],
Audits_Crosstab2.[12], Audits_Crosstab2.[13]
FROM Audits_Crosstab2
UNION SELECT Audits_Crosstab3.Expr1, Audits_Crosstab3.[11],
Audits_Crosstab3.[12], Audits_Crosstab3.[13]
FROM Audits_Crosstab3
UNION SELECT Audits_Crosstab4.Expr1, Audits_Crosstab4.[11],
Audits_Crosstab4.[12], Audits_Crosstab4.[13]
FROM Audits_Crosstab4;
 
Back
Top