count record

[ghostman via AccessMonster.com]

i have this SQL on my query that counts the number of trainees (attendees) on
each Training Session (TSID)...it shows the right numbers..but when it comes
to counting the number of NO-SHOWS (failed to attend), it shows the same
number as the number of attendees. (I have 2 NO SHOWS on my record)

can someone have a look to the code and tell me why the NO SHOW field is not
counting the right number and suggest at solution. im not familiar with SQL.

Thanks!

SQL:

SELECT Count([Training Records].TraineeID) AS CountOfTraineeID, [Training
Records].TSID, Count([Training Records].NoShow) AS CountOfNoShow, [Training
Sessions].ModuleName, [Training Sessions].SessionDate, [Training Sessions].
SessionTimeFrom, [Training Sessions].SessionTimeTo, [Training Sessions].
SessionVenue, [Training Sessions].InstructorID
FROM [Training Records] INNER JOIN [Training Sessions] ON [Training Records].
TSID = [Training Sessions].TSID
GROUP BY [Training Records].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID
HAVING (((Count([Training Records].NoShow))=True));

 

[KARL DEWEY]

Try dropping the 'HAVING' statement.

 

[ghostman via AccessMonster.com]

i did removed the HAVING statement, but nothing change:

SELECT Count([Training Records].TraineeID) AS CountOfTraineeID, [Training
Records].TSID, Count([Training Records].NoShow) AS CountOfNoShow, [Training
Sessions].ModuleName, [Training Sessions].SessionDate, [Training Sessions].
SessionTimeFrom, [Training Sessions].SessionTimeTo, [Training Sessions].
SessionVenue, [Training Sessions].InstructorID
FROM [Training Records] INNER JOIN [Training Sessions] ON [Training Records].
TSID = [Training Sessions].TSID
GROUP BY [Training Records].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID;


Aside from counting the number of trainees in each training session, it
should also count the NO SHOW record on each training session.

here's the query looks like:
TSID CountOfTraineeID ModuleName SessionDate InstructorID
CountOfNoShow
101-LVR-11-08 5 Furniture 101 8/11/2009 100015 5
101-MAS-12-08 2 Janitorial 101 8/12/2009 100021 2

i have 2 NO SHOWS in session 101-LVR-11-08 and 1 on 101-MAS-12-08 on my
record.

Try dropping the 'HAVING' statement.

i have this SQL on my query that counts the number of trainees (attendees) on
each Training Session (TSID)...it shows the right numbers..but when it comes

[quoted text clipped - 20 lines]

.InstructorID
HAVING (((Count([Training Records].NoShow))=True));

 

[AccessVandal via AccessMonster.com]

Not sure it will work for you...Try

HAVING (((Count(CountOfNoShow))>=1));

or

HAVING (((Count([Training Records].NoShow))>=1));

i did removed the HAVING statement, but nothing change:

SELECT Count([Training Records].TraineeID) AS CountOfTraineeID, [Training
Records].TSID, Count([Training Records].NoShow) AS CountOfNoShow, [Training
Sessions].ModuleName, [Training Sessions].SessionDate, [Training Sessions].
SessionTimeFrom, [Training Sessions].SessionTimeTo, [Training Sessions].
SessionVenue, [Training Sessions].InstructorID
FROM [Training Records] INNER JOIN [Training Sessions] ON [Training Records].
TSID = [Training Sessions].TSID
GROUP BY [Training Records].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID;

Aside from counting the number of trainees in each training session, it
should also count the NO SHOW record on each training session.

here's the query looks like:
TSID CountOfTraineeID ModuleName SessionDate InstructorID
CountOfNoShow
101-LVR-11-08 5 Furniture 101 8/11/2009 100015 5
101-MAS-12-08 2 Janitorial 101 8/12/2009 100021 2

i have 2 NO SHOWS in session 101-LVR-11-08 and 1 on 101-MAS-12-08 on my
record.

Try dropping the 'HAVING' statement.

