Qry Running Average

[Carrie_Loos]

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

 

[Michael Gramelspacher]

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];

 

[Carrie_Loos via AccessMonster.com]

I can't find these queries in the Northwind Database, is there a newer
version I need to download?

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

 

[Carrie_Loos via AccessMonster.com]

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");

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];

 

[John Spencer]

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
..

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");

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];

 

[Carrie_Loos via AccessMonster.com]

Thank you so much, it works very well. This execise has helped me
tremendously with Sequel syntax, still have a ways to go though.

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];