Query Lookup help

[Tark221]

Hi Everyone,

I have one table with raw data in with various columns one which is CustomerName.

I have a table with 2 columns "CorrectCustomerName" and "VariationCustomerName". This contains all the correct Customer Names and how they should appear with the variation of the name next to it. The idea behind this is the raw data we receive is garbage. So I want to be able to run a query which pulls all the raw data and replaces poor CustomerName with the correct name.

I'm currently building a test query which has 3 cols "Dates" from the raw data "CustomerName" from the raw and then the 3rd col I wanted to build an expression which replaces the incorrect name with the correct name.

Any help would be much appreciated

Thanks

 

[plog]

You didn't ask anything specific, nor did you post table names so I'm guessing here. You want a query to combing table 2 with the raw data?

SELECT RawData.*, Table2.CorrectCustomerName
FROM RawData
LEFT JOIN Table2 ON RawData.CustomerName = Table2.VariationCustomerName

If that doesn't help, post your table names and some sample data showing the results you want based on that sample data.

One note--Table2 needs to have unique VariationCustomerName values, otherwise joining it to another table will cause duplicates to appear. Run this query to verify they are unique:

SELECT COUNT([VariationCustomerName]) AS NameCount, VariationCustomerName
FROM Table2
GROUP BY VariationCustomerName
ORDER BY COUNT([VariationCustomerName]) DESC

If the first value of that query is more than 1, then you have duplicates and need to fix them.

 

[Tark221]

Hi, Thanks for the reply, I will try to explain the schema a little better.

I have two tables:
RAW
RawID
Product
CustomerName

VariantCustomerName
VariantID
VariantName
ActualName

I want a query which will take all the details from the raw data but where CustomerName is in the raw I want to show the actual name. The reason for this is that the CustomerName ofter has variances from poor input control. So the query needs to take the CustomerName and check it against the variance list if it finds a hit then it brings back the real name.

Apologies for the poor explanation the first time, appreciate the help.

Thanks

 

[plog]

Sounds like my guess was correct.