Query to count how many months a record is open.

Hey guys,

So I stoped by the Query section and spent a few days trying to figure out how to make a query that will allow me to count how many open records I have each month.

That thread can be forund: http://www.access-programmers.co.uk/forums/showthread.php?t=214642

It might be a good idea to scan over that as that for more info.

Here is what I am looking for:


I have two dates:

• AwareDate
• DateClosed

I need to create a report that shows, by month how many records are open, so if I had 15 records open in 1/1/2011 and they all closed on 3/1/2011 my end table should show.

1/1/2011 = 15
2/1/2011 = 15
3/1/2011 = 15

The issue with SQL is that I can group by month, BUT since there are no values for 2/1/2011 and 3/1/2011 I cant get a count, the output would look like.

1/1/2011 = 15
2/1/2011 = 0
3/1/2011 = 0

If there is a WAY to do this in SQL alone I would love to know but at this point I belive we will need a function to do the math.

I am kinda new to looping over tables of data so any advice or direction you can provide would be great!

You need a table that holds all the report dates you want to use and then build a Cartesian Product using that table and the table with your records in it.

Let's call the new table 'DateTable'. It will have one date field which will hold the dates you want to run your reports for (from above you used 1/1/2011, 2/1/2011 and 3/1/2011). And we will call the existing table 'MainTable'. This will be the SQL to generate the total open records on a report date:


If you look at it in design view you will see that it is using both tables, but they aren't linked together--that's a Cartesian Product and is what you need to solve this issue using only queries.

plog said:

You need a table that holds all the report dates you want to use and then build a Cartesian Product using that table and the table with your records in it.

Let's call the new table 'DateTable'. It will have one date field which will hold the dates you want to run your reports for (from above you used 1/1/2011, 2/1/2011 and 3/1/2011). And we will call the existing table 'MainTable'. This will be the SQL to generate the total open records on a report date:

Code:

SELECT DateTable.ReportDate, Sum(IIf([AwareDate]<=[ReportDate] And [DateClosed]>=[ReportDate],1,0)) AS [Open]
FROM DateTable, MainTable
GROUP BY DateTable.ReportDate;

If you look at it in design view you will see that it is using both tables, but they aren't linked together--that's a Cartesian Product and is what you need to solve this issue using only queries.

Click to expand...

Ok I have created the new query that will give me a list of dates I called it qryReportDates and its grouped by month.

When I run the query I get a data mismatch error, guessing thats cause by trying to sum up dates? I did some google searches and found the CDate() but when I wrap my dates in that I get an error "Invalid use of Null"

This is my new query:

This seems to work, I just need to check the values

I had to update the query a bit more to make it work and show the proper dates, but all in all it was the solution I needed.

My SQL:


The one thing that’s going to cause me some issues is the table that shows all the dates to report on (ReportDate). It’s easy enough for me to type out the dates but is there a way to look at my AwareDate and query it so that if a date is 6/25/2011 it would return 6/1/2011?

That way I can group that query and it would automatically keep everything up to date.

I'm not exactly following what you are trying to do, but this will return the first day of the month when passed any day of that month:

FirstOfMonth: Format(Month([AwareDate]) & "/1/" & Year([AwareDate]), "Short Date")