Follow-up: Dynamic 90 Day Running Average

That works beautifly - almost.  It will give me the 90 day average for today. 
 Very nice, thank you.

Is there a way to generate a 90 day average for each day in the database?

PS - when I started writing this "Frankenstein," it was just designed to 
keep attendance, so the field names weren't that important.  (Poor form, I 
know.  But at the time, I had never used Access before [old DBMS programmer 
of FoxPro and dBase].)  I want to change it, and will take the time to do 
that here soon...or write the whole thing in VB.  :D

Thanks again.

"Ofer Cohen" wrote:

> Let start with the fact that 
> DateSerial(Year([Date]),Month([Date])-3,Day([Date])) 
> 
> Is not a good way of getting the start date
> 
> 1/1/2007 will return -2 for the month
> 
> So, you should have used the DateAdd function to subtruct 3 month's from the 
> date
> DateAdd("m",-3,[Date])
> 
> ***************************
> But for your case, if you want the number of entries in the last three 
> month's try
> 
> Select Count(*) As CountOfEntry From attendance Where [Date] Between 
> DateAdd("m",-3,Date()) And Date()
> 
> Just copy this SQL and try it
> ***************************
> Note: It's not good to use reserved words in Access (Like Date) as Fields 
> names, it requires you to put it in square brackets

0
Utf
5/29/2007 1:10:02 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
507 Views

Similar Articles

[PageSpeed] 8

Doom,

Something like the following should work.  Note that I actually subtracted 
90 days from the the date field in your table, so this assumes that you want 
90 continuous days.  If you want 90 weekdays this becomes a little more 
difficult.

Select T1.DateField, AVG(T2.SomeOtherField) as 90DayMovingAverage
FROM yourTable T1, yourTable T2
WHERE T2.DateField BETWEEN DateAdd("d", -90, T1.DateField) AND T1.DateField
GROUP BY T1.DateField

HTH
Dale
-- 
Email address is not valid.
Please reply to newsgroup only.


"Admiral O. F. Doom" wrote:

> That works beautifly - almost.  It will give me the 90 day average for today. 
>  Very nice, thank you.
> 
> Is there a way to generate a 90 day average for each day in the database?
> 
> PS - when I started writing this "Frankenstein," it was just designed to 
> keep attendance, so the field names weren't that important.  (Poor form, I 
> know.  But at the time, I had never used Access before [old DBMS programmer 
> of FoxPro and dBase].)  I want to change it, and will take the time to do 
> that here soon...or write the whole thing in VB.  :D
> 
> Thanks again.
> 
> "Ofer Cohen" wrote:
> 
> > Let start with the fact that 
> > DateSerial(Year([Date]),Month([Date])-3,Day([Date])) 
> > 
> > Is not a good way of getting the start date
> > 
> > 1/1/2007 will return -2 for the month
> > 
> > So, you should have used the DateAdd function to subtruct 3 month's from the 
> > date
> > DateAdd("m",-3,[Date])
> > 
> > ***************************
> > But for your case, if you want the number of entries in the last three 
> > month's try
> > 
> > Select Count(*) As CountOfEntry From attendance Where [Date] Between 
> > DateAdd("m",-3,Date()) And Date()
> > 
> > Just copy this SQL and try it
> > ***************************
> > Note: It's not good to use reserved words in Access (Like Date) as Fields 
> > names, it requires you to put it in square brackets
> 
0
Utf
5/29/2007 4:43:01 PM
Dale,

My deepest thanks to you for your assistance on this.  It works BEAUTIFULLY. 
 I will endeavor to name my first born after you...oops, already did.  :D

Thanks so very much.

Richard

"Dale Fye" wrote:

