Qry Running Average

I am really stuck. I have tried this a dozen different ways without results.
I want to take a simple query (Query has date formatted to month and a summed
quantity) and create a running average by 12 month groupings as well as
extend out to 6 months beyond for running average forecast; as shown below:
[The 12 Month Running Avg are my own calculations, this is where I am stuck]

Part Nbr	Month	SumOfOrd Qty	12 Month Running Avg
51009	Mar-06	8	
51009	Apr-06	1	
51008	May-06	1	
51008	Jun-07	0	
51008	Jul-07	0	
51008	Aug-06	3	
51008	Sep-06	0	
51008	Oct-06	12	
51008	Nov-06	2	
51008	Dec-06	7	
51008	Jan-07	10	
51008	Feb-07	7	4
51008	Mar-07	9	4
51008	Apr-07	7	5
51008	May-07	5	5
51008	Jun-07		6
51008	Jul-07		6
51008	Aug-07		7
51008	Sep-07		7
51008	Oct-07		7
51008	Nov-07		8

I just don't know sequel well enough to get what I need. And if there is a
way to get standard deviation on the actuals vs forecast (12 month running
average), I would be thrilled. Currently I have to export to Excel for this
calculation. Can anybody help?

Thanks
Carrie

0
Carrie_Loos
5/12/2007 9:08:18 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
515 Views

Similar Articles

[PageSpeed] 0

In article <72153df2fbe24@uwe>, u34134@uwe says...
> I am really stuck. I have tried this a dozen different ways without results.
> I want to take a simple query (Query has date formatted to month and a summed
> quantity) and create a running average by 12 month groupings as well as
> extend out to 6 months beyond for running average forecast; as shown below:
> [The 12 Month Running Avg are my own calculations, this is where I am stuck]
> 
> Part Nbr	Month	SumOfOrd Qty	12 Month Running Avg
> 51009	Mar-06	8	
> 51009	Apr-06	1	
> 51008	May-06	1	
> 51008	Jun-07	0	
> 51008	Jul-07	0	
> 51008	Aug-06	3	
> 51008	Sep-06	0	
> 51008	Oct-06	12	
> 51008	Nov-06	2	
> 51008	Dec-06	7	
> 51008	Jan-07	10	
> 51008	Feb-07	7	4
> 51008	Mar-07	9	4
> 51008	Apr-07	7	5
> 51008	May-07	5	5
> 51008	Jun-07		6
> 51008	Jul-07		6
> 51008	Aug-07		7
> 51008	Sep-07		7
> 51008	Oct-07		7
> 51008	Nov-07		8
> 
> I just don't know sequel well enough to get what I need. And if there is a
> way to get standard deviation on the actuals vs forecast (12 month running
> average), I would be thrilled. Currently I have to export to Excel for this
> calculation. Can anybody help?
> 
> Thanks
> Carrie
> 
> 
Not that I can say I am entirely clear on what you want, but here are two 
queries for the Northwind sample database which include a running rum.

Northwind Query: Monthly Sales Totals by Product
-------------------------------------
SELECT   products.productname,
         FORMAT(orders.[orderdate],"mmm-yyyy")  AS [month-year],
         DATEADD("m",DATEDIFF("m",1,orders.[orderdate]),1)   AS [month 
ending],
         SUM(CCUR([order details].unitprice * [quantity] * (1 - [discount]) 
/ 100) * 100) 
                                                                                  
AS productsales
FROM     products
         INNER JOIN (orders
                     INNER JOIN [order details]
                       ON orders.orderid = [order details].orderid)
           ON products.productid = [order details].productid
