combination of 2 UniqueIDs appearing only once ?

[sanzoo]

Hello guys,
I have one table with following records

Id1 Id2
123 456
456 123
444 555
555 444
666 888
888 666

My aim is to get result probably by using query which will contain combination of both Uniques only once doesnt matter if its 123456 or 456123...something like this:

Id1 Id2
123 456
444 555
666 888

Any ideas how to do it ?
Thanks

 

[Brianwarnock]

In a query

Expr1: iif(id1<id2,id1 & id2,id2 & id1)

Group on expr1

Brian

 

[RainLover]

Brian's solution places the smaller number first regardless of which Field.

Is this what you want?

Why do you call them Both ID. Is this a Composite Primary Key.

 

[Brianwarnock]

He said that he didn't mind which way .

Brian

 

[sanzoo]

Brian`s solution works just fine for me. Yes, RainLover basically its a composite unique ID ...the reason behind is that those 2 Ids represent 2 records each with its own Id and they are in my table next to each other and my goal is to have them in the final query appearing just once because "A" and "B" is the same as "B" and "A" in my case so its doesnt matter which one is the first.

 

[RainLover]

Glad to see Brian's help was useful.

 

[sanzoo]

Hi guys,
I found out that its not working the way I expected because when I use Brian`s formula and group it Im able to get unique records only for that one Unique(Expr1) field however when I want to have also rest of the records in the query it is duplicating the rows because the rest of the records are slightly different therefore it cannot be grouped, below is example:
Expr1 NAME1 NAME2
I3AFR04D3AFR05 CCCC CCCB
I3AFR04D3AFR05 CCCB CCCC
I3BAL01D3BAL02 DDDD DDDH
I3BAL01D3BAL02 DDDH DDDD

 

[Brianwarnock]

The names are switched just like the IDs , if the order of those also doesnt matter then in the Totals row select First for them, or Last if you prefer.

Brian