Using MS Access/Append query data


I have a data table with 4 columns (Name, Moodys, SP, Fitch). Each "Name" should have a unique code from each of the other 3 (Moodys, SP, Fitch). However in the table (30,000 records) some names are repeated because the same name may have ex. "BBB" from SP and in another row "AAA" from SP also. How can I automatically find the duplicate entries (could be 2 or more), delete the excess rows (leave only 1 entry) which will have only the most conservative rating (for my example. Name "NICK" exists in rows 1 and 2 and under column "SP" row 1 has "AAA" and row 2 has "BBB") the final entry must show the "BBB" which is more conservative). Any help? (I have a separate table with the classification order for the ratings (ranging from AAA to CCC) can I use that to find the most conservative?)
Thank you in advance.

Hi Sortis,

The way to do is:

Add a number rank to the AAA, BBB, .... in the table with the classification. That should be easy. You just add a new field and the numbers. This is done one time. You can add an "Autonumber" field in place of a manual number field. I prefer the manual number field - that way you can revise the rank.

Then, do a Group By query by joining the two tables on "Classification". In the query, for column Classification you need to use the "Max" function.

Now for each name the query will pull out only the classification with maximum rank number. And you can use this information to update the table. Deleting the record having AAA and add the record from query that hass BBB will update your table.

In order to find duplicate records, you can just use a group by query with a conditional clause "Where count(classification) > 1".

I hope this helps.

Please check online solutions for Group By queries.


Using MS Access

All Answers

Answers by Expert:

Ask Experts


Mark Kaphle


I can answer most questions on database design using Access. I can help with VBA too. I can integrate engineering and business needs in the database design.


I have more than 15 years of MS Access database design experience.

College degree in engineering. Graduate degrees in engineering and management. Current PHD student - concentration Industrial Engineering.

©2016 All rights reserved.