How to run a date based query for many months

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.

0
le
4/6/2010 6:19:03 AM
access 16762 articles. 3 followers. Follow

9 Replies
1198 Views

Similar Articles

[PageSpeed] 49

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 wrote:
> 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.
> 
0
John
4/6/2010 1:35:21 PM
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.

0
le
4/6/2010 3:00:23 PM
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 wrote:
> 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.
> 
0
John
4/6/2010 4:12:44 PM
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







0
le
4/7/2010 6:22:48 AM
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.
> =A0 =A0 =A0 eg : if i typed 12/02/2010 =A0 i want the result displaying f=
rom
> september 2009 =A0to 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

0
le
4/7/2010 7:51:08 AM
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

le Nordiste wrote:
> 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
> 
0
John
4/7/2010 1:25:46 PM
Hi John,

> PARAMETERS [MaDate] =A0DateTime;
> 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
=85                  one line per working day
=85
2009_09 :54
2009_10 : 43
=85
2009_10 : 24
2009_11 : 2
.....


We are on the way but=85 a little effort more.

THANKS
0
le
4/7/2010 2:19:47 PM
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

le Nordiste wrote:
> 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
0
John
4/7/2010 3:04:22 PM
John HURRA !

This time it's good !
THANKS A LOT


Le Nordiste - the man from the North of FRANCE-


On 7 avr, 17:04, John Spencer <spen...@chpdm.edu> wrote:
> Forgot the GROUP BY clause. =A0Sorry.
>
> PARAMETERS [MaDate] =A0DateTime;
> 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
>
0
le
4/8/2010 8:09:48 AM
Reply:

Similar Artilces:

easy way around "not updateable query" for sums?
I have a table. I am trying to update that table based on a query. The query involved used sums. When I try to this, I get "this is not an updatable query." I know the cause for it, the fact that the query has sums. I also know the way around it. Run my query as a make table query, then build my update query from that. This project would have me doing this a couple of dozen times. I really do not want to build 24 make table queries, 24 update queries, and then have to delete those 24 tables. If nothing else, I would expect that would seriously fragment my databse,...

Sum of a column based on color coded cells
Looking for some help on what sounded like an easy thing to do, but has me stumped. I have multiple columns of job names that get color coded when complete or need to be escalated etc.. A typical column may look like this. JS-2133 JO-2011 MO-3320 NT-4510 and so on. As the jobs are completed the cell is filled in green. I would like a total at the bottom of the column, how do you total only the green cells? Thanks, MikeM Mike, Take a look here for some help and examples: http://www.cpearson.com/excel/colors.htm John "MikeM" <anonymous@discussions.microsoft.com> wro...

Need only one DLL instance to run...
Hi all, 1: if two apps load the same DLL - LoadLibrary(...) - system will create to different instance of the DLL... Now, in my DLL i've a CList and i need it to be visible to all instance and all apps!!!! Is there a way!? 2: I need the dll to remain loaded till machine reboot!!! Is it possible!? Thanks Ale >if two apps load the same DLL - LoadLibrary(...) - system will create to >different instance of the DLL... >Now, in my DLL i've a CList and i need it to be visible to all instance and >all apps!!!! > >Is there a way!? It'll be difficult to share a ...

Dynamic source data based on Today
I am creating a production barchart that is updated daily. I want the chart to plot from 3 days old to 4 days in the future. I would like the middle of the chart to represent today. Tomorrow's data will become today's data when everything shifts by one at midnight. I am pretty new to macro so please speak in laymans terms. No need for a macro. Adapt the ideas at Dynamic Charts http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html particularly example 2 -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary app...

credentials to run this report are not stored
Hi all, I'm getting this problem when I try and create a timed subscription. Error: ...credentials to run this report are not stored..... ok I created another folder under the same folder where I'm having this problem. loaded the report and I have no problem. I even moved the current folder with that report in it. Creted another folder with the same name and still the same problem with that named folder. With a different name for the folder I have no problem??? I'm stuck here.. almost all post just talk about storing the credentials, Already done and works...

Automatically run macro
My name is Mike and i have a question about microsoft excel macro's. Attached is a copy of the excel sheet im working on. Below the excel sheet is the macro I built. Some of the cells contain given values and some cells are calculated from formulas. Cell (G4) is my given value...it is related to cell (C32). The point is, I plug a value into cell (C10) and it runs through the rest of the calcs in the other cells and gives me a value to cell (C32). I built a macro that works as a goal seek pretty much. The macro makes cell (C32) equal to cell (G4) and gives me the value for cell (C10). I wan...

