Query to display records in rolling 4 moth period.

[jake7363]

Hi,
I am trying to create a query that will show data in a rolling four month period. Example: For September, show June through September; for October, show July through October.
I am using a general date format in the field. I can use >DATE() - 120 to get a 120 day period, but that obviously does not give me data for the actual Calendar dates. Is there a better way?


Any and all suggestions will be greatly appreciated.
Thanks in advance,
Jake

 

[SkipVought]

Hi,

Where [YourDate] >= DateValue(format(Date,"yyyy/mm") & "/01")

Skip,
[sub]
[red][/red]
[/sub]

 

[Golom]

I'm not clear on this.

Do you want to compute things (e.g. Sums, Averages, Counts) for rolling 4-month periods?

Do you just want to select records in a particular 4- month period?

 

[SkipVought]

oops, I forgot to do the math...

Where [YourDate] >= DateValue(format(Date-120,"yyyy/mm") & "/01")

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

or...

Where [YourDate] >= DateSerial(year(date()), month(date()-4), 1))

Skip,
[sub]
[red][/red]
[/sub]

 

[jake7363]

I just want to show the data, no other calculations.

 

[Golom]

Skip's code will give you the previous 4 months before today ... although not necessarily calendar months.

This code just lets you specify a period and see all the records in that period. It assumes that data in the table is all within the same year.

Select IIF([Enter The Period] = 1, "Jan-Apr",
           [Enter The Period] = 2, "May-Aug", "Sep-Dec") As [Period]
     , Other fields ...

from MyTable

Where IIF(Month(DateField)<=4, 1,
          Month(DateField)<=8, 2, 3) = [Enter The Period]

 

[jake7363]

The idea is that this will be a continuing rolling process. It needs to keep rolling the months over as they change, without user intervention. This will include changing from year to year, so that once it is set up, to run it will not require any interaction.

 

[SkipVought]

Golom,

My corrected code give 4 full calendar months.

Skip,
[sub]
[red][/red]
[/sub]

 

[jake7363]

As I see it, the code designates specific months. But this is a "rolling" period, so that without intervention, it drops one month and adds another when the month changes.

Thx.

 

[SkipVought]

What code are you referring to?

Skip,
[sub]
[red][/red]
[/sub]

 

[jake7363]

SkipVought -
I was one reply late - it was referring to the post by Golom. Sorry for the confusion.

 

[Golom]

SkipVought

Yes it does ... sorry.

For some strange reason, I was interpreting the requirement to mean a specific 4-month period rather than just the most recent 4 months.

I guess my hang-up is with the terms "rolling period" or "continuing rolling process".

Given that no computations are being done such as a "rolling" average, it sounds like these terms must equate to "the last 4 months" which is what your code does.