Count YES/NO Field

[ghostman via AccessMonster.com]

I have this Query SQL: (based on 2 tables: Training Record and Training
Sessions)....

SELECT [Training Record].TSID, [Training Record].TraineeID, [Training Record].
NoShow, [Training Sessions].ModuleName, [Training Sessions].SessionDate,
[Training Sessions].InstructorID
FROM [Training Sessions] INNER JOIN [Training Record] ON [Training Sessions].
TSID=[Training Record].TSID
WHERE ((([Training Record].TraineeID) In (SELECT TraineeID FROM [Training
Record] WHERE NoShow=True GROUP BY TraineeID HAVING COUNT >2)) AND ((
[Training Record].NoShow)=True));


...that displays NOSHOW records 3 and above. But the results are for all
TRAINEES. Just like this:

TraineeID TSID ModuleName SessionDate InstructorID NoShow
8651354 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8762548 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8753545 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8651354 101-MAS-20-08 Furniture 101 8/20/2009 100015 Yes
8651354 102-MRA-30-08 Furniture 101 8/30/2009 100015 Yes


How can i make the query display ONLY the records of the TRAINEE(S) who have
3 or more NOSHOWS? just like this:

TraineeID TSID ModuleName SessionDate InstructorID NoShow
8651354 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8651354 101-MAS-20-08 Driving 101 8/20/2009 100015 Yes
8651354 101-MRA-30-08 Safety 101 8/30/2009 100015 Yes

 

[Tom van Stiphout]

On Sun, 16 Aug 2009 12:24:19 GMT, "ghostman via AccessMonster.com"

I would do this in two steps. The first query finds the trainees with

=3 noshows. You create a Totals query with only TraineeID being a

visible column, set the criteria for NoShow=Yes, count the TraineeIDs,
and set the criteria for this count to be >= 3 (this will create a
HAVING clause in the SQL pane).
Then create a new query, take the original one and the above one, and
inner join on the TraineeID. That automatically restricts the original
query to those occurring in the above one.

-Tom.
Microsoft Access MVP

I have this Query SQL: (based on 2 tables: Training Record and Training
Sessions)....

SELECT [Training Record].TSID, [Training Record].TraineeID, [Training Record].
NoShow, [Training Sessions].ModuleName, [Training Sessions].SessionDate,
[Training Sessions].InstructorID
FROM [Training Sessions] INNER JOIN [Training Record] ON [Training Sessions].
TSID=[Training Record].TSID
WHERE ((([Training Record].TraineeID) In (SELECT TraineeID FROM [Training
Record] WHERE NoShow=True GROUP BY TraineeID HAVING COUNT >2)) AND ((
[Training Record].NoShow)=True));


..that displays NOSHOW records 3 and above. But the results are for all
TRAINEES. Just like this:

TraineeID TSID ModuleName SessionDate InstructorID NoShow
8651354 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8762548 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8753545 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8651354 101-MAS-20-08 Furniture 101 8/20/2009 100015 Yes
8651354 102-MRA-30-08 Furniture 101 8/30/2009 100015 Yes


How can i make the query display ONLY the records of the TRAINEE(S) who have
3 or more NOSHOWS? just like this:

TraineeID TSID ModuleName SessionDate InstructorID NoShow
8651354 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8651354 101-MAS-20-08 Driving 101 8/20/2009 100015 Yes
8651354 101-MRA-30-08 Safety 101 8/30/2009 100015 Yes

 

[MGFoster]

I have this Query SQL: (based on 2 tables: Training Record and Training
Sessions)....

SELECT [Training Record].TSID, [Training Record].TraineeID, [Training Record].
NoShow, [Training Sessions].ModuleName, [Training Sessions].SessionDate,
[Training Sessions].InstructorID
FROM [Training Sessions] INNER JOIN [Training Record] ON [Training Sessions].
TSID=[Training Record].TSID
WHERE ((([Training Record].TraineeID) In (SELECT TraineeID FROM [Training
Record] WHERE NoShow=True GROUP BY TraineeID HAVING COUNT >2)) AND ((
[Training Record].NoShow)=True));


..that displays NOSHOW records 3 and above. But the results are for all
TRAINEES. Just like this:

TraineeID TSID ModuleName SessionDate InstructorID NoShow
8651354 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8762548 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8753545 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8651354 101-MAS-20-08 Furniture 101 8/20/2009 100015 Yes
8651354 102-MRA-30-08 Furniture 101 8/30/2009 100015 Yes


How can i make the query display ONLY the records of the TRAINEE(S) who have
3 or more NOSHOWS? just like this:

TraineeID TSID ModuleName SessionDate InstructorID NoShow
8651354 101-LVR-11-08 Furniture 101 8/11/2009 100015 Yes
8651354 101-MAS-20-08 Driving 101 8/20/2009 100015 Yes
8651354 101-MRA-30-08 Safety 101 8/30/2009 100015 Yes

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need more identification in the sub-query.

Probably like this:

SELECT R.TraineeID, R.TSID, S.ModuleName, S.SessionDate, S.InstructorID,
R.NoShow
FROM [Training Sessions] As S INNER JOIN [Training Record] As R ON
S.TSID=R.TSID
WHERE (SELECT COUNT(*)
FROM [Training Sessions] As S2 INNER JOIN [Training Record]
As R2 ON S2.TSID=R2.TSID
WHERE R2.NoShow=True
AND R2.TraineeID = R.TraineeID
) > 2

Your input and output ModuleNames don't match. If they are always the
same, but have different TSIDs, put

AND S2.ModuleName = S.ModuleName

in the subquery WHERE clause - after the TraineeID comparison.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSog6xoechKqOuFEgEQJ2+gCgv9HmG1TEIJzSt6qagyMGShzywRIAn3As
Rlf0dkqcHQeJ8nvueokGAJgc
=rEf1
-----END PGP SIGNATURE-----