Tasks: Created Date appears incorrect
If I add "Created Date" to my tasks view, it seems to be updated to the current date each time the task is assigned to a new person. If I go into the task properties however, it shows the actual task creation date. Any idea how to display the actual task creation date? Thanks, Walt ...

Query Exchange/AD for users that have OWA enabled
Does anyone know if there is a query that I can run to give me a list of all user accounts that have the OWA feature enabled? We have 900 accounts, and we only allow some user "types" to have OWA access... We are running Exchange 2003 on Win2003 server, Active Directory, etc. Please forgive me if I am posting to the incorrect group (Exchange instead of AD?) Thanks. Donna ...

run time error 10-22-03
I am having a lot of trouble when I open up word I get run time error 52 in VB. I have tried uninstalling word and reinstalling it. WE have tried deleting the macro but still to no avail can someone help me please? ...

Repost: Error running Report in an Access 2003 db from Access 2007
Ok, clarification - ignore the code from my original post, some of the reports do work. The ones that don't are reports that I have being filtered. Here is the code from one of those buttons: Private Sub Ok_Click() On Error GoTo Ok_Click_Err 'using the customer sub form for customer state report to filter the report, clicking ok will open report for selected state Dim stDocName As String Dim stLinkCriteria As String If Not IsNull(Me.Search_Results) Then stLinkCriteria = "[StateOrProvince] = """ & Me![Search Results] & """"...

Silly date/text question
Hi- is there an easy way to show dates/text with the day eg. July 17th similar to Word 2k format eg July 17th . Currently cut/paste from Word 2k - to get raised 'th' or 'rd' or 'nd' etc. TIA Dan In custom format use mmmm dt\h >-----Original Message----- >Hi- is there an easy way to show dates/text with the day eg. July 17th >similar to Word 2k format eg July 17th . Currently cut/paste from Word 2k - >to get raised 'th' or 'rd' or 'nd' etc. >TIA >Dan > > >. > Are you looking for the th, rd, nd stuff or are ...

Automatically display set text based on users composition
Hi, im trying to do something really simple, trouble is i dont know what the feature's called to be able to search for tips on how to do it. Basically in outlook messages, when a user begins writing a sentence e.g. "in the terms of" i need a tag to pop up that allows the user to press enter and then the remainder of what they will want to type in will be inserted in, its a yellow tag that comes up above the words. i dont know where it needs to be created and enabled. Cheers, Rhys. ...

Date Formatting #11
Can someone help me with my date formatting problem? I want to enter a date as text as in: January 25, 2001 and want Excel to display the date as: 1/25/01. I have tried using a date format but when I type the date in as text it is just displayed as i typed it in (January 25, 2001) Thanks to anyone who can help. :) format the cell as custom and in the input box type m/d/y -- MACRE ----------------------------------------------------------------------- MACRE0's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1084 View this thread: http://www.excelforum.com/showt...

Excel Continuous Running Total
I posted a message earlier and have received a partial solution. I want to keep track of how much stock prices go up or down with a running total of how much they go up or down over several days until the direction changes. For example, if price go up 10 on Mon, 20 on Tues, 30 on Thurs and down 10 on Fri I want my running total column to show a positive number of 60 and then a red number of 10 and continue adding the amount of the total of the down days until the market shows an up day. The formula I am now using total the first and second day but does not do a running total count if t...

display changing label caption on form as sub runs w/o screen flic
let's say i have this routine Sub Test label1.caption = "Starting ... " 'do events label1.caption = "Getting there ... " 'do events label1.caption = "Finished! ... " End Sub on my form, i have label1 right in the middle what happens is sometimes the message will change, and then sometimes it wont, or it will show the first one, skip the second and jump to the third etc etc etc so it is inconsistent. is there a way to make sure the label caption displays consistently, on time, wh...

Automaticly change Void date to system date when Voiding document
When voiding payable documents Checks/Vouchers. When the use clicks the check box change the Void Date and Post date to the system date. This will stop user for getting the error that the period is closed or posting to the incorrect period. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" ...

Get query from Access
Hello, trying to do a query. So I do the following: Data->get external data->New database query, then from the Choose Data source, I choose MS 97 ACCESS DATABASE*, then I select the database. This brings me to the QUERY wizard-Choose columns When I double click on the table/query I want, it doesn't bring the columns to choose. It changes the + sign to - sign but nothing else. I try a different database, and on this one it does work. So not sure why only on one database it works. Any suggestions? Would appreciate it. thanks, Juan ...

Money 2002 will not run
I had been using Money 2002 for approx. 3 yrs on my home pc (Dell Dim 2100, XP Home Ed. w/SP2). Last year, it simply would not launch. No error message, no splash screen, no app opening, no process listed in Task Manager. Just.... nothing.... The only change that was made to the system since M2k2 last ran was upgrading my a/v solution from Trend Micro PC-Cillin to TM Internet Security. I have tried disabling every aspect of the Internet Security product, as well as completely un-installing the app, and then attempting to run Money, but the same thing (nothing) happens. I was considerin...

What tables and database does the Knowledge Base use?
Hi, we are wanting to make Knowledge Basee articles available online for customers. From what I understand we would have to setup something manually to display SQL information on the website. So what I would need to know is what fields and databases will we be using if we do something like this? ------=_NextPart_0001_DB294826 Content-Type: text/plain Content-Transfer-Encoding: 7bit "Admin Matt" <Admin.Matthew@gmail.com> wrote: > Hi, we are wanting to make Knowledge Basee articles available online > for customers. From what I understand we would have to setup something ...

HOW TO: Plot a Team Name on a chart based on two values
Hi, I have the following table exmaple, what I like to do is to plot the team as where their values cross, e.g. for TEAM 01 where 5 and 60 cross on a graph having Revenue as the Y axis and Clients as the X axis. COLUMN A COLUMN B COLUMN C TEAMS CLIENTS REVENUE TEAM 01 5 60 TEAM 02 10 120 TEAM 03 15 180 TEAM 04 6 72 TEAM 05 12 144 TEAM 06 18 216 TEAM 07 7 84 TEAM 08 14 168 TEAM 09 21 252 TEAM 10 8 96 Any guidence, much appriciated. I've done this using Ron Bovey's XY Chart Labeler www.appspro.com "Kevin McCartney" <KevinMcCartney@discussions.microsoft.com> wrot...

Re: Workflow just wont run automatically, i have to run them manually
Yes, but i realized what i was doing wrong. I assumed [bad idea] that if i create a case and hit Save & Close the first time, taht the rule will run. In order for the rule to run automatically, it has to be Save, once it saves it, then Save & Close. Thanks for your reply. "Hi, Did you check the workflow monitor to see if the rules get triggered correctly and complete sucessfully ? Have a nice day, St=E9phane Dorrekens " --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://...

MRP Workbench Schedule Receipt Date
When using the MRP workbench, if the flag is on in MRP Preference Defaults to move in, the scheduled receipt reflects the MRP suggested date, not the current promise date on the Purchase Order or MO Due Date. The workbench makes the assumption that the exception message has been acted upon. If a Planner\Buyer used the workbench when the Move In flag is on, they will be given information that lets them believe that the component delivery schedule (PO or MO) supports the Forecast\Sales Order Demand, which is not true. The Workbench should have an option to use the PO promise date and the...

Adding a month to a series of dates
Hi I'm probably being stupid, but here's my dilemma! 8o) I have a date in cell A1, say 19/3/04 and I need to fill in the next 35 dates automatically - each being incremented by exactly one month: 19/4/04 19/5/04 19/6/04 ....etc I can't figure out an easy way to do this, such as adding a fixed number of days, as each of the month's have a different number of days. Any ideas? TIA Andy fill in A1 with 19/03/04 fill in A2 with 19/04/04 select both put the mouse pointer in the lower right corner of the selection (you'll get a black cross) and drag it down using the le...

Outlook Still Running
I am running XP Pro with Office 2003 Pro and sometimes when I exit out of Outlook, Outlook.exe and Winword.exe stay as processes still running. Does anyone else have this problem and what can do to make sure this does not happen? Thanks for your help -- Neil Remove ABCD from Email address to reply <neil154ABCD@earthlink.net> wrote: > I am running XP Pro with Office 2003 Pro and sometimes when I exit > out of Outlook, Outlook.exe and Winword.exe stay as processes still > running. Does anyone else have this problem and what can do to make > sure this does not happ...

combo box in a query
Hi, Is it possible, do you think, to insert a combo box into the criteria section of a query? The user of the query will have a lot of units to choose from in the query and it would be easier for the user to have a drop down box to make a selection from. Can this be inserted into the query so that it appears and is functional when the query is run? If so...how would a novice go about it? Thanks, Regards, oic3120 Not directly, but you can enter a reference to a combo box as a parameter in the query. Create an unbound form with a combo box on it which lists all the possible values in o...