a self join and a many to many connection

[hugh0177]

Hi
I know very little about access and I am still learning. I am trying to create a database that has some complicated things I don't know how to work out.
I have a table of people and there contact information. They are professors and researchers in a field of science. I also have create another table with all of these peoples connections to each other.

For example:
person one worked with person 2, been a mentor to person 4, and cofounded an organization with person 6
in return
person 2 has worked with person 1, and been a student of person 11

(I hope this is not to vague, do you understand what I mean?)
And so the second table of connections is set up like a joining table in the middle of a many to many connection.

I want to be able to search for person 2, that persons contact information, all the people person 2 has ever been associated with, and bring up their contact information as well.

I have read about self joins and I thought I understood it, but I can't make them work. I thought understanding them would help me with this problem.

So what I want is some kind of loop back to the people table from the people table with a stop at the connections table in the middle. Maybe I am not setting up the primary and foreign keys correctly, how should I do this? Is there another way to do it?

Kelly

 

[Pat Hartman]

This isn't a self join because you have the relation table in the middle. A self join happens when you have a 1-to-many relationship as in employees to their immediate supervisor.

To create your query add the person table to the grid, then add the relation table, then add another instance of person table. Add a join line between the PersonID of the first table to the PersonIDA of the relation table. Then add a join line between PersonIDB of the relation table to the PersonID of the second instance of the person table. To avoid confusion in your recordset, I would alias all the names from the first table with an A prefix and all the names from the third table with a B prefix. That way, you'll be able to easily identify the source of the columns. So to start, select the LastName from the first person table and modify its Field cell to look like:
ALastName:LastName
When you select the LastName field from the third table, modify its Field cell to look like:
BLastName:LastName.

 

[hugh0177]

HI

thank you for your help so far
I hope we are talking about the same thing because I have only two tables I am working with and you were talking about three.
I have one table with people and their contact information, and then another table with all of those people's connections.
I do not have two seperate tables of people of two different types of people. (Nor can I create one).

Well, I tried what you told me to do and I can't get the queries to work. I have tried it with referential integrity turned on and off, and with the connection on the same join, and on two different joins (when you put the people table up there twice and there are two one to many connections).

Maybe I am querying worng. I am confused how to query this anyway to get what I want to pop out. So lets say I want to do a search on Jim Smith. I want him when he is in Person IDA and PersonIDB. IF I only search one of these columns, I'll only get half of all of his connections he has ever had. So how would I do that?
kelly

 

[Pat Hartman]

You always need to search two columns since a person could be on either side of a join.