Pulling the same field twice in a select statement 1

[Bubbalouie]

I do some computer work for a charitable foundation. Mostly networking and computer repair. However, I once made the mistake of writing a simple SQL select statement for their Access 2000 Donor database and they are now convinced I'm a whiz...

They have asked me to do something for them now with the DB that I just can't quite wrap my head around though I've agreed to try. Their Donor database has a bunch of tables. One of them is a table called Individuals where everyone, including donors, volunteers and anyone else they want to keep track of is entered. A lot of these people in this table have some type of relationship with each other; family member, work at the same company or foundation, etc. Some of these people may have several such relationships.

They would like to be able to record that relationship. As I kind of alluded to earlier, database design and SQL really ain't my schtick...

I was thinking I would just create a new table called Relationships and use the primary key for each person in the in the Indviduals table to record who was associated to who. I created a table like this:

Primary key (autonumber)
Indiv_ID (the person I am creating the relationship for; comes from the primary key for that person in the Individuals table)
Assoc_ID (the person I am creating the relationship with; comes from the primary key for that person in the Individuals table)
Assoc_Type (probably a lookup field, coworker, relative, etc. whatever they decide they want to track. right now it just a text field though)
Assoc_Note (a text field that will hold a note on the relationship)

That seems to me to be the way to do it. But I can't figure out how to write a select/update statement that matches the Indiv_ID from the Individuals table so I can get their first name and last name AND at the same time get the Assoc_ID so I can get their first name and last name from the Individuals table too. It's like I need two simultaneous select statements!

Like I said, this isn't my forte and I may be violating some fundamental law of database design. Any feedback, including "Fool! You can't do that!" will be greatly appreciated.

Thanks In Advance

 

[MajP]

Looks like you did it correct. One thing to keep in mind you have to create the relation both ways
So if A and B are coworkers you will need
A B Coworker
B A Coworker
C D Parent (C is parent of D)
D C Child (D is child of C)

SELECT A.FirstName, 
       A.LastName, 
       tblRelations.assocType, 
       B.FirstName, 
       B.LastName, 
       B.EmployeeID
FROM 
   (Employees AS A INNER JOIN tblRelations ON
   A.EmployeeID=tblRelations.individualID) INNER JOIN
   Employees AS B ON tblRelations.assocID=B.EmployeeID
WHERE 
   B.EmployeeID)<>[A].[EmployeeID]

 

[Bubbalouie]

right on the money!

Thanks!