Moving average trouble

[haydenbottoms]

I'm having difficulty figuring out the sql code to compute a moving average. I've looked at other forum posts and looked online, and I can't figure out exactly what part of the code I'm supposed to replace with my own things.

I have a query that pulls data from a table called Data. I'd like to add a column to the query that will be the moving average -- For example, for a Month Beginning Date of 12/2013, it would average the SumOfCount variable for 10/2013, 11/2013, and 12/2013.


This is the sql code for my query without the moving average:
SELECT Data.Behavior, Sum(Data.Count) AS SumOfCount, Data.[Month Beginning Date], [First Name + Last Name].Wing, Data.[Full Name]
FROM Data INNER JOIN [First Name + Last Name] ON Data.[Full Name] = [First Name + Last Name].[Full Name]
GROUP BY Data.Behavior, Data.[Month Beginning Date], [First Name + Last Name].Wing, Data.[Full Name]
HAVING (((Data.Behavior)="PRNs") AND ((Data.[Full Name])=[forms]![report parameters]![fullname]));


Is anyone able to help me figure out how to insert that column into my code?

Thanks!!

 

[plog]

Can you provide sample data from your tables (include table and field names), then also provide the data that should be returned from this query based on that sample data.

Format your data like this:

TableNameHere
Field1Name, Field2Name, Field3Name...
David, 12/2/2012, 17
Steve, 1/3/2008, 91
Ann, 4/7/2009, 18

 

[haydenbottoms]

Query name: Individual Longitudinal Query (based on table: Data)

Behavior, SumOfCount, Month Beginning Date, Wing, Full Name

PRN, 8, 01/2012, Behavioral, John Smith
PRN, 7, 02/2012, Behavioral, John Smith
PRN, 4, 03/2012, Behavioral, John Smith
PRN, 4, 04/2012, Behavioral, John Smith
PRN, 5, 05/2012, Behavioral, John Smith
PRN, 11, 06/2012, Behavioral, John Smith
PRN, 9, 07/2012, Behavioral, John Smith
PRN, 21, 08/2012, Behavioral, John Smith
PRN, 27, 09/2012, Behavioral, John Smith
PRN, 24, 10/2012, Behavioral, John Smith

 

[plog]

Now you've confused me even further. I would like you to post starting data and ending data. I have no idea which you posted.

Try again. Post sample data going in and what the query should produce based on that sample data.

 

[haydenbottoms]

Sorry.

The query pulls simple data from a table (variable names: Behavior, SumOfCount, Month Beginning Date, Wing, Full Name). Everything I posted before is in the table, and the query just pulls that as is. Then, I want the query to compute the moving average, which I forgot to put in before.



Table name: Data

Field names: Behavior, SumOfCount, Month Beginning Date, Wing, Full Name, Moving Average

PRN, 8, 01/2012, Behavioral, John Smith
PRN, 7, 02/2012, Behavioral, John Smith
PRN, 4, 03/2012, Behavioral, John Smith, 3.67
PRN, 4, 04/2012, Behavioral, John Smith, 5
PRN, 5, 05/2012, Behavioral, John Smith, 4.33
PRN, 11, 06/2012, Behavioral, John Smith, 6.67
PRN, 9, 07/2012, Behavioral, John Smith, 8.33
PRN, 21, 08/2012, Behavioral, John Smith, 13.67
PRN, 27, 09/2012, Behavioral, John Smith, 19
PRN, 24, 10/2012, Behavioral, John Smith , 24

 

[plog]

That doesn't make sense. How is the moving average for 3/2012 3.67?

(8 + 7 + 4)/3 = 6.33

Is that just a typo? Also provide more examples--is your entire dataset all PRN, Behavioral, John Smith data? If not, give me samples of what else is in there and how that should look.

 

[haydenbottoms]

You're right -- that was a typo.

The query has criteria in it to only select PRN, John Smith data. That's what the HAVING line represents in the SQL code:

