Date calculation in query

[John]

Hello.

I'm having a bit of a problem with a query. First of all, let me just
emphasize that the final result of the query I want to build would be
relatively simple to create using VBA and a temporary table, but one of
the reasons I'm working on this database is that the old version has
temporary tables all over the place, so it's very messy and much larger
than it needs to be.

Anyway, here's what I want to do:

I have a form with two datepicker controls - one do pick a start date,
and one to pick an end date, so that I end up with a date range. I then
have a query that reads these two dates and returns a bunch of records
that fall within the chosen range, and summarizes them by month. The
challenge I'm having is that if there is no data for a month within the
range, then the query does not show that month. I want it to show the
month, with all the summary fields showing zeros. For example, if I
chose Jan 2008 to Mar 2008, but there was no data for Feb 2008, I want
it to show this:

January 2008 516 72 9
February 2008 0 0 0
March 2008 834 291 34

But what it's showing is this:

January 2008 516 72 9
March 2008 834 291 34

Using a temporary table, I could loop through the months between Jan and
Mar, adding them to the table, and then use that table to do a left join
on my actual data queries. This would ensure that all the months are
listed, but it would create some junk data within the database. Also, it
could cause problems if multiple users tried to run the same report
(with different parameters) at the same time. So, I'd like to accomplish
this in a query, or a combination of queries. E.g. if I had a query that
listed all dates between two given dates, I could create another query
based on that one to reduce the dates to months and years.

Any thoughts?

Thanks,

John

 

[Allen Browne]

The core concept is to create some kind of query that generates 1 record for
each month, and then outer-join it to your existing one so every month shows
up.

There's several ways to do that, and the best approach will depend on how
you have you existing data set up. If 'January 2008' is actually a date/time
field that holds the value 1/1/2008, it would be quite easy to create a
table and enter a record for the first of each month for the months you need
to cover.

If you are not familiar with outer joins, you double-click the line joining
the 2 tables in the upper pane of query design. Access pops up a dialog with
3 options, and you choose the 2nd or the 3rd one (depending on the direction
you want.)

Once you get that, you want to insert zeros into the report. You can do that
with Nz() or a IIf() expression. Or, if this is just for display purpose,
you could use the Format property of the text box on your form/report to
show a zero for null.

 

[jrtwynam]

Thanks for the reply, Allen.

The field that I'm constraining on in my data table is a date/time
field, so it's easy to take those values and convert them to just a
month and year and then summarize based on that. The challenge is if
there is no data for a given month, that month will not be returned in
the results of such a query. I want this query to be based solely on
the values in the datepicker controls on my form, but I don't know if
that's possible. I can easily create a query that displays the value
of the start date and the end date (in 1 record with 2 fields), but
what I want to do is start by displaying those in 2 records with 1
field. Then, I want another record for each month in between those
months. So, if the user chooses a range of January 2008 to March 2008,
I want the query to return 3 records (one each for Jan, Feb, and Mar).

I'm trying to avoid doing this with the method you suggest, because I
don't want to have any temporary tables in the database, and I want
this to work for multiple simultaneous users.

I'm not sure I'll be able to get around the temporary table issue, but
as I've been typing this, I think I may have thought of a way around
the multiple user issue. I could have a permanent table that has 3
fields: some sort of ID, month number, and year. When the user clicks
the "run report" button, it would automagically generate a report ID,
maybe based on the username and the date/time the button was clicked,
and programmatically insert the required records into the table with
the generated ID. This ID would be stored in an invisible text box on
the parameter form, and there would be a query that reads this ID
field so that it only returns the months and years that the current
user has selected. Another user could run this report at the same
time, but it would have a completely different ID, so the first user's
selections shouldn't have an effect on this user. Of course, there
would have to be some cleanup procedure, or else this table would just
continue to grow.

 

[Allen Browne]

From your reply, I'm not sure you understood the concept of a counter table
(rather than a temporary table), and an outer join.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply, Allen.

The field that I'm constraining on in my data table is a date/time
field, so it's easy to take those values and convert them to just a
month and year and then summarize based on that. The challenge is if
there is no data for a given month, that month will not be returned in
the results of such a query. I want this query to be based solely on
the values in the datepicker controls on my form, but I don't know if
that's possible. I can easily create a query that displays the value
of the start date and the end date (in 1 record with 2 fields), but
what I want to do is start by displaying those in 2 records with 1
field. Then, I want another record for each month in between those
months. So, if the user chooses a range of January 2008 to March 2008,
I want the query to return 3 records (one each for Jan, Feb, and Mar).

I'm trying to avoid doing this with the method you suggest, because I
don't want to have any temporary tables in the database, and I want
this to work for multiple simultaneous users.

I'm not sure I'll be able to get around the temporary table issue, but
as I've been typing this, I think I may have thought of a way around
the multiple user issue. I could have a permanent table that has 3
fields: some sort of ID, month number, and year. When the user clicks
the "run report" button, it would automagically generate a report ID,
maybe based on the username and the date/time the button was clicked,
and programmatically insert the required records into the table with
the generated ID. This ID would be stored in an invisible text box on
the parameter form, and there would be a query that reads this ID
field so that it only returns the months and years that the current
user has selected. Another user could run this report at the same
time, but it would have a completely different ID, so the first user's
selections shouldn't have an effect on this user. Of course, there
would have to be some cleanup procedure, or else this table would just
continue to grow.