coding 3 queries into one query that use group by.

[zero_fusion via AccessMonster.com]

Hello everyone,
this is my first post here, and hopefully someone could help me out with my
newbie question:

I did a database for a survey that we did at work, its about jobs and how
many ppl would be interested in them.
Each job is a field (in the table [jobs]) and has 3 options (data) : I like
it, Not Sure, I dont like it.

Also there is another Field : Gender in the table [page V]... which is either
male or female.

So i needed to make a query that would show the number of males that chose
(for me) the number that chose (not sure) and same for (not for me) for each
job (lets take medical assistant as an example). and the same for females.

so i did 3 queries for each job (query for each option) and a 4th query to
join the 3 queries together .

here is an example :

SELECT [Page V].Gender, Count(jobs.[Medical Assistant]) AS [Not sure]
FROM jobs LEFT JOIN [Page V] ON jobs.ID=[Page V].ID
WHERE ((jobs.[Medical Assistant]) Like 'not sure')
GROUP BY [Page V].Gender;

But that would be hectic as i have 22 jobs and 22x4=88 queries !

So is there a way to put the 3 options (for me, not sure, not for me) in one
query and then group it by Gender just like the original query ?
Any help would be much appreciated,
thank you.

 

[Jeff Boyce]

I'm not sure from your description, but it sounds like you might be able to
use a Totals query, using GroupBy on Gender and GroupBy on your "like it"
field, and Count on, say recordID (whatever the table is using for a primary
key).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[zero_fusion via AccessMonster.com]

Thanks for the reply,
what i need is to use GroupBy on 3 options "like it" , "not sure" and "dont
like it" in just one query as well as GroupBy Gender !

My database looks like this :
Jobs
-ID (PK)
-Medical Assistant
-IT Admin
-Chemical Engineer
*etc.

Page V
-ID (PK)
-Gender
-Name
-Age
-School Name

I'm not sure from your description, but it sounds like you might be able to
use a Totals query, using GroupBy on Gender and GroupBy on your "like it"
field, and Count on, say recordID (whatever the table is using for a primary
key).

Hello everyone,
this is my first post here, and hopefully someone could help me out with my

[quoted text clipped - 27 lines]

Any help would be much appreciated,
thank you.

 

[Jeff Boyce]

Perhaps I assumed too much? Are you saying that you have THREE fields for
your options, rather than one field with three choices? If the former, you
have a ... spreadsheet! The reason you are having a problem is that you are
trying to get Access to use its relationally-oriented features on the
"sheet" data you're feeding it.

If the latter, you can GroupBy the field that holds the three choices.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks for the reply,
what i need is to use GroupBy on 3 options "like it" , "not sure" and "dont
like it" in just one query as well as GroupBy Gender !

My database looks like this :
Jobs
-ID (PK)
-Medical Assistant
-IT Admin
-Chemical Engineer
*etc.

Page V
-ID (PK)
-Gender
-Name
-Age
-School Name

I'm not sure from your description, but it sounds like you might be able to
use a Totals query, using GroupBy on Gender and GroupBy on your "like it"
field, and Count on, say recordID (whatever the table is using for a primary
key).

Hello everyone,
this is my first post here, and hopefully someone could help me out

with my
[quoted text clipped - 27 lines]

Any help would be much appreciated,
thank you.