Compare two rows, and keep 1

[It_thug]

Okay, this is a long shot, but y'all have helped me out a lot in the past, so I figured I'd see if anyone had any ideas.


Let's say I have a table with the following information.

ID ORDER_TYPE Payment
245 Sub_Basic_Free 0.00
245 Sub_Renewal 125.00
300 Sub_Basic 0.00
400 Sub_Basic 0.00
400 Sub_Renewal 125.00
525 Sub_Full 200.00

Okay, to explain.... there are three types of subscriptions, Basic(which every member gets), Renewal (if they purchased last year they get a discount), and Full (Brand new, never purchased before - so does not get Basic)

What I need to do, is filter out those ID's that are Basic when a Renewal has been purchased. (Because the data for the Basic Stays in the DB. This is not my rule, or I'd change it)

So, from the above list, I need to have the result of the query look like this:

ID ORDER_TYPE Payment

245 Sub_Renewal 125.00
300 Sub_Basic 0.00
400 Sub_Renewal 125.00
525 Sub_Full 200.00

So the Sub_Basic for 245 and 400 are filtered.

I'm stumped on this. If I have to do it in VBA, I will, but even then, I'm not sure where to start.

Any ideas of where to go next? (As always, am not asking for a solution.. just a push in the right direction).

TIA

 

[It_thug]

I just wanted to post that I was able to figure something out about this. I'm working on the ramifications.

But what I did was created a query that would filter for MAX amount for the Paid Amount.

And then used that to join to the other query on ID and Payment. Giving me only those that matched.

It will work, it's a bit messy I think. But for now that's the solution.