Try this:
Code:
SELECT SurveyData.SiteID, Min(SurveyData.SurveyDate) AS SurveyDate, First(SurveyData.EstimatedArea) AS Area
FROM SurveyData
WHERE (((SurveyData.SiteID) In
(SELECT TOP 1 SiteID
FROM SurveyData AS S
WHERE S.SiteID = SurveyData.SiteID)))
GROUP BY SurveyData.SiteID
UNION
SELECT SurveyData.SiteID, Max(SurveyData.SurveyDate) AS SurveyDate, Last(SurveyData.EstimatedArea) AS Area
FROM SurveyData
WHERE (((SurveyData.SiteID) In
(SELECT TOP 1 SiteID
FROM SurveyData AS S
WHERE S.SiteID = SurveyData.SiteID)))
GROUP BY SurveyData.SiteID
Order By SiteID, SurveyDate;;
NOTE: I named the table "SurveyData" and I removed spaces in the field names.
Change the table name to your table names and the field names to your field names (no spaces ).
Based on the data in the Excel file you posted, these are the results:
SiteID |
SurveyDate |
Area |
18279 |
5/30/2002 |
0.0013 |
18279 |
5/28/2016 |
0.03 |
18387 |
6/3/2002 |
0.0013 |
18387 |
5/26/2017 |
0 |
18395 |
6/6/2002 |
1 |
18395 |
7/29/2017 |
0.01 |
18398 |
6/7/2002 |
0.001 |
18398 |
7/6/2018 |
0 |
18404 |
6/18/2002 |
0.0013 |
18404 |
5/31/2017 |
0 |
18543 |
6/5/2002 |
0.0013 |
18543 |
5/27/2017 |
0 |
Close????