how about counting the number of NOSHOWS on each training session?

I have two tables:

[Training Sessions] (the details of a training session)
- TSID (pk)
- ModuleName
- SessionDate
- SessionTimeFrom
- SessionTimeTo
- SessionVenue
- InstructorID

[Training Records] (holds the records of trainees who attended the training
session)
- ID
- TSID (fk)
- TraineeID
- NTSMonth
- NTSModule
- NoShow

then i created a query using the above

qryTrainingRecordsNoShow

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


the query should be counting something like this:

TSID ModuleName SessionDate CountOfTraineeID CountOfNoShow
401-MAS-18-08 Life Guards 401 8/18/2009 2 1
101-MAS-05-08 Attendant 201 8/5/2009 1 0

- CountOfTraineeID shows how many trainees attended
- CountOfNoShow should be showing how many trainees failed to attend a
session

What i've got so far is a query showing like this:

TSID ModuleName SessionDate CountOfTraineeID CountOfNoShow
401-MAS-18-08 Life Guards 401 8/18/2009 2 2
101-MAS-05-08 Attendant 201 8/5/2009 1 1

- CountOfNoShow is counting the same number as CountOfTraineeID...

im stuck with this...pls help!



John Spencer wrote:
For the current month, you can use the following expression:
DCOUNT("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

For Last Month the third argument would be
"SessionDate between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date())+1-1,0)"

The expression
DateSerial(Year(Date()),Month(Date())+1,0)
returns the last day of the month. It helps to think of this as
DateSerial(Year(Date()),Month(Date())+1,1) -1
which can be expressed as
DateSerial(Year(Date()),Month(Date())+1,1-1)
and 1-1 is zero.

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

i have this DCOUNT formula on a text box which counts number of training
sessions in my record. The text box is in my main form.
[quoted text clipped - 12 lines]
counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1