Chuck wrote:
it was just a comment.
Your comment gave me an idea. Maybe the following can avoid
concatenation issues:
qryDistinctList:
SELECT lastname, firstname, address, phonenumber FROM MyTable UNION
SELECT lastname, firstname, address, phonenumber FROM MyTable;
There's likely to be a simple way to remove duplicates using an
extension of this idea.
tblDuplicates
DID Autonumber
Field1 Text
Field2 Text
Field3 Text
DID Field1 Field2 Field3
1 A B D
2 A C D
3 A B D
4 B C E
5 A C E
qryDistinctList:
SELECT Field1, Field2, Field3 FROM tblDuplicates UNION SELECT Field1,
Field2, Field3 FROM tblDuplicates;
qryUnique:
SELECT First(DID) AS UID, tblDuplicates.Field1, tblDuplicates.Field2,
tblDuplicates.Field3 FROM tblDuplicates INNER JOIN qryDistinctList ON
(tblDuplicates.Field3 = qryDistinctList.Field3) AND
(tblDuplicates.Field2 = qryDistinctList.Field2) AND
(tblDuplicates.Field1 = qryDistinctList.Field1) GROUP BY
tblDuplicates.Field1, tblDuplicates.Field2, tblDuplicates.Field3 ORDER
BY First(DID);
!qryUnique:
UID Field1 Field2 Field3
1 A B D
2 A C D
4 B C E
5 A C E
What if only the combinations of Field1 and Field2 must be unique yet
Field3 must be shown?
qryDistinctList:
SELECT Field1, Field2 FROM tblDuplicates UNION SELECT Field1, Field2
FROM tblDuplicates;
!qryUnique:
SELECT First(DID) AS UID, tblDuplicates.Field1, tblDuplicates.Field2,
First(tblDuplicates.Field3) As FirstField3 FROM tblDuplicates INNER
JOIN qryDistinctList ON (tblDuplicates.Field2 = qryDistinctList.Field2)
AND (tblDuplicates.Field1 = qryDistinctList.Field1) GROUP BY
tblDuplicates.Field1, tblDuplicates.Field2 ORDER BY First(DID);
!qryUnique:
UID Field1 Field2 FirstOfField3
1 A B D
2 A C D
4 B C E
Since there are potentially many Field3 values for the unique
combinations of Field1 and Field2, a choice had to be made about which
one is to be returned. You can get fancy from here about which ones to
keep using subqueries (use the same criteria for both DID and Field3
noting that ordering by DID is not necessary in qryUnique). The most
recent dates or maximum values (price? :-)) are typical choices. An
unmatched query on DID and UID can identify which records to
delete/exclude. I hope these comments give you some ideas.
James A. Fortune
CD********@FortuneJames.com 
