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.

 

[Dennisk]

I get dbs like this all the time to fix.

what I do is to create a distinct query for the supplier. Turn the query into a make table.

Edit the table to create an Autonumber PK and I add another column called mapping.

the next step is to enter one autonumber in the mapping column for each group of miss-spelled suppliers.

eg
PK Supplier Mapping
1 Bloggs 1
2 Blogs 1
3 Smith 3
4 Smth 3

finally I create a supplier ID in the main table and create an update query to set the supplier code. When that is complete go back into the supplier table and delete all the 'bad' suppliers where the mapping code <> PK.

 

[CaptainBalrog]

Hi there.

Thanks for the response i will give it a try.

Sorry to all about the multi posting. I kept getting a network error so i thought my post had not been submitted.