Query that will display record if it has 3 or more YESes

[ghostman via AccessMonster.com]

I have a training record query with a YES/NO field.

I want it to display the YES answers ONLY IF the records has 3 or more YES
answer...

ex.
if i have 5 records with 2 answered YES, and 3 NO...it will display NONE.
but if 3 answered, and 2 NO...it will display the 3 records who answered YES..

 

[ghostman via AccessMonster.com]

EDIT:

sorry, i put my question in general...
what it actually the query should do is to display the list of EMPLOYEE(s)
with more than 3 NO-SHOWS on his record...less will not be displayed.

(NoShow is YES/NO field)

i am thinking of adding something (code) on the criteria option in the query
design but no idea about it...

 

[Piet Linden]

EDIT:

sorry, i put my question in general...
what it actually the query should do is to display the list of EMPLOYEE(s)
with more than 3 NO-SHOWS on his record...less will not be displayed.

(NoShow is YES/NO field)

i am thinking of adding something (code) on the criteria option in the query
design but no idea about it...

how about a table structure... or at least the relevant part(s)? My
psychic powers just aren't working yet. big difference if you have
something like

PersonID, Q1, Q2,...Q(n)

or (PersonID, QuestionID, Answer).

 

[ghostman via AccessMonster.com]

sorry about that...

its a query based on my training table:

TrainingSessionID
TraineeID
CourseName
CourseDate
NoShow (Yes/No field) - means trainee failed to attend the course

lets say i have these training session records:

TraineeID | CourseName | CourseDate | NoShow
50612 Course 1 06/21/09 Yes
50612 Course 2 06/23/09 Yes
50612 Course 3 06/25/09 Yes
50612 Course 4 06/27/09 No

50610 Course 1 06/21/09 No
50610 Course 2 06/23/09 No
50610 Course 3 06/25/09 Yes
50610 Course 4 06/27/09 Yes

50613 Course 1 06/21/09 Yes
50613 Course 2 06/23/09 Yes
50613 Course 3 06/25/09 Yes
50613 Course 4 06/27/09 Yes

and the query willl display:

TraineeID | CourseName | CourseDate | NoShow
50612 Course 1 06/21/09 Yes
50612 Course 2 06/23/09 Yes
50612 Course 3 06/25/09 Yes
50613 Course 1 06/21/09 Yes
50613 Course 2 06/23/09 Yes
50613 Course 3 06/25/09 Yes
50613 Course 4 06/27/09 Yes

where it displays only the employee who have more than 3 NO SHOWS on their
record.


and is it also possible to count the total NO SHOWS of each employee?? just
like this:

TraineeID | CourseName | CourseDate | NoShow
50612 Course 1 06/21/09 Yes
50612 Course 2 06/23/09 Yes
50612 Course 3 06/25/09 Yes
Total = 3

50613 Course 1 06/21/09 Yes
50613 Course 2 06/23/09 Yes
50613 Course 3 06/25/09 Yes
50613 Course 4 06/27/09 Yes
Total = 4


hope this helps...

EDIT:

[quoted text clipped - 9 lines]
how about a table structure... or at least the relevant part(s)? My
psychic powers just aren't working yet. big difference if you have
something like

PersonID, Q1, Q2,...Q(n)

or (PersonID, QuestionID, Answer).

 

[John Spencer]

Basic query to identify who has 3 or more No shows.

SELECT TraineeID
FROM [TrainingSessions]
WHERE NoShow = True
GROUP BY TraineeID
HAVING COUNT > 2

Query to return the results you specified
SELECT *
FROM [TrainingSessions]
WHERE NoShow = Yes
AND TraineeID IN
(SELECT TraineeID
FROM [TrainingSessions]
WHERE NoShow = True
GROUP BY TraineeID
HAVING COUNT > 2)