This is the sql code for my query without the moving average:
SELECT Data.Behavior, Sum(Data.Count) AS SumOfCount, Data.[Month Beginning Date], [First Name + Last Name].Wing, Data.[Full Name]
FROM Data INNER JOIN [First Name + Last Name] ON Data.[Full Name] = [First Name + Last Name].[Full Name]
GROUP BY Data.Behavior, Data.[Month Beginning Date], [First Name + Last Name].Wing, Data.[Full Name]
HAVING (((Data.Behavior)="PRNs") AND ((Data.[Full Name])=[forms]![report parameters]![fullname]));




If I wanted assaults, then it might be:

Assaults, 0, 01/2012, Behavioral, John Smith
Assaults, 1, 02/2012, Behavioral, John Smith
Assaults, 2, 03/2012, Behavioral, John Smith, 1
Assaults, 0, 04/2012, Behavioral, John Smith, 1
Assaults, 1, 05/2012, Behavioral, John Smith, 1
Assaults, 2, 06/2012, Behavioral, John Smith, 1
Assaults, 0, 07/2012, Behavioral, John Smith, 1
Assaults, 1, 08/2012, Behavioral, John Smith, 1
Assaults, 2, 09/2012, Behavioral, John Smith, 1
Assaults, 0, 10/2012, Behavioral, John Smith, 1


Or if I wanted a different patient, it might be
PRN, 7, 01/2012, Environmental, Matt Anderson
PRN, 6, 02/2012, Environmental, Matt Anderson
PRN, 3, 03/2012, Environmental, Matt Anderson, 5.33
PRN, 3, 04/2012, Environmental, Matt Anderson, 4
PRN, 4, 05/2012, Environmental, Matt Anderson, 3.33
PRN, 10, 06/2012, Environmental, Matt Anderson, 5.67
PRN, 8, 07/2012, Environmental, Matt Anderson, 7.33
PRN, 20, 08/2012, Environmental, Matt Anderson, 1.67
PRN, 26, 09/2012, Environmental, Matt Anderson, 18
PRN, 23, 10/2012, Environmental, Matt Anderson, 23

 

[plog]

I'm trying to determine what makes the data group together. I'm not certain if its the Behavior field or the Full Name field. Is it just one of those or both?

 

[haydenbottoms]

Both, I think. Both of those variables have selection criteria. The record has to have a particular behavior (e.g., PRN) AND a particular Full Name (e.g., John Smith) to be returned in the query.


By the way, I really really really appreciate your help with this.

 

[plog]

No problem. Last question--what happens if data isn't there for a month:



Assaults, 0, 01/2012, Behavioral, John Smith
Assaults, 1, 02/2012, Behavioral, John Smith
Assaults, 2, 04/2012, Behavioral, John Smith,
Assaults, 3, 05/2012, Behavioral, John Smith,


What should the running total be for 4/2012 and 5/2012?

 

[haydenbottoms]

Hmmm good question. Hopefully this will never happen. It seems like it would make the most sense to do the average of the points that are available.

for 4/2012, you would avg 4/2012, 3/2012, and 2/2012
(2 + ? + 1)/2 = 1.5

for 5/2012, you would avg 5/2012, 4/2012, and 3/2012
(3 + 2 + ?)/2 = 2.5


Assaults, 0, 01/2012, Behavioral, John Smith
Assaults, 1, 02/2012, Behavioral, John Smith
Assaults, 2, 04/2012, Behavioral, John Smith, 1.5
Assaults, 3, 05/2012, Behavioral, John Smith, 2.5



Is that doable?

 

[plog]

Yes and here's how to do it. First make a sub query using this SQL:

SELECT Data.Behavior, Data.SumOfCount,
   CDate(Replace([Month Beginning Date],"/","/1/")) AS MonthBegin, 
   Data.Wing, Data.[Full Name], [Behavior] & [Wing] & [Full Name] AS Linker,
   Data.[Month Beginning Date]
FROM Data;

Paste that into a query and save it as 'sub_ThreeMonthAverage'. It prepares your data: Because [Month Beginning Date] is not a date, but needs to be to do comparison among other dates, it converts it to a date in the [MonthBegin] field. Also, it sets up [Linker] field which is a string of all the field that you are grouping your data with so that it can find records to calculate the average on.

