need help with relationships

[Mugen2k2]

hi to all!

I need help in creating a relationship between one primary table and 6 secundary tables! once I made the 6 relationships and I open the tblproc (my primary table), access asks me to choose the table between the six! the problem is, that he has to take the informations from all six and not only one table!!! pls, help me!!! hope to hear from you as soon as possible

hopefully


daniel

email:daniel.zanetti@freenet.de

 

[ColinEssex]

Have you got an AutoNumber as an ID field in the primary table? and then ID fields in the others to link on?

Col

 

[Mugen2k2]

hi,

yes! cause I have one table with 6 process and 6 tables with more operations. each process and each operation has an own code (or id) and I have to make a relationship between this six tables about the operations and the table with the six process in my primary table! cause each process has an own table of operations.
but I can't make a relationship between 6 and 1 table!

many thx for your help and I hope to hear from you soon again

daniel

email:daniel.zanetti@freenet.de

 

[The_Doc_Man]

mugen, you most certainly CAN have six different tables with a relationship to a single table. Even on the same key. I've got a case like that here at the office, total of seven relationships from seven different tables, each based on the same exact field in the "parent" table.

If this is giving you trouble, then I have to consider that you asked the question unclearly or else you are confusing yourself over what a relationship means.

Please reconsider your question and let us know where the real problem lies. We like helping people but sometimes we need to help you ask a better question first before we can tell you what you REALLY wanted to know.

 

[Mugen2k2]

@ The_Doc_Man

hi,

pls, could you explain me how you fixed the case at your office?
many thx

daniel

 

[Cosmos75]

Like Windows Explorer

"I need help in creating a relationship between one primary table and 6 secundary tables! once I made the 6 relationships and I open the tblproc (my primary table), access asks me to choose the table between the six!"

The reason it asks you this is because ACCESS want to show you related records for the Primary Key [Primary Table] from one of the 6 Secondary Tables that are related to the Primary Table. Like how you use Windows Explorer to view the Subfolders of a Folder

BUT, it can only show related records from ONE secondary table.

You most certainly can create more then one Secondary with a relationship to the Primary Table.

Hope this helps!

 

[vangogh228]

Mugen:

How about listing the tables and the fields here? That would be a huge help.

 

[The_Doc_Man]

A thought that occurs to me is that you are trying to do too much at once.

Each relationship you define is between two tables. PERIOD.

There is such a thing as having collateral relationships. But when you go to define a relationship, you can only define one at a time.

I.e. in the relationships pane,

add the primary table.

add the secondary table.

Drag the linking field from the secondary table to the corresponding field in the primary table.

Define the relationship characteristics.

Now add another secondary table. Do it all again, one item at a time for your other 5 tables.

When it is done, you have 6 relationships, each in whatever form you chose, each between one primary table and one secondary table, each an INDEPENDENT relationship.

Now, since you are having trouble with this concept, a word of caution:

There is no DIRECT relationship between your secondary tables even after they are linked to the primary table. But if you write a query that relates TWO of your secondary tables to the primary table, they will enjoy a secondary relationship. That is, both JOINs will work correctly. But perhaps unexpectedly as well.

They will also probably form a limited permutation set in which all possible combinations of the two secondary tables will apply for each record from the primary table. So depending on the cardinality of each relation (average number of records returned for a single value), you might get a number of records equal to the product of the cardinalities.

I.e. imagine a three-way join between table PrimaryTable and tables Secondary1 and Secondary2. If the population of the secondary tables is such that you expect about 10 records returned for each of secondary1 and secondary2, you will get 100 records in the JOIN set, on average.

So be careful when joining multiple tables together. It is a pitfall associated with having multiple relationships linked to a common value. And God forbid that you should create a JOIN based on two secondary tables without including the primary table to whittle down the result set.