Yes/No

[ghostman via AccessMonster.com]

I have this SQL that display training NO SHOWs.

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

The query should only display trainees with more than 3 NO SHOWs: but as you
can see on the below table, it is also showing less than 3 NO SHOWs...

TraineeID TSID ModuleName SessionDate CountOfNoShow
8765124 101-LVR-16-07 Janitorial 101 7/16/2009 1
8765124 201-MRA-05-08 Attendant 201 8/5/2009 3
8765124 401-MAS-18-08 Life Guards 401 8/18/2009 3

how can i make it display trainees with 3 or more NO SHOWS???

 

[John Spencer]

You are checking whether of not a trainee has 3 no shows. You are not
checking if the trainee had 3 no shows in a specific module.

If you want the latter, then you might need to do something like the
following:


SELECT [Training Records].TraineeID
, Count([Training Records].NoShow) AS CountOfNoShow
, [Training Sessions].TSID
, [Training Sessions].ModuleName
, [Training Sessions].SessionDate
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID = [Training Records].TSID

GROUP BY [Training Records].TraineeID
, [Training Sessions].TSID
, [Training Sessions].ModuleName
, [Training Sessions].SessionDate
HAVING Abs(Sum(NoShow)) >=3

The possible problem with that is that the count of no shows is for the
combination of TraineeID, ModuleName, TSID, and Sessiondate. IN other
words the trainee must have 3 no shows on the same date for the same module.

Perhaps you can explain exactly what you are trying to achieve.


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

 

[ghostman via AccessMonster.com]

thanks for the reply...and, your right!

Actually, what it should show is the trainees having more than 3 NOSHOWs in
NO particular date or training module. Because in the report for example, it
should display:

TraineeID Name
8765124 SomeName
TSID ModuleName
SessionDate NoShow
101-LVR-16-07 Janitorial 101
7/16/2009 Yes
201-MRA-05-08 Attendant 201 8/5/2009
Yes
401-MAS-18-08 LifeGuard 401 8/18/2009
Yes

The report displays the trainee with 3 NoShows and under it, lists the
modules he failed to attend to.
hope this helps...

You are checking whether of not a trainee has 3 no shows. You are not
checking if the trainee had 3 no shows in a specific module.

If you want the latter, then you might need to do something like the
following:

SELECT [Training Records].TraineeID
, Count([Training Records].NoShow) AS CountOfNoShow
, [Training Sessions].TSID
, [Training Sessions].ModuleName
, [Training Sessions].SessionDate
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID = [Training Records].TSID

GROUP BY [Training Records].TraineeID
, [Training Sessions].TSID
, [Training Sessions].ModuleName
, [Training Sessions].SessionDate
HAVING Abs(Sum(NoShow)) >=3

The possible problem with that is that the count of no shows is for the
combination of TraineeID, ModuleName, TSID, and Sessiondate. IN other
words the trainee must have 3 no shows on the same date for the same module.

Perhaps you can explain exactly what you are trying to achieve.

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

I have this SQL that display training NO SHOWs.

[quoted text clipped - 19 lines]

how can i make it display trainees with 3 or more NO SHOWS???

 

[ghostman via AccessMonster.com]

i cleared the SQL with this one: (no criterias)

SELECT [Training Records].TraineeID, [Training Records].NoShow, [Training
Sessions].TSID, [Training Sessions].ModuleName, [Training Sessions].
SessionDate
FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
TSID = [Training Records].TSID;

thanks for the reply...and, your right!

Actually, what it should show is the trainees having more than 3 NOSHOWs in
NO particular date or training module. Because in the report for example, it
should display:

TraineeID Name
8765124 SomeName
TSID ModuleName
SessionDate NoShow
101-LVR-16-07 Janitorial 101
7/16/2009 Yes
201-MRA-05-08 Attendant 201 8/5/2009
Yes
401-MAS-18-08 LifeGuard 401 8/18/2009
Yes

The report displays the trainee with 3 NoShows and under it, lists the
modules he failed to attend to.
hope this helps...

You are checking whether of not a trainee has 3 no shows. You are not
checking if the trainee had 3 no shows in a specific module.

[quoted text clipped - 34 lines]

 

[AccessVandal via AccessMonster.com]

Sometimes the criteria may not work this way. Since your NoShow is boolean
(assumption)

"AND ((Count([Training Records].NoShow))=True))"

Have you change it to something like

AND (([Training Records].NoShow)=True)) AND CountOfNoShow >= 3

or

