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

There is an FAQ that describes how to do this at
http://www.tek-tips.com/faqs.cfm?fid=4524. You may need to normalize your
table prior to creating the crosstab.
--
Duane Hookom
Microsoft Access MVP


"John S. Ford, MD" wrote:

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

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

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
news:E8F56B5F-20AE-413E-A1AB-(E-Mail Removed)...
> There is an FAQ that describes how to do this at
> http://www.tek-tips.com/faqs.cfm?fid=4524. You may need to normalize your
> table prior to creating the crosstab.
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "John S. Ford, MD" wrote:
>
> > 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
> >
> >
> >

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

"John S. Ford, MD" wrote:
>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
>
>

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.


"John S. Ford, MD" <(E-Mail Removed)> wrote in message
news:ubdiAt$(E-Mail Removed)...
>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
>
>
>

Thanks for all the suggestions. That's all VERY helpful. I've got a lot of
things to try now!

John