Join query from the same table

[poporacer]

I have a table that has two fields for similiar information.

tblName
ID
Name
Nickname1
Nickname2
Nickname3
I have a form that I want to search for someone by nickname... on the form I have a text box for the user to input the search criteria. There is a Listbox that I need to populate with a list of the AKA's (there could be some names that have more than one nickname and some that have none... I don't want the list to include Null entries for the nickname). Hopefully this explains what I am trying to accomplish. If I can figure out the query to populate the listbox, I have the rest figured out.

tblName

ID Name Nickname1 Nickname2 Nickname3
1 Paul Smarty Einstien
2 John
3 Sally Silly Smarty BoMally
4 Kim Girl

Form

Listbox (query results)

BoMally Sally
Einstien Paul
Girl Kim
Silly Sally
Smarty Sally
Smarty Paul

 

[fneily]

You might want to read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You table violates the first normal form in all kind of ways. First duplicate column headings. Drop the number and you have Nickname, Nickname, etc. Can't have that in a relational database. This can cause blank values in fields, as your table shows. Can't have blanks throughout a table. This also leads to variable length records. Sally's record is longer then Kim's data wise. All tables must be rectangular.
Table should look like:
tblName
NameID
FirstName
LastName
Nickname

So:
Name1 Paul Smarty
Name2 Paul Einstein
Name3 John
Name4 Sally Silly
etc.

Now someone enters a name, Sally. A query uses this value as a filter and populates the listbox with her nicknames.
If tables are built correctly using normalization, the rest is easy.

 

[PHV]

RowSource for your ListBox:
SELECT Nickname1 AS Nickname,Name FROM tblName WHERE Nickname1 Is Not Null
UNION SELECT Nickname2,Name FROM tblName WHERE Nickname2 Is Not Null
UNION SELECT Nickname3,Name FROM tblName WHERE Nickname3 Is Not Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

The answer that PHV gave is known around here as a "normalizing query". Your output will look like the way your table should have originally looked. Basically the query is a fix for not normalizing.

 

[poporacer]

Thanks....I should have caught that...I changed the fields and added the tables.

thanks