Tables - Multiple Instance - Why do this? (1 Viewer)

[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".

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

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

Create a table that needs two relationships to the same table.
Create the first relationship. Fine
Create the second relationship. You are prompted. "A relationship already exists, Do you want to edit the existing relationship? To create a new relationship, click No" The question is badly worded so you have to say No to create the new relationship. Access then creates a suffixed table. If you delete the suffixed table (this does not alter the relationship). No prompt happens. Close the Relationships window and then reopen. The suffixed version reappears. It is quite possible that this behavior varies with versions but this is how mine is working "today"

You can always see the actual relationships by opening the MSysRelationships table.

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

There are instances where the Relationship window "forgets" all your hard work laying out the graphic design. When Access rebuilds the graphic, I think it builds it in the order the relationships were created but that is just a guess. In any case, it is almost always a mess. SQL Server has a better graphic tool. It allows you to segment parts of the schema to minimize noise and multiple pages of crossing lines.

However, If you put your mind to it, you can remove most of the noise by changing the placement of the tables to make the relationships more logical. Usually, it is only my state table that causes the crisscross lines but sometimes I have other lookup tables that are used in multiple places such as Prefix and Suffix for names.

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

There isn't any but a messy diagram makes the schema much more difficult to comprehend. If you have a schema that takes more then two pages to print, it becomes next to impossible to keep the picture organized. I've been known to upsize the tables to SQL Server simply to be able to produce a better diagram. There are other tools but I don't own any of them these days. I've forgotten the name of my favorite but it was very expensive. Several thousand dollars per seat.

I print out the diagrams, and hang them on the wall for reference.

I also save a copy of the diagram report so that if Access "helps" me and scrambles my diagram, I don't have to think about it, I have a copy that shows me how to fix it again.