Query help please

[CaptainBalrog]

Hi there.
I have inherited a crazy old database.
It has a payments table with multiple instances of supplier code and supplier name. I need to build a query that returns a single record for each supplier code and name but in some cases the supplier name is spelled differently so using DISTINCT will still return multiple records for some supplier codes.

I'm not vastly experienced with sql or access so any help on how to do this would be much appreciated.

 

[ByteMyzer]

Short of building artificial intelligence into Microsoft Access in such a way that it is able to GUESS which entries are duplicates, despite bad spelling, your best bet is to simply go through the database and FIX the spelling issues, then run a DISTINCT query.

No SQL can provide reliable results when the data itself is bad, especially when it is inconsistent. Garbage in, garbage out.

 

[pbaldy]

I would group by supplier code and use First, Last, Min or Max on the name. That should get one record per supplier, but presumes you don't really care which version of name it gets.