A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

DCOUNT



 
 
Thread Tools Display Modes
  #1  
Old August 22nd, 2009, 01:35 PM posted to microsoft.public.access.gettingstarted
ghostman via AccessMonster.com
external usenet poster
 
Posts: 45
Default DCOUNT

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

  #2  
Old August 22nd, 2009, 02:45 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 2,364
Default DCOUNT

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.

  #3  
Old August 23rd, 2009, 05:44 AM posted to microsoft.public.access.gettingstarted
ghostman via AccessMonster.com
external usenet poster
 
Posts: 45
Default DCOUNT

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

  #4  
Old August 23rd, 2009, 07:08 AM posted to microsoft.public.access.gettingstarted
ghostman via AccessMonster.com
external usenet poster
 
Posts: 45
Default DCOUNT

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

  #5  
Old August 23rd, 2009, 06:41 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 2,364
Default DCOUNT

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.


  #6  
Old August 24th, 2009, 06:05 AM posted to microsoft.public.access.gettingstarted
ghostman via AccessMonster.com
external usenet poster
 
Posts: 45
Default DCOUNT

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/For...arted/200908/1

  #7  
Old August 29th, 2009, 07:14 AM posted to microsoft.public.access.gettingstarted
ghostman via AccessMonster.com
external usenet poster
 
Posts: 45
Default DCOUNT

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/For...arted/200908/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:51 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.