How to return preset values based on query result?

[N L]

Greetings,

I have a database table that has a row called "Company", which may
return different variations on a company name, depending on how it was
entered. For example, it may return one of the following:
WidgetWerks, Inc.
WidgetWerks, Inc.
Widget Holdings Corporation
WidgetWerks Co
WidgetWerks Ltd.
WidgetWerks Canada
WidgetWerks

For grouping and simplification, I would like to add another row to my
query results called "Simplified Company", where it would just return
"WidgetWerks" if "Company" is any of those above values.

In other databases, I might use CASE for this. Any tips on how I could
do it in Access?

Thanks,
N. Lee

 

[Michel Walsh]

You can use a translation table.


Aliases ' table name
CityName AliasOf ' field names
-------------- ------------
Constantinople Istanbul
Byzance Istanbul
Bytown Ottawa
New-York New York
... data sample




----------------------------------
SELECT a.cityName, NZ(b.aliasOf, a.cityName) AS translated
FROM myTable AS a LEFT JOIN aliases AS b
ON a.cityName = b.cityName
----------------------------------



will display the 'translation', if one is known, in the second column. Sure,
the table Aliases has to be filled with all the known alias you will be
using.






Hoping it may help,
Vanderghast, Access MVP

 

[N L]

You can use a translation table.

Aliases  ' table name
CityName    AliasOf    ' field names
--------------    ------------
Constantinople    Istanbul
Byzance              Istanbul
Bytown                Ottawa
New-York            New York
                                ... data sample

----------------------------------
SELECT a.cityName, NZ(b.aliasOf, a.cityName) AS translated
FROM myTable AS a LEFT JOIN aliases AS b
        ON a.cityName = b.cityName
----------------------------------

will display the 'translation', if one is known, in the second column. Sure,
the table Aliases has to be filled with all the known alias you will be
using.

Hoping it may help,
Vanderghast, Access MVP










- Show quoted text -

That's terrific. Why didn't I think of that?

Thanks.