To build that in the query design view (and not in the SQL view)
== Add your table
== Add the fields you want to see
== Criteria under NoShow should be True
== Criteria under TraineeID will need to be typed in and should read as
follows - substitute your field and table names as appropriate
IN (SELECT TraineeID FROM [TrainingSessions] WHERE NoShow = True GROUP BY
TraineeID HAVING COUNT > 2)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

sorry about that...

its a query based on my training table:

TrainingSessionID
TraineeID
CourseName
CourseDate
NoShow (Yes/No field) - means trainee failed to attend the course

lets say i have these training session records:

TraineeID | CourseName | CourseDate | NoShow
50612 Course 1 06/21/09 Yes
50612 Course 2 06/23/09 Yes
50612 Course 3 06/25/09 Yes
50612 Course 4 06/27/09 No

50610 Course 1 06/21/09 No
50610 Course 2 06/23/09 No
50610 Course 3 06/25/09 Yes
50610 Course 4 06/27/09 Yes

50613 Course 1 06/21/09 Yes
50613 Course 2 06/23/09 Yes
50613 Course 3 06/25/09 Yes
50613 Course 4 06/27/09 Yes

and the query willl display:

TraineeID | CourseName | CourseDate | NoShow
50612 Course 1 06/21/09 Yes
50612 Course 2 06/23/09 Yes
50612 Course 3 06/25/09 Yes
50613 Course 1 06/21/09 Yes
50613 Course 2 06/23/09 Yes
50613 Course 3 06/25/09 Yes
50613 Course 4 06/27/09 Yes

where it displays only the employee who have more than 3 NO SHOWS on their
record.


and is it also possible to count the total NO SHOWS of each employee?? just
like this:

TraineeID | CourseName | CourseDate | NoShow
50612 Course 1 06/21/09 Yes
50612 Course 2 06/23/09 Yes
50612 Course 3 06/25/09 Yes
Total = 3

50613 Course 1 06/21/09 Yes
50613 Course 2 06/23/09 Yes
50613 Course 3 06/25/09 Yes
50613 Course 4 06/27/09 Yes
Total = 4


hope this helps...

EDIT:

[quoted text clipped - 9 lines]how about a table structure... or at least the relevant part(s)? My
psychic powers just aren't working yet. big difference if you have
something like

PersonID, Q1, Q2,...Q(n)

or (PersonID, QuestionID, Answer).

 

[ghostman via AccessMonster.com]

Hi again,

i did you what said, thanks!

I add the table and the fields required,
set the criteria under NoShow = True
and added: IN (SELECT TraineeID FROM [TrainingSessions] WHERE NoShow = True
GROUP BY

TraineeID HAVING COUNT > 2) on the criteria under TraineeID..

i run the query and asked me to enter value for the number of no shows...
i enter 2 and nothing shows which is good..but when i entered number 3 which
what i am looking for, it shows all the records with no shows...including
trainee with only ONE no show in his record.

i want it to show ONLY the TRAINEE with 3 or more NO SHOWS..
is there something wrong on what i did?

Basic query to identify who has 3 or more No shows.

SELECT TraineeID
FROM [TrainingSessions]
WHERE NoShow = True
GROUP BY TraineeID
HAVING COUNT > 2

Query to return the results you specified
SELECT *
FROM [TrainingSessions]
WHERE NoShow = Yes
AND TraineeID IN
(SELECT TraineeID
FROM [TrainingSessions]
WHERE NoShow = True
GROUP BY TraineeID
HAVING COUNT > 2)

To build that in the query design view (and not in the SQL view)
== Add your table
== Add the fields you want to see
== Criteria under NoShow should be True
== Criteria under TraineeID will need to be typed in and should read as
follows - substitute your field and table names as appropriate
IN (SELECT TraineeID FROM [TrainingSessions] WHERE NoShow = True GROUP BY
TraineeID HAVING COUNT > 2)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

sorry about that...

[quoted text clipped - 69 lines]

 

[John Spencer]

My fault I missed something in the sub-query. I forgot to add "(*)" after the
word count. Or you could count the NoShow Field Count(NoShow) > 2

