Each Item's Most Recent/Month/Half Year Average Price

[jo3c]

I have a Table like this

ITEM	         DATE	      Q	        Price Dollar
668CY9YX56Y	1/3/2011	300000	0.0137	 4,110.00

The same ITEM may have multiple different price at different time
For example

ITEM
668CY9YX56Y
has different price shown as below

ITEM   	DATE	Q	Price	Dollar
668CY9YX56Y	1/11/2011	6300	0.325	 2,047.50 
668CY9YX56Y	1/11/2011	5200	0.325	 1,690.00 
668CY9YX56Y	2/14/2011	5660	0.325	 1,839.50 
668CY9YX56Y	2/17/2011	3360	0.325	 1,092.00 
668CY9YX56Y	3/15/2011	140	0.325	 45.50 
668CY9YX56Y	3/15/2011	17140	0.325	 5,570.50 
668CY9YX56Y	4/19/2011	9900	0.325	 3,217.50 
668CY9YX56Y	5/4/2011	2140	0.325	 695.50 
668CY9YX56Y	5/4/2011	3280	0.325	 1,066.00 
668CY9YX56Y	5/4/2011	5640	0.325	 1,833.00 
6689Y9YX97G	6/2/2011	1000	0.4	 400.00 
668CY9YX56Y	6/14/2011	40	0.325	 13.00 
668CY9YX56Y	6/14/2011	80	0.325	 26.00 
668CY9YX56Y	6/14/2011	40	0.325	 13.00 
668CY9YX56Y	6/14/2011	5920	0.325	 1,924.00

How do I calculate below: ??

Each Item's Most Recent Price Average
Each Item's Past one Month Price Average
Each Item's Past 6 Months Price Average

Thank you so much

Example File is here
http://dl.dropbox.com/u/23447837/ExcelForumA.xls

[jzwp11]

You will need some queries to determine each value by item #. You can then bring all of the values together using a final query.

We'll need some more clarification as to what "Each Item's Most Recent Price Average" means. What time period is included in "Most Recent"?

Similarly, when you say "Each Item's Past one Month Price Average" is it for the 30 day period relative today (6/28/11 to 7/28/11) or just the average price in the previous month (6/1/11-6/30/11)?

Like wise, does the 6 month period end with today's date of the last day of the previous month?

[TheShabz]

Just to add on, you should consider changing the name of your Date field to something other than Date. Date is a reserved word in Access and it will cause you issues querying for it down the line.

[rpeare]

Each Item's Most Recent Price Average

What does this mean, the most recent price in your recordset? For instance in the example data you would average the most recent day's data 6/14/2011? (average price, average Q and average dollars?)

Also, are you weighting your cost per item? For instance you have 15 items, if you were to average the price you would get something close to .33. However that's not really accurate if you had a huge volume of items at the .325 (which you do) price and a very small quantity at the .4 price (which you do) it should be weighted by the volume of items related to each price. in this case you'd have the sum of the price divided by the sum of the q to get the average price.

You also do not need to store the dollar column in your table, it can be calculated and is redundant.

so let's say your table containing this data is called Tbl_Test (and your DATE field is actually called ITEMDATE because date is a reserved word)

Create this query

SELECT Tbl_Test.Item, Max(Tbl_Test.ItemDate) AS MaxDate
FROM Tbl_Test
GROUP BY Tbl_Test.Item;

Call it Qry_Prelim

Create this query

SELECT Qry_Prelim.Item, Sum(IIf([itemdate]=[maxdate],[q],0)) AS MRDQuantity, Sum(IIf([itemdate]=[maxdate],[q]*[price],0)) AS MRDDollars, Sum(IIf([itemdate]=[maxdate],[q]*[price],0))/Sum(IIf([itemdate]=[maxdate],[q],0)) AS MRDAvgCost, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q],0)) AS MRWQuantity, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q]*[price],0)) AS MRWDollars, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q]*[price],0))/Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q],0)) AS MRWAvgCost, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q],0)) AS MRMQuantity, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q]*[price],0)) AS MRMDollars, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q]*[price],0))/Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q],0)) AS MRMAvgCost
FROM Qry_Prelim LEFT JOIN Tbl_Test ON Qry_Prelim.Item = Tbl_Test.Item
GROUP BY Qry_Prelim.Item;

My code assumes you want to figure the average most recent price based on the most recent date AND that the most recent cost is the total cost divided by the total units.

EDIT: my query is the most recent day, the most recent 7 days from the most recent date (going backwards, and the most recent 30 days from the most recent date (going backwards) you just have to change the [maxdate] - 30 to [maxdate] - <x> where x would be the number of days you want to go backward.

[jo3c]

We'll need some more clarification as to what "Each Item's Most Recent Price Average" means. What time period is included in "Most Recent"?

To define most recent which is the Date that's closest to that's most recent to today
in the doc should be June 30th would be consider the most recent

by using June 30th as the most recent date counting back by 1 month and 6 month

Just to add on, you should consider changing the name of your Date field to something other than Date. Date is a reserved word in Access and it will cause you issues querying for it down the line.

Noted with thanks

[jo3c]

What does this mean, the most recent price in your recordset? For instance in the example data you would average the most recent day's data 6/14/2011? (average price, average Q and average dollars?)

Also, are you weighting your cost per item? For instance you have 15 items, if you were to average the price you would get something close to .33. However that's not really accurate if you had a huge volume of items at the .325 (which you do) price and a very small quantity at the .4 price (which you do) it should be weighted by the volume of items related to each price. in this case you'd have the sum of the price divided by the sum of the q to get the average price.

You also do not need to store the dollar column in your table, it can be calculated and is redundant.

so let's say your table containing this data is called Tbl_Test (and your DATE field is actually called ITEMDATE because date is a reserved word)

Create this query

SELECT Tbl_Test.Item, Max(Tbl_Test.ItemDate) AS MaxDate
FROM Tbl_Test
GROUP BY Tbl_Test.Item;

Call it Qry_Prelim

Create this query

SELECT Qry_Prelim.Item, Sum(IIf([itemdate]=[maxdate],[q],0)) AS MRDQuantity, Sum(IIf([itemdate]=[maxdate],[q]*[price],0)) AS MRDDollars, Sum(IIf([itemdate]=[maxdate],[q]*[price],0))/Sum(IIf([itemdate]=[maxdate],[q],0)) AS MRDAvgCost, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q],0)) AS MRWQuantity, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q]*[price],0)) AS MRWDollars, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q]*[price],0))/Sum(IIf([itemdate] Between [maxdate] And [maxdate]-7,[q],0)) AS MRWAvgCost, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q],0)) AS MRMQuantity, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q]*[price],0)) AS MRMDollars, Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q]*[price],0))/Sum(IIf([itemdate] Between [maxdate] And [maxdate]-30,[q],0)) AS MRMAvgCost
FROM Qry_Prelim LEFT JOIN Tbl_Test ON Qry_Prelim.Item = Tbl_Test.Item
GROUP BY Qry_Prelim.Item;

My code assumes you want to figure the average most recent price based on the most recent date AND that the most recent cost is the total cost divided by the total units.

EDIT: my query is the most recent day, the most recent 7 days from the most recent date (going backwards, and the most recent 30 days from the most recent date (going backwards) you just have to change the [maxdate] - 30 to [maxdate] - <x> where x would be the number of days you want to go backward.

Thank you rpeare, ill try your query when i get home.
and I will keep you posted

Thanks