How to find all related records in one table from a "lineage" of related records in another table

[pharmacologist]

I'll try to explain better if this doesn't make sense. I have records in a table we'll call "Members" with a field called "ParentID" which indicates the parent of the record (which exists in the same table). Those parents also have a ParentID listed in their and so on so there are different lineages of related records. I have another table that relates to the primary key of the Members table and I would like to be able to pull all the records from that second table that are associated with any specified primary key of Members and all of the parent records and/or all of the children records (i.e. Member records for which their ParentID matches to follow the lineage downward). I hope that makes sense. Basically it's like a family tree and I want to be able to get all the records from one table that are related to a primary key and all it's relatives in another table. I'm pretty novice with SQL commands and can't think of how this would be possible.

Thanks for all help!

[CJ_London]

Not really following the description of what you have and what you want. Always better to provide some example data to illustrate what you have, what you want to do and the outcome required.

Best guess is what you are taking about is a recursive query. These exist in sql server but not ace or jet (the default access dB). It can be done but you will need to use vba

[pharmacologist]

OK, here's a basic design of the tables

Members			DesiredTable
ID	Parent ID		ID	MemberID	DesiredField
1	-		1	1	A
2	1		2	2	B
3	2		3	2	C
4	3		4	3	D
5	2		5	4	E
6	5		6	5	F
			7	5	G
			8	6	H

Here's what I'm looking to do.

1) Select all records from table DesiredTable where MemberID matches Members.ID = 5 and all family members within its lineage, so it would give me:

Results
DesiredTable.ID	MemberID	DesiredField
1	1	A
2	2	B
3	2	C
6	5	F
7	5	G
8	6	H

2) Select all records from table DesiredTable where MemberID matches Members.ID = 3 and all family members above its lineage (i.e. parents, grandparents etc), so it would give me:

Results
DesiredTable.ID	MemberID	DesiredField
1	1	A
2	2	B
3	2	C
4	3	D

3) Select all records from table DesiredTable where MemberID matches Members.ID = 3 and all family members below its lineage (i.e. children, grandchildren etc), so it would give me:

Results
DesiredTable.ID	MemberID	DesiredField
4	3	D
5	4	E

I'm very comfortable with VBA and everything I'm doing with this is already coded in VBA so that's no problem and in fact the type of solution I'm looking for.

[CJ_London]

you will definitely need to use a recursive function. suggest google 'vba recursive function' or 'vba tree functions' to get some ideas

you can also search on this and other forums for examples that might more closely meet your needs