[turnbuk]

I have a database that includes repeat names but with different dollar value's attached to each name. For example:

| Joe Smith | $10,925 |

| Joe Smith | $10,327 |

| Joe Smith | $10,725 |

I want to create a query that will find a repeat name, identify the record with the highest $ and then delete the rest. Allowing me to have a quesry that shows no repeat names but keeps the record with the highest $.

Any suggestions?

[OBP]

Take a copy of the Table using the "Structure Only".
Make a Query of your original Table using Totals and group by the Name and set the Dollar value to Max.
Run the Query and it should give you the record you need.
Change the Query type to an Append Query and append the data to the new Table, save and run it.
If it allows you to run it, it will do what want.

[turnbuk]

i'm going to add a couple more fields.

____Facility______Name_____SSN______Amount_
| 3722100100 | Joe Smith | xxxxxxxxx | 25,239.97 |
| 3720100100 | Joe Smith | xxxxxxxxx | 25,239.97 |
| 3720100101 | Joe Smith | xxxxxxxxx | 25,239.97 |
| 3722140900 | Joe Smith | xxxxxxxxx | 25,239.97 |
| 3720100100 | Joe Smith | xxxxxxxxx | 23,863.34 |
| 3722100100 | Joe Smith | xxxxxxxxx | 23,863.34 |

How can i create a query to so that the results is:

____Facility______Name_____SSN______Amount_
| 3722100100 | Joe Smith | xxxxxxxxx | 25,239.97 |

Its very important that i keep all the fields. i've got a database with over 7000 records and this is a frequent occurence. I try to delete repeat occurences but because the facility numbers are different it doesn't consider them repeats. Again, i need the highest $ and facility number that goes with that record plus name and SSN. Delete the rest. Any suggestions?

[OBP]

It is still the same advice as in post #2, except have the facility Number set to max as well.
Once you have created the new table you should arrange to only "Update the data" rather than appending new data to the table.

Can you post a copy of the database with just a few dummy records in it so that I can work on it and demostrate the principle?