i have this SQL on my query that counts the number of trainees (attendees) on
each Training Session (TSID)...it shows the right numbers..but when it comes

[quoted text clipped - 20 lines]

.InstructorID
HAVING (((Count([Training Records].NoShow))=True));

 

[ghostman via AccessMonster.com]

nope...it didn't work!

counting Trainees per training is working fine, but not the number of noshows.
..

can i do it separately? i mean 1 query to do the counting of trainees per
session and another for noshows per session...Any comments?


SELECT Count([Training Records].TraineeID) AS CountOfTraineeID, [Training
Records].TSID, [Training Sessions].ModuleName, [Training Sessions].
SessionDate, [Training Sessions].SessionTimeFrom, [Training Sessions].
SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions].
InstructorID
FROM [Training Records] INNER JOIN [Training Sessions] ON [Training Records].
TSID = [Training Sessions].TSID
GROUP BY [Training Records].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID;

Not sure it will work for you...Try

HAVING (((Count(CountOfNoShow))>=1));

or

HAVING (((Count([Training Records].NoShow))>=1));

i did removed the HAVING statement, but nothing change:

[quoted text clipped - 28 lines]

.InstructorID
HAVING (((Count([Training Records].NoShow))=True));

 

[AccessVandal via AccessMonster.com]

Yes, you try it with a query not sure it with work though.

I’m not sure about the structure of your tables and it’s relationships and
the datatype for NoShow. So can’t help much unless someone has more
experience in your setup.

Which is the primary key TSID in Training Records and Training Sessions and
are they index? (Yes Dulplicates or No Dulplicates or just No)

Yes, you try it with a query not sure it with work though.

I’m not sure about the structure of your tables and it’s relationships and
the datatype for NoShow. So can’t help much unless someone has more
experience in your setup.

Which is the primary key TSID in Training Records and Training Sessions and
are they index? (Yes Dulplicates or No Dulplicates or just No)

Have you tried with just one table "Training Records" using Group By
something like….

SELECT [Training Records].TraineeID, Count([Training Records].NoShow) AS
CountOfNoShow
FROM [Training Records]
GROUP BY [Training Records].TraineeID
HAVING (((Count([Training Records].NoShow))=True));

nope...it didn't work!
counting Trainees per training is working fine, but not the number of noshows.
can i do it separately? i mean 1 query to do the counting of trainees per
session and another for noshows per session...Any comments?

SELECT Count([Training Records].TraineeID) AS CountOfTraineeID, [Training
Records].TSID, [Training Sessions].ModuleName, [Training Sessions].
SessionDate, [Training Sessions].SessionTimeFrom, [Training Sessions].
SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions].
InstructorID
FROM [Training Records] INNER JOIN [Training Sessions] ON [Training Records].
TSID = [Training Sessions].TSID
GROUP BY [Training Records].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID;

 

[ghostman via AccessMonster.com]

let me try it then ill post back..
thanks!

Yes, you try it with a query not sure it with work though.

I’m not sure about the structure of your tables and it’s relationships and
the datatype for NoShow. So can’t help much unless someone has more
experience in your setup.

Which is the primary key TSID in Training Records and Training Sessions and
are they index? (Yes Dulplicates or No Dulplicates or just No)

Yes, you try it with a query not sure it with work though.

I’m not sure about the structure of your tables and it’s relationships and
the datatype for NoShow. So can’t help much unless someone has more
experience in your setup.

Which is the primary key TSID in Training Records and Training Sessions and
are they index? (Yes Dulplicates or No Dulplicates or just No)

Have you tried with just one table "Training Records" using Group By
something like….

SELECT [Training Records].TraineeID, Count([Training Records].NoShow) AS
CountOfNoShow
FROM [Training Records]
GROUP BY [Training Records].TraineeID
HAVING (((Count([Training Records].NoShow))=True));

nope...it didn't work!
counting Trainees per training is working fine, but not the number of noshows.

[quoted text clipped - 12 lines]

Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID;