Select Distinct problem

[deanbri75]

I have the following code that I want to return the room assignment and occupants of that room.

SELECT     TOP 100 PERCENT A.Assignment, A.FirstName AS FN1, A.LastName AS LN1, B.FirstName AS FN2, B.LastName AS LN2
FROM         dbo.housemail A LEFT OUTER JOIN
                      dbo.housemail B ON A.Assignment = B.Assignment AND A.FirstName + A.LastName <> B.FirstName + B.LastName
GROUP BY A.Assignment, A.FirstName, B.FirstName, B.LastName, A.LastName
ORDER BY A.Assignment

These are the results I get:

[tt]
Assignment FN1 LN1 FN2 LN2
CDV B206 Johnson Jasmine Rossouw Aileen
CDV B206 Rossouw Aileen Johnson Jasmine
CDV B207 Hailey STANLEY Roberson Reginald
CDV B207 Roberson Reginald Hailey STANLEY
CDV B208 Brown Tamaneka Cammack LAUREN
CDV B208 Cammack LAUREN Brown Tamaneka
[/tt]

What I want is for it to return only one row per Assignment.

 

[dhookom]

You didn't type what you would expect to see in your results. "one row per Assignment" is a bit vague.

You may need to start with a union query that normalizes your data. Then create a totals or select query based on the union query.

Sorry this was such a vague reply but you didn't give us much more to work with.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[deanbri75]

Sorry, I wasn't intentionally vague. The output I'm after should look something like this:

Assignment FN1 LN1 FN2 LN2
CDV B206 Johnson Jasmine Rossouw Aileen
CDV B207 Hailey STANLEY Roberson Reginald
CDV B208 Brown Tamaneka Cammack LAUREN

Instead of what I'm getting now which looks like this:

Assignment FN1 LN1 FN2 LN2
CDV B206 Johnson Jasmine Rossouw Aileen
CDV B206 Rossouw Aileen Johnson Jasmine
CDV B207 Hailey STANLEY Roberson Reginald
CDV B207 Roberson Reginald Hailey STANLEY
CDV B208 Brown Tamaneka Cammack LAUREN
CDV B208 Cammack LAUREN Brown Tamaneka

I'd like it to return only one row per assignment instead of two. The results i'm getting currently give me two rows and just transposes the position of the names.

 

[Golom]

Change <> to > in your from clause.

 

[deanbri75]

Doing that just omits the FN2, LN2 on the 1st row for each duplicate assignment.

Assignment FN1 LN1 FN2 LN2
CDV B206 Johnson Jasmine
CDV B206 Rossouw Aileen Johnson Jasmine
CDV B207 Hailey STANLEY
CDV B207 Roberson Reginald Hailey STANLEY
CDV B208 Brown Tamaneka
CDV B208 Cammack LAUREN Brown Tamaneka