Select multiple rows from a table

[Ambre]

Hi,



In my 'Family Tree' database I have a table (PersonTBL) that has columns PersonID (Autonumber), Mother ID (Long), FatherID (Long).
Every person in the database has a record in this table. I want to build a query based on this table to show a person's family. This query will be called by a button on the PersonFRM.

So, whatever person is being displayed on the PersonFRM the FamilyQRY should select from the PersonTBL: Mother (using MotherID), Father (using FatherID) and any children of these parents (using MotherID AND FatherID). If either of these parents re-marry it is important that the family only includes brothers/sisters from the same parents as indicated on the original PersonFRM.

Sorry if this sounds very simple, but I am new to Access and cannot get a 'simple' query to to do this.

Any assistance would be very much appreciated.

Thanks,
Ambre

[CJ_London]

You haven’t said what you want your result to look like but try this

Join person table to itself (aliased as person_1) on mother and father ids

set criteria on the person table to the person you want.

you will need to join the aliased table to further aliased person table to get the parent and sibling names if that is what you require

[xps35]

There are (at least) two issue here. First you need to find the proper criteria to select persons. Second is you want to show the role of a person. To solve this I think you need a UNION query. Something like (NOT tested):

SELECT *,  "Person" AS Role FROM PersonTBL WHERE PersonID = Forms!PersonFRM!PersonID
UNION 
SELECT *,  "Mother" AS Role FROM PersonTBL WHERE PersonID = Forms!PersonFRM!MotherID
UNION 
SELECT *, "Father" AS Role FROM PersonTBL WHERE PersonID = Forms!PersonFRM!FatherID
UNION 
SELECT *, "Brother/Sister" AS Role FROM PersonTBL WHERE MotherID = Forms!PersonFRM!MotherID AND FatherID = Forms!PersonFRM!FatherID AND PersonID <> Forms!PersonFRM!PersonID

[orange]

You may get some additional ideas and insight for a genealogy database from this Richard Rost Youtube video.

[Ambre]

Hi,

In my 'Family Tree' database I have a table (PersonTBL) that has columns PersonID (Autonumber), Mother ID (Long), FatherID (Long).
Every person in the database has a record in this table. I want to build a query based on this table to show a person's family. This query will be called by a button on the PersonFRM.

So, whatever person is being displayed on the PersonFRM the FamilyQRY should select from the PersonTBL: Mother (using MotherID), Father (using FatherID) and any children of these parents (using MotherID AND FatherID). If either of these parents re-marry it is important that the family only includes brothers/sisters from the same parents as indicated on the original PersonFRM.

Sorry if this sounds very simple, but I am new to Access and cannot get a 'simple' query to to do this.

Any assistance would be very much appreciated.

Thanks,
Ambre

Many thanks for the reply.

I did not know you could join a table to itself! Your suggestion has led me to a solution and INCREASED my knowledge and understanding of Access.

[Ambre]

There are (at least) two issue here. First you need to find the proper criteria to select persons. Second is you want to show the role of a person. To solve this I think you need a UNION query. Something like (NOT tested):

SELECT *,  "Person" AS Role FROM PersonTBL WHERE PersonID = Forms!PersonFRM!PersonID
UNION 
SELECT *,  "Mother" AS Role FROM PersonTBL WHERE PersonID = Forms!PersonFRM!MotherID
UNION 
SELECT *, "Father" AS Role FROM PersonTBL WHERE PersonID = Forms!PersonFRM!FatherID
UNION 
SELECT *, "Brother/Sister" AS Role FROM PersonTBL WHERE MotherID = Forms!PersonFRM!MotherID AND FatherID = Forms!PersonFRM!FatherID AND PersonID <> Forms!PersonFRM!PersonID

Many thanks for your reply.

CJ_London's suggestion and yours has led me to a working solution.

[Ambre]

You may get some additional ideas and insight for a genealogy database from this Richard Rost Youtube video.

I will check this out. Thank you.