Counting Current Ongoing Activities at a specific date

[usheikh]

Hi,

I am trying to calculate the number of activities taking place at any given date. There is an ActivityStartDate and an ActivityEndDate for every activity. I want to be able to calculate the number of activities taking place every day.

Any sugguestions? Are there any modules out there that can help?

Thanks!

 

[usheikh]

by the way both the start and finish dates are always known for each activity. so basicially i just want to know how many activities are in progress on a given date.

 

[Remou]

A single specific date:
[tt]SELECT tblActivities.Activity, tblActivities.StartDate, tblActivities.EndDate
FROM tblActivities
WHERE (((tblActivities.StartDate)<=[forms]![frmForm]![txtDate]) AND ((tblActivities.EndDate)>=[forms]![frmForm]![txtDate]));[/tt]

Or something more complicated?

 

[usheikh]

Many thanks ... Good query. It returns the list of activities for a given date and works. However, instead of a list, is it possible to return just the number of activities? So if there were two activites on that day then it would return just 2.

 

[Remou]

Perhaps:
[tt]SELECT Count(*) AS CountOfActivities
FROM tblActivities
WHERE (((tblActivities.StartDate)<=[forms]![frmForm]![txtDate]) AND ((tblActivities.EndDate)>=[forms]![frmForm]![txtDate]));[/tt]

 

[usheikh]

THANKS! thats great. I came up with this and its similar to what you stated:

SELECT Count(DCount("ID","tblRequest","[Booking Ref]")) AS [No of Projects]
FROM tblRequest
WHERE (((tblRequest.[Start Date Requested])<=[forms]![frmForm]![txtDate]) AND ((tblRequest.[End Date Requested])>=[forms]![frmForm]![txtDate]));

Is it possible to see the whole month at once broken down day by day as opposed to just one total for the day? How can I utilise the query above to do this? Therefore, the query would output the count of each day of a given month. I didn't think this was possible but I heard this is possible by using a crosstab query? Any guidance would be appreciated. Thanks.

 

[Remou]

Perhaps you could create a calendar table with a date for each day. Let us call this tblCalendar. Then you could say:
[tt]SELECT tblCalendar.DayDate, Sum(IIf([startdate]<=[DayDate] And [enddate]>=[DayDate],1,0)) AS CountOfActivities
FROM tblCalendar, tblActivities
GROUP BY tblCalendar.DayDate;[/tt]

 

[usheikh]

thanks ill give it a try