Remove duplicates made up from different columns

[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

 

[Tombra]

I don't know if this is what you expected but try

SELECT Min(Tabela2.System_ID1) AS MinimumOfSystem_ID1, Max(Tabela2.System_ID2) AS MaksimumOfSystem_ID2
FROM Tabela2
GROUP BY IIf([System_ID1]>[System_ID2],[System_ID1],[System_ID2]) & IIf([System_ID1]>[System_ID2],[System_ID2],[System_ID1]);

 

[tvbrooks]

Hi T,

Thanks for the suggestion I gave it a go. Unfortunately it left a single duplicate and I am not sure it would work on the larger dataset that I have.

Thanks again.

I don't know if this is what you expected but try

SELECT Min(Tabela2.System_ID1) AS MinimumOfSystem_ID1, Max(Tabela2.System_ID2) AS MaksimumOfSystem_ID2
FROM Tabela2
GROUP BY IIf([System_ID1]>[System_ID2],[System_ID1],[System_ID2]) & IIf([System_ID1]>[System_ID2],[System_ID2],[System_ID1]);

 

[neileg]

Create two queries, one that returns just ID1 and the other just ID2 and make the field name the same in each query.

Then create a union query (look in Access Help) to union these both together. If you use UNION and not UNION ALL, this will return unique values.