The below query will produce the results you want:

SELECT sub_ThreeMonthAverage.Behavior, sub_ThreeMonthAverage.SumOfCount, 
   sub_ThreeMonthAverage.[Month Beginning Date], sub_ThreeMonthAverage.Wing, 
   sub_ThreeMonthAverage.[Full Name],
   DAvg("[SumOfCount]","sub_ThreeMonthAverage","[Linker]='" & [Linker] & "' AND (CDate('" & [MonthBegin] & "') - [MonthBegin]) <95 AND  (CDate('" & [MonthBegin] & "') - [MonthBegin]) >=0") AS MovingAverage
FROM sub_ThreeMonthAverage;

 

[haydenbottoms]

This is working great! Well, kinda. It seems to be doing things right, except I'm getting #Error for MonthBegin, so then of course I'm getting #Error for MovingAverage

Here's what the results of sub_ThreeMonthAverage look like:
Behavior, SumOfCount, MonthBegin, Full Name, Linker, Month Beginning Date
Assaults, 6, #Error, John Smith, 08/2013
Assaults, 0, #Error, John Smith, 09/2013
Assaults, 1, #Error, John Smith, 10/2013
Assaults, 3, #Error, John Smith, 11/2013


And here's what the results of ThreeMonthAverage look like:
Behavior, SumOfCount, Month Beginning Date, Full Name, MovingAverage
Assaults, 6, 08/2013, John Smith, #Error
Assaults, 0, 09/2013, John Smith, #Error
Assaults, 1, 10/2013, John Smith, #Error
Assaults, 3, 11/2013, John Smith, #Error

 

[plog]

Can you post a database with some records from Data? In my coding I assumed it was a text field and always had a '/' in it.

 

[haydenbottoms]

Yes, but don't make fun of me if I've done things in a stupid way -- I'm new at this.

 

[plog]

[Month Beginning Date] was a date, so change sub_ThreeMonthAverage to this:

SELECT Data.Behavior, Data.Count, Data.[Month Beginning Date] AS MonthBegin, Data.[Full Name], [Behavior] & [Full Name] AS Linker, Data.[Month Beginning Date]
FROM Data;

I also found a slight calculation error, which made it read 4 months instead of 3. So change the main query to this:

SELECT sub_ThreeMonthAverage.Behavior, sub_ThreeMonthAverage.Count, sub_ThreeMonthAverage.[Month Beginning Date], sub_ThreeMonthAverage.[Full Name], DAvg("[Count]","sub_ThreeMonthAverage","[Linker]='" & [Linker] & "' AND (CDate('" & [MonthBegin] & "') - [MonthBegin]) <95 AND  (CDate('" & [MonthBegin] & "') - [MonthBegin]) >=0") AS MovingAverage
FROM sub_ThreeMonthAverage;

 

[haydenbottoms]

Getting close! Thanks so so so much!!!

No errors this time, but the calculation isn't correct

Here's what I'm getting vs what I expect to get:
PRNs, 20, 8/2012, Michael Anderson, 20 (should be nothing)
PRNs, 16, 9/2012, Michael Anderson, 18(should be nothing)
PRNs, 11, 10/2012, Michael Anderson, 15.67 (correct!!!)
PRNs, 0, 11/2012, Michael Anderson, 11.75 (should be 9)
PRNs, 0, 12/2012, Michael Anderson, 6.75 (should be 3.67)
PRNs, 1, 1/2013, Michael Anderson, 3 (should be 0.33)

 

[plog]

I gave you the wrong code for the main query last time. Change this part of the code:

(CDate('" & [MonthBegin] & "') - [MonthBegin]) <95

to this:

(CDate('" & [MonthBegin] & "') - [MonthBegin]) <90


To get the first 2 records not to show data, you would have to add a test on there to see if they were the first 2 records. Maybe by creating a query to determine those 2.

 

[haydenbottoms]

That works perfectly! I'm so excited.


If you're ever passing through Iowa, I owe you a cupcake.

 

[plog]

Kansas City here. Just went through Des Moines and Quad Cities in July and get to Council Bluffs twice a year.