If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date calculation in query
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 |
#2
|
|||
|
|||
Date calculation in query
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "John" wrote in message ng.com... 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 |
#3
|
|||
|
|||
Date calculation in query
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. On Oct 23, 11:57*pm, "Allen Browne" wrote: The core concept is to create some kind ofquerythat 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 adate/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 ofquerydesign. 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. |
#4
|
|||
|
|||
Date calculation in query
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. wrote in message ... 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. On Oct 23, 11:57 pm, "Allen Browne" wrote: The core concept is to create some kind ofquerythat 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 adate/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 ofquerydesign. 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. |
Thread Tools | |
Display Modes | |
|
|