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
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