List each day in the SELECT clause of Access query

Wednesday, July 9, 2014

I have an Access database with the following table.

ID : Autonumber
dtmDateOpen : Date/Time (date call logged)
dtmDateClosed : Date/Time (date call closed)
dtmTime : Date/Time (time call logged)
strStatus (always 'Open', 'Pending' or 'Closed')
strCategory (always one of 10 categories, held as as list in tblCatgory, and used in lookup lists in the ASP web page)
dtmDatePending : Date/Time (date call set to pending, if it ever was)

Calls come in as Open. If user sets to pending, the dtmDatePending is populated with the current date.

If the user sets the call to Closed when it's completed, the dtmDateClosed is populated with the current date.

Not all calls are set to pending.

some days, users do not change status of calls because they are doing other things. Also, no activity at weekends.

I want to monitor the call ownder's activity.

I have a query as below (in a classic ASP within a loop which goes through each user, variable strCallOwner, dates are set by the user in previous page)

SELECT dtmDateClosed, Count(ID) AS CallsClosed
FROM tblFaultCall "
WHERE strCallOwner = """ & strCallOwner & """
GROUP BY dtmDateClosed
HAVING dtmDateClosed BETWEEN #"& FromMonth & "/" & FromDay & "/" & FromYear &"# and #"& ToMonth & "/" & ToDay & "/" & ToYear &"#

This is fine, and gives me output such as below

Day Calls Closed
09/04/2014 4
15/04/2014 4
16/04/2014 2
25/04/2014 1
30/04/2014 1
01/05/2014 7

We need to clearly see where users have not done any activity. So, I want to be able to show every day in the period in the HAVING clause (even where no calls Closed that day, and if its a weekend, I want something in the results to indicate it's a weekend day). I also want an additional column for number of calls pended based on dtmDatePending.

I want it to look something like this

Day Calls Closed Calls Pended
Wed 09/04/14 4 0
Thu 10/04/14 0 0
Fri 11/04/14 0 1
Sat 12/04/14 - -
Sun 13/04/14 - -
Mon 14/04/14 0 1
Tue 15/04/14 0 0
Wed 16/04/14 2 1

I am populating a table in the ASP, but really it's the query that I'm stuck with.

I know I need outer joins for the second and third columns, but I need to know how to create a list of days for the first column. I not know if this is possible in an Access query? I may have to use an array in the ASP? Or create an additional table in the database?