Access Query to show non dups 2 fields. 1 table

[Amedeo]

I'm not an SQL expert so I was looking for some help that would allow me to get the following results using 1 table:

Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it

Table A

Field1 ID Field2 ID
1 5
2 4
3 6


4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8

I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4, hope this makes sense.

Thanks,
Amedeo

[RuralGuy]

What is in Field2 when there is NO roommate?

[Amedeo]

What is in Field2 when there is NO roommate?

It's just blank, i had a query to just select non blanks in field 2 to eliminate people with no roommates.

Thanks

[RuralGuy]

So if the table were sorted by Field1 number and the number in Field2 is < the number in Field1 then it would have already been included in the Recordset, right?

[Amedeo]

Yes, field 1 is just the record auto ID which all records will have, when a roommate is assigned to a record basically we are adding the same record ID into that records RoommateID field on the same table.

I've attached a sample database, so based on the sample data,

If the query works it should show only the following 4 records:
record 4 but not 5
record 6 but not 7
record 8 but not 12
record 9 but not 11

I have a pre-query in the database to eliminate all records that do not have a roommate.

Thanks,
Amedeo

[RuralGuy]

Try the query now.

[Amedeo]

Awesome, looks like it works!!

Just so you know the reason behind this, this list is supplied to the hotels, so the hotels only want to see the name of the person once, if we were to pull all the records with roommates without this query you would see:

john smith rooming with jane smith and
jane smith rooming with john smith

Thanks for you help, really appreciate it.

[RuralGuy]

You're welcome. All I did was implement the suggestion I had in Post #4.