Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table of transactions with 880,095 records that I am querying.

There are duplicate Transaction ID's in the table that I would like hidden/removed when querying the table.

Eg 1.

  • Transaction ID 001 is has two records
  • The data in both rows is identical except for Interest Rate.
  • Row 1 has interest rate of 5%
  • Row 2 has interest rate has no rate and value in the cell is 0

I would like all duplicate Transaction ID's with no interest rate to be removed. This is simple enough, the complication is the following.

Eg 2.

  • Transaction ID 002 has 4 records
  • The data in all 4 rows is identical
  • The interest rate in all rows is 0, ie there is no interest rate.
  • I would like the query to return any 1 of these rows, and remove the other 3.

In summary, I need help designing an MS Access query where duplicate transaction Id's are removed where:

  • Transaction ID has multiple rows, remove the row which has a 0 Interest Rate value
  • Transaction ID has multiple rows, with all Interest Rates values as 0. Remove all duplicates leaving just one row.

Please let me know if more explanation is needed.

share|improve this question
If you don't want to simply select the non-duplicates, but actually remove (DELETE) them, please clarify. – ypercubeᵀᴹ Dec 31 '12 at 18:59
I do have a view of deleting the duplicates in the future. How would I go about doing this? – user16631 Jan 2 '13 at 11:35
up vote 3 down vote accepted

If all the "duplicate" rows differ only at the InterestRate and you never have more than one row with rate over 0, and (one more assumption) there is no negative interest rate, then you can simply:

    TransactionID, MAX(InterestRate) AS InterestRate,
    col1, col2, ..., colN               -- all the other columns

    tableX AS t
    col1, col2, ..., colN ;             -- all the other columns
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.