Crosstab queries with more than one field

  • Thread starter Thread starter John S. Ford, MD
  • Start date Start date
J

John S. Ford, MD

I understand the basic concept of crosstab queries and can create one in
Access 2000 (using the crosstab wizard). What I'm wondering is if it's
possible to create such a query using more than one fields. For example,
suppose I have table tblEmployeeReviews with fields ReviewIDNum, Name,
Efficiency (Good, Fair, Bad) and Speed (Good, Fair, Bad):

1 John Good Fair
2 John Bad Fair
3 Steven Fair Good
4 John Good Bad
5 Steven Bad Good

I want to create a crosstab query whose result looks like this:

Name EfficiencyTotal EfficiencyGood EfficiencyFair
EfficiencyBad SpeedTotal SpeedGood SpeedFair SpeedBad
John 3 1 0
2 3 0 2
1
Steven 2 0 1
1 2 2 0
0

The crosstab query wizard appears to only allow one to use one field at a
time (eg. Efficiency). Is there to use two or more?

John
 
By the way, my post didn't format the way I intended which was to have all
the fields of my crosstab query show up on one line with their counts lined
up below them.

John
 
Duane,

Is that technique the same as creating multiple crosstab queries (one for
each field) and then inner joining them on the grouping field?

John
 
PMFBI

possibly another way of looking at it using a
normalizing query ("qryUnion") on "yurtable"
(change to real name of your table) where I
just couldn't use reserved Access word "Name"
as a field name....

SELECT
t.AName,
"Efficiency" AS ReviewProp,
t.Efficiency AS Rating
FROM yurtable t
UNION ALL SELECT
t.AName,
"Speed",
t.Speed
FROM yurtable AS t;

result from your example data:

AName ReviewProp Rating
John Efficiency Good
John Efficiency Bad
Steven Efficiency Fair
John Efficiency Good
Steven Efficiency Bad
John Speed Fair
John Speed Fair
Steven Speed Good
John Speed Bad
Steven Speed Good


TRANSFORM Nz(Count([ReviewProp] & [Rating]),0) AS cnt
SELECT
q.AName,
Sum(cnt) As TotalRatingsCnt,
Abs(Sum([ReviewProp]="Efficiency")) AS EfficiencyTotal,
Abs(Sum([ReviewProp]="Speed")) AS SpeedTotal
FROM qryUnion AS q
GROUP BY q.AName
PIVOT [ReviewProp] & [Rating]
IN
("EfficiencyGood",
"EfficiencyFair",
"EfficiencyBad",
"SpeedGood",
"SpeedFair",
"SpeedBad");

Apologies again for butting in...
 
maybe "more accurate" row headings:

TRANSFORM Nz(Count([ReviewProp] & [Rating]),0) AS cnt
SELECT
q.AName,
Sum(cnt) AS TotalBothRatings,
Count(cnt) AS NumDistinctRatings,
Abs(Sum([ReviewProp]="Efficiency")) AS EfficiencyTotal,
Abs(Sum([ReviewProp]="Speed")) AS SpeedTotal
FROM qryUnion AS q
GROUP BY
q.AName
PIVOT [ReviewProp] & [Rating]
In
("EfficiencyGood",
"EfficiencyFair",
"EfficiencyBad",
"SpeedGood",
"SpeedFair",
"SpeedBad");
 
Oner way to go about this is to concatenate the fields to make a single
field using format staements or other methods to ensure proper column
alignment.
 
Thanks for all the suggestions. That's all VERY helpful. I've got a lot of
things to try now!

John
 
Back
Top