No. of Open cases per day per agent

[Daan den Hollander via AccessMonster.com]

Goodday,

I have a table that holds a start date [Datum] and a end date
[Sluitingsdatum].
What I would like to built is a query with in it per day the number of open
cases per agent [Agent].

Hopefully someone can help me.

Thanks in advance,

Daan

 

[Allen Browne]

The dates have to come from somewhere, so a table of dates will be the
easiest solution.

1. Create a table that contains one record for each date.
Just one field named TheDate, of type Date/Time.
Mark it primary key.
Save with the name tblDate.

2. Enter all the dates for the range you need. You can use the function at
the end of this answer to populate the table for you.

3. Create a query that has both your main table and tblDate.
No join between them.

4. Drag tblDate.TheDate into the grid.
In the Criteria row under this field, enter:
Between [Datum] And [Sluitingsdatum]
This will give you one for for every date between Datum and Sluitingsdatum.

5. Change the query to a Totals query (Totals on View menu).
Access adds a Total row to the grid.
Accept GroupBy in the Total row under TheDate.

6. Drag your primary key into the grid.
In the Total row under this field, choose:
Count


The function to populate tblDate:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2004# To #12/31/2013#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function