How to track # of open issues per day

[StrikeEagleII]

I'm creating a database that tracks open issues on a product. the data includes the date the issue was opened and the date it was closed. I am trying to figure out how to create a query that will return the number of open issues per day

Simplified sample data:
pkID DateOpened DateClosed
1 2/1/2010 2/4/2010
2 2/2/2010 2/5/2010
3 2/3/2010 2/7/2010
4 2/3/2010 2/5/2010
5 2/5/2010 2/7/2010
6 2/7/2010 2/8/2010
7 2/7/2010 2/9/2010


desired output:
Date: OpenItems:
2/1/2010 1
2/2/2010 2
2/3/2010 4
2/4/2010 3
2/5/2010 2
2/6/2010 2
2/7/2010 2
2/8/2010 1
2/9/2010 0

Ultimately, it will be exported to excel to make a pretty graph for management... the initial thought was to create a temp table that has a row for each date between the earliest DateOpened and latest DateClosed and then loop through and for each Date, count the number of records where Date > DateOpened AND Date < DateClosed, but that seems pretty clunky and inefficient.

 

[dhookom]

Since you have "a temp table that has a row for each date", create a query with your date table and your "Simplified sample data". Set the criteria under the date field from the date table to:

  Between DateOpened And DateClosed

Group by the date field from the date table and count the pkID field.

Duane
Hook'D on Access
MS Access MVP

 

[StrikeEagleII]

With a temp table it would be easy enough--i was just wondering if there was a way to do it without one.

 

[dhookom]

All you need is a table/query that can return all possible dates. You can derive this from almost any table if you are good enough with SQL. I find it best to have either a table of all dates or a table of numbers.

Duane
Hook'D on Access
MS Access MVP