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='" & rst(0) & "' And Date1<>'' ) WHERE Date2 = ( SELECT Max(Date2) FROM InputTable WHERE ID = '" & rst(0) & "' and Date1 <> '' ) );
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.