> Doom,
> 
> Something like the following should work.  Note that I actually subtracted 
> 90 days from the the date field in your table, so this assumes that you want 
> 90 continuous days.  If you want 90 weekdays this becomes a little more 
> difficult.
> 
> Select T1.DateField, AVG(T2.SomeOtherField) as 90DayMovingAverage
> FROM yourTable T1, yourTable T2
> WHERE T2.DateField BETWEEN DateAdd("d", -90, T1.DateField) AND T1.DateField
> GROUP BY T1.DateField
> 
> HTH
> Dale
> -- 
> Email address is not valid.
> Please reply to newsgroup only.
> 
> 
> "Admiral O. F. Doom" wrote:
> 
> > That works beautifly - almost.  It will give me the 90 day average for today. 
> >  Very nice, thank you.
> > 
> > Is there a way to generate a 90 day average for each day in the database?
> > 
> > PS - when I started writing this "Frankenstein," it was just designed to 
> > keep attendance, so the field names weren't that important.  (Poor form, I 
> > know.  But at the time, I had never used Access before [old DBMS programmer 
> > of FoxPro and dBase].)  I want to change it, and will take the time to do 
> > that here soon...or write the whole thing in VB.  :D
> > 
> > Thanks again.
> > 
> > "Ofer Cohen" wrote:
> > 
> > > Let start with the fact that 
> > > DateSerial(Year([Date]),Month([Date])-3,Day([Date])) 
> > > 
> > > Is not a good way of getting the start date
> > > 
> > > 1/1/2007 will return -2 for the month
> > > 
> > > So, you should have used the DateAdd function to subtruct 3 month's from the 
> > > date
> > > DateAdd("m",-3,[Date])
> > > 
> > > ***************************
> > > But for your case, if you want the number of entries in the last three 
> > > month's try
> > > 
> > > Select Count(*) As CountOfEntry From attendance Where [Date] Between 
> > > DateAdd("m",-3,Date()) And Date()
> > > 
> > > Just copy this SQL and try it
> > > ***************************
> > > Note: It's not good to use reserved words in Access (Like Date) as Fields 
> > > names, it requires you to put it in square brackets
> > 
0
Utf
5/29/2007 4:58:02 PM
If you want the count of previous 3 months for each record,
you will need to use a subquery.

SELECT A.[Date], A.f1, A.f2, ... ,
		(SELECT Count(*)
		 FROM attendance As X
		 WHERE X.[Date] Between
										DateAdd("m",-3, A.[Date]) And A.[Date]
		) As CountOfEntry
FROM attendance As A
-- 
Marsh
MVP [MS Access]


Admiral O. F. Doom wrote:
>That works beautifly - almost.  It will give me the 90 day average for today. 
> Very nice, thank you.
>
>Is there a way to generate a 90 day average for each day in the database?
>
>
>"Ofer Cohen" wrote:
>> But for your case, if you want the number of entries in the last three 
>> month's try
>> 
>> Select Count(*) As CountOfEntry From attendance Where [Date] Between 
>> DateAdd("m",-3,Date()) And Date()

0
Marshall
5/29/2007 5:29:09 PM
Glad I could help.

Dale

"Admiral O. F. Doom" <AdmiralOFDoom@discussions.microsoft.com> wrote in 
message news:C863B8D8-0FB5-4830-BF0D-B326EE9C86F3@microsoft.com...
> Dale,
>
> My deepest thanks to you for your assistance on this.  It works 
> BEAUTIFULLY.
> I will endeavor to name my first born after you...oops, already did.  :D
>
> Thanks so very much.
>
> Richard
>
> "Dale Fye" wrote:
>
>> Doom,
>>
>> Something like the following should work.  Note that I actually 
>> subtracted
>> 90 days from the the date field in your table, so this assumes that you 
>> want
>> 90 continuous days.  If you want 90 weekdays this becomes a little more
>> difficult.
>>
>> Select T1.DateField, AVG(T2.SomeOtherField) as 90DayMovingAverage
>> FROM yourTable T1, yourTable T2
>> WHERE T2.DateField BETWEEN DateAdd("d", -90, T1.DateField) AND 
>> T1.DateField
>> GROUP BY T1.DateField
>>
>> HTH
>> Dale
>> -- 
>> Email address is not valid.
>> Please reply to newsgroup only.
>>
>>
>> "Admiral O. F. Doom" wrote:
>>
>> > That works beautifly - almost.  It will give me the 90 day average for 
>> > today.
>> >  Very nice, thank you.
>> >
>> > Is there a way to generate a 90 day average for each day in the 
>> > database?
>> >
>> > PS - when I started writing this "Frankenstein," it was just designed 
>> > to
>> > keep attendance, so the field names weren't that important.  (Poor 
>> > form, I
>> > know.  But at the time, I had never used Access before [old DBMS 
>> > programmer
>> > of FoxPro and dBase].)  I want to change it, and will take the time to 
>> > do
>> > that here soon...or write the whole thing in VB.  :D
>> >
>> > Thanks again.
>> >
>> > "Ofer Cohen" wrote:
>> >
>> > > Let start with the fact that
>> > > DateSerial(Year([Date]),Month([Date])-3,Day([Date]))
>> > >
>> > > Is not a good way of getting the start date
>> > >
>> > > 1/1/2007 will return -2 for the month
>> > >
>> > > So, you should have used the DateAdd function to subtruct 3 month's 
>> > > from the
>> > > date
>> > > DateAdd("m",-3,[Date])
>> > >
>> > > ***************************
>> > > But for your case, if you want the number of entries in the last 
>> > > three
>> > > month's try
>> > >
>> > > Select Count(*) As CountOfEntry From attendance Where [Date] Between
>> > > DateAdd("m",-3,Date()) And Date()
>> > >
>> > > Just copy this SQL and try it
>> > > ***************************
>> > > Note: It's not good to use reserved words in Access (Like Date) as 
>> > > Fields
>> > > names, it requires you to put it in square brackets
>> > 


