Normalizing Vendor Names

[stephiesunny]

I have two queries:

Query 1: Normalization - which normalizes thousands of vendor records to
make them have one basic format from the Vendor_Names table (which does not
include all vendors, only those needing to be normalized)

SELECT PaymentNet_Transactions.[Trans ID], Vendor_Names.DisplayName,
PaymentNet_Transactions.Merchant
FROM PaymentNet_Transactions, Vendor_Names
WHERE (((PaymentNet_Transactions.Merchant) ALike
('%'+[Vendor_Names].[SearchName]+'%')));

Query 2: PaymentNet_Spend - Provides all vendors with normalized names (and
not normalized) so that I will be able to group together to get total vendor
spending

SELECT PaymentNet_Transactions.[Trans ID], PaymentNet_Transactions.[Trans
Amount], PaymentNet_Transactions.[Post Date], Normalization.MERCHANT,
Normalization.DISPLAYNAME, PaymentNet_Transactions.Merchant
FROM PaymentNet_Transactions LEFT JOIN Normalization ON
PaymentNet_Transactions.[Trans ID] = Normalization.[Trans ID];


I'm trying to get the PaymentNet_Spend query to include all the vendor names
that have been normalized from the Normalization query but to also include
those transactions that have vendors that don't need to be normalized. It
brings back a blank field but I would like it to bring back the display name
to be the same as the merchant name in the PaymentNet_Transactions table. I
don't know how to include this so that the Display name will equal the
merchant name without normalization.

Please let me know if this information is detailed enough, thanks!!

 

[John Spencer (MVP)]

Perhaps what you want is

SELECT PaymentNet_Transactions.[Trans ID]
, PaymentNet_Transactions.[Trans Amount]
, PaymentNet_Transactions.[Post Date]
, Normalization.MERCHANT


, IIF(Normalization.DISPLAYNAME is null,
PaymentNet_Transactions.Merchant,Normalization.DISPLAYNAME is null) as
DisplayName


, PaymentNet_Transactions.Merchant
FROM PaymentNet_Transactions LEFT JOIN Normalization ON
PaymentNet_Transactions.[Trans ID] = Normalization.[Trans ID];


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County