Access 2003: Link field in table to key field in same table

[Guest]

I am new to Access and having a difficult time. I have built some tables and
would like to create queries and reports. One of my tables has a key field
which, for the sake of simplicity, think of as a unique person_id. Two of
the fields related to this key field, think of as fields containing unique
person_ids for the parents. If I wish to build a query/report displaying the
names of the parents, not their unique person_ids, how do I do this?

e.g., key-field = person_id
other fields include name of person related to person_id and unique
person_ids for the parents.

I wish my report or query to list, say, the name of the person and the names
of his parents.

To the very nice and very intelligent people who help people like me, thank
you, thank you, thank you.

Sincerely, Marnie Shaw

 

[Guest]

Hi Marnie,

Put the two tables you've created in the querywindow. Make sure the tables
are joined on the appropriate fields (if you've done this in the
relationshipwindow they will appear autimatically). Now instead of adding the
ID-fields to the querygrid add the name and other fields you would like to
see. This will produce a recordoverview with just the fields you would like
to see.

hth

 

[Guest]

Hi Maurice. The problem is that, in this case, there is only one table
involved, not two. The two 'parent' fields in one record contain values
which point to the key field of two other records - in the same table.

key field = person1 name=Marnie father=person2 mother=person3
key field = person2 name=Melvin father=person4 mother=person5
key field = person3 name=Edith father=person6 mother=person7
key field = person4 name=John father=... mother=...
key field = person5 name=Ane father=... mother=...
....

Looking up person1, I can easily display (in a query or report) the name of
Marnie. What I cannot figure out how to do is how to display the names of
Melvin and Edith for person1. That is, I do not want to display 'person2'
and 'person3'; I want to display Melvin and Edith.

Maurice or anyone else, any solutions for me?

Sincerely, Marnie Shaw

 

[John Spencer]

Add your table to the query three times. You will then see tables with
names like Person, Person_1, and Person_2

Join Father in Person to KeyField in Person_1
Join Mother in Person to Keyfield in Person_2

Get the Person Name from Person
Get the Person Name from Person_1 as the Father's Name
Get the Person Name from Person_2 as the Mother's Name

Note: You can alias the Person_1 Table as FatherData by showing
properties and clicking on the heading of person_1 table and editing the
alias that shows. Do this before joining the tables and selecting the
fields.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thank you, John. That sent me off on the right track, with a bit of
sidetracking (e.g., alias is apparently used for ADP projects). Once I found
some information on self-join, it was similar to what you described for me.
So, I have that particular problem solved anyway - with lots more where that
one came from though. I know the things I want to do and am relatively sure
that I can do them, but the how is far from straightforward!

Sincerely, Marnie Shaw (CALGARY, Alberta, Canada)