Number of open cases over a time

[beginner33]

Dear All!



I have no idea how to solve one problem in MS Access, that even to me sounds like Lesson nr.1, although I am more or less beginner in MS Access. I am stuck! Any help is much appreciated.

I have a table with numerous records (15.000 and it's growing). Each record contains all sorts of data about defective devices (When device was broken down, when it was repaired, etc.)
Simplified: Each record in my table contains three columns, like CASE_ID, DATE_STARTED, DATE_CLOSED. Parameter DATE_CLOSED can also be empty if the case is still open today.

As a final result, I would like to create a Graph that shows how many service cases were open each day (can also be grouped by week; if the number of records will become critical) over a several years. For that I need a Query with Rows like running date (or week) and column that shows how many cases were opened that particular day (or week).

Can anybody help me to create that Query? Thanks in advance!

[jzwp11]

For the count by date, the query would look something like this

SELECT DATE_STARTED, COUNT(somefield)
FROM tablename
GROUP BY DATE_STARTED

For the count by week it is a little more complicated, but luckily there are some built-in functions in Access that we can use

SELECT year(DATE_STARTED), datepart("ww",DATE_STARTED), Count(somefield)
FROM tablename
GROUP BY year(DATE_STARTED), datepart("ww",DATE_STARTED)

The datepart("ww", DATE_STARTED) returns the week number based on the start date. There are a couple of optional parameters to the datepart() function that allow you to assign the specific start date of the week. If nothing is specified, the start date is assume to be Sunday. The other optional parameter allows you to define the first week of the year; if nothing is specified the first week is based on the week in which January 1st occurs.

[beginner33]

Maybe I was’t clear enough. Here is one example:

1’st case was open on 1.4.2012 and closed on 2.4.2012
2’nd case was opened on 30.3.2012 and closed on 4.4.2012

Result:

Date…………..Number of open cases
28.03.2012……………0
29.03.2012……………0
30.03.2012……………1
31.03.2012……………1
01.04.2012……………2
02.04.2012……………2
03.04.2012……………1
04.04.2012……………1
05.04.2012……………0
06.04.2012……………0

[jzwp11]

Sorry for my misinterpretation. Yes, that is a much different issue.

Assuming that the sequential dates are not stored in a table elsewhere in the database, then they would have to be generated as needed. Then for each date the count would have to be made. A query would be needed for this and it would have to check to see which cases have open dates <= the date of interest but that also have close dates > the date of interest. The query would also have to check for records that have open dates <= the date of interest that have a null close date. So the query would have a WHERE clause (criteria) as follows:


WHERE (opendate<= dateofinterest AND closedate>dateofinterest) OR (opendate<= dateofinterest AND closedate is null)

I think the only way to do this would be with and unbound form where the user would specify the date range of interest and then use Visual Basic for Application (VBA) code to loop through each date in the range and run the query to get the count. A temp table would probably need to be created to hold those values so that a report can be generated.

[recyan]

Just check out if below gives some guidelines :

https://www.accessforums.net/showthr...lease-help-moi!

Thanks

[beginner33]

Dear All! Thank you very much. On first sight it seems that at:

https://www.accessforums.net/showthre...lease-help-moi!

there is very similar problem solved.
If I am going to be stuck again: I'll be back!

[recyan]

The link provided was a first draft & I believe is over complicated. You should be able to simplify things to a great extent.
Also we did not get any feedback from the OP whether there was any problem with the suggested solution in case it was tried.

Also definitely take a look at this (Credit pbaldy) :

http://www.baldyweb.com/OverLap.htm

Thanks

[beginner33]

Recyan, you are right. This seems too complicated (for me) to be able to somehow transfer it to my solution. No real progress so far. ;(

[jzwp11]

As indicated in my post early on, the problem lies with not having a table that has all dates. In the link that recyan initially provided, there is a query that creates that table. Without a table like that, the only solution is to use a loop in code. Even if using the loop in code you will have to store the counts somewhere (i.e. another table--but a temporary table) so that they would be available for use with a form or report. If you want to use strictly a query solution, then you will need a table with dates or sequential numbers, but they would have to encompass the range of the dates you would anticipate using. The decision is up to you how you want to proceed. We can help as needed.

[beginner33]

I am opened for any solution, solution that strictly uses queries or solution that also uses VBA code.
I have created the table with all dates, but I don't know how to fill it.
I can count the open cases only for one particular Date, but not for Dates. (If I am interested for one year history, I don't want to create 365 queries for that....).
I don't know how to achieve that loop, that loops through the table and counts the open cases for each day.

[jzwp11]

(If I am interested for one year history, I don't want to create 365 queries for that....).

You would not need 365 queries just the table with 365 records (initially). Of course, those records would need to be updated or additional records for the new year added.

Could you post your database as it stands now? It is generally easier to create queries and/or code with the actual table and field names. Please make sure to remove or alter any sensitive information

[recyan]

Just check out attached sample & see if it gives some guidelines :
Open the form frmReport_2.

Thanks

open_cases.zip