ELIMINATE RECORDS WITH ONLY ONE FIELD DUPLICATED?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Back
Top