Multiple names for the same item

[Cully]

Is it possible to create a table, that will allow for
multiple names for the same item? I am creating
(attempting at least) a database to track materials
related to production of operas. However each opera has
the potential to be called several names, depending on who
is looking up the information. I have assigned each opera
an ID#. Is it possible to tell Access that, for instance,
ID# 108 represents Magic Flute, THE Magic Flute,
Zauberflotte, AND/OR DIE Zauberflotte? So that a query
written for any of these names will return the same
information? Alternatively is there a way to tell Acess to
ignore certain words, such as The, Die, La, Les, etc. etc.

Thanks in advance,
Cully

 

[Adrian Jansen]

The only sure way is to have a table with ALL of the possible
language/spelling variations in it, with the same ID number for the opera.
Unfortunately someone will always use a variation you havent thought of...

You might consider having just some of the most likely keywords, like
'Flute', 'Zauberflotte', 'Magic' and its variations in other languages, then
use queries with the LIKE *xxx* syntax. Where xxx is the string test from
your user entry.

Its a difficult problem.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Tim Ferguson]

However each opera has
the potential to be called several names, depending on who
is looking up the information.

Simple way to do this is to have a separate table of OperaNames linked
many-to-one with the Works table. But -- someone will have to type in all
the variations, either all in one go or when they find a particular one is
missing.

The possibility exists, of course, for having a many-to-many relationship,
so that more than work could be known as "tacky-German-one-that-ends-with-
them-all-dying"....

Alternatively is there a way to tell Acess to
ignore certain words, such as The, Die, La, Les, etc. etc.

Perhaps the most flexible solution is to use the Regular Expression object
in the Scripting Library to look for "(The)? Magic Flute" and so on, with
the use of multiple actual names (see above) and strict user training to
get the spelling write (sic).

Hope that helps


Tim F

 

[John Nurick]

The possibility exists, of course, for having a many-to-many relationship,
so that more than work could be known as "tacky-German-one-that-ends-with-
them-all-dying"....

You definitely need a many-to-many relationship: there are at least two
Barbers of Seville (Paesiello's and Rossini's) and various other
"duplicate" operas.