I have a database that calculates fees for the use of certain products. There are fees for each rep's use of each product and also a firm fee, which is a fee that the firm pays if they have one or more reps using a certain product.
Right now I have 2 queries filtering out those firms that have to pay firm fees, lets say Product A has a firm fee of $50 and Product B has a firm fee of $100.
The problem I am having is there are firms that have reps that just use product A and reps that use product B, so they are getting charged $50 and $100. I want to only charge the firm $100 since it includes every product that is cheaper than that. I hope this makes sense. Example:
Firm 1 product A
Firm 2 product A
Firm 3 product B
Firm 4 product A
Firm 4 product B
Is there any way to filter so each firm is listed only once in the first field and is charged with the higher price in the second field?
Sorry, I would attach the database but it has sensitive information and it is a HUGE database. Thank you for the help!