Query by Scale and Project

[Jazzman Dazzman]

I need help with the following DBase query example:

Fields: Project Indiv Water Food Waste Recycle
PJ1 Sam 2 1 3 1
PJ2 Beth 2 5 4 2
PJ1 Dave 5 3 2 0
PJ3 Al 1 1 4 2

Scale is 1 thru 5

I've tried Group on "Project" and Group/Count on the
fields with the results listing each count for each field
numerious of times. I mainly need to to group and query
on 250 Invididuals rated on the Project and Scales as
follows:

Proeject 1

Scale Water Food Waste Recycle
5 12 3 4
4 8 8 2
3 9 49 50
2 15 22 36

Thanks and looking forward to a response. JMDM

 

[Duane Hookom]

You are a little anxious with this aren't you. Your table should have a
structure that is more normalized. Your records should look something like:
Project Indiv Category Score
PJ1 Sam Water 2
PJ1 Sam Food 1
etc.
Then you could create a crosstab query. If you can't change table structure,
you can normalize this with a UNION QUERY
SELECT Project, Indiv, "Water" as Category, [Water] as Score
FROM tblYourtable
UNION ALL

SELECT Project, Indiv, "Food", [Food]
FROM tblYourtable
UNION ALL
SELECT Project, Indiv, "Waste", [Waste]
FROM tblYourtable
UNION ALL
SELECT Project, Indiv, "Recycle", [Recycle]
FROM tblYourtable;
Then you can use this union query as the basis for a crosstab that has
Project and Score as the Row Headings, Category as the Column Heading and
Count(Score) as the value.

 

[Jazzman Dazzman]

Yes, very anxious. Thanks, your guidance helps a great
deal. There actually are 10 Field headings, with each
records reflecting a scale under 7 of the headings. This
is the structure of the query Dbase, with scale ratings.

Project Indiv Position PVL CES CHAM EMP SAP TAG PARS
PJ1 Sam Office 1 3 4 2 1 5 5

-----Original Message-----
You are a little anxious with this aren't you. Your table should have a
structure that is more normalized. Your records should look something like:
Project Indiv Category Score
PJ1 Sam Water 2
PJ1 Sam Food 1
etc.
Then you could create a crosstab query. If you can't change table structure,
you can normalize this with a UNION QUERY
SELECT Project, Indiv, "Water" as Category, [Water] as Score
FROM tblYourtable
UNION ALL

SELECT Project, Indiv, "Food", [Food]
FROM tblYourtable
UNION ALL
SELECT Project, Indiv, "Waste", [Waste]
FROM tblYourtable
UNION ALL
SELECT Project, Indiv, "Recycle", [Recycle]
FROM tblYourtable;
Then you can use this union query as the basis for a crosstab that has
Project and Score as the Row Headings, Category as the Column Heading and
Count(Score) as the value.

--
Duane Hookom
MS Access MVP

I need help with the following DBase query example:

Fields: Project Indiv Water Food Waste Recycle
PJ1 Sam 2 1 3 1
PJ2 Beth 2 5 4 2
PJ1 Dave 5 3 2 0
PJ3 Al 1 1 4 2

Scale is 1 thru 5

I've tried Group on "Project" and Group/Count on the
fields with the results listing each count for each field
numerious of times. I mainly need to to group and query
on 250 Invididuals rated on the Project and Scales as
follows:

Proeject 1

Scale Water Food Waste Recycle
5 12 3 4
4 8 8 2
3 9 49 50
2 15 22 36

Thanks and looking forward to a response. JMDM

.

 

[Duane Hookom]

Is there still a question here or did you apply my reply to your situation.
If you haven't tried, then try. If you have tried and something went awry
then tell us what and why?

--
Duane Hookom
MS Access MVP

Yes, very anxious. Thanks, your guidance helps a great
deal. There actually are 10 Field headings, with each
records reflecting a scale under 7 of the headings. This
is the structure of the query Dbase, with scale ratings.

Project Indiv Position PVL CES CHAM EMP SAP TAG PARS
PJ1 Sam Office 1 3 4 2 1 5 5

-----Original Message-----
You are a little anxious with this aren't you. Your table should have a
structure that is more normalized. Your records should look something like:
Project Indiv Category Score
PJ1 Sam Water 2
PJ1 Sam Food 1
etc.
Then you could create a crosstab query. If you can't change table structure,
you can normalize this with a UNION QUERY
SELECT Project, Indiv, "Water" as Category, [Water] as Score
FROM tblYourtable
UNION ALL

SELECT Project, Indiv, "Food", [Food]
FROM tblYourtable
UNION ALL
SELECT Project, Indiv, "Waste", [Waste]
FROM tblYourtable
UNION ALL
SELECT Project, Indiv, "Recycle", [Recycle]
FROM tblYourtable;
Then you can use this union query as the basis for a crosstab that has
Project and Score as the Row Headings, Category as the Column Heading and
Count(Score) as the value.

--
Duane Hookom
MS Access MVP

I need help with the following DBase query example:

Fields: Project Indiv Water Food Waste Recycle
PJ1 Sam 2 1 3 1
PJ2 Beth 2 5 4 2
PJ1 Dave 5 3 2 0
PJ3 Al 1 1 4 2

Scale is 1 thru 5

I've tried Group on "Project" and Group/Count on the
fields with the results listing each count for each field
numerious of times. I mainly need to to group and query
on 250 Invididuals rated on the Project and Scales as
follows:

Proeject 1

Scale Water Food Waste Recycle
5 12 3 4
4 8 8 2
3 9 49 50
2 15 22 36

Thanks and looking forward to a response. JMDM

.