Query Union

[poporacer]

I have a table (tblRoster) with the data on each person. I have another table (tblNickname) that has nicknames (some people have more than one nickname). I want a query that will give me all the names from tblRoster (no duplicates)with the nicknames. I did a LEFT JOIN and tried using DISTINCT but both ways if someone had more than one nickname they are listed twice.
Here are the queries I have tried:
SELECT tblRoster.ID,tblRoster.LName, tblRoster.FName, tblRoster.Address, tblNickname.Name FROM tblRoster LEFT JOIN tblNickname ON tblRoster.ID=tblNickname.ID

This gives me multiple entries for the same person.

SELECT DISTINCT tblRoster.ID,tblRoster.LName, tblRoster.FName, tblRoster.Address, tblNickname.Name FROM tblRoster LEFT JOIN tblNickname ON tblRoster.ID=tblNickname.ID

This gives me multiple entries for the same person.

SELECT DISTINCTROW tblRoster.ID,tblRoster.LName, tblRoster.FName, tblRoster.Address, tblNickname.Name FROM tblRoster LEFT JOIN tblNickname ON tblRoster.ID=tblNickname.ID

Any Ideas?

 

[ClydeData]

I think you are looking to concatenate see faq701-3499

Hope tis heps jimmy

 

[poporacer]

That isn't quite what I want I hope this makes it a little clearer than mud.
I have two tables (tblRoster and tblAKA) tblRoster has the specifics on each person (ID#(unique identifier) Name, Address, Phone) tblAKA has the ID# and nicknames, sometimes more than one nickname for a person. I need a query that will join the two tables BUT I only want one entry for each person.

Here is an example of what I am trying to do:

tblRoster:
ID Name Address Phone
1 Betty 123 St. 555-123-1234
2 Sam 321 W. 555-987-1234
3 Tom 456 S. 555-456-9876

tblAKA:
ID Nick
1 Betty Boop
2 Shorty
1 Scary

And I want to get the following results:
ID Name Address Phone Nick
1 Betty 123 St. 555-123-1234 Betty Boop
2 Sam 321 W. 555-987-1234 Shorty
3 Tom 456 S. 555-456-9876

I tried a Left Join and tried to use the DISTINCT property on the ID but still got duplicates:

ID Name Address Phone Nick
1 Betty 123 St. 555-123-1234 Betty Boop
1 Betty 123 St. 555-123-1234 Scary
2 Sam 321 W. 555-987-1234 Shorty
3 Tom 456 S. 555-456-9876

I also tried something but didn't work but seemed close:
SELECT ID, Name, Address, Phone, tblAKA.Nick
FROM tblRoster
LEFT JOIN
(SELECT DISTINCT tblAKA.ID, tblAKA.Nick FROM tblAKA) on tblRoster.ID = tblAKA.ID

Any Ideas???

 

[PHV]

SELECT tblRoster.ID,tblRoster.LName, tblRoster.FName, tblRoster.Address, First(tblNickname.Name) AS NickName
FROM tblRoster LEFT JOIN tblNickname ON tblRoster.ID=tblNickname.ID
GROUP BY tblRoster.ID,tblRoster.LName, tblRoster.FName, tblRoster.Address

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