0
Dale
5/30/2007 1:30:04 AM
Reply:

Similar Artilces:

Charge Interest from 45 days Invoice
Could I let GP begin to calculate interest on 45 days invoices? From where to setup this? Thanks! -- Lucy First you would need an aging bucket that starts at 45 days, like 45 - 60 days or something. You would set that up in RM setup (tools>setup>sales>receivalbes). Then when you access finance charges (tools>routines>sales>finance charge) you put the bucket that includes the 45 day start date in the lower right hand corner of that window. "Sunstream" wrote: > Could I let GP begin to calculate interest on 45 days invoices? From where to > setup thi...

plotted Average
i have a series of data as a line graph, i want my average to be plotted as well. the average is a single value based on the series not a moving average. i have put the average 'series' in and told the chart to plot empty cells but nothing has appeared except my single value average and it is not formatted the same as the actual series, i.e. the series is 0.895whatever and my average is 0.774589etc. but the series is showing up as a whole number but the average is not, i.e. series is 8.95whatever and average is still 0.774589 thanks anyone Can you give an example of the data as...

how to run other application from ur program
dear friends, i m new to MFC ,but i want to open application like notepad using MFC . i want to send window messages like VK_LWIN VK_UP VK_UP and certain no of other messages to open any other application. how is it possible. Seems like you want a CreateProcess() function. Read about the parameters you provide for this program. You may also use a very simple function ShellExecute(), but it may not give you much flexibility like a control over the process you're creating. Here is an example of opening notepad.exe: ShellExecute(NULL, "open", "notepad.exe", GetPathName...

Laser printers that support check printing (MICR?) from Dynamics GP
I am looking for a list of laser printers that can support check printing by Dynamics GP. We are using dot-matrix on pre-printed form and got tired of them! Your help is greatly appreciated. Bill Are you using Mekorma MICR Check Printing? If so the documentation has list of laser printers. "Bill N" wrote: > I am looking for a list of laser printers that can support check printing by > Dynamics GP. > We are using dot-matrix on pre-printed form and got tired of them! > > Your help is greatly appreciated. > > Bill > > . >...

running macros in excel
Does anyone know if I can run a macro automatically when I open the spreadsheet ? thanks. Call the macro Auto_Open, or add your code to the Workbook_Open event. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "bobuk" <anonymous@discussions.microsoft.com> wrote in message news:fbb201c3f219$2e99ee50$a401280a@phx.gbl... > Does anyone know if I can run a macro automatically when I > open the spreadsheet ? thanks. ...

Protecting while still allowing macros to run
Hi, I have a spreadsheet with macros that update formulas in cells. Is there any way of protecting the worksheet while still allowing the user to run the macros. Hi Tammy, If you use VBA to turn on protection you can look in the help system for the arguments for Protect and you will find one called UserInterfaceOnly:=True which allows the macro to run in a protected workbook, but doesn't allow users to modify stuff manually. Cheers, Shane Devenshire "Tammy" <Tammy@discussions.microsoft.com> wrote in message news:1D21508D-2564-4AFB-AB73-98C7ABC27A7C@microsoft.com...

Running Rules in Special Folders (Outlook and CallPilot)
Software: Outlook 2000 SP-3 and CallPilot Desktop Messaging 2.50 Background: Incoming voice messages are placed in the Callpilot Desktop Messaging Inbox in Outlook. A small icon lights up to indicate that a new message has been received. Question: How can I use a Rule in Outlook to also play a sound in a special folder? So far I am unable to have a rule continuously checking this particular inbox. Thanks, Lee Outlook rules doesn't support that - you might be able to use VBA to alert you - see outlookcode.com if you need help with VBA. -- Diane Poremsky [MVP - Outlook] Author, Teac...

