Conditionally Selecting Column Unique Data

[JWilliams]

I've been racking my brain on this for a few of hours now. I have a table
that contains four columns. They are ID, FirstName, LastName, and
DisplayName. ID is the primary key. What I would like to do is if the
DisplayName is unique select it, otherwise, select the concatenation of the
FirstName and ListName separated by a single whitespace, of course. I can
figure out how to find what data is duplicated, but I cannot seem to get the
the fully unique data. The Distinct clause removes all but one row of the
duplicated data. I'm looking to get rid of all rows that have been
duplicated, not just all but one row. I think that if I determine how to get
the truly unique rows and I can get the rows with the duplicated column
entries, that I can use an IIF to select the value that should be displayed
accordingly.

 

[JWilliams]

Apologies for double posting, I didn't realize the post was processed after I
verified my eMail.

JWilliams

 

[Mo]

I've been racking my brain on this for a few of hours now. I have a table
that contains four columns. They are ID, FirstName, LastName, and
DisplayName. ID is the primary key. What I would like to do is if the
DisplayName is unique select it, otherwise, select the concatenation of the
FirstName and ListName separated by a single whitespace, of course. I can
figure out how to find what data is duplicated, but I cannot seem to get the
the fully unique data. The Distinct clause removes all but one row of the
duplicated data. I'm looking to get rid of all rows that have been
duplicated, not just all but one row. I think that if I determine how to get
the truly unique rows and I can get the rows with the duplicated column
entries, that I can use an IIF to select the value that should be displayed
accordingly.

The usual way - or one of the usual ways - is to create a SELECT
DISTINCT query and then switch to a MAKE TABLE query.

There's more information here:

http://www.databasejournal.com/feat...lete-Duplicate-Records-From-Access-Tables.htm

 

[JWilliams]

Distinct doesn't work in the manner that I wish. A single copy of the
duplicated records would still exist in the select statement's results. I
want ALL duplicates removed.

After sleeping, I had another go at it. I've found a solution, but it seems
very messy to me. Placed in code tags only for readability.

Select Users.ID, Users.FirstName, Users.LastName, Users.DisplayName
From Users
Where Users.ID Not In (Select DuplicateEntries.ID From (Select
(Users.FirstName & " " & Users.LastName) As DisplayName, Users.ID As ID
From Users, (Select Distinct Users.DisplayName As DuplicateDisplayName From
Users, Users As DuplicateUsers Where Users.ID <> DuplicateUsers.ID And
Users.DisplayName = DuplicateUsers.DisplayName) As DuplicateEntries
Where Users.DisplayName = DuplicateEntries.DuplicateDisplayName) As
DuplicateEntries)
UNION Select DuplicateEntries.ID, Users.FirstName, Users.LastName,
DuplicateEntries.DisplayName
From Users, (
Select DuplicateEntries.ID, DuplicateEntries.DisplayName From (Select
(Users.FirstName & " " & Users.LastName) As DisplayName, Users.ID As ID
From Users, (Select Distinct Users.DisplayName As DuplicateDisplayName From
Users, Users As DuplicateUsers Where Users.ID <> DuplicateUsers.ID And
Users.DisplayName = DuplicateUsers.DisplayName) As DuplicateEntries
Where Users.DisplayName = DuplicateEntries.DuplicateDisplayName) As
DuplicateEntries) As DuplicateEntries
Where Users.ID = DuplicateEntries.ID;

 

[John Spencer]

Build a query that returns only those records that have only 1 record

SELECT *
FROM SomeTable
WHERE DisplayName in
(SELECT DisplayName
FROM SomeTable
GROUP BY DisplayName
HAVING Count = 1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

I would think the following would work to give you what you are asking for.

Select Users.ID
, Users.DisplayName as ShowName
From Users
WHERE Users.DisplayName in
(SELECT DisplayName
FROM Users
GROUP BY Users
HAVING Count(DisplayName) = 1)

UNION

Select Users.ID
, Users.FirstName & " " & Users.LastName as ShowName
From Users
WHERE Users.DisplayName in
(SELECT DisplayName
FROM Users
GROUP BY Users
HAVING Count(DisplayName) <> 1)

An alternative


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Distinct doesn't work in the manner that I wish. A single copy of the
duplicated records would still exist in the select statement's results. I
want ALL duplicates removed.

After sleeping, I had another go at it. I've found a solution, but it seems
very messy to me. Placed in code tags only for readability.

Select Users.ID, Users.FirstName, Users.LastName, Users.DisplayName
From Users
Where Users.ID Not In (Select DuplicateEntries.ID From (Select
(Users.FirstName & " " & Users.LastName) As DisplayName, Users.ID As ID
From Users, (Select Distinct Users.DisplayName As DuplicateDisplayName From
Users, Users As DuplicateUsers Where Users.ID <> DuplicateUsers.ID And
Users.DisplayName = DuplicateUsers.DisplayName) As DuplicateEntries
Where Users.DisplayName = DuplicateEntries.DuplicateDisplayName) As
DuplicateEntries)
UNION Select DuplicateEntries.ID, Users.FirstName, Users.LastName,
DuplicateEntries.DisplayName
From Users, (
Select DuplicateEntries.ID, DuplicateEntries.DisplayName From (Select
(Users.FirstName & " " & Users.LastName) As DisplayName, Users.ID As ID
From Users, (Select Distinct Users.DisplayName As DuplicateDisplayName From
Users, Users As DuplicateUsers Where Users.ID <> DuplicateUsers.ID And
Users.DisplayName = DuplicateUsers.DisplayName) As DuplicateEntries
Where Users.DisplayName = DuplicateEntries.DuplicateDisplayName) As
DuplicateEntries) As DuplicateEntries
Where Users.ID = DuplicateEntries.ID;

The usual way - or one of the usual ways - is to create a SELECT
DISTINCT query and then switch to a MAKE TABLE query.

There's more information here:

http://www.databasejournal.com/feat...lete-Duplicate-Records-From-Access-Tables.htm