ELIMINATE RECORDS WITH ONLY ONE FIELD DUPLICATED?

[Guest]

I HAVE A TABLE I HAVE SORTED BY FIELD 6 - CLAIM # (WHICH HAS SOME DUPLICATE
INFO) AND FURTHER SORTED BY FIELD 16 - DATE (OLDEST FIRST). ALL OTHER FIELDS
ARE DIFFERENT FROM ONE ANOTHER. I WANT TO ELIMINATE THOSE RECORDS WHERE FIELD
6 CONTAINS THE SAME NUMBER AS ANOTHER RECORD BUT WHERE THE FIELD 16 DATE IS
EARLIER THAN THE OLDEST DATE. IN DOING THIS, I WANT TO SEE ALL THE DATA IN
THE OTHER FIELDS WHEN I AM THROUGH.

FOR EXAMPLE, THERE ARE 3 RECORDS WITH A FIELD 6 CLAIM # OF 1234. ONE HAS A
DATE OF 12-01-05, ONE HAS A DATE OF 11-01-05, AND THE THIRD HAS A DATE OF
10-01-05. I WANT TO ELIMINATE THE RECORDS WITH THE 10 AND 11 DATES AND KEEP
ONLY THE 12-01-05 RECORD. I ALSO WANT TO SEE THE DATA IN THE OTHER 28 FIELDS
I HAVE WHEN I'M LEFT WITH THE 12-01-05 RECORD.

THE DUPLICATE QUERY SEEMS TO ELIMINATE ONLY RECORDS WHERE ALL 28 FIELDS ARE
THE SAME. THAT WON'T SOLVE MY PROBLEM.

 

[Guest]

You'll need to stack your queries. The first query needs to check for
duplicates based on Claim#, sorted by date DESC, using the MAX value grouped
by Claim #. The second will add the rest of the fields linking on both of the
values (MAX Date and Claim #) returned by the first.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access