How to run a date based query for many months

[le Nordiste]

Hello,

I have one query based on a parameter "MyDate".
I enter a full date ( dd/mm/yyyy french format but no matter )
and the query result is expressed by the month ( yyyy_mm)
eg : i enter 25/12/2009 and the result is expressed in
2009_12

I have to run this query for n months before the entered date and 2
months after this date.
Is it possible to get a SQL code for this ?

Can you help me ?

Thanks by anticipation.

 

[John Spencer]

It would help if you posted the SQL of your query. Also, your requirement as
stated is a bit vague.

If you enter 25/12/2009 and want 1 month before and 2 months after does that mean
== From 25/11/2009 to 25/02/2010
or
== FROM 01/11/2009 to 28/02/2010
or something else

Assuming the first result you can try:
Field: SomeDateField
Criteria: Between DateAdd("m",-1,[MyDate]) and DateAdd("m",2,[MyDate])

If the number of prior months varies, you need a second parameter.
Field: SomeDateField
Criteria: Between DateAdd("m",-[Number of Prior Months],[MyDate]) and
DateAdd("m",2,[MyDate])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[le Nordiste]

Query 1 :
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])-6,1),"yyyy_mm") AS date_m6 INTO T_m6;

Query 2 :
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])-4,1),"yyyy_mm") AS date_m4 INTO T_m4;

Query 3 :
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])+4,1),"yyyy_mm") AS date_p4 INTO T_p4;

Final query :
SELECT T_m6.* FROM T_m6
UNION
SELECT T_m4.* FROM T_m4;
UNION
SELECT T_p4.* FROM T_p4;

and by typing 02/2/2010, i get 3 recordset : 2009_08, 2009_10,
2010_06

Is it possible to write an UNION query with Query 1 to query 3, here
above ?
Something like :

SELECT MyAliases.* FROM (
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])-6,1),"yyyy_mm"
UNION ALL
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])-4,1),"yyyy_mm"
) AS MyAliases;

Thanks for your help.

 

[John Spencer]

I'm confused about what you are attempting to do.
You can write a union query like the one below to generate the dates.

I used a table with only a few records in it for performance reasons.

PARAMETERS MaDate DateTime;
SELECT
FORMAT(DateSerial(Year([madate]),Month([maDate])-6,1),"YYYY_MM") as SomeDate
FROM TableA
UNION
SELECT
FORMAT(DateSerial(Year([madate]),Month([maDate])-4,1),"YYYY_MM")
FROM TableA
UNION
SELECT
FORMAT(DateSerial(Year([madate]),Month([maDate])+4,1),"YYYY_MM")
FROM TableA

Or you can generate date ranges.
PARAMETERS MaDate DateTime;
SELECT DateSerial(Year(madate),Month(maDate)-6,1) as StartDate
, DateSerial(Year(madate),Month(maDate)-5,0) as EndDate
FROM TableA
UNION
SELECT DateSerial(Year(madate),Month(maDate)-4,1) as StartDate
, DateSerial(Year(madate),Month(maDate)-3,0) as EndDate
FROM TableA
UNION
SELECT DateSerial(Year(madate),Month(maDate)+4,1) as StartDate
, DateSerial(Year(madate),Month(maDate)+5,0) as EndDate
FROM TableA


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[le Nordiste]

Hi John,


First of all : Thaks to spend your time on my problem.


I have this query, who give me for each typed date the count of refREA
of the month

SELECT Format([one date ?],"yyyy_mm") AS periode,
Count(MyTable.refREA) AS nbreREA
FROM MyTable
GROUP BY Format([one date ?],"yyyy_mm");

(I discovered the PARAMETERS clause in your mail, thanks.; and will
add it)

I want to run the here above query over a 6 month period before the
month of "one date", for each complete month.
eg : if i typed 12/02/2010 i want the result displaying from
september 2009 to feb 2010
My idea is to get "insert" automatically a date of each of the 6
months automatically

 

[le Nordiste]

SORRY I HAVE BEEN INTERUPTED

I want to run the here above query over a 6 month period before the
month of "one date", for each complete month.
      eg : if i typed 12/02/2010   i want the result displaying from