AND ((Count([Training Records].NoShow))>=3)) AND [Training Records].
NoShow=True"

You have a criteria for the boolean NoShow but you don't have a criteria for
the count. You probably need another criteria for this?

I have this SQL that display training NO SHOWs.

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

The query should only display trainees with more than 3 NO SHOWs: but as you
can see on the below table, it is also showing less than 3 NO SHOWs...

TraineeID TSID ModuleName SessionDate CountOfNoShow
8765124 101-LVR-16-07 Janitorial 101 7/16/2009 1
8765124 201-MRA-05-08 Attendant 201 8/5/2009 3
8765124 401-MAS-18-08 Life Guards 401 8/18/2009 3

how can i make it display trainees with 3 or more NO SHOWS???

 

[AccessVandal via AccessMonster.com]

Sorry typos! remove the parenthesis.

AND ((Count([Training Records].NoShow))>=3 AND [Training Records].
NoShow=True"

You'll need to correct the sql syntax just in case the above is still wrong.

I have this SQL that display training NO SHOWs.

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

The query should only display trainees with more than 3 NO SHOWs: but as you
can see on the below table, it is also showing less than 3 NO SHOWs...

TraineeID TSID ModuleName SessionDate CountOfNoShow
8765124 101-LVR-16-07 Janitorial 101 7/16/2009 1
8765124 201-MRA-05-08 Attendant 201 8/5/2009 3
8765124 401-MAS-18-08 Life Guards 401 8/18/2009 3

how can i make it display trainees with 3 or more NO SHOWS???

 

[ghostman via AccessMonster.com]

i played with the SQL (of course based on your suggestions) and i got lucky! :
)

SELECT [Training Records].TraineeID, Count([Training Records].NoShow) AS
CountOfNoShow, [Training Sessions].TSID, [Training Sessions].ModuleName,
[Training Sessions].SessionDate, Count([Training Records].TraineeID) AS
CountOfTraineeID, Trainees.LastName, Trainees.ContractorName, Trainees.
FirstName, [Training Records].NoShow
FROM [Training Sessions] INNER JOIN (Trainees INNER JOIN [Training Records]
ON Trainees.TraineeID = [Training Records].TraineeID) ON [Training Sessions].
TSID = [Training Records].TSID
GROUP BY [Training Records].TraineeID, [Training Sessions].TSID, [Training
Sessions].ModuleName, [Training Sessions].SessionDate, Trainees.LastName,
Trainees.ContractorName, Trainees.FirstName, [Training Records].NoShow
HAVING ((([Training Records].TraineeID) In (SELECT TraineeID FROM [Training
Records] WHERE NoShow = True GROUP BY TraineeID HAVING COUNT (*) > 2)) AND (
(Count([Training Records].NoShow))=True));

i know the code can be shorten but it's working the way it should be...

thanks guys! another problem solved!

Sorry typos! remove the parenthesis.

AND ((Count([Training Records].NoShow))>=3 AND [Training Records].
NoShow=True"

You'll need to correct the sql syntax just in case the above is still wrong.

I have this SQL that display training NO SHOWs.

[quoted text clipped - 19 lines]

how can i make it display trainees with 3 or more NO SHOWS???

 

[AccessVandal via AccessMonster.com]

That's good to hear, Thanks for the feedback.

i played with the SQL (of course based on your suggestions) and i got lucky! :
)

SELECT [Training Records].TraineeID, Count([Training Records].NoShow) AS
CountOfNoShow, [Training Sessions].TSID, [Training Sessions].ModuleName,
[Training Sessions].SessionDate, Count([Training Records].TraineeID) AS
CountOfTraineeID, Trainees.LastName, Trainees.ContractorName, Trainees.
FirstName, [Training Records].NoShow
FROM [Training Sessions] INNER JOIN (Trainees INNER JOIN [Training Records]
ON Trainees.TraineeID = [Training Records].TraineeID) ON [Training Sessions].
TSID = [Training Records].TSID
GROUP BY [Training Records].TraineeID, [Training Sessions].TSID, [Training
Sessions].ModuleName, [Training Sessions].SessionDate, Trainees.LastName,
Trainees.ContractorName, Trainees.FirstName, [Training Records].NoShow
HAVING ((([Training Records].TraineeID) In (SELECT TraineeID FROM [Training
Records] WHERE NoShow = True GROUP BY TraineeID HAVING COUNT (*) > 2)) AND (
(Count([Training Records].NoShow))=True));

i know the code can be shorten but it's working the way it should be...

thanks guys! another problem solved!