Adding the samen field twice.

[RamonEJ9]

Hello everyone,



I got a question about a database I'm trying to design. It's a small cable management system.

I came acros a very frustrating problem. I want to create a form that shows where the port of a computer is connected to.

For example; A computer has one ethernet port. That port is connected to a switch. I can add the fields for the Unique hardware ID and for the ports of the computer. But I can't seem to chose a device where the computer is connected to.
Ive tried working with queries.. but nothing seems to work so far.

I created a small example of the database I'm trying to make. I hope someone can take a look at it and point me in the right direction .

Thanks!

example_database.zip

[jzwp11]

First, I would strongly recommend that you do not use lookup fields in your tables; they can cause problems as detailed here.

A hardware item can have many ports and (the names of) the ports can apply to many hardware items. That describes a many-to-many relationship which you have essentially captured in your hardwareportmode table, but you need another table to define when the port of one device is related to the port of another device.

Also, I do see some opportunities for normalization with respect to your hardware table. Specifically, you repeat the type names and brand names several times. These can be set up in separate tables and then referenced in your hardware table.


The attached database has the new table that ties related hardware/port combinations to each other (tblRelatedHardwarePorts) and has the additional tables for types & brands. I've also added the applicable relationships in the relationship window.

[RamonEJ9]

Hello,

Thanks for your quick reply and helpfull comments and changes to the database file. I only used the lookup files because it was said to be the easiest way to make relations.

I opened your database and took a look at it. I kind of understand it now, thanks for that. They only question I got right now is;

When I for example; load the form for WS001 and connect it to port Fa0/0 of SWS01, how can I make this appear in the form of SWS01, because that one stays blank.

Thanks again!

[jzwp11]

I'm not sure I quite follow your question, but I came up with a question that could impact the structure that I provided to you earlier. Can a single port from one device be associated with more than one port on another single device or single ports on multiple devices?

Also, I was unsure of how to handle the sending & receiving. Could you explain what you mean by these terms?

[RamonEJ9]

Sorry English ain't my native language. So some things are hard for me to explain.

No, one port from one device can only be associated with one port from another device.

Well, for example; a workstation has one networkport, this port is used to receive a networkconnection from another device. But the port of a switch can have both, it can receive the networkconnection from another switch or router. But it can also be used the forward (or send) the networkconnection to another device.

I hope I awnsered your questions with this

Thanks!

[jzwp11]

OK, the design I provided earlier is incorrect.

Well, for example; a workstation has one networkport, this port is used to receive a networkconnection from another device.But the port of a switch can have both, it can receive the networkconnection from another switch or router. But it can also be used the forward (or send) the networkconnection to another device.

But, I'm a little confused about your statement above. I assume that a switch can have many ports but a single port on that switch can only be connected to 1 device but the communication to that other device can be in either mode (send or receive) depending on what type of device makes up the other connection.

Even in the case of a workstation, doesn't the port that receives the network connection also allows data to be sent back to the server thus making it both sending & receiving?

I guess I still am not clear on the sending/receiving part & I think that will impact the table structure.

[RamonEJ9]

Your right about that. But we want to implement a function that can trace a signal. We added the receiving and sending statements to trace a signal.

For example.

WS001 / Port Fa > Receiving (the connection from SW01 Fa0/1)
----> Connected to:
SW01 / Port Fa0/1 > Sending (the connection to WS001 Fa)

SW01 / Port Fa0/24 > Receiving (the connection from RTR01 Fa0/0)
----> Connected to:
RTR01 / Port Fa0/0 > Sending (the connection to SW01 Fa0/24)

RTR01 / Port Fa0/1 > Receiving (the connection from ISP Fa0/0)
----> Connected to:
ISP / Port Fa0/0 > Sending (the connection to RTR01 Fa0/1)

I hope this example will make all clear!

Thanks for your time and patience with me :-P

[jzwp11]

OK, I understand what you want to do, but I struggled on how to accomplish it. Since a connection has two ports, that describes a one-to-many relationship, so what I did was added two new tables tblConnections and tblConnectionPorts. In the attached database, I would use the form frmHardware to define the units and the ports applicable to each unit. I would then use the form frmConnections to define the ports associated with each other that make up the connection. I'm not sure if this is what you were after, but it is the best way that I could see to handle what you wanted to do.