title: List each day in the SELECT clause of Access query
body: <p>I have an Access database with the following table.</p>
  
  <pre><code>tblFaultCall
  ------------
  ID : Autonumber
  strName
  strPhone
  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)
  strFaultDesc 
  strResolution
  strCallOwner
  dtmDatePending : Date/Time (date call set to pending, if it ever was)
  </code></pre>
  
  <p>Calls come in as Open. If user sets to pending, the dtmDatePending is populated with the current date.<br>
  If the user sets the call to Closed when it's completed, the dtmDateClosed is populated with the current date.<br>
  Not all calls are set to pending.<br>
  some days, users do not change status of calls because they are doing other things. Also, no activity at weekends. </p>
  
  <p>I want to monitor the call ownder's activity. </p>
  
  <p>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)</p>
  
  <pre><code>SELECT dtmDateClosed, Count(ID) AS CallsClosed 
  FROM tblFaultCall " 
  WHERE strCallOwner = """ &amp; strCallOwner &amp; """ 
  GROUP BY dtmDateClosed 
  HAVING dtmDateClosed BETWEEN  #"&amp; FromMonth &amp; "/" &amp; FromDay &amp; "/" &amp; FromYear &amp;"# and #"&amp; ToMonth &amp; "/" &amp; ToDay &amp; "/" &amp; ToYear &amp;"#  
  </code></pre>
  
  <p>This is fine, and gives me output such as below</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I want it to look something like this</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am populating a table in the ASP, but really it's the query that I'm stuck with.</p>
  
  <p>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?</p>
  
  <p>Thanks.</p>
  
  <p>Edit - Thanks to the responses thus far, I have got a working version which gives me the CallsClosed, as below</p>
  
  <pre><code>SELECT d.dtmDate, Count(fc.ID) AS CallsClosed
  FROM [select distinct dtmDateClosed as dtmDate
        from tblFaultCall
        where dtmDateClosed BETWEEN #5/20/14# and #5/30/14#
       ]. AS d LEFT JOIN [select fc.*
        from tblFaultCall as fc
        where fc.strCallOwner = 'Ben Hamilton'
       ]. AS fc ON fc.dtmDateClosed = d.dtmDate
  GROUP BY d.dtmDate
  ORDER BY d.dtmDate;
  </code></pre>
  
  <p>This reports</p>
  
  <pre><code>dtmDate     CallsClosed
  20/05/2014  5
  21/05/2014  16
  22/05/2014  0
  23/05/2014  5
  27/05/2014  0
  28/05/2014  5
  29/05/2014  2
  30/05/2014  2
  </code></pre>
  
  <p>These numbers are correct as I've cross checked.</p>
  
  <p>However, I have tried to amend this to include another column for pended cases, as below</p>
  
  <pre><code>SELECT d.dtmDate, Count(fc.ID) AS CallsClosed, COUNT(fp.ID) AS CallsPended
  FROM ([select distinct dtmDateClosed as dtmDate
        from tblFaultCall
        where dtmDateClosed BETWEEN #5/20/14# and #5/30/14#
       ]. AS d LEFT JOIN [select fc.*
        from tblFaultCall as fc
        where fc.strCallOwner = 'Ben Hamilton' AND fc.strStatus = 'Closed'
       ]. AS fc ON fc.dtmDateClosed = d.dtmDate) LEFT JOIN [SELECT fp.ID, fp.dtmDatePending
  FROM tblFaultCall AS fp 
  WHERE fp.strCallOwner = 'Ben Hamilton' ]. AS fp ON fp.dtmDatePending = d.dtmDate
  GROUP BY d.dtmDate
  ORDER BY d.dtmDate;
  </code></pre>
  
  <p>This runs, but for some reason reports incorrect figures :- </p>
  
  <pre><code>dtmDate     CallsClosed CallsPended
  20/05/2014  4           0
  21/05/2014  16          0
  22/05/2014  0           0
  23/05/2014  15          15
  27/05/2014  0           0
  28/05/2014  20          20
  29/05/2014  2           0
  30/05/2014  2           0
  </code></pre>
  
  <p>For 23/05/14, it should report 5 Closed calls, and 3 pending. Other days are wrong too.</p>
  
  <p>Edit 11/07/14 </p>
  
  <p>The SQL provided by @Gord Thompson works well. But I am struggling with the parameter query. Part of the problem is that I am looping through another recordset containing list of users;</p>
  
  <pre><code>&lt;%@ Language="VBScript" %&gt;
  &lt;%' Option Explicit %&gt;  
  &lt;HTML&gt;
  &lt;HEAD&gt;
  &lt;title&gt;Fault Call - User Activity Stats&lt;/title&gt;
  &lt;/HEAD&gt;
  &lt;BODY&gt;
  &lt;% 
  
  Dim FCConnString, FCConn
  
  FCConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" &amp; _ 
      "Data Source=" &amp; Server.MapPath("/bactu/spoc/db/FaultCall.mdb") 
  
  set FCConn = CreateObject("ADODB.Connection") 
  FCConn.Open(FCConnString)
  
  Dim RSUserDays, SQLUserDays, cmdUserDays 'As ADODB.Command
  Dim FromDay, FromMonth, FromTo, ToDay, ToMonth, ToYear
  Dim RSUserList, SQLUserList, strCallOwner
  Const adParamInput = 1
  Const adDBDate = 133
  const adVarWChar = 202
  
  FromDay = Request.QueryString("from_day")
  FromMonth = Request.QueryString("from_month")
  FromYear = Request.QueryString("from_year")
  
  ToDay = Request.QueryString("to_day")
  ToMonth = Request.QueryString("to_month")
  ToYear = Request.QueryString("to_year")
  
  SQLUserList = "SELECT strCallOwner FROM tblCallOwner WHERE ID in (6, 12, 20, 21, 28, 30)"
  
  SQLUserDays = "SELECT d.dtmDate, fc.ClosedCount, fp.PendingCount " &amp; _
          "FROM " &amp; _
              "( " &amp; _
                  "( " &amp; _
                      "SELECT DISTINCT dtmDateOpen AS dtmDate " &amp; _
                      "FROM tblFaultCall " &amp; _
                      "WHERE dtmDateOpen BETWEEN ? AND ? " &amp; _
                  ") AS d " &amp; _
                  "LEFT JOIN " &amp; _
                  "( " &amp; _
                      "SELECT dtmDateClosed, COUNT(*) AS ClosedCount " &amp; _
                      "FROM tblFaultCall " &amp; _
                      "WHERE strCallOwner = ? " &amp; _
                      "GROUP BY dtmDateClosed " &amp; _
                  ") AS fc ON d.dtmDate = fc.dtmDateClosed " &amp; _
              ") " &amp; _
              "LEFT JOIN " &amp; _
              "( " &amp; _
                  "SELECT dtmDatePending, COUNT(*) AS PendingCount " &amp; _
                  "FROM tblFaultCall " &amp; _
                  "WHERE strCallOwner = ? " &amp; _
                  "GROUP BY dtmDatePending " &amp; _
              ") AS fp ON d.dtmDate = fp.dtmDatePending"
  
  'Create and Open Recordset for UserList
  Set RSUserList = Server.CreateObject("ADODB.Recordset")
  RSUserList.Open SQLUserList, FCConn, 3, 3
  
  'create ADODB.Command object cmdUserDays and set CommandText, ActiveConnection properties
  Set cmdUserDays = CreateObject("ADODB.Command")
  cmdUserDays.ActiveConnection = FCConn
  cmdUserDays.CommandText = SQLUserDays
  
  'Append cmdUserDays parameters - CreateParameter(Name, Type, Direction, SizeIfString, Value)
  cmdUserDays.Parameters.Append cmdUserDays.CreateParameter("QueryFromDate", adDBDate, adParamInput, , DateSerial(FromYear, FromMonth, FromDay))
  cmdUserDays.Parameters.Append cmdUserDays.CreateParameter("QueryToDate", adDBDate, adParamInput, , DateSerial(ToYear, ToMonth, ToDay))
  
  'we do not know the strCallOwner yet, but must assign, so default value of "Nothing")
  cmdUserDays.Parameters.Append cmdUserDays.CreateParameter("QueryCallOwner1", adVarWChar, adParamInput, 255, "Nothing")
  cmdUserDays.Parameters.Append cmdUserDays.CreateParameter("QueryCallOwner2", adVarWChar, adParamInput, 255, "Nothing")
  
  'create recordset from command's execute method
  Set RSUserDays = cmdUserDays.Execute
  
  'Iterate through RSUserList using a DO loop, display tables
  Do While Not RSUserList.EOF 
  strCallOwner = RSUserList("strCallOwner")
  
  'set the 2 command params for call owner, re-execute command - I think this is the bit which is not working
  cmdUserDays("QueryCallOwner1") = strCallOwner
  cmdUserDays("QueryCallOwner2") = strCallOwner
  Set RSUserDays = cmdUserDays.Execute
  
  %&gt;
  
  &lt;b&gt;&lt;%=RSUserList("strCallOwner")%&gt;&lt;/b&gt;
  &lt;%'Response.Write(cmdUserDays.CommandText)%&gt;
  &lt;table border="1px"&gt;        
      &lt;tr&gt;
          &lt;th&gt;Day&lt;/th&gt;
          &lt;th&gt;Calls Closed&lt;/th&gt;   
          &lt;th&gt;Calls Pended&lt;/th&gt;
      &lt;/tr&gt;
      &lt;%Do While not RSUserDays.EOF %&gt;
      &lt;tr&gt;
          &lt;td&gt;&lt;%= RSUserDays("dtmDate")%&gt;
          &lt;% DayOfWeek = DatePart("W", RSUserDays("dtmDate"))
  
          Select Case DayOfWeek
          Case 1 Response.Write " Sun"
          Case 2 Response.Write " Mon"
          Case 3 Response.Write " Tue"
          Case 4 Response.Write " Wed"
          Case 5 Response.Write " Thu"
          Case 6 Response.Write " Fri"
          Case 7 Response.Write " Sat"
          End Select%&gt;&lt;/td&gt;
  
          &lt;td&gt;&lt;%= RSUserDays("ClosedCount") %&gt;&lt;/td&gt;       
          &lt;td&gt;&lt;%= RSUserDays("PendingCount") %&gt;&lt;/td&gt;
      &lt;/tr&gt;
      &lt;%RSUserDays.MoveNext 
      Loop
      %&gt;
  &lt;/table&gt;
  
  &lt;%
  RSUserDays.Close
  RSUserList.MoveNext 
  Loop
  
  Set RSUserDays = Nothing
  
  RSUserList.Close
  Set RSUserList = Nothing
  
  Set cmdUserDays = Nothing
  
  FCConn.Close
  Set FCConn = Nothing
  
  Response.ContentType = "application/vnd.ms-excel"
  Response.AddHeader "Content-Disposition", "attachment; filename=excelTest.xls"
  %&gt;
  &lt;/BODY&gt;
  &lt;/HTML&gt;
  </code></pre>
  
  <p>Runs fine but returns empty results.</p>