Tools, Relationships keeps inserting extraneous tables

[elviajero]

When I go to view my relationships, I should have a total of 10 tables. However I am seeing two new tables being created in the relationships window for one of the tables, adding _1 and _2 to the name, and one new table _1 for each of 7 other tables. The remaining 2 tables don't do this at all.

I can manually delete the extraneous tables and save the layout but if I exit and go back into Relationships, they always come back.

I'm relatively confident in my tables and relationships, but it's possible I've made some egregious error. Has anyone ever seen this, or have any ideas why it's happening? I'm feeling rather stupid.

 

[Quehay]

If anyone has a good answer to this I promise to push the TipMaster button as many times as I've deleted those stupid "this will probably help the visual reference" extra tables1 tables2 tables3 tables4 tables5 tables6.

Sorry not to answer the question but I can commiserate. It is irritating as hell to have the tables reappear after "saving" a layout.

Here's another one: If you have a database password (general file access password) and have Compact On Close selected the password prompt appears every time after exiting the program--makes you look sloppy, and users typically don't understand the nature of "source code" problems.

Quitaselo MSoft!

 

[JerryDennison]

This typically occurs when you are using a front end/back end situation and you've set relationships in the BE but then also set relationships in the front end using the linked tables.

 

[RickSpr]

It also happens when you have self-joins, that is, relationships (such as parent-child or manager-employee relationships) between rows in a single table. And, it happens if you have multiple columns in a table all related to the same column in another table.

Essentially, the reason is that, if only one copy of the table were used, the joins would conflict. For instance, suppose you have a table with employees, some of whom are managers, and you create a relationship between EmployeeID and ManagerID (the manager for a given employee, related to a different row representing the employee who is the manager). If the join were represented as a line from ManagerID to EmployeeID in the same copy of the table, it would select only rows where the ManagerID and EmployeeID were equal. Presumably, that doesn't happen (there are no employees who are their own managers, except possibly the president of the company.)

Likewise, if you have a table with 3 columns of type codes that are all related to the same code table, and the join lines were all drawn to that same copy of the code table, it would select only records where the type codes were all the same.

So you see, you really don't want what you think you want. The multiple copies of a table don't represent different tables. They show that relationships can go to different rows in the same table. In SQL, they become &quot;<tblname> AS <tblname>1&quot; clauses. Rick Sprague