WHERE    (((orders.shippeddate) BETWEEN #1 / 1 / 1997 # 
                                        AND #12 / 31 / 1997 #))
GROUP BY products.productname,
         DATEADD("m",DATEDIFF("m",1,orders.[orderdate]),1),
         FORMAT(orders.[orderdate],"mmm-yyyy");

Northwind Query: Monthly Running Totals by Product
---------------------------------------------------
SELECT   a.productname,
         a.[month-year],
         a.[month ending],
         a.productsales,
         SUM(b.productsales)              AS [running sales],
         COUNT(b.[month ending])       AS months,
         CCUR(SUM(b.productsales) / COUNT(b.[month ending])) 
                                                  AS [monthly average]
FROM     [monthly sales totals by product] AS a
         INNER JOIN [monthly sales totals by product] AS b
           ON (b.productname = a.productname)
              AND (a.[month ending] >= b.[month ending])
GROUP BY a.productname,a.[month ending],a.[month-year],
         a.productsales
ORDER BY a.productname,
         a.[month ending];
0
Michael
5/13/2007 4:22:28 PM
I can't find these queries in the Northwind Database, is there a newer
version I need to download?

Carrie_Loos wrote:
>I am really stuck. I have tried this a dozen different ways without results.
>I want to take a simple query (Query has date formatted to month and a summed
>quantity) and create a running average by 12 month groupings as well as
>extend out to 6 months beyond for running average forecast; as shown below:
>[The 12 Month Running Avg are my own calculations, this is where I am stuck]
>
>Part Nbr	Month	SumOfOrd Qty	12 Month Running Avg
>51009	Mar-06	8	
>51009	Apr-06	1	
>51008	May-06	1	
>51008	Jun-07	0	
>51008	Jul-07	0	
>51008	Aug-06	3	
>51008	Sep-06	0	
>51008	Oct-06	12	
>51008	Nov-06	2	
>51008	Dec-06	7	
>51008	Jan-07	10	
>51008	Feb-07	7	4
>51008	Mar-07	9	4
>51008	Apr-07	7	5
>51008	May-07	5	5
>51008	Jun-07		6
>51008	Jul-07		6
>51008	Aug-07		7
>51008	Sep-07		7
>51008	Oct-07		7
>51008	Nov-07		8
>
>I just don't know sequel well enough to get what I need. And if there is a
>way to get standard deviation on the actuals vs forecast (12 month running
>average), I would be thrilled. Currently I have to export to Excel for this
>calculation. Can anybody help?
>
>Thanks
>Carrie

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200705/1

0
Carrie_Loos
5/14/2007 5:10:13 PM
I keep getting the message of 'invalid bracketing'. I took the Nwind example
and just replaced the correct names, can anyone see why I would get that
error?

SELECT MROs.[Core Part Nbr], MROs.[Date Open], MROs.[Ord Qty]
,FORMAT(MROs.[Date Open],"mmm-yy")  AS [month-year],
        DATEADD("m",DATEDIFF("m",1,MROs.[Date Open]),1)  AS [monthending],
        SUM(MROs.[Ord Qty])

AS RepairQty
FROM MRO's
WHERE    (((MROs.[Date Open]) BETWEEN #5 / 1 / 2005 # 
                                       AND #5 / 31 / 2005 #))
GROUP BY MROs.[Core Part Nbr],
        DATEADD("m",DATEDIFF("m",1,MROs.[Date Open]),1),
        FORMAT(MROs.[Date Open],"mmm-yy");




Michael Gramelspacher wrote:
>> I am really stuck. I have tried this a dozen different ways without results.
>> I want to take a simple query (Query has date formatted to month and a summed
>[quoted text clipped - 32 lines]
>> Thanks
>> Carrie
>
>Not that I can say I am entirely clear on what you want, but here are two 
>queries for the Northwind sample database which include a running rum.
>
>Northwind Query: Monthly Sales Totals by Product
>-------------------------------------
>SELECT   products.productname,
>         FORMAT(orders.[orderdate],"mmm-yyyy")  AS [month-year],
>         DATEADD("m",DATEDIFF("m",1,orders.[orderdate]),1)   AS [month 
>ending],
>         SUM(CCUR([order details].unitprice * [quantity] * (1 - [discount]) 
>/ 100) * 100) 
>                                                                                  
>AS productsales
>FROM     products
>         INNER JOIN (orders
>                     INNER JOIN [order details]
>                       ON orders.orderid = [order details].orderid)
>           ON products.productid = [order details].productid
>WHERE    (((orders.shippeddate) BETWEEN #1 / 1 / 1997 # 
>                                        AND #12 / 31 / 1997 #))
>GROUP BY products.productname,
>         DATEADD("m",DATEDIFF("m",1,orders.[orderdate]),1),
>         FORMAT(orders.[orderdate],"mmm-yyyy");
>
>Northwind Query: Monthly Running Totals by Product
>---------------------------------------------------
>SELECT   a.productname,
>         a.[month-year],
>         a.[month ending],
>         a.productsales,
>         SUM(b.productsales)              AS [running sales],
>         COUNT(b.[month ending])       AS months,
>         CCUR(SUM(b.productsales) / COUNT(b.[month ending])) 
>                                                  AS [monthly average]
>FROM     [monthly sales totals by product] AS a
>         INNER JOIN [monthly sales totals by product] AS b
>           ON (b.productname = a.productname)
>              AND (a.[month ending] >= b.[month ending])
>GROUP BY a.productname,a.[month ending],a.[month-year],
>         a.productsales
>ORDER BY a.productname,
>         a.[month ending];

-- 
Message posted via http://www.accessmonster.com

0
Carrie_Loos
5/14/2007 6:03:26 PM
You can try the following.

Note that you referred to MRO's  in the FROM clause and not MROs (note the 
apostrophe).

You didn't have every field that was in the SELECT clause in either the 
group by clause or using an aggregate function such as SUM.

Also, I was not sure what you were trying to calculate as MonthEnding, so I 
guessed that you wanted the last day of the month.

SELECT MROs.[Core Part Nbr]
 , MROs.[Date Open]
,  MROs.[Ord Qty]
,  FORMAT(MROs.[Date Open],"mmm-yy")  AS [month-year]
,  DateSerial(Year([Date open]),Month([Date Open])+1, 0) as MonthEnding
, SUM(MROs.[Ord Qty]) AS RepairQty

FROM MROs

WHERE    (((MROs.[Date Open]) BETWEEN #5 / 1 / 2005 #
                                       AND #5 / 31 / 2005 #))
GROUP BY MROs.[Core Part Nbr]
 , MROs.[Date Open]
,  MROs.[Ord Qty]
,  FORMAT(MROs.[Date Open],"mmm-yy")
, DateSerial(Year([Date open]),Month([Date Open])+1, 0)

Hope this helps.

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Carrie_Loos via AccessMonster.com" <u34134@uwe> wrote in message 
news:722cc61d0b180@uwe...
>I keep getting the message of 'invalid bracketing'. I took the Nwind 
>example
> and just replaced the correct names, can anyone see why I would get that
> error?
>
> SELECT MROs.[Core Part Nbr], MROs.[Date Open], MROs.[Ord Qty]
> ,FORMAT(MROs.[Date Open],"mmm-yy")  AS [month-year],
>        DATEADD("m",DATEDIFF("m",1,MROs.[Date Open]),1)  AS [monthending],
>        SUM(MROs.[Ord Qty])
>
> AS RepairQty
> FROM MRO's
> WHERE    (((MROs.[Date Open]) BETWEEN #5 / 1 / 2005 #
>                                       AND #5 / 31 / 2005 #))
> GROUP BY MROs.[Core Part Nbr],
>        DATEADD("m",DATEDIFF("m",1,MROs.[Date Open]),1),
>        FORMAT(MROs.[Date Open],"mmm-yy");
>
>
>
>
> Michael Gramelspacher wrote:
>>> I am really stuck. I have tried this a dozen different ways without 
>>> results.
>>> I want to take a simple query (Query has date formatted to month and a 
>>> summed
>>[quoted text clipped - 32 lines]
>>> Thanks
>>> Carrie
>>
>>Not that I can say I am entirely clear on what you want, but here are two
>>queries for the Northwind sample database which include a running rum.
>>
>>Northwind Query: Monthly Sales Totals by Product
>>-------------------------------------
>>SELECT   products.productname,
>>         FORMAT(orders.[orderdate],"mmm-yyyy")  AS [month-year],
>>         DATEADD("m",DATEDIFF("m",1,orders.[orderdate]),1)   AS [month
>>ending],
>>         SUM(CCUR([order details].unitprice * [quantity] * (1 - 
>> [discount])
>>/ 100) * 100)
>>
>>AS productsales
>>FROM     products
>>         INNER JOIN (orders
>>                     INNER JOIN [order details]
>>                       ON orders.orderid = [order details].orderid)
>>           ON products.productid = [order details].productid
>>WHERE    (((orders.shippeddate) BETWEEN #1 / 1 / 1997 #
>>                                        AND #12 / 31 / 1997 #))
>>GROUP BY products.productname,
>>         DATEADD("m",DATEDIFF("m",1,orders.[orderdate]),1),
>>         FORMAT(orders.[orderdate],"mmm-yyyy");
>>
>>Northwind Query: Monthly Running Totals by Product
>>---------------------------------------------------
>>SELECT   a.productname,
>>         a.[month-year],
>>         a.[month ending],
>>         a.productsales,
>>         SUM(b.productsales)              AS [running sales],
>>         COUNT(b.[month ending])       AS months,
>>         CCUR(SUM(b.productsales) / COUNT(b.[month ending]))
>>                                                  AS [monthly average]
>>FROM     [monthly sales totals by product] AS a
>>         INNER JOIN [monthly sales totals by product] AS b
>>           ON (b.productname = a.productname)
>>              AND (a.[month ending] >= b.[month ending])
>>GROUP BY a.productname,a.[month ending],a.[month-year],
>>         a.productsales
>>ORDER BY a.productname,
>>         a.[month ending];
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
John
5/14/2007 7:15:15 PM
Thank you so much, it works very well. This execise has helped me
tremendously with Sequel syntax, still have a ways to go though.

John Spencer wrote:
>You can try the following.
>
>Note that you referred to MRO's  in the FROM clause and not MROs (note the 
>apostrophe).
>
>You didn't have every field that was in the SELECT clause in either the 
>group by clause or using an aggregate function such as SUM.
>
>Also, I was not sure what you were trying to calculate as MonthEnding, so I 
>guessed that you wanted the last day of the month.
>
>SELECT MROs.[Core Part Nbr]
> , MROs.[Date Open]
>,  MROs.[Ord Qty]
>,  FORMAT(MROs.[Date Open],"mmm-yy")  AS [month-year]
>,  DateSerial(Year([Date open]),Month([Date Open])+1, 0) as MonthEnding
>, SUM(MROs.[Ord Qty]) AS RepairQty
>
>FROM MROs
>
>WHERE    (((MROs.[Date Open]) BETWEEN #5 / 1 / 2005 #
>                                       AND #5 / 31 / 2005 #))
>GROUP BY MROs.[Core Part Nbr]
> , MROs.[Date Open]
>,  MROs.[Ord Qty]
>,  FORMAT(MROs.[Date Open],"mmm-yy")
>, DateSerial(Year([Date open]),Month([Date Open])+1, 0)
>
>Hope this helps.
>
>>I keep getting the message of 'invalid bracketing'. I took the Nwind 
>>example
>[quoted text clipped - 65 lines]
>>>ORDER BY a.productname,
>>>         a.[month ending];

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200705/1

0
Carrie_Loos
5/14/2007 8:44:49 PM
Reply:

Similar Artilces: