I have:
tblSalesHeaders joined with tblSalesItems
I also have
tblCosting
Obviously sales get entered into sales and all COGS get entered into tblCosting
We sell commodity items so the prices are constantly changing.
I want to create a report that shows the customer and what the customer purchased. I want to show the price the customer was charged next to the cost we paid for the item. I can't use the most recent cost because it would not be the same cost as when the item was sold say a month ago.
I need a way to say, if in the costing table the purchase date is <= the sell date then grab the cost from the costing table and include only that cost in the query results.
Any idea how I would do this? Keep in mind I'm far from an expert developer.
Many many thx in advance!!!
Jason