Identify max date for a sell price

[Guest]

06:30I have a table with the following format (date and time in one field and
price in a second field).

3/19/2000 0:00 1.47
4/23/2000 15:30 1.485
5/02/2000 06:30 1.32

etc., etc.

I have a second table with dates and times in one field.

3/19/2000 15:15
4/23/2000 15:45
6/15/2000 0:00

When I run the first table against the second table:

3/19/2000 15:15 should return 1.485
4/23/2000 15:45 should return 1.485
6/15/2000 0:00 should return 1.32

In essence I have got to compare the date/time in the second table to all
dates/times in the first file and then find the price appropriate for when
the item was sold. Any help would be appreciated.

 

[Duane Hookom]

Shouldn't 3/19/2000 15:15 return 1.47 since the price doesn't change until
4/23/2000?
Consider this sql (my table and field names since you didn't supply them):

SELECT tblDateTimes.DateTimeField,
(SELECT TOP 1 Price
FROM tblPrices
WHERE DateTimeField>=PriceDate
ORDER BY PriceDate DESC) AS Price
FROM tblDateTimes;