Using subform to display and edit relationships

[joshearl]

I'm relatively new to Access but have some experience working with relational database structures. I'm completely stuck on what seems like a simple task.



Here's the scenario:

I have two tables, People and Relationships. The People table has a primary key, personID, as well as first and last name fields.

The Relationships table has two foreign key fields, personID1 and personID2, as well as a relationshiptype field.

As an example of what I want to accomplish, say we have a person who is a student, Lisa Smith. That person has a relationship with another person, Jerry Smith, who is her father. She also has a relationship with Michele Brian, who is her teacher.

I'd like my subform to show the following:

Jerry Smith Parent
Michele Brian Instructor

Instead, what I'm getting is:

Lisa Smith Parent
Lisa Smith Instructor

I know exactly what's happening--the form is displaying the name based on the personID of the record that's currently open, rather than the personID2 of the relationships table (the relate-ee, if you will).

This seems like something that I should be able to do using the form-building tools, rather than having to write a custom query.

I am somewhat proficient in SQL, but I'm not sure how to write this one in the context of using it in a subform.

Could someone point me in the right direction?

Thanks,
Josh

[Rainlover]

Need to see your Table design to be able to help.

[joshearl]

OK, what's the best way to do that?

[Rainlover]

Table Name

Primary Key (Data Type)
Foregin Key (Data Type)
Other Fields (Data Type) etc

Then

Other Tables

Explain Relationships etc

Or Post a copy of your Database in 2003 but not 2007.

[joshearl]

Access won't let me save the database in 2003 format, so I'll just have to make do with screen shots.

These are the two tables I'm trying to work with. The relationships.personID1 and relationships.personID2 fields are the foreign keys that relate back to the people.personID field in the person table.

The personID1 field's value would be whatever record I currently have open. I'm trying to take the people.personID value of the open record, find all relationships where people.personID matches relationships.personID1, and then display the first and last names for all values in relationships.personID2.

Does that make sense?

Thanks for the help,
Josh

[Rainlover]

You only need ONE foregin key in the second table.

Then show all records that the Primary key matches those in the second table.