Most recent Cost (2003 All Updates)

Topic

New Reply

I have a table that has records of a series of stock purchases with date and cost of each stock item. As each bill from a supplier is recorded separately in the table, obviously there could be more than one purchase of an item over a period of time eg 2 in January for $10 each and 3 in June for $15 each. Is there a way to have a query show the average cost per item of only the most recent purchase for each item – ie the June one in this example
TIA
Steve

Reply | Quote

Replies

Does the purchase date field record only the month pf purchase, or does it record the exact date (so that you want to group by date)?

Reply | Quote

Records the full date

Reply | Quote

1) Start by creating a query based on your table.
Add the * and a calculated column

M: [PurchaseDate]-Day([PurchaseDate])+1

Replace PurchaseDate with the actual name of the field in your table. This column will calculate the month of purchase (in fact the first day in the month).
Save this query as (for example) qryPurchasesByMonth.

2) Create a new query based on qryPurchasesByMonth.
Add only the item field and M to the query grid.
Select View | Totals or click the Totals button on the toolbar.
Set the Total option for M to Max.
This query will return the month of the most recent purchase for each item.
Save this query as (for example) qryMaxMonth.

3) Create a new query based on qryPurchasesByMonth and qryMaxMonth.
Join them on the item field and on M vs MaxOfMonth.
Add the item field and the cost field from qryPurchasesByMonth to the query grid.
Select View | Totals or click the Totals button on the toolbar.
Set the Total option for the cost field to Avg.
This query will return the average cost per item for the most recent month of purchase.

Reply | Quote

Have a look at the attachment. I have imported MYOB_Purchases and MYOB_ItemPurchaseLines from Clearwater.

Then I use three queries.

Query1 joins the two tables and adds a calculated that drops all dates back to the first of their month.
Query2 find the max of these collapsed dates for each item.
Query3 joins query1 back to query2 and finds the average price for each item.

Reply | Quote

Thanks again both of you for your help and interest- I’ll give it a shot

Steve
PS believe it or not John, its not MYOB for a change but ODBC from Quick Books

Reply | Quote

I imagine that Quickbooks will have a similar structure.

Reply | Quote

Hans/John
Thanks again. I assumed MAX would come into it somewhere but wasnt sure how- Much appreciated

John, I am sure I can get this to work with Quick Books but surprisingly the structure is very different – not quite flat but very non-normalised. However still workable
Steve

Reply | Quote