Address commonality issue

[mcoates10]

Hi All

I have a database of telecoms circuits. For each circuit I have an A end and B end. Currently these two ends are in separate tables and joined to the circuit table by a unique identifier.

How can I have all address of both A and B ends in one table and draw upon this from the circuit table. What sort of relationship is it and how do I do it?

The problem for me is that I want to search the database for all circuits at 1 location, but the actual location could be in the a or b site table. I want to be able to search all addresses from one table, yet be able to site nominate and a and b end for each circuit.

Cheers

 

[SJMcAbney]

CircuitID (PK - Autonumber)
CircuitName (Text)
CicuitType (A or B)

Stewart

 

[PHV]

I want to search the database for all circuits at 1 location, but the actual location could be in the a or b site table
You may search an Union query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Both a Union query and one-to-one relationship internal link will work.

A possible table design would be...

tblCircuit
CircuitID - primary key
CircuitName
CircuitType
TerminusID - foreign key to CircuitID

You can also add...
ConnectedHardwareID - foreign key to a hardware device table
SupportVenderID - foreign key to a vender table
etc
etc

This is sort of like the an employee table pointing back to itself for the manager of the employee, except the two records would reference each other.

The way it works may best be seen by dummy data...
[tt]
tblCircuit
CircuitID CircuitName CircuitType TerminusID
1 Jack-124 CAT5e 2
2 Catalyst1924-Z45-11 CAT5e 1
3 Jack-125 CAT5e 4
4 Catalyst1924-Z45-12 CAT5e 3
5 Catalyst1924-Z45-F1 Fiber-SC 6
6 Catalyst3500-Z46-F3 Fiber-SC 5
[/tt]

I used network terms, since I a much more familiar with these, but hopefully, you can see what I am getting at.

Also, for the circuit name, I included the name of the device at the termination point. I would normally use the a separated fields to separate device and port, and used a device table.

I realize with telcom circuits, you will have circuit "numbers" for your different circuit types, ATM, ISDN, Frame relay, etc. But the conscept should still work.

Note that by referencing a device ID for a circuit termination point, you can easily see how many circuits are attached to a specific device. For example, a router with 10BaseT, Frame, voice and ISDN.

Richard