Determining EVERY month between 2 given dates
in [Queries]
|
From: Edwinah63 on 16 May 2010 04:53 Hi everyone, I have a table with the following data upon which I would like to report: SomeDt SomeValue 1-sep-09 100 12-dec-09 200 02-feb10 50 14-apr-10 75 I need to report on this data showing values for all months within the given min and max dates on the file so: These dates need to be part of a *rolling* report. I have data for Sep, Dec,Feb and Apr, but I want to display on the report ALL months regardless of whether they have data in the table and return 0 where they do not. Thus: Month Value Sep 09 100 Oct 09 0 Nov 09 0 Dec 09 200 Jan 10 0 Feb 10 50 Mar 10 0 Apr 10 75 I can get the min and max and the count of months easily enough: select min(SomeDt) as StDt, max(SomeDt) as EndDt, datediff("m" , StDt, EndDt) as CountofMonthsBetween from SomeTable What I cannot get is how to then determine what the months are in between. What I want if possible is a resultset that looks something like this StDt EndDt Mth 01/9/09 14/4/10 Sep 09 01/9/09 14/4/10 Oct 09 01/9/09 14/4/10 Nov 09 01/9/09 14/4/10 Dec 09 01/9/09 14/4/10 Jan 10 01/9/09 14/4/10 Feb 10 01/9/09 14/4/10 Mar 10 01/9/09 14/4/10 Apr 10 How can I do it? As much as possible I want to use QUERY resultsets, rather than creating intermediary tables etc which are just messy in MS Access, but will use them if no other choice. Happy to write a function if needs be to calc the Mth column. Am absolutely desperate so all help greatly appreciated. If it can't be done at all via queries alone, please let me know this too. Edwina63
From: Allen Browne on 16 May 2010 07:01 The missing months have to come from somewhere, so you need a table to generate them. 1. Create a new table with just one field named (say) CountID, type Number, and mark it as primary key. Save the table as (say) tblCount. 2. Enter records into this table, from zero to the largest number of months you will need for your report. If there could be a large number of months, here's some code to enter the records for you: http://allenbrowne.com/ser-39.html 3. Create a query using tblCount as the source table. Drag CountID into the grid. In the Criteria row under this field, enter: < [How many months?] 4. Type this expression into the next column, the Field row: TheMonth: DateAdd("m", [CountID], [StartMonth]) 5. Declare the Parameters (Parameters on ribbon or Query menu.) Access opens the Parameters dialog. Enter 2 rows, using exactly the same names as you used above: [How many months?] Long StartMonth Date/Time 6. Test. This should generate a record for every month. Save the query as qryCount. 7. Create a query using your existing table. Enter this expression into the Field row: MonthStart: [SomeDt] - Day([SomeDt]) + 1 8. Depress the Total button on the toolbar/ribbon. Access adds a Total row to the design grid. Accept Group By under this field. 9. Drag SomeValue into the grid. In the Total row under this field, choose Sum. Test: the query will give you one total for each month that has data. Save the query as qryMonthData. 10. Create another query using qryCount and qryMonthData as input 'tables'. 11. Drag qryCount.TheMonth onto qryMonthData.MonthStart. Access draws a line between the two tables. 12. Double-click the line between the 2 dialogs. Access opens a dialog with 3 options. Choose the one that says: All records from qryCount, and any matches from qryMonthData. (Technically, that's known as an outer join, and it's what gives you every month.) -- 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. "Edwinah63" <edwinah(a)customercare.com.au> wrote in message news:592bf063-f748-40d6-9b87-5270e4f575f6(a)42g2000prb.googlegroups.com... > Hi everyone, I have a table with the following data upon which I would > like to report: > > SomeDt SomeValue > 1-sep-09 100 > 12-dec-09 200 > 02-feb10 50 > 14-apr-10 75 > > I need to report on this data showing values for all months within the > given min and max dates on the file so: > These dates need to be part of a *rolling* report. > > I have data for Sep, Dec,Feb and Apr, but I want to display on the > report ALL months regardless of whether they have data in the table > and return 0 where they do not. Thus: > > Month Value > Sep 09 100 > Oct 09 0 > Nov 09 0 > Dec 09 200 > Jan 10 0 > Feb 10 50 > Mar 10 0 > Apr 10 75 > > > I can get the min and max and the count of months easily enough: > > select min(SomeDt) as StDt, max(SomeDt) as EndDt, datediff("m" , StDt, > EndDt) as CountofMonthsBetween from SomeTable > > What I cannot get is how to then determine what the months are in > between. What I want if possible is a resultset that looks something > like this > > StDt EndDt Mth > 01/9/09 14/4/10 Sep 09 > 01/9/09 14/4/10 Oct 09 > 01/9/09 14/4/10 Nov 09 > 01/9/09 14/4/10 Dec 09 > 01/9/09 14/4/10 Jan 10 > 01/9/09 14/4/10 Feb 10 > 01/9/09 14/4/10 Mar 10 > 01/9/09 14/4/10 Apr 10 > > How can I do it? > > As much as possible I want to use QUERY resultsets, rather than > creating intermediary tables etc which are just messy in MS Access, > but will use them if no other choice. > > Happy to write a function if needs be to calc the Mth column. Am > absolutely desperate so all help greatly appreciated. If it can't be > done at all via queries alone, please let me know this too. > > Edwina63 > > > > > >
From: Edwinah63 on 16 May 2010 07:56 Hi Allen, Thanks for your solution, it is very neat. Now let me ask the truly stoopid question: is it possible to generate those eight records that are put into tblCount via a query alone ie: based on the the record count (in this example 8 months between Sep and Apr), is it possible to force a *query* to return 8 records (containing 1 to 8, 0 to 7 or whatever)? I am certain the answer is no, but I am interested to have that confirmed by someone who knows Access well (or queries in general and likes a challenge!). As a quick background, I am used to doing these sorts of things in MS SQL stored procs. I would create the kind of table you suggested as a temp table within the procedure and outer join that table back to my query containing the data all as you have outlined. The advantage of stored procs is that when they go out of scope, all the temp tables I have created are destroyed with them. I am worried about creating the table because once I have the final resultset I want all the intermediary steps destroyed. This is coupled with the fact that the owner of this database wants it to be as codeless as possible. Hence the idea of forcing a query to return 8 records in a resultset (which seems to be the nearest thing to a temp table I can find in Access). Any suggestions as the best way to approach this? Edwinah63 ps not sure how i would go about forcing a query to return 8 empty records in ms sql either!! :-) so over to the floor!
From: PieterLinden via AccessMonster.com on 16 May 2010 15:50 >ps not sure how i would go about forcing a query to return 8 empty >records in ms sql either!! :-) so over to the floor! Bite the bullet. Create the tblDates table and then use it in your query. Then you'll create a deliberate partial cartesian product, constraining the dates to be between a start and end date. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: Bob Barrows on 16 May 2010 16:33
Do you already have a table in your database that contains an autonumber field? If so, use that instead of creating a new table. Otherwise, a dynamic solution is not possible. You can of course hard-code some unioned queries to force the missing records to be included. As an alternative, create a new database in code, create the Numbers table in it, link to it, and use it in your query, deleting the database when finished. I'm sure Allen has an example of VBA code to create a database on his website. If not, try www.rogersaccesslibrary.com As for SQL Server, given the existence of a Numbers table, the exact same solution is possible without the overhead of temp tables. Edwinah63 wrote: > Hi Allen, > > Thanks for your solution, it is very neat. Now let me ask the truly > stoopid question: is it possible to generate those eight records that > are put into tblCount via a query alone ie: based on the the record > count (in this example 8 months between Sep and Apr), is it possible > to force a *query* to return 8 records (containing 1 to 8, 0 to 7 or > whatever)? > > I am certain the answer is no, but I am interested to have that > confirmed by someone who knows Access well (or queries in general and > likes a challenge!). As a quick background, I am used to doing these > sorts of things in MS SQL stored procs. I would create the kind of > table you suggested as a temp table within the procedure and outer > join that table back to my query containing the data all as you have > outlined. The advantage of stored procs is that when they go out of > scope, all the temp tables I have created are destroyed with them. > > I am worried about creating the table because once I have the final > resultset I want all the intermediary steps destroyed. This is coupled > with the fact that the owner of this database wants it to be as > codeless as possible. Hence the idea of forcing a query to return 8 > records in a resultset (which seems to be the nearest thing to a temp > table I can find in Access). > > Any suggestions as the best way to approach this? > > Edwinah63 > > ps not sure how i would go about forcing a query to return 8 empty > records in ms sql either!! :-) so over to the floor! -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |