Linking or Joining a Table to itself? 2

[Smite]

Is it possible to link a field in a table to the same field but in a different record? I have asked people in the past and was told there about a Phantom Link where you add the same table multiple times in the relationship window but this is not working.

 

[cmmrfrds]

Yes, you can relate a table to itsself. Can you show an example of what you expect the sql statement to deliver. Also, an example of the field names.

 

[LittleSmudge]

Smite - you said
Is it possible to link a field in a table to the same field but in a different record?

The answer to what you ACTUALLY said is "technically yes - but it does not do any useful purpose."

But you can link a field to another field in another record in the same table.

One usually comes across this type of structure in an employee organisation chart.

tblEmployee
EmployeeId
FName
SName
Department tblEmployee
Manager . . . . . . EmployeeId
FName
SName
Department
Manager

The Manager field is the Foreign Key in a One-to-many relationship.

The Managing Director (CEO) will have a blank Manager field
All the MD's direct reports have the MD's EmployeeId in their Manager field and so on down the line.


To achieve this in the Relationship window you do need to create two copies of the tblEmployee and then link EmployeeId to Manager in the usual way.

Is this the sort of thing that you're looking for?


'ope-that-'elps.

G LS

 

[Smite]

First let me preference this with what this database is accomplishing. I am in charge of the infrastructure in multiple computer rooms for the company I work for. I plan to use the database to document where all network connectivity is being routed i.e. from switch to server. So basically you have a device that connects to a backbone piece of CAT5 cabling and then a connection is made in our MDF (Main Distribution Facility) to another place in the MDF which then connects to a swith. The table only needs 4 fields: Device, Device interface, Local Cable Plant, and MDF Cable plant. then if I can link that MDF Cable Plant field to itself but in another record I can complete the link. Hopefully this helps.

 

[Smite]

First let me preference this with what this database is accomplishing. I am in charge of the infrastructure in multiple computer rooms for the company I work for. I plan to use the database to document where all network connectivity is being routed i.e. from switch to server. So basically you have a device that connects to a backbone piece of CAT5 cabling and then a connection is made in our MDF (Main Distribution Facility) to another place in the MDF which then connects to a switch. The table only needs 4 fields: Device, Device interface, Local Cable Plant, and MDF Cable plant. then if I can link that MDF Cable Plant field to itself but in another record I can complete the link. Hopefully this helps.

Ex:
Device Interface Local MDF
dell-01 NIC 82-01-01 102-01-01
switch 5/2 201-01-01 103-01-01

The above would represent 2 records but if linked together via the MDF would represent 1 cable pathway or link.

 

[LittleSmudge]

then if I can link that MDF Cable Plant field to itself but in another record I can complete the link. Hopefully this helps.

If you 'link' two fields together then they need to contain the same data in that field.

So if you link the SAME field with itself in different records then they still need to contain the same data.

I think you are going to need a linking table.
Which makes sence from a data Normalisation point of view.
The table you have is a table of machines. A link between two machines does not have a relation to any one machine ( but between two ). So you need a new table

tblCable
SwitchMDF
OtherMDF

so your structure becomes

tblMachines
dell-01
NIC
82-01-01            tblCable
102-01-01 . . .|    103-01-01 . . .|
               |. . 102-01-01      |      tblMachines
                                   |      switch
                                   |      5/2
                                   |      201-01-01
                                   |. . . 103-01-01

'ope-that-'elps.

G LS