Query performance issues I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the most recent and Date1 must exist:

For each distinct ID in InputTable (= rst(0) ) do:

INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1) 
SELECT InputTable.ID, 
       InputTable.FID, 
       InputTable.KeyValue1, 
       InputTable.KeyValue2, 
       InputTable.Date1
FROM
  ( SELECT TOP 1 * 
    FROM
      ( SELECT * 
        FROM InputTable 
        WHERE ID='" &amp; rst(0) &amp; "' And Date1&lt;&gt;''
      )
    WHERE Date2 = 
          ( SELECT Max(Date2) 
            FROM InputTable 
            WHERE ID = '" &amp; rst(0) &amp; "' and Date1 &lt;&gt; ''
          )
  );


The InputTable has about 400.000 records and ~200.000 distinct IDs and only contains the 5 fields that will be in the Final Table, all of which are TEXT. 

InputTable is an importet .txt file and i am working with ACCESS outside of any real database. The Result will be again written into a .txt file.

It takes about 1 second for each ID, which is too long... Is it possible to improve the performance significantly?

I really appreciate any help.
Performing those subqueries repeatedly for each distinct ID value is what's killing you. I ran a test of your method on a sample table of 10,000 rows and it took ~2.5 minutes to execute on my notebook. By comparison, the following code consistently returned the same results in less than 0.5 seconds:

Option Compare Database
Option Explicit

Sub UsingAggregateQuery()
    Dim cdb As DAO.Database, t0 As Single
    Set cdb = CurrentDb
    cdb.Execute "DELETE FROM FinalTable", dbFailOnError
    t0 = Timer
    cdb.Execute _
            "INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1) " &amp; _
            "SELECT InputTable.ID, " &amp; _
            "       Last(InputTable.FID), " &amp; _
            "       Last(InputTable.KeyValue1), " &amp; _
            "       Last(InputTable.KeyValue2), " &amp; _
            "       Last(InputTable.Date1) " &amp; _
            "FROM " &amp; _
            "    (" &amp; _
            "        SELECT ID, FID, KeyValue1, KeyValue2, Date1 " &amp; _
            "        FROM InputTable " &amp; _
            "        WHERE Date1 &lt;&gt; '' " &amp; _
            "        ORDER BY ID, Date2 " &amp; _
            "    ) " &amp; _
            "GROUP BY ID;", _
            dbFailOnError
    Debug.Print Format(Timer - t0, "0.0") &amp; " seconds"
    Set cdb = Nothing
End Sub


Edit

I just ran another test on a sample table of 400,000 rows and the code above completed in under 7 seconds