Discussion Locked

Access Relationships

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

7 replies

by timwalsh

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.

by john_wills

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.

by john_wills

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.

by john_wills

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.

This discussion is closed to new replies.