find corect price

S

serban

Hi

I have a table with same records:

Products DATE Price($)
A1 01.09.2007 0.258
A2 05.09.2007 0.125
A1 15.09.2007 0.652
A1 25.09.2007 3.258
A1 17.09.2007 1.258
A1 24.09.2007 2.258


How can I find the price available in 20.09.2007 ( =price in 17.09.2007 =
1.258$)
Thanks
 
K

KARL DEWEY

Try these two queries. If you know subqueries then you can do it in one.
serban_1 ----
SELECT serban.Products, Max(serban.PriceDate) AS MaxOfPriceDate
FROM serban
WHERE (((serban.PriceDate)<=[Enter date for price]))
GROUP BY serban.Products;

SELECT serban.Products, serban.PriceDate, serban.[Price($)]
FROM serban INNER JOIN serban_1 ON (serban.PriceDate =
serban_1.MaxOfPriceDate) AND (serban.Products = serban_1.Products)
ORDER BY serban.Products;
 
B

B. Edwards

SELECT TOP 1 tblProduct.Products, tblProduct.DateField, tblProduct.Price
FROM tblProduct
WHERE (((tblProduct.Products)="A1") AND
((tblProduct.DateField)<=#9/20/2007#))
ORDER BY tblProduct.DateField DESC;
 
Top