Unknown program running
I get the "critical Stop" sound periodically even when my computer is not being used. Sometimes when I go to click my user name after it has been idle it will say two programs running even if only one is open. Hitting Alt-Ctrl-Del shows either no applications running or only the one I have open never two. How do I find out what program is causing the "critical Stop" alarm? Dennis wrote: > I get the "critical Stop" sound periodically even when my computer > is not being used. Sometimes when I go to click my user name after > it has been idle it w...

vc++ 6.0 application runs on windows 20003 (64 bit CPU 32 bit OS)
Hi I have a VC++ 6.0 application runs on windows 20003 (64 bit CPU and 32 bit OS), it does not work. The same application runs fine on Windows XP and Windows 2000. When I debug it with VC++ 6.0, the break points and the source code totally messed up. I have a if else statement, the debug goes from inside if goes to else block if (...) { ....................... } else { ............... } Does any one have any idea about this? Thank you for your help There is no content whatsoever in the phrase "does not work". There is always a specific statement as to why something do...

running balance
Hi I need help figuring out a formula for a running balance. I have 3 columns one called deposits, another called petty cash disbursements and another called expenses. I want to be able to know how can I get the total amount of money that I have on an account even though I keep on entering different dollar amounts at all times. I know that the formula =A39-B39 is giving me the amount for the first two columns, but I don't know what to do for the third column. I would really appreciate it if someone can help me out. Thanks. Jackie deposits-disbursements-expenses+previous balance ...

Calculating Averages but excluding zero's
Hi, i want to calculate the average of a column but exclude anyzeros in that column. For example: If column A1 contained 2, 2, 0, 2, 2, 0 then the average would be 2. (8/4) NOT (8/6) Thanks. -- fodman ------------------------------------------------------------------------ fodman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31941 View this thread: http://www.excelforum.com/showthread.php?threadid=555103 Try this: =AVERAGE(IF(A1:A6>0,A1:A6)) Enter this as an array formula. Use CTRL-SHIFT-ENTER instead of just Enter. HTH, Elkar "fodman"...

domain controllers do not appear to be running #2
Hi There are a number of errors when running CRM 3.0 and I have the following error when I run the environment diagnostics check : "Some of the domain controllers do not appear to be running. The following list of servers should be checked. PrivUserGroup {8654dfe6-2d69-4ead-8f86-d49f26814c64} ReportingGroup {8654dfe6-2d69-4ead-8f86-d49f26814c64} SQLAccessGroup {8654dfe6-2d69-4ead-8f86-d49f26814c64} UserGroup {8654dfe6-2d69-4ead-8f86-d49f26814c64} " Does anyone have any suggestions ? If I re-run the setup do I risk losing any data/configuration setting on my current installatio...

Running average of yearly ave and monthly data
Hello, I am trying to create a a query that keeps a running percentage of this month's activity versus the yearly average (yealyavg/thismonth). I have already created a query for the yearly average and that is running smooth and updates automatically when new data is added to future months in the table. Goal - to determine % changes in this month's activity versus the yearly average up to this point in time. I am looking at consumer spending habits and want to know where they stand this month (number of purchases) versus their average year to date Table structure, Account Number ...

I can't run the Internet Mail Wizard.
I can't run the Internet Mail Wizard. (I ran once) How I can set the virtual server configuration back to default. Thanks, Holguer On Tue, 9 May 2006 11:58:02 -0700, Holguer W. Borja <HolguerWBorja@discussions.microsoft.com> wrote: >I can't run the Internet Mail Wizard. (I ran once) >How I can set the virtual server configuration back to default. > >Thanks, > >Holguer You shouldn't need to run it again. What is it that makes you think you need to run it again. ...

