A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Determining EVERY month between 2 given dates



 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old May 16th, 2010, 09:53 AM posted to microsoft.public.access.queries
Edwinah63
external usenet poster
 
Posts: 16
Default Determining EVERY month between 2 given dates

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






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.