5

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.

  • 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
3

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:

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

FROM
    tableX AS t
GROUP BY
    TransactionID,
    col1, col2, ..., colN ;             -- all the other columns

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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