Loop for counting Dates

[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.

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
01.04.2012……………2
02.04.2012……………2
03.04.2012……………1
04.04.2012……………1
05.04.2012……………0
06.04.2012……………0


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).

I don't know how to achieve that loop, that loops through the table and counts the open cases for each day.

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

 

[Pat Hartman]

You would not use a code loop, you would use a query. I'm not sure exactly what you want to count so I'll do the simplest option. Count the number of cases opened on a particular day.

Select DATE_STARTED, Count(*) as CasesOpened
From yourtable
Group by DATE_STARTED;

 

[pbaldy]

Unfortunately it isn't as simple as that. One solution that comes to mind is a query on a table of all the dates and a subquery:

SELECT tblDates.DateField, (SELECT Count(*) FROM tblReservations WHERE StartDate <= DateField AND EndDate >=DateField) AS HowMany
FROM tblDates

Where tblDates is a table with a record for each date.

 

[sparks80]

In addition to pbaldy's reply, you could get the list of dates from a query instead of a separate table:

Query 1: SELECT tblDates.DateField FROM tblSetInfo GROUP BY tblDates.DateField;

Query 2: SELECT Query1.DateField, (SELECT Count(*) FROM tblReservations WHERE StartDate <= DateField AND EndDate >=DateField) AS HowMany
FROM Query1

 

[pbaldy]

In addition to pbaldy's reply, you could get the list of dates from a query instead of a separate table:

Query 1: SELECT tblDates.DateField FROM tblSetInfo GROUP BY tblDates.DateField;

Query 2: SELECT Query1.DateField, (SELECT Count(*) FROM tblReservations WHERE StartDate <= DateField AND EndDate >=DateField) AS HowMany
FROM Query1

I fail to see how that would be any benefit sparks80. It wouldn't even run as you have it there. Even if it did, what does it gain you?

 

[sparks80]

I was thinking that if you get the list of dates from a table that you will need to manually change these dates each time you want to display the data.

Does that make sense?

 

[pbaldy]

I was thinking that if you get the list of dates from a table that you will need to manually change these dates each time you want to display the data.

Where are you getting them? Your query has to be getting them from someplace. I don't think you can count on getting them from the transaction table, as you couldn't be guaranteed to get every date, as the desired result seems to require. You are correct that you'd have to change them in the table I used, or include all dates and include a criteria for the desired range. It would be fairly easy to add dates to the table for any given date range; I wouldn't be manually adding them.