Querying data from two tables based on most recent date

[GabyArco]

Hello!,
I've been trying for a bit to figure this out, please be kind if this seems like a simple question, I am teaching myself access as I go...

So, I have these two tables:
1. "Samples"

ID	Date_Samp	Site	Treatment	Temp_C
1	3/5/2013	SITE1	1B	25
2	3/5/2013	SITE1	2B	47
3	4/5/2013	SITE1	1B	35
4	5/3/2013	SITE1	2B	28
5	3/5/2013	SITE2	1B	23
6	5/3/2013	SITE2	2B	23
7	5/8/2013	SITE2	2B	24

and 2. "Plot_Area"

ID	Date_Measured	Site	Treatment	Plot_area_m2
1	3/3/2013	SITE1	1B	4
2	3/3/2013	SITE1	2B	3
3	5/5/2013	SITE1	1B	8
4	5/3/2013	SITE1	2B	5
5	3/3/2013	SITE2	1B	6
6	5/3/2013	SITE2	2B	4

The problem:
I need to assign a "plot area" value to each of my records in the "Samples" table. The criteria to choose which plot size to assign to each record are:
the site must be the same
the treatment must be the same
the date of the "plot area" must be the closest possible to the sample date, but, only as long as it is an equal or earlier date. For example, if my sample date is June 10 and I have to choose between "plot size" dates March 03 and June 11, I would like to choose March 03.

I have tried different things, for example:

SELECT Samples.ID, Samples.Date_Samp, Samples.Site, Samples.Treatment, Plot_Area.Date_Measured, Plot_Area.Plot_area_m2, Samples.Temp_C
FROM Samples LEFT JOIN Plot_Area ON (Samples.Treatment = Plot_Area.Treatment) AND (Samples.Site = Plot_Area.Site)
WHERE (((Samples.Date_Samp)>=[Date_Measured]))
GROUP BY Samples.ID, Samples.Date_Samp, Samples.Site, Samples.Treatment, Plot_Area.Date_Measured, Plot_Area.Plot_area_m2, Samples.Temp_C;



But I get this result:

ID	Date_Samp	Site	Treatment	Date_Measured	Plot_area_m2	Temp_C
1	3/5/2013	SITE1	1B	3/3/2013	4	25
2	3/5/2013	SITE1	2B	3/3/2013	3	47
3	4/5/2013	SITE1	1B	3/3/2013	4	35
4	5/3/2013	SITE1	2B	3/3/2013	3	28
4	5/3/2013	SITE1	2B	5/3/2013	5	28
5	3/5/2013	SITE2	1B	3/3/2013	6	23
6	5/3/2013	SITE2	2B	5/3/2013	4	23
7	5/8/2013	SITE2	2B	5/3/2013	4	24

where record number 4 is showing both available/possible dates, and I would like to see only the most recent one (as long as it's an equal or earlier date). I've tried using "Max" but I get the same result. Any idea on how to get the results I need?

Any and all help is greatly appreciated!

Thank you!!!

[June7]

Options:

1. Do a query with Plot_Area that returns the Top 1 for each Site/Treatment group then join that query to the Samples table. Review: http://allenbrowne.com/subquery-01.html#TopN

2. DLookup("Plot_area_m2", "Plot_Area", "Site='" & [Site] & "' AND Treatment='" & [Treatment] & "' AND Date_Measured=#" & DMax("Date_Measured", "Plot_Area", "Site='" & [Site] & "' AND Treatment='" & [Treatment] & "' AND Date_Measured<=#" & [Date_Samp] & "#"))