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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rolling Window
I need a 7 day rolling window average for each date shown on a query, for
example: Date range 10/1/2008 - 10/31/2008 Fields: Daily Average MTD Average Previous Month Average 12 Month Average 7 day rolling window average Explination: Daily average comes from the primary table, just the total line average MTD avg comes from a second query, linked in PrevMo avg comes form a second query, linked in 12Mo avg comes from a second query, linked in Problem: The 7 day average is supposed to show the last 7 days average for each date. For example, if the report was ran on 10/23/2008, a single line for every day for the month of october would be shown, Daily average would flucuate for each day, the mtd, prevmo, and 12mo averages would all be the same, but the 7 day rolling window should show the average as such; so that the line for the 23rd, would show an average for 10/17-10/23, likewise the line for the 22nd, would show an average for 10/16-10/22. I have accomplished this task with a simple program shown below, but the query takes a couple of hours to run, I need it in a couple of minutes, max. Any help would be AWESOME! Thanks! Function Scrap7DayAverage(ByVal IDMachine As Integer, ByVal Dt As Date) As Single On Error GoTo ErrHand Dim ScrapPerc(7) As Single, I As Integer For I = 0 To 6 If ECount("*", "qryScrapPercDailyCurMo", "IDMachine = " & IDMachine & " And Date = #" & Dt - I & "#") = 0 Then ScrapPerc(I) = 0 Else ScrapPerc(I) = ELookUp("ScrapPerc", "qryScrapPercDailyCurMo", "IDMachine = " & IDMachine & " And Date = #" & Dt - I & "#") End If Next I ScrapPerc(7) = ScrapPerc(0) + ScrapPerc(1) + ScrapPerc(2) + ScrapPerc(3) + ScrapPerc(4) + ScrapPerc(5) + ScrapPerc(6) ScrapPerc(7) = ScrapPerc(7) / 7 Scrap7DayAverage = ScrapPerc(7) ExitHand: For I = 0 To 7 ScrapPerc(I) = 0 Next I I = 0 IDMachine = 0 Dt = 0 Exit Function ErrHand: Echo True DoCmd.SetWarnings True eNumb = Err.Number eDesc = Err.Description ErrorLog "Production", "Scrap7DayAverage", "ePr-305", ActiveFrm, eNumb, eDesc eNumb = 0 eDesc = "" GoTo ExitHand End Function |
#2
|
|||
|
|||
Rolling Window
A rolling seven day average could be calculated in a query
SELECT A.TheDate, Avg(B.Quantity) as RollingAverage FROM YourTable as A INNER JOIN YourTable as B ON A.TheDate = B.TheDate-6 and A.TheDate = B.TheDate John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Doc wrote: I need a 7 day rolling window average for each date shown on a query, for example: Date range 10/1/2008 - 10/31/2008 Fields: Daily Average MTD Average Previous Month Average 12 Month Average 7 day rolling window average Explination: Daily average comes from the primary table, just the total line average MTD avg comes from a second query, linked in PrevMo avg comes form a second query, linked in 12Mo avg comes from a second query, linked in Problem: The 7 day average is supposed to show the last 7 days average for each date. For example, if the report was ran on 10/23/2008, a single line for every day for the month of october would be shown, Daily average would flucuate for each day, the mtd, prevmo, and 12mo averages would all be the same, but the 7 day rolling window should show the average as such; so that the line for the 23rd, would show an average for 10/17-10/23, likewise the line for the 22nd, would show an average for 10/16-10/22. I have accomplished this task with a simple program shown below, but the query takes a couple of hours to run, I need it in a couple of minutes, max. Any help would be AWESOME! Thanks! Function Scrap7DayAverage(ByVal IDMachine As Integer, ByVal Dt As Date) As Single On Error GoTo ErrHand Dim ScrapPerc(7) As Single, I As Integer For I = 0 To 6 If ECount("*", "qryScrapPercDailyCurMo", "IDMachine = " & IDMachine & " And Date = #" & Dt - I & "#") = 0 Then ScrapPerc(I) = 0 Else ScrapPerc(I) = ELookUp("ScrapPerc", "qryScrapPercDailyCurMo", "IDMachine = " & IDMachine & " And Date = #" & Dt - I & "#") End If Next I ScrapPerc(7) = ScrapPerc(0) + ScrapPerc(1) + ScrapPerc(2) + ScrapPerc(3) + ScrapPerc(4) + ScrapPerc(5) + ScrapPerc(6) ScrapPerc(7) = ScrapPerc(7) / 7 Scrap7DayAverage = ScrapPerc(7) ExitHand: For I = 0 To 7 ScrapPerc(I) = 0 Next I I = 0 IDMachine = 0 Dt = 0 Exit Function ErrHand: Echo True DoCmd.SetWarnings True eNumb = Err.Number eDesc = Err.Description ErrorLog "Production", "Scrap7DayAverage", "ePr-305", ActiveFrm, eNumb, eDesc eNumb = 0 eDesc = "" GoTo ExitHand End Function |
#3
|
|||
|
|||
Rolling Window
Forgot the group by clause
SELECT A.TheDate, Avg(B.Quantity) as RollingAverage FROM YourTable as A INNER JOIN YourTable as B ON A.TheDate = B.TheDate-6 and A.TheDate = B.TheDate GROUP BY A.TheDate John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County John Spencer wrote: A rolling seven day average could be calculated in a query SELECT A.TheDate, Avg(B.Quantity) as RollingAverage FROM YourTable as A INNER JOIN YourTable as B ON A.TheDate = B.TheDate-6 and A.TheDate = B.TheDate John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Doc wrote: I need a 7 day rolling window average for each date shown on a query, for example: Date range 10/1/2008 - 10/31/2008 Fields: Daily Average MTD Average Previous Month Average 12 Month Average 7 day rolling window average Explination: Daily average comes from the primary table, just the total line average MTD avg comes from a second query, linked in PrevMo avg comes form a second query, linked in 12Mo avg comes from a second query, linked in Problem: The 7 day average is supposed to show the last 7 days average for each date. For example, if the report was ran on 10/23/2008, a single line for every day for the month of october would be shown, Daily average would flucuate for each day, the mtd, prevmo, and 12mo averages would all be the same, but the 7 day rolling window should show the average as such; so that the line for the 23rd, would show an average for 10/17-10/23, likewise the line for the 22nd, would show an average for 10/16-10/22. I have accomplished this task with a simple program shown below, but the query takes a couple of hours to run, I need it in a couple of minutes, max. Any help would be AWESOME! Thanks! Function Scrap7DayAverage(ByVal IDMachine As Integer, ByVal Dt As Date) As Single On Error GoTo ErrHand Dim ScrapPerc(7) As Single, I As Integer For I = 0 To 6 If ECount("*", "qryScrapPercDailyCurMo", "IDMachine = " & IDMachine & " And Date = #" & Dt - I & "#") = 0 Then ScrapPerc(I) = 0 Else ScrapPerc(I) = ELookUp("ScrapPerc", "qryScrapPercDailyCurMo", "IDMachine = " & IDMachine & " And Date = #" & Dt - I & "#") End If Next I ScrapPerc(7) = ScrapPerc(0) + ScrapPerc(1) + ScrapPerc(2) + ScrapPerc(3) + ScrapPerc(4) + ScrapPerc(5) + ScrapPerc(6) ScrapPerc(7) = ScrapPerc(7) / 7 Scrap7DayAverage = ScrapPerc(7) ExitHand: For I = 0 To 7 ScrapPerc(I) = 0 Next I I = 0 IDMachine = 0 Dt = 0 Exit Function ErrHand: Echo True DoCmd.SetWarnings True eNumb = Err.Number eDesc = Err.Description ErrorLog "Production", "Scrap7DayAverage", "ePr-305", ActiveFrm, eNumb, eDesc eNumb = 0 eDesc = "" GoTo ExitHand End Function |
#4
|
|||
|
|||
Rolling Window
Thank you for the response!
I'm slightly confused, so I'm hoping if I tell you my table names, you can hook me up with a solution: tblProduction 'Main data table tblDates 'List of dates from 1/1/2007 - 12/31/2009 qryActiveMachines 'Query of active machines qryDatesCurMo 'Creates list of machines for every date (for example, Machine 1 will have a line for every date beginning 1/1/2007 through 12/31/2009) qryScrapPercDailyCurMo 'Sums production data to create daily scrap percentage for each machine, and each date. If no data was entered, shows a 0% scrap rate qryScrapPercMTD 'Sums production data to create MTD average scrap rate qryScrapPerc12Mo 'Sums production data to create 12 Month scrap rate qryScrapPercPrevMo 'Sums production data for previous month All queries are brought together in qryChartData, here's the SQL SELECT qryDatesCurMo.IDMachine, qryDatesCurMo.Machine, qryDatesCurMo.Date, Format([qryDatesCurMo].[Date],"d") AS [Day], IIf(IsNull([qryScrapPercDailyCurMo].[ScrapPerc]),0,[qryScrapPercDailyCurMo].[ScrapPerc]) AS DailyScrap, IIf(IsNull([qryScrapPercMTD].[ScrapPerc]),0,[qryScrapPercMTD].[ScrapPerc]) AS MTDScrap, IIf(IsNull([qryScrapGoalCur].[Goal]),0,[qryScrapGoalCur].[Goal]) AS ScrapGoal, IIf(IsNull([qryScrapPercPrevMo].[ScrapPerc]),0,[qryScrapPercPrevMo].[ScrapPerc]) AS ScrapPrevMo, IIf(IsNull([qryScrapPerc12Mo].[ScrapPerc]),0,[qryScrapPerc12Mo].[ScrapPerc]) AS Scrap12Mo FROM ((((qryDatesCurMo LEFT JOIN qryScrapPercDailyCurMo ON (qryDatesCurMo.IDMachine = qryScrapPercDailyCurMo.IDMachine) AND (qryDatesCurMo.Date = qryScrapPercDailyCurMo.Date)) LEFT JOIN qryScrapPercMTD ON qryDatesCurMo.IDMachine = qryScrapPercMTD.IDMachine) LEFT JOIN qryScrapGoalCur ON qryDatesCurMo.IDMachine = qryScrapGoalCur.IDMachine) LEFT JOIN qryScrapPercPrevMo ON qryDatesCurMo.IDMachine = qryScrapPercPrevMo.IDMachine) LEFT JOIN qryScrapPerc12Mo ON qryDatesCurMo.IDMachine = qryScrapPerc12Mo.IDMachine ORDER BY qryDatesCurMo.Date, qryDatesCurMo.Machine; So what I need added to this, is a 7 day rolling window for each machine and date of the data in qryScrapPerCailyCurMo. Mean while, I'm going to be trying to figure out the 'RollingAverage' bit. Thanks again! "John Spencer" wrote: A rolling seven day average could be calculated in a query SELECT A.TheDate, Avg(B.Quantity) as RollingAverage FROM YourTable as A INNER JOIN YourTable as B ON A.TheDate = B.TheDate-6 and A.TheDate = B.TheDate John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Doc wrote: I need a 7 day rolling window average for each date shown on a query, for example: Date range 10/1/2008 - 10/31/2008 Fields: Daily Average MTD Average Previous Month Average 12 Month Average 7 day rolling window average Explination: Daily average comes from the primary table, just the total line average MTD avg comes from a second query, linked in PrevMo avg comes form a second query, linked in 12Mo avg comes from a second query, linked in Problem: The 7 day average is supposed to show the last 7 days average for each date. For example, if the report was ran on 10/23/2008, a single line for every day for the month of october would be shown, Daily average would flucuate for each day, the mtd, prevmo, and 12mo averages would all be the same, but the 7 day rolling window should show the average as such; so that the line for the 23rd, would show an average for 10/17-10/23, likewise the line for the 22nd, would show an average for 10/16-10/22. I have accomplished this task with a simple program shown below, but the query takes a couple of hours to run, I need it in a couple of minutes, max. Any help would be AWESOME! Thanks! Function Scrap7DayAverage(ByVal IDMachine As Integer, ByVal Dt As Date) As Single On Error GoTo ErrHand Dim ScrapPerc(7) As Single, I As Integer For I = 0 To 6 If ECount("*", "qryScrapPercDailyCurMo", "IDMachine = " & IDMachine & " And Date = #" & Dt - I & "#") = 0 Then ScrapPerc(I) = 0 Else ScrapPerc(I) = ELookUp("ScrapPerc", "qryScrapPercDailyCurMo", "IDMachine = " & IDMachine & " And Date = #" & Dt - I & "#") End If Next I ScrapPerc(7) = ScrapPerc(0) + ScrapPerc(1) + ScrapPerc(2) + ScrapPerc(3) + ScrapPerc(4) + ScrapPerc(5) + ScrapPerc(6) ScrapPerc(7) = ScrapPerc(7) / 7 Scrap7DayAverage = ScrapPerc(7) ExitHand: For I = 0 To 7 ScrapPerc(I) = 0 Next I I = 0 IDMachine = 0 Dt = 0 Exit Function ErrHand: Echo True DoCmd.SetWarnings True eNumb = Err.Number eDesc = Err.Description ErrorLog "Production", "Scrap7DayAverage", "ePr-305", ActiveFrm, eNumb, eDesc eNumb = 0 eDesc = "" GoTo ExitHand End Function |
Thread Tools | |
Display Modes | |
|
|