challenge of filter values that exist in another field.

[czarjosh]

Roommate Report.
I am trying to make a report that lists, people and roommates. However, because both people are stored in the table, I am getting doubles.
Name Roommate
_________ _________
Josh F Barb A.
Barb A. Josh F.

SELECT Registration.[First Name]+" "+Registration.[Last Name] AS Name, Registration.[First Name], Registration.[Last Name], Registration_1.[First Name] & " " & Registration_1.[Last Name] AS [Roommate Name] FROM Registration LEFT JOIN Registration AS Registration_1 ON Registration.Roommate=Registration_1.RecNumber;

I want it to just be
_______ __________
Josh F. Barb A.

Do you have any clue on this? This database is something I inherited, and I admit there are serious design flaws, however I do not have the tiem to sit and redesign the entire thing.

 

[Crowley16]

do you have a primary key for this table?

you could do select distinct on just the primary key, and then use these to populate the next qry or report...

 

[czarjosh]

yes RecNumber is the primary key.

Is this the way teh SQL would look?

SELECT DISTINCT Registration.RecNumber, Registration.Roommate, Registration.[First Name]+" "+Registration.[Last Name] AS Name, Registration.[Room Type]
FROM Registration LEFT JOIN Registration AS Registration_1 ON Registration.Roommate = Registration_1.RecNumber
WHERE (((Registration.[Room Type]) Is Not Null));

I have included links to pictures so that you can start to see what is happening with the data.

http://www.pitt.edu/~jlf12/Clipboard01.jpg

http://www.pitt.edu/~jlf12/Clipboard02.jpg