september 2009  to feb 2010

My idea is to "insert" automatically a date of each of the 6 months
automatically

Something like :
my_here_above_query WITH [one date ?] IN ( myNewQuery)

myNewQuery giving a sequence of date, one per month between now and 6
months before.


Thanks

 

[John Spencer]

I would expect that you want a query that looks like the following, if you
wanted to group the records by month and year and get a count of some field
then the query would look like:

PARAMETERS [MaDate] DateTime;
SELECT Format(SomeDateField,"yyyy\_mm") as Periode
, Count([refRea]) as nbreREA
FROM [SomeTable]
WHERE [SomeDateField] Between DateSerial(Year([MaDate]),Month([MaDate])-3,1)
and DateSerial(Year([MaDate]),Month([MaDate])+3,0)


So if you enter December 2, 2009 you would get all the records from
September 1, 2009 until February 28, 2010
grouped by year and month with a count. If there were NO records at all for
any specific month you would not get a row returned.

Your results would be something like the following
Periode : nbreREA
2009_09 : 22
2009_10 : 43
2009_11 : 2
2009_12 : 18
2010_01 : 238
2010_02 : 75

You can adjust the range by changing the -3 and +3 in the DateSerial function
calls.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SORRY I HAVE BEEN INTERUPTED

I want to run the here above query over a 6 month period before the
month of "one date", for each complete month.
eg : if i typed 12/02/2010 i want the result displaying from
september 2009 to feb 2010

My idea is to "insert" automatically a date of each of the 6 months
automatically

Something like :
my_here_above_query WITH [one date ?] IN ( myNewQuery)

myNewQuery giving a sequence of date, one per month between now and 6
months before.


Thanks

 

[le Nordiste]

Hi John,

PARAMETERS [MaDate]  DateTime;
SELECT Format(SomeDateField,"yyyy\_mm") as Periode
, Count([refRea]) as nbreREA
FROM [SomeTable]
WHERE [SomeDateField] Between DateSerial(Year([MaDate]),Month([MaDate])-3,1)
and DateSerial(Year([MaDate]),Month([MaDate])+3,0)

With this adapted code i get the error message :
"You try to execute a query without the expression "
Format(SomeDateField ,"yyyy_mm") as part of agregate function


I tried also


PARAMETERS [MaDate] DateTime;
SELECT Format(SomeDateField ,"yyyy_mm") AS Periode,
Count(SomeTable.refRea) AS nbreREA
FROM SomeTable
GROUP BY Format(SomeDateField ,"yyyy_mm"), SomeTable.SomeDateField
HAVING (((SomeTable.SomeDateField ) Between
DateSerial(Year([MaDate]),Month([MaDate])-3,1) And
DateSerial(Year([MaDate]),Month([MaDate])+3,0)));



But i get be something like the following
Periode : nbreREA
2009_09 : 22
… one line per working day
…
2009_09 :54
2009_10 : 43
…
2009_10 : 24
2009_11 : 2
.....


We are on the way but… a little effort more.

THANKS

 

[John Spencer]

Forgot the GROUP BY clause. Sorry.

PARAMETERS [MaDate] DateTime;
SELECT Format([SomeDateField],"yyyy\_mm") as Periode
, Count([refRea]) as nbreREA
FROM [SomeTable]
WHERE [SomeDateField] Between DateSerial(Year([MaDate]),Month([MaDate])-3,1)
and DateSerial(Year([MaDate]),Month([MaDate])+3,0)
GROUP BY Format([SomeDateField],"yyyy\_mm")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

PARAMETERS [MaDate] DateTime;
SELECT Format(SomeDateField,"yyyy\_mm") as Periode
, Count([refRea]) as nbreREA
FROM [SomeTable]
WHERE [SomeDateField] Between DateSerial(Year([MaDate]),Month([MaDate])-3,1)
and DateSerial(Year([MaDate]),Month([MaDate])+3,0)

With this adapted code i get the error message :
"You try to execute a query without the expression "
Format(SomeDateField ,"yyyy_mm") as part of agregate function

 

[le Nordiste]

John HURRA !

This time it's good !
THANKS A LOT


Le Nordiste - the man from the North of FRANCE-