Tables - Multiple Instance - Why do this?

[MatthewB]

When building relationships in the relationship window, it is possible to add multiple instances of a table. Generally they look something like this; Contacts_T_1, Contacts_T_2, Contacts_T_3., ect...

Why add multiple instances of a table? Are these instances related. So for example if you have a relationship: Contacts_T : Invoices_T (where the colon denotes a relationship, if I create a relationship Invoices_T_1 : Products_T , have I also created a relationship between Contacts_T and Products_T ?



Thank you

 

[theDBguy]

Why add multiple instances of a table?

This is mostly done when you have what is called a self-referencing relationship. For example, you might have a table of Employees and want to also track the employee's supervisor.

...have I also created a relationship between Contacts_T and Products_T ?

I believe, yes, you would have created that relationship.

 

[MatthewB]

Can one do this as a way to create less of a spider web in the relationships window?

 

[MajP]

Can one do this as a way to create less of a spider web in the relationships window?

Unfortunately Access always saves into one table.

So if you had something simple like an Title table (Dr. Mr., Mrs. Miss, etc) and you had this big relationship with Employees on one end and Clients on the other, you may want to make it neat with two instances of the Title table. When you save it will force it into one and will be more "spider webby".

 

[MatthewB]

The suffixed copies are there to make relationships clear. For example, you have BillToState and ShipToState in one record. Both point to tblState. However, they don't have to point to the same record. Therefore, you need to make it clear that there are TWO distinct and separate relationships between tblOrder and tblState. This is a more common situation than a self referencing relationship.

The third reason you do it is for visual neatness as Maj suggested.

PS, the current version of the Relationships dialog is not managing the sufixed relationships properly. I reported it but haven't heard back from MS as to whether they consider dropping the relationships to be a bug or a feature.

thank you. Your example is interesting. So what happens, or what disadvantage is there if you point BillToState and ShipToState to the same Table instance? Any?

 

[MajP]

It is only a graphical representation of the concept. Both are identical in execution.

 

[561414]

The instances allow you to create the relationship within the editor when you have two foreign key columns pointing to the same table, but you can also do this within VBA like this:

Sub CreateTableWithRelationship()

    Dim db As DAO.Database
    Set db = CurrentDb
   
    ' Create parent table "states"
    db.Execute "CREATE TABLE states (" _
        & "state_pk AUTOINCREMENT PRIMARY KEY, " _
        & "state_name TEXT(255));"
   
    ' Create child table "orders"
    db.Execute "CREATE TABLE orders (" _
        & "order_pk AUTOINCREMENT PRIMARY KEY, " _
        & "someData TEXT(255), " _
        & "ShipToState LONG, " _
        & "BillToState LONG, " _
        & "FOREIGN KEY (ShipToState) REFERENCES states(state_pk), " _
        & "FOREIGN KEY (BillToState) REFERENCES states(state_pk))"
   
    ' Clean up objects
    Set db = Nothing
   
    MsgBox "Tables 'states' and 'orders' created with relationship successfully."
   
End Sub

Which may or not be easier to understand, but handy to know for testing, developing and to know how things are working behind the scenes.

 

[GPGeorge]

Can one do this as a way to create less of a spider web in the relationships window?

I don't see any correlation between the spider webby appearance and the proper designation of relationships.