I have two tables in Access XP an Inventory table that has the itemnumber, quantityonhand, a purchaseorder table that has itemnumber, qunatityordered, pricepaid.
What I need and believe it can be done is a SQL query that will pull the last purchase orders and give me a real price on the items. This can be tricky because if I have 10 items in inventory and 3 purchase orders of different prices and different quantities for the item. I need to only grab the last purchase orders that equate to the amount of quantityonhand in inventory then average the cost for a single item as my real cost.
So if I had 3 purchase orders of the same item using the data below the 1st order would not even be pulled in the query, and the second order would be evaluated to be reduced
1 on 05/01/09 for a quantity of 2 at a price of $3.50
2 on 05/12/09 for a quantity of 4 at a price of $1.25
3 on 05/18/09 for a quantity of 7 at a price of $2.75
the query would need to give me the real cost of those 10 items left in inventory which would look something like (7*2.75+3*1.25)/10 =realcost of $2.30 per item