Cost of Sales Query by Date

[jason1838]

I am trying to compute the cost of a sale based on the last purchase prior to the sale date. Here are my 2 tables. SJ for Sales Journal, PJ for Purchase Journal.

SJ
item UnitPrice trxDate
A100 $20.00 11/1/2007
A101 $25.00 11/12/2007
A102 $15.00 11/12/2007

PJ
item UnitPrice trxDate
A100 $10.00 10/1/2007
A100 $12.00 9/2/2007
A100 $15.00 10/20/2007
A100 $17.00 11/30/2007
A101 $10.00 10/12/2007

I want to run a query which will return this:

Item Cost CostDate Sale SaleDate
A100 $15.00 10/20/2007 $20.00 11/1/2007
A101 $10.00 10/12/2007 $15.00 11/12/2007
A102 Null Null $15.00 11/12/2007

Any ideas?

Thanks,
-Jason

 

[dhookom]

I think your "return this" is incorrect. A101 never had a sale cost of $15.

Try SQL of:

SELECT SJ.Item, SJ.UnitPrice, SJ.TrxDate, 
(SELECT TOP 1 PJ.UnitPrice FROM PJ WHERE PJ.Item = SJ.Item AND PJ.TrxDate <=SJ.TrxDate ORDER BY PJ.TrxDate DESC) AS Cost, 
(SELECT TOP 1 PJ.TrxDate FROM PJ WHERE PJ.Item = SJ.Item AND PJ.TrxDate <=SJ.TrxDate ORDER BY PJ.TrxDate DESC) AS CostDate
FROM SJ;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jason1838]

Thank you! This seems to give me what i was looking for.