I was wondering if someone could assist me in creating a query that will select a row with the highest amount(money) amount duplicate records.
This is how it is structured:
1. I have a BatchNumber, ItemNumber and Amount,
2. An item is identified by a combination of the first two columns above ie BacthNumber and ItemNumber
3. There can be duplicates of an item but they will have different Amounts
I want to create a query that will look for duplicate items, select the one with the highest amount and then add to that amount. I have managed to create a query that will find the duplicate items and arrange them in a way that the item record with the highest amount always appears first for each of the respective duplicate items. I'm lost on how to select that amount and add to it