Combine name changes in query

**Jim Doherty** · May 24 '10, 04:38 PM

Originally posted by lcmts

I have a customer database in which a number of companies have modified their company names. When I look at my sales performance query I have to manually total the numbers for these different "name changes" for comparison purposes.

Is it possible to make a query in which I can total the "name changes" as one company.

eg. Joe Bloggs, Joe Bloggs & Co, Joe Bloggs & Co Ltd, J Bloggs Ltd. I would like to total all under name Joe Bloggs.

Yes you can.... but the extent of your client database and the number of potential quirks would dictate the method by which you might wish to approach this. Just going by your simple example is easy enough.

A simple search through the Company name string for the rather unique sequence of characters ie 'Bloggs' which if existing would trigger logic where you could replace by way of calculation the name of the company with something of your choosing. Your table data remains the same, it is not affected by this, only the company name is calculated to appear differently.

This would then serve to do as you ask specifically. An example of this:- imagine a table called tblSales where grouping by the Company name expressed as a calculation and summing the Amount field

Code:

SELECT IIf(InStr(1,[CompanyName],"Bloggs")>0,"Joe Bloggs Ltd",[CompanyName]) AS Company, Sum(tblSales.Amount) AS Total
FROM tblSales
GROUP BY IIf(InStr(1,[CompanyName],"Bloggs")>0,"Joe Bloggs Ltd",[CompanyName]);

The criticism I have of this is that it only applies where you know the safety and uniqueness of the sequence you intend to use in the calculated replacement and is too specific to an individual case of 'Bloggs'. What if there are more than one set of 'Bloggs' or same name companies with different purpose and so on.......not very practical or safe as I am sure you can see.

My advice would be this (if you are NOT desirous of standardising your data in the table for whatever reason and there can be quite legitimate reasons for this) then to create another table of the proper company names only maybe call the field 'AliasCompany' and then create an extra name in your main table and populate it with the actual proper name of the company from this alias table. This way you keep a controlled handle on any query outcome. You would group by on the alias name instead of the disparate names that would appear in the usual company name field.

Obviously this 'new' table would be linked to your client table as a 'one to many' potential relationship

The fundamental design however of your system should take into account practical issues such as company name changes. It 'does' happen we know that and my latter point using the 'alias' concept when used properly will track and account for that

I hope this helps you a little

Combine name changes in query

Combine name changes in query

Comment