UNION Query for Alternate Names

[OldBirdman]

Assume 2 tables
tblP {Primary Table}
tblP.Key {AutoNumber and all that}
tblP.Name {Name of EE, Movie, Bird Species, or Whatever}

tblA {Alternate Table}
tblA.Key {AutoNumber and all that}
tblA.PtrToP {Link to tblP.Key so tblA can be joined, or displayed in form}
tblA.AltName {Alternate Name for EE, Movie, Bird Species, or Whatever}

Assume I am working with 2 tables with movie titles. Title for a movie might be "2 Days", or is it "Two Days" I want the query to find this, whether user types "2 Day" or "Two Day" So my primary table, tblP, has a row with key=7 and title = "2 Days" and tblA has a row with PtrToP=7 and AltName="Two Days"

My user enters "Two Days"

SELECT PtrToP FROM tblA WHERE (((AltName) Like "*Two Days*")) UNION SELECT tAAA.Key FROM tblP WHERE (((Name) Like "*Two Days*"))

This query gives me one row, with one field, and that field has the value 7. No problem.

Now I want to display all titles found. I somehow need to do another union, this time for the Movie Name, but will each half of the union have an inner join?

The best example I can come with is to go to "www.Netflix.com" and type "Rogue" into the search box. "Rogue" is an alternate title to a movie called "War", and their search finds both a movie called "Rogue" and a movie called "War" with an alternate title "Rogue". That is what I am trying to do!

OldBirdman

[FishVal]

Hi, OldBirdman.

I think the current table structure is not suited well for it.
The following scheme will be more suitable.

tblIdentities
keyIdentityID Autonumber(Long), PK
lngPrimaryName Number(Long), FK(tblIdentityNames.keyIdentityNameID)

tblIdentityNames
keyIdentityNameID Autonumber(Long), PK
keyIdentityID Number(Long), FK(tblIdentities.keyIdentityID)
txtIdentityName Text

Regards,
Fish

[OldBirdman]

I knew that. As this program is for my personal use, managing a table with the name in it is easier, rather than a table of names. "Good" database design says you are correct.

It seemed so easy to get a list of keys and then query both tables. I was doing this for a learning experience as well as a solution. What started as a simple list to keep track of movies I have rented has become a fairly complex program.

Thank you again for your thoughts and ideas. As I do not know anybody who uses Access, this forum is my only place to exchange ideas.

OldBirdman

[FishVal]

Well.

Seems that you'll need to use your query returning keys to filter both tables (using INNER JOIN) and then union both datasets - the cost of tables' structure current design. ;)
I appreciate "good" database design because it makes things easier.

Regards,
Fish

[OldBirdman]

Thanks again for humoring me. Sometimes "Good" database design conflicts with simple table design. As there is only one user, me (at least for now, but I'm trying to get some local DVD Rental stores to put in a slightly different version as a kiosk for location. Instead of "Seen"/"Not Seen", it would return a classification or location, ie Drama, Comedy, . . .)

I'll probably move all titles to their own table, but I may play awhile longer to see if I can get it to work this way, as a learning exercise.

OldBirdman

[OldBirdman]

After 2 months of meditation and cogitation (and some rumination), I have decided that this question belongs in an AddressBook environment. I have begun rewriting my address book, and I certainly do not want UNION queries.

See also "SQL INNER JOIN with another SELECT Query" also posted to this forum.

When I first wrote my Address Book program, everyone had 1 phone, 1 address, and 1 spouse.

I now look at my primary table (tAAA) and note that every field can have multiple entries, therefore these are related tables, with fields pointing to the Key field in tAAA.

Name = "Foreign Auto Repair" or "Dave"
Address = "123 Main, Miami, FL" or "c/o FedEx, 876 Central, Miami, FL"
Phone = Cell: ; Land: ; VoiceMail: ;
etc

Has anyone designed a system such that the primary/central/main table has only 1 field, the KEY field? That seems to be where I am headed, but I never heard of such a thing.

Please point me in the direction of discussions, white papers, blogs, etc. on this concept.

OldBirdman