DCOUNT

ghostman via AccessMonste · External Since: Aug 22, 2009 Posts: 7

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.

=DCount("[TSID]","Training Sessions","[TSID]")

how can i make it count the number of training sessions on the current month??

Training Session table:
TSID
ModuleName
SessionDate
(some fields..)

I am counting records from THIS MONTH & Year To Date. (I have no problem in
counting YTD, i can't get the THIS MONTH work - showing errors!)
Thanks.

--
Message posted via http://www.accessmonster.com

John Spencer · External Since: Jan 21, 2007 Posts: 135

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
'====================================================

ghostman via AccessMonster.com wrote:
> 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.
>
> =DCount("[TSID]","Training Sessions","[TSID]")
>
> how can i make it count the number of training sessions on the current month??
>
>
> Training Session table:
> TSID
> ModuleName
> SessionDate
> (some fields..)
>
> I am counting records from THIS MONTH & Year To Date. (I have no problem in
> counting YTD, i can't get the THIS MONTH work - showing errors!)
> Thanks.
>

ghostman via AccessMonste · External Since: Aug 22, 2009 Posts: 7

Perfect!!

thank you very much!

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 http://www.accessmonster.com

ghostman via AccessMonste · External Since: Aug 22, 2009 Posts: 7

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/Forums.aspx/access-gettingstarted/200908/1

John Spencer · External Since: Jan 21, 2007 Posts: 135

Assumption: NoShow is a Boolean (yes/no) field.

Count counts the presence of a value (True and False are both values) so
that is why you get the same number with Count(NoShow).

You can count the true values with either of the following expressions.

Abs(Sum(NoShow))

OR

COUNT(IIF(NoShow,1,Null))

So your query might look like the following. By the way, I moved the
criteria to a WHERE clause (faster performance) and I tested a date
range (again faster then testing 3 parts of the date).

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

, Abs(Sum(NoShow)) as CountNoShow

FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID = [Training Records].TSID
WHERE SessionDate Between DateSerial(Year(Date()),Month(Date()),1)
AND Date()
GROUP BY [Training Sessions].TSID
, [Training Sessions].ModuleName
, [Training Sessions].SessionDate
, [Training Sessions].SessionTimeFrom
, [Training Sessions].SessionTimeTo
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID
ORDER BY [Training Sessions].SessionDate DESC;

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

ghostman via AccessMonster.com wrote:
> 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.
>

ghostman via AccessMonste · External Since: Aug 22, 2009 Posts: 7

wow! that works like a charm!
thank you very much John!

John Spencer wrote:
>Assumption: NoShow is a Boolean (yes/no) field.
>
>Count counts the presence of a value (True and False are both values) so
>that is why you get the same number with Count(NoShow).
>
>You can count the true values with either of the following expressions.
>
>Abs(Sum(NoShow))
>
>OR
>
>COUNT(IIF(NoShow,1,Null))
>
>So your query might look like the following. By the way, I moved the
>criteria to a WHERE clause (faster performance) and I tested a date
>range (again faster then testing 3 parts of the date).
>
>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
>
>, Abs(Sum(NoShow)) as CountNoShow
>
>FROM [Training Sessions] INNER JOIN [Training Records]
>ON [Training Sessions].TSID = [Training Records].TSID
>WHERE SessionDate Between DateSerial(Year(Date()),Month(Date()),1)
>AND Date()
>GROUP BY [Training Sessions].TSID
>, [Training Sessions].ModuleName
>, [Training Sessions].SessionDate
>, [Training Sessions].SessionTimeFrom
>, [Training Sessions].SessionTimeTo
>, [Training Sessions].SessionVenue
>, [Training Sessions].InstructorID
>ORDER BY [Training Sessions].SessionDate DESC;
>
>'====================================================
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>'====================================================
>
>> how about counting the number of NOSHOWS on each training session?
>>
>[quoted text clipped - 86 lines]
>>>> counting YTD, i can't get the THIS MONTH work - showing errors!)
>>>> Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200908/1

ghostman via AccessMonste · External Since: Aug 22, 2009 Posts: 7

now i want to count training sessions NO SHOW occured in the CURRENT MONTH. I
want this to be in a textbox and ill put it in my form.

i am using this:

=DCount("[NoShow]","Training Records","[NoShow] = True")

and it counts all NOSHOWS in my record which includes also NOSHOWs from
previous months.

i have no idea how to do this by month...i want something the same as:

=DCount("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

which count Training Session on the current month.
please help..

here is my query SQL:

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, Abs
(Sum(NoShow)) AS CountNoShow
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;

ghostman wrote:
>wow! that works like a charm!
>thank you very much John!
>
>>Assumption: NoShow is a Boolean (yes/no) field.
>>
>[quoted text clipped - 49 lines]
>>>>> counting YTD, i can't get the THIS MONTH work - showing errors!)
>>>>> Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200908/1

John Spencer · External Since: Jan 21, 2007 Posts: 135

Perhaps something like the following. Eliminate group by fields to get
counts my groups. For instance if you need a count by instructor then
include instructor, but if you need the count by sessionvenue but not by
Session Venue plus instructor, remove instructor from the group by and
select clauses of the queryl

SELECT [Training Sessions].TSID
, [Training Sessions].ModuleName
, Format([Training Sessions].SessionDate,"YYYY-MM") as YearMonth
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID
, Count([Training Records].TraineeID) AS CountOfTraineeID
, Abs(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID=[Training Records].
GROUP BY [Training Sessions].TSID
, [Training Sessions].ModuleName
, Format([Training Sessions].SessionDate,"YYYY-MM")
, [Training Sessions].SessionVenue
, [Training Sessions].InstructorID

WHERE (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
<=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))

ORDER BY Format([Training Sessions].SessionDate,"YYYY-MM")

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

ghostman via AccessMonster.com wrote:
> now i want to count training sessions NO SHOW occured in the CURRENT MONTH. I
> want this to be in a textbox and ill put it in my form.
>
> i am using this:
>
> =DCount("[NoShow]","Training Records","[NoShow] = True")
>
> and it counts all NOSHOWS in my record which includes also NOSHOWs from
> previous months.
>
> i have no idea how to do this by month...i want something the same as:
>
> =DCount("TSID","[Training Sessions]","SessionDate between
> DateSerial(Year(Date()),Month(Date()),1) And
> DateSerial(Year(Date()),Month(Date())+1,0)")
>
>
> which count Training Session on the current month.
> please help..
>
> here is my query SQL:
>
> 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, Abs
> (Sum(NoShow)) AS CountNoShow
> 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;
>
>
>
> ghostman wrote:
>> wow! that works like a charm!
>> thank you very much John!
>>
>>> Assumption: NoShow is a Boolean (yes/no) field.
>>>
>> [quoted text clipped - 49 lines]
>>>>>> counting YTD, i can't get the THIS MONTH work - showing errors!)
>>>>>> Thanks.
>

ghostman via AccessMonste · External Since: Aug 22, 2009 Posts: 7

how can i apply this to a TEXTBOX?

i am adding a summary of the training on my main form (i.e., how many
training session conducted, trainees attended, training noshows, etc.,) so it
will give an instant info of the records.

This Month Year to Date
Training Sessions Conducted 01 05
Training NoShow 00 01

something like that.

on textbox1 (Training Sessions Conducted - This Month count) i have this:
=DCount("TSID","[Training Sessions]","SessionDate between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

while textbox2 (year to date count):
=DCount("[TSID]","Training Sessions","[TSID] = True")

for textbox3 (NOSHOW - Year To Date)
=DCount("[NoShow]","Training Records","[NoShow] = True")

and for textbox4 (NOSHOW - This Month), i tried this:
=DCount("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")

and showing ERROR!

John Spencer wrote:
>Perhaps something like the following. Eliminate group by fields to get
>counts my groups. For instance if you need a count by instructor then
>include instructor, but if you need the count by sessionvenue but not by
>Session Venue plus instructor, remove instructor from the group by and
>select clauses of the queryl
>
>SELECT [Training Sessions].TSID
>, [Training Sessions].ModuleName
>, Format([Training Sessions].SessionDate,"YYYY-MM") as YearMonth
>, [Training Sessions].SessionVenue
>, [Training Sessions].InstructorID
>, Count([Training Records].TraineeID) AS CountOfTraineeID
>, Abs(Sum(NoShow)) AS CountNoShow
>FROM [Training Sessions] INNER JOIN [Training Records]
>ON [Training Sessions].TSID=[Training Records].
>GROUP BY [Training Sessions].TSID
>, [Training Sessions].ModuleName
>, Format([Training Sessions].SessionDate,"YYYY-MM")
>, [Training Sessions].SessionVenue
>, [Training Sessions].InstructorID
>
>WHERE (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
><=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))
>
>ORDER BY Format([Training Sessions].SessionDate,"YYYY-MM")
>
>'====================================================
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>'====================================================
>
>> now i want to count training sessions NO SHOW occured in the CURRENT MONTH. I
>> want this to be in a textbox and ill put it in my form.
>[quoted text clipped - 40 lines]
>>>>>>> counting YTD, i can't get the THIS MONTH work - showing errors!)
>>>>>>> Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200908/1

John Spencer · External Since: Jan 21, 2007 Posts: 135

As I recall you NoShow field is a boolean - which means it always has a
value of True (yes) or False (no). Count and DCount both count the
presence of ANY value.

One way to do this is to apply one more bit of criteria in the DCount.

DCount("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0) AND CountNoShow = True")

Alternative is to use DSum instead of DCount. True is equal to -1 and
False is equal to zero. So summing all this will give you a negative
count of NoShow. You can strip the negative off by using the ABS function.

Abs(DSUM("CountNoShow","[Training Sessions]","CountNoShow between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)"))

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

ghostman via AccessMonster.com wrote:
> how can i apply this to a TEXTBOX?
>
> i am adding a summary of the training on my main form (i.e., how many
> training session conducted, trainees attended, training noshows, etc.,) so it
> will give an instant info of the records.
>
> This Month Year to Date
> Training Sessions Conducted 01 05
> Training NoShow 00 01
>
> something like that.
>
>
>
> on textbox1 (Training Sessions Conducted - This Month count) i have this:
> =DCount("TSID","[Training Sessions]","SessionDate between
> DateSerial(Year(Date()),Month(Date()),1) And
> DateSerial(Year(Date()),Month(Date())+1,0)")
>
> while textbox2 (year to date count):
> =DCount("[TSID]","Training Sessions","[TSID] = True")
>
>
> for textbox3 (NOSHOW - Year To Date)
> =DCount("[NoShow]","Training Records","[NoShow] = True")
>
> and for textbox4 (NOSHOW - This Month), i tried this:
> =DCount("CountNoShow","[Training Sessions]","CountNoShow between
> DateSerial(Year(Date()),Month(Date()),1) And
> DateSerial(Year(Date()),Month(Date())+1,0)")
>
> and showing ERROR!
>
>
>
> John Spencer wrote:
>> Perhaps something like the following. Eliminate group by fields to get
>> counts my groups. For instance if you need a count by instructor then
>> include instructor, but if you need the count by sessionvenue but not by
>> Session Venue plus instructor, remove instructor from the group by and
>> select clauses of the queryl
>>
>> SELECT [Training Sessions].TSID
>> , [Training Sessions].ModuleName
>> , Format([Training Sessions].SessionDate,"YYYY-MM") as YearMonth
>> , [Training Sessions].SessionVenue
>> , [Training Sessions].InstructorID
>> , Count([Training Records].TraineeID) AS CountOfTraineeID
>> , Abs(Sum(NoShow)) AS CountNoShow
>>FROM [Training Sessions] INNER JOIN [Training Records]
>> ON [Training Sessions].TSID=[Training Records].
>> GROUP BY [Training Sessions].TSID
>> , [Training Sessions].ModuleName
>> , Format([Training Sessions].SessionDate,"YYYY-MM")
>> , [Training Sessions].SessionVenue
>> , [Training Sessions].InstructorID
>>
>> WHERE (((Year([SessionDate]))=Year(Date())) AND ((Month([SessionDate]))
>> <=Month(Date())) AND ((Day([SessionDate]))<=Day(Date())))
>>
>> ORDER BY Format([Training Sessions].SessionDate,"YYYY-MM")
>>
>> '====================================================
>> John Spencer
>> Access MVP 2002-2005, 2007-2009
>> The Hilltop Institute
>> University of Maryland Baltimore County
>> '====================================================
>>
>>> now i want to count training sessions NO SHOW occured in the CURRENT MONTH. I
>>> want this to be in a textbox and ill put it in my form.
>> [quoted text clipped - 40 lines]
>>>>>>>> counting YTD, i can't get the THIS MONTH work - showing errors!)
>>>>>>>> Thanks.
>

ghostman via AccessMonste · External Since: Aug 22, 2009 Posts: 7

No luck with it sir... It shows error...

can you have a look on my query, please Smile

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, Abs
(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
TSID = [Training Records].TSID
WHERE ((([Training Sessions].SessionDate) Between DateSerial(Year(Date()),
Month(Date()),1) And Date()))
GROUP BY [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
.InstructorID
ORDER BY [Training Sessions].SessionDate DESC;

i am counting THIS MONTH numbers based on the above query.

John Spencer wrote:
>As I recall you NoShow field is a boolean - which means it always has a
>value of True (yes) or False (no). Count and DCount both count the
>presence of ANY value.
>
>One way to do this is to apply one more bit of criteria in the DCount.
>
>DCount("CountNoShow","[Training Sessions]","CountNoShow between
>DateSerial(Year(Date()),Month(Date()),1) And
>DateSerial(Year(Date()),Month(Date())+1,0) AND CountNoShow = True")
>
>Alternative is to use DSum instead of DCount. True is equal to -1 and
>False is equal to zero. So summing all this will give you a negative
>count of NoShow. You can strip the negative off by using the ABS function.
>
>Abs(DSUM("CountNoShow","[Training Sessions]","CountNoShow between
>DateSerial(Year(Date()),Month(Date()),1) And
>DateSerial(Year(Date()),Month(Date())+1,0)"))
>
>'====================================================
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>'====================================================
>
>> how can i apply this to a TEXTBOX?
>>
>[quoted text clipped - 64 lines]
>>>>>>>>> counting YTD, i can't get the THIS MONTH work - showing errors!)
>>>>>>>>> Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200908/1

John Spencer · External Since: Jan 21, 2007 Posts: 135

Simplify. Start off with something like this and see if you get no
errors and the number you want. If it errors then try to track down why.

SELECT Count([Training Records].TraineeID) AS CountOfTraineeID
, Abs(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID = [Training Records].TSID
WHERE [Training Sessions].SessionDate
Between DateSerial(Year(Date()),Month(Date()),1) And Date()

If you get no errors add in one or two fields and see if that works and
if you get the desired result. Continue this process until you have the
final desired outcome.

SELECT [Training Sessions].TSID
, [Training Sessions].ModuleName
, Count([Training Records].TraineeID) AS CountOfTraineeID
, Abs(Sum(NoShow)) AS CountNoShow
FROM [Training Sessions] INNER JOIN [Training Records]
ON [Training Sessions].TSID = [Training Records].TSID
WHERE [Training Sessions].SessionDate
Between DateSerial(Year(Date()),Month(Date()),1) And Date()
GROUP BY [Training Sessions].TSID
, [Training Sessions].ModuleName

That said, I'm not sure what this has to do with using DCount or DSum as
the source for a text box.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

ghostman via AccessMonster.com wrote:
> No luck with it sir... It shows error...
>
> can you have a look on my query, please Smile

>
> 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, Abs
> (Sum(NoShow)) AS CountNoShow
> FROM [Training Sessions] INNER JOIN [Training Records] ON [Training Sessions].
> TSID = [Training Records].TSID
> WHERE ((([Training Sessions].SessionDate) Between DateSerial(Year(Date()),
> Month(Date()),1) And Date()))
> GROUP BY [Training Sessions].TSID, [Training Sessions].ModuleName, [Training
> Sessions].SessionDate, [Training Sessions].SessionTimeFrom, [Training
> Sessions].SessionTimeTo, [Training Sessions].SessionVenue, [Training Sessions]
> .InstructorID
> ORDER BY [Training Sessions].SessionDate DESC;
>
> i am counting THIS MONTH numbers based on the above query.
>
>
> John Spencer wrote:
>> As I recall you NoShow field is a boolean - which means it always has a
>> value of True (yes) or False (no). Count and DCount both count the
>> presence of ANY value.
>>
>> One way to do this is to apply one more bit of criteria in the DCount.
>>
>> DCount("CountNoShow","[Training Sessions]","CountNoShow between
>> DateSerial(Year(Date()),Month(Date()),1) And
>> DateSerial(Year(Date()),Month(Date())+1,0) AND CountNoShow = True")
>>
>> Alternative is to use DSum instead of DCount. True is equal to -1 and
>> False is equal to zero. So summing all this will give you a negative
>> count of NoShow. You can strip the negative off by using the ABS function.
>>
>> Abs(DSUM("CountNoShow","[Training Sessions]","CountNoShow between
>> DateSerial(Year(Date()),Month(Date()),1) And
>> DateSerial(Year(Date()),Month(Date())+1,0)"))
>>
>> '====================================================
>> John Spencer
>> Access MVP 2002-2005, 2007-2009
>> The Hilltop Institute
>> University of Maryland Baltimore County
>> '====================================================
>>
>>> how can i apply this to a TEXTBOX?
>>>
>> [quoted text clipped - 64 lines]
>>>>>>>>>> counting YTD, i can't get the THIS MONTH work - showing errors!)
>>>>>>>>>> Thanks.
>