Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Match value with certain date or later

Status
Not open for further replies.

JITA

MIS
Sep 6, 2009
28
BE
Hi, I am trying to figure out how to match a sale date to a price date.
I have three tables:
tblProd
ID
Desc

tblPrices
PriceDate
Price
ProdID

tblSales
SaleDate
SaleQ
ProdID

Prices do not change frequently, so the tblPrice is not updated regularly.
What I would like to achieve is to find a match between a sale at a certain date and the valid price at the time of the sale. Currently I am working with only one product, but even then I am not able to find the solution.
This is the query I have so far

SELECT
tblSale.SaleQ,
tblPrices.Price,
Max(tblPrices.PriceDate),
tblSales.SaleDate
FROM
tblSales
LEFT JOIN tblPrices
ON tblSales.ProdID = tblPrices.ProdID
WHERE (((tblSales.Date)>=[tblPrices].[Date]))
GROUP BY
tblSales.SaleQ,
tblPrices.Price,
tblSales.Date
ORDER BY
tblSales.Date;

Running the query results in multiple rows with different prices (different staring dates) for a sale at a certain date. I understand it has to do with my statement tblSales.Date)>=[tblPrices].[Date], but I have no clue on how to select just one price.

Example results:
PriceDate Price ProdID
02-01-2015 2.30 1
02-03-2015 2.70 1

shows
SaleQ Price PriceDate SalesDate
5 2.30 02-01-2015 02-01-2015
2 2.30 02-01-2015 02-02-2015
7 2.70 02-03-2015 02-03-2015
7 2.30 02-01-2015 02-03-2015

Thanks for your support
 
Sorry wrong forum
I will post this on an other form
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top