Counting the # of open tickets for each day w/ only open/closed 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

I have searched for similar inquiries and have only come up with a few theories on how it can be done (including normalizing the data). 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 (like how to normalize the data) I would greatly appreciate it.

 

[fneily]

Well, at least you found the right forum. Didn't explain very precisely or show your table structures, though. So
you can check out faq701-5268
or
If records are still opened, then they have no closed date. So first create a query with TicketNumber, Opened and Closed and on the criteria line for Closed, put Is Null. So you get all Open records with no closes.
Create a new report - Select design view and select your query. In design view, from the field list, place the TicketNumber and Opened in the Detail section. Click on the Sorting and Grouping button. Under Field/Expression column, select Opened to group and in the bottom options pick Yes for Group footer.
Create a new textbox in the group footer from the toolbox toolbar. For the control source of the textbox put =Count(*)
If you preview the report, you'll see the TicketNumeber listing and the count for each day. You can now pretty the report up.

 

[cthesupremeg]

Thanks again for the info. It was a bad assumption on my part that the following information would not be needed to identify the solution to my problem in my original post. (I appreciate the response. I wish my problem was that simple.)

The Data: I am extracting ticket records from Remedy (our ticket application) and dumping those records into access. It's important to note that I do not use access to extract the data from Remedy.

What I need to do is provide a report to management that shows year to date the 'backlog' of tickets per day. Backlog is defined as the number of tickets that were still "in progress" at the end of the day. So if on 1/2/08 five new tickets were submitted and all five tickets were closed that day, then the backlog for 1/2/08 would be 0 (assuming there wasn't any backlog prior to 1/2/08). If on 1/3/08 nine new tickets were submitted, but only three of them were closed, then the backlog for 1/3/08 would be six. This also means that we start 1/4/08 six tickets behind. If we get 10 tickets on 1/4 and only close two of them, then the ticket backlog for 1/4/08 is 14 (total tickets in progress on 1/4 = 16 less the two that were closed = 14).

We have gone from a roughly consitent 5-10 daily ticket backlog at the beginning of the year (Dec-Jan) to over 400 today. (Clearly there have been more tickets coming in than can be closed.) There is a huge backlog today for several reasons but mostly because of resource constraints. Now that the business has stepped up on resources, they are going to want to see a decline in the daily backlog. (More tickets being closed than coming in.)

The challenge I have in solving this problem is lies with how I get the data from Remedy. When I query Remedy, the ticket data reflects the status of the ticket at the time I query (for example, today). This means when I pull a report in July on the tickets opened on 1/2/08, they all show closed. However, the tickets opened on 1/2/08 weren't all closed on 1/2/08. So if I only have an open date and a close date on a ticket, how do I build a query/report that will show me that on on 1/2/08, my ticket backlog was five (six tickets were opened 1/2, one was closed 1/2). **See attached. I uploaded my access file. The only thing in there is the table of data... that's as far as I have gotten with this problem.

One last note, I'm not looking for an aging report. (i.e. two tickets opened in Feb are still open, 13 in Mar, 50 in April, etc...) I can and have provided aging reports as part of my metrics deck, but it doesn't show the ticket volume of any given day.

 

[fneily]

Since your Arrival Time and Resolve Time are in Date/time format, I created a query to transpose them to just dates. In a QBE pane, use your table as source. In the grid, bring down Case ID+, Arrival Time, Resolved Time. Then create three new fields on the Field row.

Date1: DatePart("m",[Arrival time]) & DatePart("d",[arrival time]) & DatePart("yyyy",[arrival time])

Date2: DatePart("m",[Resolved time]) & DatePart("d",[Resolved time]) & DatePart("yyyy",[Resolved time])

Difference: IIf([Date1]=[Date2],0,1)

If you switch to SQL View, it'll look like:

SELECT Backlog.[Case ID+], Backlog.[Arrival Time], Backlog.[Resolved Time], DatePart("m",[Arrival time]) & DatePart("d",[arrival time]) & DatePart("yyyy",[arrival time]) AS Date1, DatePart("m",[Resolved time]) & DatePart("d",[Resolved time]) & DatePart("yyyy",[Resolved time]) AS Date2, IIf([Date1]=[Date2],0,1) AS Difference
FROM Backlog
ORDER BY Backlog.[Case ID+];

Now use this as input to a report. Create a new report in Design view selecting the above query. Using the field list, drag Case ID+, Arrival Time, Resolved Time and Difference into the detail section. Delete the labels. Create labels in the Page Header section (Do not create one for Difference). Line them up horizontally, Line up the respective textboxes under them. Click on the Difference Textbox and on the property sheet make it invisible.

Click on the Sorting and Grouping Button. Under Field/Expression, click the dropdown and select Arrival Time. Under Group Properties, Select Yes for Group Footer. Change Group On to Day.

Create a new unbound textbox in the Arrival Time Footer section. Change the label to Total Not Closed. Click the textbox and make it's Control Source =Sum([difference])

When you run the report, it'll show the tickets for that day and how many were NOT closed.

Pretty up the report with Titles and stuff.