Web Development

Ask something
Discussion Locked

SQL "inverse duplicates" query?

0 helpful
7 replies

by mr. origami

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Anyone have any ideas for a "find inverse duplicates" query in Access? Here's what I mean. I have one table - tblColors. Two fields - color1 and color2. Table tracks colors that look good together. So, someone may have:

Color1........Color2
----------------------
red...........orange
blue..........green
orange........red

How do I weed out the duplicate red/orange pair? It's not really a duplicate record, but kind of like an inverse duplicate.

I need the SQL syntax to display color1and color2 which in this case would return only 2 records - red/orange and blue/green. And it doesn't matter if it returns red/orange or orange/red, as long as the pair is returned only once. So I'd run the query and get:

color1...........color2-----------------------
red..............orange
blue.............green

7 replies

by mr. origami

I don't understand. There is only 1 table - tblColors. It has only two fields - Color1 and Color2.

So - why did you code "Colors1.Color1" etc...?

by avhohlov

Correction to previous answer.
Simply replace Colors with tblColors.
Colors1&Colors2 are aliases (temporary names) of Colors (tblColors).

by mr. origami

Still extremely confusing with the word Colors every other word! Any chance of giving this to me again with the following: table = T1, fields = C1 and C2, aliases = myC1 and myC2?

by avhohlov

--table = T1, fields = C1 and C2,
table aliases myT1_1 and myT1_2 (both T1 aliases)
field aliases myC1 and myC2

SELECT DISTINCT IIf(myT1_1.C1

This discussion is closed to new replies.