Start and End Date Count Records Open per Day

[mikerea90]

Hey all,

I have a database that tracks specific issues. The issue comes into the office on a specific day (OpenDate) and is closed out on a specific day (ClosedDate). What I am trying to do is create a graph from a query that shows Opened issues by date, Closed issues by date, Overdue issues by date (Due date < Closed date), but also show the amount of open issues by date.

This is the problem - how do you put all of this in one graph - I can do the first three. But the last one - showing open issues by date is giving me some problems.

The user defines a Start Date and an End Date. I would like to be able to count how many records are open on each date between the Start and End dates. The records can be closed, but the graph will still show that they were open on days in between there OpenDate and ClosedDate.

Thanks in advance!

 

[mikerea90]

That is to say I am trying to count per day, how many Issues were open. I know it sounds easy, but for some reason I cannot put this into a query. I have been looking for the past few hours so any help you can give me would be much appreciated.

 

[pbaldy]

A couple of ways come to mind, but for both I'd have a table that had a record for each of the dates in the user defined range. This would be to account for the possibility that there were no issues on a given day, and you'd still want to display that date with zero on your chart. If that's not a concern, a query that gets the distinct dates for the period from the data.

I'm visualizing a query that joins the above table/query back against the data table, with non-equi joins on the date fields. That should produce a result that can be totaled up and produce the open issues per day.

 

[mikerea90]

I think I am not being as clear as I can be. So I will try to describe this a little more. And thank you so far - I appreciate it.

Alright the final result is a dynamic report that changes based on the month. In this report I have a graph that changes each month. The part of the query that is giving me the most problems is that I need to count the amount of issues open at the end of the month. So lets say the 30th. I have a handful of issues that were closed between the 15th of last month and the 15th of this month. Lets say 5 issues follow that pattern. Then on the 30th, the query would show that 5 issues were open.

If I could do this at week end, that would be ideal.

Thanks in advance!

 

[pbaldy]

That should be a relatively simple query, so I'm probably misunderstanding:

SELECT Count(*) AS HowMany
FROM TableName
WHERE OpenDate <= #6/30/09# AND ClosedDate >= #6/30/09#

should return the number outstanding on that date. In production, I'd get the date from a form or whatever, but you get the idea.

 

[mikerea90]

That works out really well, thank you! Quick question - how would I be able to make this dynamic so that it shows all of the weeks in the month snapshot at the end of the week? Or if I could do this as a snapshot at the end of each month?

 

[mikerea90]

*at the end of each week. (And then show multiple weeks). Or at the end of each month and then show multiple months.

 

[mikerea90]

I guess what I am saying is how would I be able to make this continously sum open issues and then subtract the issue from that sum once its closed date has passed.

So let's say 3 issues were opened as follows:
Issue DateOpened DateClosed
A 6/5/09 6/12/09
B 6/9/09 6/18/09
C 6/10/09 6/24/09

So the table would look like this:
Date Open
6/1/09 0
6/8/09 1
6/15/09 2
6/22/09 1
6/29/09 0

 

[mikerea90]

The hard part I've found is that each record has its own start and end date. On top of that, there is a Query Start and End date (for a report) that needs to go through and count how many records are currently open each day.

 

[pbaldy]

That's where I was heading with my first response. That method should result in each day's open issues. I suppose it could be modified for weekly or monthly.

 

[mikerea90]

Alright, I am still not sure exactly how I would do this. When I try to do this, I am told that I need to adjust the relationships.

 

[pbaldy]

Can you post the db, or a sample db with enough sample data to make for meaningful testing?

 

[mikerea90]

Here is a quick example of what I am trying to do. I had to make a new db to show you. What I would like to do is show how many issues are overdue on each day, how many issues were opened on each day, how many issues were closed on each day, and how many issues were open at the end of the day. Thank you so much for your help, I have been working on this all week.

 

[mikerea90]

Just make sure that you insert the dates into the form field before opening the query. Just put in 06/01/2009 to 06/30/2009. Thanks!

 

[pbaldy]

Is the result at the bottom what you're looking for?

 

[mikerea90]

That is exactly what I'm looking for. How'd you do that? And then would I be able to copy that to also show how many issues were closed, opened, and overdue on each day? Thanks!

 

[pbaldy]

I did it how I described it.

Here are parts of it (I only did the final query for the open issues). Note they all depend on tblDates containing the dates in the requested period. What I would do is run a process after the user filled in the dates to empty that table and repopulate it based on the user selections. You'd use a recordset and a loop.

There may be other ways of accomplishing the goal; using that extra table and joins is what came to me first.

 

[mikerea90]

This is great - but quick question - is there any way that I can make this dynamic so that I don't have to go back and change the ReportDate table each month?

 

[pbaldy]

Already addressed that:

What I would do is run a process after the user filled in the dates to empty that table and repopulate it based on the user selections. You'd use a recordset and a loop.

 

[mikerea90]

Alright well the way that I have my reports page set up, the dates are automatically filled in to reflect the current month. Is there any other way to have the recordset and loop? And how do you set up a recordset and loop? Thanks, I have never used recordset.