@lcmts
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
- 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
1
