How do I count dates in a month?

[JOM]

I have a table named tracking with the following fields date and tasks. I
would like to count how many days in a month that the tasks occured. e.g. if
on the 05/05/05 3 tasks occured( task x, task y, task z), on the 05/06/05 2
tasks occured(task a, task y) etc I would like to count the report to say
total days reported = 2

 

[Lynn Trapp]

SELECT YourDate, Count(YourDate) AS CountOfDate
FROM YourTable
GROUP BY YourDate;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Van T. Dinh]

I interpret your question slightly different from Lynn's interpretation so I
come up with a different SQL.

Assuming that you have 3 Records for the same date 05/05/05 (one for each
task x, y & z), you need something like:

********
SELECT YearMonth, Count(DatesOfTask) As NoOfDatesWithTasksInMonth
FROM
(
SELECT DISTINCT DateOfTask,
Format(DateOfTask, "yyyy-mm") As YearMonth
FROM [Tracking]
)
GROUP BY YearMonth
********

HTH
Van T. Dinh
MVP (Access)

 

[JOM]

Thanks that was helpful, but this is what my reports is supposed to do

Month = May (Month group Level)
5/5/05 (date group level)
Task x
Task y
Task z
5/6/05
Task x
Task a

Total days reported in May = 2

Currently my in my report am having 5 days reported instead of 2

I interpret your question slightly different from Lynn's interpretation so I
come up with a different SQL.

Assuming that you have 3 Records for the same date 05/05/05 (one for each
task x, y & z), you need something like:

********
SELECT YearMonth, Count(DatesOfTask) As NoOfDatesWithTasksInMonth
FROM
(
SELECT DISTINCT DateOfTask,
Format(DateOfTask, "yyyy-mm") As YearMonth
FROM [Tracking]
)
GROUP BY YearMonth
********

HTH
Van T. Dinh
MVP (Access)

I have a table named tracking with the following fields date and tasks. I
would like to count how many days in a month that the tasks occured. e.g. if
on the 05/05/05 3 tasks occured( task x, task y, task z), on the 05/06/05 2
tasks occured(task a, task y) etc I would like to count the report to say
total days reported = 2

 

[Van T. Dinh]

In Report, IIRC:

* Add a Calculated TextBox "txtCountDay" in the "Date Group Level" Footer
and set its ControlSource to:

= 1

(including the equal sign). Set its Visible Property to False.

* Add a Calculated TextBox in the "Month Group Level" Footer and set its
ControlSource to:

= Sum([txtCountDay])

This should give you the number of days with Tasks in the month.

HTH
Van T. Dinh
MVP (Access)

 

[JOM]

I tried as you said, but the = Sum([txtCountDay]) part does not work, when I
run the report, a the txtCountDay textbox pops up and requires me to input
data...
I appreaciate your help thanks...

In Report, IIRC:

* Add a Calculated TextBox "txtCountDay" in the "Date Group Level" Footer
and set its ControlSource to:

= 1

(including the equal sign). Set its Visible Property to False.

* Add a Calculated TextBox in the "Month Group Level" Footer and set its
ControlSource to:

= Sum([txtCountDay])

This should give you the number of days with Tasks in the month.

HTH
Van T. Dinh
MVP (Access)

Thanks that was helpful, but this is what my reports is supposed to do

Month = May (Month group Level)
5/5/05 (date group level)
Task x
Task y
Task z
5/6/05
Task x
Task a

Total days reported in May = 2

Currently my in my report am having 5 days reported instead of 2

 

[Van T. Dinh]

OK. Try another method:

* Still use the same txtCountDay but set its RunningSum Property to "Over
Group"

* Set the ControlSource of the TextBox in the "Month Group Level" Footer to:

= [txtCountDay]

Set the Visible of this TextBox to Yes / True.

HTH
Van T. Dinh
MVP (Access)

 

[JOM]

Thanks alot that worked perfect!
JOM!

OK. Try another method:

* Still use the same txtCountDay but set its RunningSum Property to "Over
Group"

* Set the ControlSource of the TextBox in the "Month Group Level" Footer to:

= [txtCountDay]

Set the Visible of this TextBox to Yes / True.

HTH
Van T. Dinh
MVP (Access)

I tried as you said, but the = Sum([txtCountDay]) part does not work, when I
run the report, a the txtCountDay textbox pops up and requires me to input
data...
I appreaciate your help thanks...