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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|