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