Identify month in a period and run a formula
I am having trouble with creating this one formula. Can anyone help? I have two columns of dates (start and finish dates). I would like to identify a month (e.g April)that occurs in the period between start and finish and then if the month of April does occur in the period, calculate the number of months between start and finish and divide by a constant (a figure saved on a different sheet). So far I have this: =IF(AND(("01/04/2005">=MIN(I20:I23,H20:H23)),"31/04/2005"<=MAX(I20:I23,H20:H23)),SUM((D18/((YEAR(I20:I23)-YEAR(H20:H23))*12+MONTH(I20:I23)-MONTH(H20:H2...

Running asset transfers thru income statement
I would like to transfer money from an IRA asset account at a brokerage house to a taxable account at the same broker's but want it pass thru the income statement so that it shows as an IRA distribution. A regular transfer doesn't give that option in the drop down menu. Must I run it thru a plug or cash account in a two step process? I hope not! ...

Windows Media Center
Can anyone tell me why this is running all the time on my Vista Enterprise Core to Duo machine at 65% of CPU according to Windows Task Manager? I do not even have Windows Media Center installed on this machine or at least I cannot see where I can run it. To the best of my knowledge it is not part of Vista Enterprise OS??? "Artreid" <artreid@NOSPAMverizon.net> wrote in message news:eIQtiRX5KHA.5548@TK2MSFTNGP04.phx.gbl... > > Can anyone tell me why this is running all the time on my Vista Enterprise > Core to Duo machine at 65% of CPU according to...

Run a macro to copy selective items
I receive daily several lists of part orders from the customer in Excel file that I do not have any control of. In the Excel file: Column A contents catergory 1 to 100 Column B contents items descriptions of each catergory (about 20 different items per catergory) Column C contents quantities of each item that are changed daily. How can I run a macro that will copy from the customer Excel file and paste only certain catergories within my department (i.g catergory 1 -20) and ignore the rests. Appreciate for any of your help Chris -- chrisdtran -------------------------------------------...

How do you do the following using VBA macro automatically?
Hi all, I would like to have the following run every time when the Excel 2003 sheet is opened: so I am going to put the VBA macro into AUTO_RUN function... Now when the Excel 2003 file is opened, I need the following: 1. refresh/recalculate all the sheets in the Excel 2003 file; 2. save all the sheets; 3. close out the file. Please help me translate these into VBA/MACRO... thanks a lot! Excel has no AUTO_RUN function. Did you mean Auto_Open? I would put this code into Thisworkbook Module. Private Sub Workbook_Open() Application.Calculate With ThisWorkb...

I can't follow links from my e-mail, what gives?
I get a message of "This operation has been cancelled due to restrictions in effect on this computer. Please contact you system administrator." This message appears when ever I try to open a link off of an e-mail message. Any ideas? Thanx D3 D3 wrote: > I get a message of "This operation has been cancelled due to restrictions in > effect on this computer. Please contact you system administrator." This > message appears when ever I try to open a link off of an e-mail message. Any > ideas? > > Thanx > D3 Oh, you are claim...

Rule won't run automatically
I set up a rule to forward messages with specific text in the subject to an email address that's in the Outlook global address book. The rule is defined to forward the message upon receipt. If I use "Run Rule Now", the rule works to forward messages to that address. However, the rule doesn't run automatically. Any ideas? ...

Average IF #2
I want to average the amount of time someone spends on a specific date with customers. I have it average the person by user for the entire time (1 week), but I want to average it per day. I am looking to create a formula that looks at 2 columns (Column D has dates, Column I has Usernames). It should then calculate the average time spent if the date and username match. D E I R 3 07/18/07 09:30 beckerm 4 07/18/07 09:39 beckerm :09 5 07/18/07 09:46 beckerm :07 S21 T20 T21 (Average) 07/18/07 beckerm :08 Is there a...

Set Outlook 2007 Calendar Work Week End Time to the next day
We have users in Outlook 2007 (ver: SP2 12.0.6521.5000) that when they want to set their calendar work week end time to be the next day (after midnight) they get an error: "The end date you entered occurs before the start date". This are users that work the overnight shift, how can they specify their work week? Backend: Exchange 2007 "helm" <helm@discussions.microsoft.com> wrote in message news:7BEF5380-B9C3-4B87-93BE-814E4F0D47F1@microsoft.com... > We have users in Outlook 2007 (ver: SP2 12.0.6521.5000) that when they want > to set their calen...

Loose Stock in Days
I'm trying to create a field, LsDays, where a calculation is made like this... Lstock Day1 Day2 Day3 Day4 LsDays 75 25 20 30 10 3.0 In other words LsDays is telling me how many days stock I've got. Days1-4 can be as high as Day50. I'm afraid this has beaten me can anyone help me out? You should store the data vertically, not horizontally, like: StockID, GetStock, DayNumber ' fields xxx, 75 0 xxx -25 1 xxx -20 2 xxx -30 3 xxx -10 ...

Customer Avg Days to Pay LTD is Blank
For some of our customers with a long history, the Avg Days to pay is blank and for others it is not. I suspect it has something to do with National Accounts, but cannot be sure. We've gone beyond a point where I would want to restore the pre fiscal year closing backup, and even if we did, what would we change so that GP doesn't wipe out the LTD avg days. We're on GP 9.0. What now? -- Elisabeth Are your customers performing the sales year end process properly? -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks N...