Hide Duplicates in a Query Across Two Fields

[ItsRoland]

So I've got a database that's primarily an asset inventory to put it simply. Each device in the database has ports assigned to it, and most of these ports connect to ports on other devices. I'm trying to build a query that will show every connection between devices (i.e. Device A, port 1, connects to Device B, port 3, etc.). I've got this working for the most part, however I'm getting duplicate entries that I'm not certain how to handle. For example, there's an entry showing that Device A Port 1 connects to Device B port 3, and then another entry showing that Device B port 3 connects to Device A Port 1. I want to make it so if the connection between two devices has already been displayed it won't show the connection again from the other end.

The data is structured as follows: Each individual port has it's own record in tblPorts. This includes a unique ID, IDdevice (the ID of the device the port is on), IDport (the ID of the port that it is connecting to), and then some other information that isn't relevant to the issue at hand.

I built a query (lets call it query1 for simplicity) that grabs the device name, the port ID, and IDport (the ID of the port being connected to). A device with 3 ports will have 3 entries in this query, one for each individual port. Then I built another query (query2) that grabs all the information out of query1 and uses the IDport value to grab information about the device being connected to. As stated before, this is working fine except for the duplicate entries.

So if I understand correctly, I'm looking for a way to hide an entry if the port ID from query1 has been shown already in port ID from query2 or vice versa, but I have no idea how to accomplish this.

Please let me know if any further info or explanation is needed. I apologize if I explained the issue poorly.

Thanks in advance for any help!

[isladogs]

Its difficult to give precise advice because you haven't indicated the fields used.
The thread title indicates you want to hide duplicates in a query. That's easy -set unique values =yes or in SQL use SELECT DISTINCT.

However it makes more sense to prevent duplicates in the first place.
To do that, you assign a composite INDEX on all the fields that should never be duplicated as a group.
I think that is 4 fields. Device and port twice...or is it two fields...its not clear from your account.
However you need to delete existing duplicates before you can set a composite index as that is designed to prevent them.

If you want to know how to remove duplicates, ask.

[ItsRoland]

Let me try to explain this a bit better.

There's tblDevices and tblPorts. Each device has multiple ports, so if Device A has 3 ports, it will have 3 records in tblPorts, one for each port.

So lets say we have the following in tblDevices:

ID____Location_____Hostname
1_____BuildingA____DeviceA
2_____BuildingB____DeviceB

Lets say both devices have 3 ports, with the first port on each device connecting to each other. So in tblPorts there would be:

ID____ID_device_____ID_port
1_____1(DeviceA)______4
2_____1
3_____1
4_____2(DeviceB)______1
5_____2
6_____2

Port ID 1 belongs to Device A and connects to port ID 4 on Device B
Port ID 4 belongs to Device B and connects to port ID 1 on Device A

Query1 Has the following information. It doesn't show records where the port doesn't connect to another port, so in this example it would only contain the following:

tblPorts.ID____Location____Hostname____ID_port
___1________BuildingA____DeviceA_______4
___4________BuildingB____DeviceB_______1

Query2 uses the information from Query1 to complete a connection on a single line.

Query1.tblPorts.ID____Query1.Location______Query1. Hostname____Query1.ID_port______tblPorts.ID____Loc ation_____Hostname
_______1_____________BuildingA___________DeviceA__ _______________4_______________4_______BuildingB__ ___DeviceB
_______4_____________BuildingB___________DeviceB__ _______________1_______________1_______BuildingA__ ___DeviceA

What I want to do is remove the second line from the above Query 2 example. Since It's already displayed the connection between DeviceA port ID 1 and DeviceB port ID 4, it doesn't need to show the connection again.

So in Query2, if Query1.tblPorts.ID has already shown ID '1', then a record where tblPorts.ID is '1' shouldn't be shown, and vice versa.

As is, the number of connections shown in Query2 is double the actual number of connections since it's showing both DeviceA -> DeviceB and DeviceB -> DeviceA.


Hopefully I properly understood what you were looking for and made sense. Apologies if I'm going the complete wrong direction in my explanation.

Thanks again for the help!

[isladogs]

Thank you for clarifying some of the details. However we need to see more data in order to give a generic answer.
For the two rows above, you can just filter for e.g BuildingA in query1.Location.
If that will work for all cases, fine but I suspect not.
Suggest you upload example data for both tables that will give enough info so we can advise properly.
Make sure there are sufficient records to see all required outcomes

[ItsRoland]

I actually figured it out just a minute ago.

I realized the issue would be solved by giving each connection its own ID.

So to the tune of my previous example, the device A to device B connection would have connection ID 1, and likewise the device B to device A connection would have connection ID 1.

Then I just prevented 'connectionID' duplicates in the query and that gave me what I wanted.

There's probably a better way to achieve a similar result, and if anyone knows what that is I'd appreciate any advice.

Thanks again!