SELECT *
FROM [TrainingSessions]
WHERE NoShow = Yes
AND TraineeID IN
(SELECT TraineeID
FROM [TrainingSessions]
WHERE NoShow = True
GROUP BY TraineeID
HAVING COUNT(*) > 2)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Basic query to identify who has 3 or more No shows.

SELECT TraineeID
FROM [TrainingSessions]
WHERE NoShow = True
GROUP BY TraineeID
HAVING COUNT > 2

Query to return the results you specified
SELECT *
FROM [TrainingSessions]
WHERE NoShow = Yes
AND TraineeID IN
(SELECT TraineeID
FROM [TrainingSessions]
WHERE NoShow = True
GROUP BY TraineeID
HAVING COUNT > 2)

To build that in the query design view (and not in the SQL view)
== Add your table
== Add the fields you want to see
== Criteria under NoShow should be True
== Criteria under TraineeID will need to be typed in and should read as
follows - substitute your field and table names as appropriate
IN (SELECT TraineeID FROM [TrainingSessions] WHERE NoShow = True
GROUP BY TraineeID HAVING COUNT > 2)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

sorry about that...

its a query based on my training table:

TrainingSessionID
TraineeID
CourseName
CourseDate
NoShow (Yes/No field) - means trainee failed to attend the course

lets say i have these training session records:

TraineeID | CourseName | CourseDate | NoShow 50612
Course 1 06/21/09 Yes
50612 Course 2 06/23/09 Yes
50612 Course 3 06/25/09 Yes
50612 Course 4 06/27/09 No

50610 Course 1 06/21/09 No
50610 Course 2 06/23/09 No
50610 Course 3 06/25/09 Yes
50610 Course 4 06/27/09 Yes

50613 Course 1 06/21/09 Yes
50613 Course 2 06/23/09 Yes
50613 Course 3 06/25/09 Yes
50613 Course 4 06/27/09 Yes

and the query willl display:

TraineeID | CourseName | CourseDate | NoShow
50612 Course 1 06/21/09 Yes
50612 Course 2 06/23/09 Yes
50612 Course 3 06/25/09 Yes
50613 Course 1 06/21/09 Yes
50613 Course 2 06/23/09 Yes
50613 Course 3 06/25/09 Yes
50613 Course 4 06/27/09 Yes

where it displays only the employee who have more than 3 NO SHOWS on
their
record.

and is it also possible to count the total NO SHOWS of each employee??
just
like this:

TraineeID | CourseName | CourseDate | NoShow
50612 Course 1 06/21/09 Yes
50612 Course 2 06/23/09 Yes
50612 Course 3 06/25/09 Yes
Total = 3

50613 Course 1 06/21/09 Yes
50613 Course 2 06/23/09 Yes
50613 Course 3 06/25/09 Yes
50613 Course 4 06/27/09 Yes
Total = 4


hope this helps...

On Jul 21, 6:02 am, "ghostman via AccessMonster.com" <u51724@uwe>
wrote:
EDIT:

[quoted text clipped - 9 lines]
--
Message posted viahttp://www.accessmonster.com
how about a table structure... or at least the relevant part(s)? My
psychic powers just aren't working yet. big difference if you have
something like

PersonID, Q1, Q2,...Q(n)

or (PersonID, QuestionID, Answer).

 

[ghostman via AccessMonster.com]

a simple asterisk did the right job!
Thanks a lot!!

My fault I missed something in the sub-query. I forgot to add "(*)" after the
word count. Or you could count the NoShow Field Count(NoShow) > 2

SELECT *
FROM [TrainingSessions]
WHERE NoShow = Yes
AND TraineeID IN
(SELECT TraineeID
FROM [TrainingSessions]
WHERE NoShow = True
GROUP BY TraineeID
HAVING COUNT(*) > 2)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Basic query to identify who has 3 or more No shows.

[quoted text clipped - 101 lines]