一种方法，在Access SQL中应该可以（尽管您可能需要更改别名）：

  SELECT a。* 
 FROM Table1 a 
 INNER JOIN 
（
 SELECT ID，sdate = min（SplitDate）
 FROM Table1 
 GROUP BY ID 
）b 
 ON a.ID = b.ID 
 AND a.SplitDate = b.sdate 
 

你也可以采用内部查询并在Access中制作自己的qry。

I have data that is in two tables and I have a query combining the data as below. I am trying to eliminate duplicates based on the Id column where I pick the record with the oldest split date. Could anyone kindly assist me with the SQL statement to effect this?

|ID     |SecID  |ReportingDate  |SplitDate  |Adjustor|  
|1465   |2      |31-Dec-09      |01-Nov-10  |0.1     |  
|1465   |2      |31-Dec-09      |27-Dec-12  |0.2     |  
|1466   |2      |31-Dec-10      |27-Dec-12  |0.2     |   
|1468   |2      |31-Dec-11      |27-Dec-12  |0.2     |  
|1469   |2      |31-Dec-08      |01-Nov-10  |0.1     |  
|1469   |2      |31-Dec-08      |27-Dec-12  |0.2     | 

The result should be as below:

|ID     |SecId  |ReportingDate  |Adjustor  |  
|1469   |2      |31-Dec-08      |0.1       |  
|1465   |2      |31-Dec-09      |0.1       |  
|1466   |2      |31-Dec-10      |0.2       |  
|1468   |2      |31-Dec-11      |0.2       |  

More Information:
Let me explain what I am trying to do here.

In the fundamentals table I have a row with a unique Line Id, secId( a product identifier) and a reporting date for this line.
This information needs to be adjusted using information from the splitdetails table that has a date from which it becomes applicable, the secId(product) it affects and the adjustor ratio to be used.

For each line in fundamentals table:
-Where any secId that doesnt have an entry in the splits table, adjustor should be 1.
-If secId is present in the Splits table, the split to be used is the oldest one whose date is older than the fundamentals table reporting date being checked.

I am hoping to get results from the sample above would end up look like this:
| ID |SecId |ReportingDate |Adjustor |
|1469 2 31-Dec-08 0.1
|1465 2 31-Dec-09 0.1
|1466 2 31-Dec-10 0.2
|1468 2 31-Dec-11 0.2
|1467 2 31-Dec-12 1

The query I am using is
SELECT Gotten.ID, Gotten.SecID, Gotten.ReportingDate, Gotten.SplitDate, Adjustor
FROM
(SELECT tblFundamentalsDetails.id, tblFundamentalsDetails.SecId, tblFundamentalsDetails.ReportingDate, tblSplitDetails.SplitDate, tblSplitDetails.Adjustor FROM tblFundamentalsDetails
LEFT JOIN tblSplitDetails
ON (tblFundamentalsDetails.ReportingDate

One approach, that should be ok in Access SQL (though you might need to change the aliases):

SELECT a.*
FROM Table1 a
INNER JOIN
(
SELECT ID, sdate = min(SplitDate)
FROM Table1
GROUP BY ID
) b
ON a.ID = b.ID
AND a.SplitDate = b.sdate

Also you could take the inner query and make its own qry in Access.