Remove duplicates made up from different columns (1 Viewer)

[tvbrooks]

Hello All,

I am trying to remove duplicate rows of data in an access table where the duplicate data is from a different column.

For example, you'll from my example below (i hope it shows ok) that this output gives all the combination for 3 items of data (0000000001, 0000000478, 0000000664)

system_id1 system_id2
1 0000000001 0000021880
2 0000000001 0000000478
3 0000000478 0000000001
4 0000000478 0000021880
5 0000021880 0000000001
6 0000021880 0000000478

I want to create a query/method of elimating the duplicate rows so that we are left with (in this example) just the 3 unique combinations i.e rows 1,2 and 4.

I've tried self joins and other ideas but cannot find a way to show only the unique combinations.

Any ideas are much appreciated

 

[jjturner]

since (ColA, ColB) can represent the same values as (ColB, ColA), you need a way to draw an 'atomic' equivalence. One 'trick' method I can think of is to represent them as components of a conditional field and a simple sum field:

LesserComponent: IIf(ColA <= ColB, ColA, ColB)
SumOfComponents: ColA + ColB

With this 2-field 'key', you can determine duplicates.

Hope this gives you some ideas. Joins would be another avenue to pursue, and although you've already attempted this, I suspect it can still be done...

Cheers,
John