I have a select query that has in it a date field: qry_record.date
the dates are listed in ascending order, latest at the bottom.
I also have a table, that has in it a date field: tbl_race.date
I want to put the two together in a further query, so that, for each record in tbl_race, I have the latest date from qry_record that is not greater than (i.e later than) tbl_race.date
so, if the dates in qry_record look like this:
12-Feb-1956
14-Jun-1964
17-Sep-1965
then the output of my qry should look something like this:
race.date record.date
11-Jun-1964 12-Feb-1956
13-Jun-1964 12-Feb-1956
14-Jun-1964 14-Jun-1964
17-Jun-1964 14-Jun-1964
what I've tried:
I ran this query:
Code:
SELECT tbl_race.date, Max(qry_record.date) AS MaxOfdate
FROM tbl_race LEFT JOIN qry_record ON tbl_race.date = qry_record.date
GROUP BY tbl_race.date
ORDER BY tbl_race.date;
and that gave me the following:
race.date record.date
29-Nov-1962
25-May-1963 25-May-1963
11-Jun-1963
12-Sep-1964 12-Sep-1964
which feels like progress because where the record changes is marked, but the intermediate dates are left blank and I would need some way of filling in the gaps.
gratefull for any help, thank you.