Relationship Issue

[keith701a]

I have a personnel table of employees. That table includes name, phone, email, etc.

That table has two relationships with table "request_information." The name field in personnel is linked to both "assigned" and "requester."

I'm attempting to make a query that pulls the name and contact information of the assigned person based on a request number. Instead of doing that the query is retrieving the contact information from the requester field, even though that field is not in the query.

Is there a way to force access to query the contact information on assigned?

 

[pbaldy]

You need to add the personnel table to the query twice. Access will alias one of them (personnel_1 or something like that, which you can change). Join each of your two fields to a different instance of the table.

By the way, it's a mistake to use the name field as the key. Sooner or later you'll have two "John Smith". I'd use an ID field.

 

[RuralGuy]

The Primary Key of the Personnel Table should be an AutoNumber. Names can change.

 

[keith701a]

You need to add the personnel table to the query twice. Access will alias one of them (personnel_1 or something like that, which you can change). Join each of your two fields to a different instance of the table.

By the way, it's a mistake to use the name field as the key. Sooner or later you'll have two "John Smith". I'd use an ID field.

I do have an ID field, but it's just a random number and not something people would know to query by.

 

[keith701a]

The Primary Key of the Personnel Table should be an AutoNumber. Names can change.

It is, but no user is going to know what the primary key is. There are hundreds of employees in the table.

 

[pbaldy]

They don't have to query by it, but it should be the joining field. Not only can you have duplicate names, but as Allan points out, names can change.

 

[keith701a]

They don't have to query by it, but it should be the joining field. Not only can you have duplicate names, but as Allan points out, names can change.

I'm looking at the relationship now. I do not have two personnel tables; one table is showing both relationships. Should I remove a relationship and add a second table and link it that way?

When doing so, create the relationship with the primary key and not the name?

 

[pbaldy]

The same thing I mentioned for the query would be done in the relationship window. You have one table, but you add it twice, and join the different fields to different instances of it. There would be two joins from your "request_information." table, one to each instance of the personnel table.