Can MS Access count open tickets by day with only start/end dates?

[cthesupremeg]

I am trying to figure out how to create a query/report in MS Access that will show me the number of open tickets at the end of each day using only the open date and close date of the ticket.

For example, if I have the following data set:

Ticket Opened Closed
1 1/2/08 1/3/08
2 1/2/08 1/3/08
3 1/2/08 1/4/08
4 1/2/08 1/5/08

The result set I'm looking for is:

Date Open tickets
1/2/08 4
1/3/08 2
1/4/08 1
1/5/08 0

The result set counts the number of tickets that don't have a resolve date <= the open date. The logic seems pretty simple, the solution does not appear to be this easy.

I have only brainstormed few theories on how it can maybe done, but I have to admit this problem is outside my Access skill set.

If anybody knows the solution or has any ideas that can get me going in the right direction I would greatly appreciate it.

 

[fneily]

There are 7 or so Access forums. This should be posted in
Microsoft: Access Queries and JET SQL

Try to post your table structures. Most times a problem arises when the tables are not normalized.

Your example doesn't make any sense to me. "The result set counts the number of tickets that don't have a resolve date <= the open date." None of them have a resolve date less then or equal to the open date.

 

[cthesupremeg]

Thanks for the response. I'll refine my inquiry and post it where you suggested.

 

[mintjulep]

Using bastard logical syntax:

For each ticket countif AND(Opened <= DOI, Closed > DOI)

DOI = Date of Interest.