Removing duplicates from a query with conditions in Access 2007

Question

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.

Answer 1

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