General discussion

Locked

Access Relationships

By jgreer ·
I have two fields that are designed alike in two different tables and need to create a relationship between them. But every time I try to create the relationship, it says the relationship is indeterminate. How can I resolve this? Both are numbers and long integer. I cannot see anywhere else that I can create a relationship on these tables because of how the data needs to attach to its parent record.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access Relationships

by timwalsh In reply to Access Relationships

When you create a relationship between two tables you are saying that xyz field in table 1 is the SAME as the xyz field in table 2. This IDENTICAL field is what relates the 2 tables to each other. ALL parameters for the fields must be identical including the name. The error you are getting is the database essentially saying: "I can't determine how these two tables relate to each other because they don't have anything in common."

A simplistic example: Employee database

Table 1 fields: Employee ID, Employee Name, Home Address, Montly Salary

Table 2 fields: Job Title, Yearly Salary, Work Location
Both the Monthly Salary filed and the Yearly Salary field are designed alike but there is obviously nothing in common between the 2 tables (yes they are related indirectly, but the database has no way of determining this). Thus no definitive relationship can be determined.

Hope this helps.

Collapse -

Access Relationships

by jgreer In reply to Access Relationships

Poster rated this answer

Collapse -

Access Relationships

by john_wills In reply to Access Relationships

I am not entirely clear on how the kind of relationship is determined, but if one of the variables is the primary key of its table and the other is not a primary key, the one that is not a primary key becomes a foreign key and the relationship becomes one(on the primary side)-to-many(on the foreign side). You may find it helpful to determine part of the relationship by using the Join Type button, but that will not change the relationship type.

Collapse -

Access Relationships

by jgreer In reply to Access Relationships

Poster rated this answer

Collapse -

Access Relationships

by john_wills In reply to Access Relationships

An additional point. Suppose I have tables with some common key parts. I might have SSN for the client, SSN and referral date for an occasion when the client is referred to our services, and SSN, referral date and move-in date for a sojourn in our facilities. Using the relationship edit I can join these tables by the key parts. The relationships will come out one-to-many.

Collapse -

Access Relationships

by john_wills In reply to Access Relationships

An additional point. Suppose I have tables with some common key parts. I might have SSN for the client, SSN and referral date for an occasion when the client is referred to our services, and SSN, referral date and move-in date for a sojourn in our facilities. Using the relationship edit I can join these tables by the key parts. The relationships will come out one-to-many.

Collapse -

Access Relationships

by jgreer In reply to Access Relationships

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums