Return records from one table for both parent and children?

[tunafish62]

If you have a People table and a relationship table relating people
together, by assigning one person as the main (i.e. parent) and all
others as the related (i.e. children), how do you construct a query
that will return the People records of both the parent and all the
children based on the parent ID? I am thinking it is a simple UNION
query but am getting stuck on the syntax.

For example:
tblPeople.PK_ID
tblPeople.FirstName
tblPeople.MiddleName
tblPeople.LastName
tblPeople.Birthday

tblPeopleGroups.PK_ID
tblPeopleGroups.MainPersonID (one to...)
tblPeopleGroups.RelatedPersonID (many relationship)

Given a known MainPersonID, I want to a query that returns all fields
in tblPeople of the Main Person plus all Related Persons -- if there
are no related person, the query should return just the tblPeople
record of the Main Person.

P.S. This is not the real issue but simple analogy of the problem I am
trying to solve -- just trying the baby steps method!

Thanks!

 

[Guest]

In your query in design view add the tblPeople left joined to tblPeople left
joined to tblPeopleGroups.MainPersonID.

Add the tblPeople table again to the query (Access adds a suffix like add
the tblPeople_1). Left join tblPeopleGroups.RelatedPersonID to
tblPeople_1.RelatedPersonID.

For output fields use tblPeople.MainPersonID and tblPeople_1.RelatedPersonID.

Hope this helps.

 

[tunafish]

Karl,
I tried the left joins and it sort of gets me what I want but not
exactly as it actually returns both main and related persons data in
one record. For example, if John A. Smith has related persons Mary X.
Jones and Jane P. Smith-Jones, the query is returning the records:

1, John, A., Smith, 1/30/50, 2, Mary, X., Jones, 2/1/47
1, John, A., Smith, 1/30/50, 3, Jane, P., Smith-Jones, 12/25/72
1, John, A., Smith, 1/30/50, <NULL>,<NULL>,<NULL>,<NULL>,<NULL>

I'd like a query to return actual tblPeople records:
1, John, A., Smith, 1/30/50
2, Mary, X., Jones, 2/1/47
3, Jane, P., Smith-Jones, 12/25/72

That is why I thought a UNION query might be the ticket -- somehow
union a subquery that gets John with one that gets Mary and Jane...Or
am I not understanding what you mean by how to define the output
fields?

Thanks!

 

[Gary Walter]

If you have a People table and a relationship table relating people
together, by assigning one person as the main (i.e. parent) and all
others as the related (i.e. children), how do you construct a query
that will return the People records of both the parent and all the
children based on the parent ID? I am thinking it is a simple UNION
query but am getting stuck on the syntax.

For example:
tblPeople.PK_ID
tblPeople.FirstName
tblPeople.MiddleName
tblPeople.LastName
tblPeople.Birthday

tblPeopleGroups.PK_ID
tblPeopleGroups.MainPersonID (one to...)
tblPeopleGroups.RelatedPersonID (many relationship)

Given a known MainPersonID, I want to a query that returns all fields
in tblPeople of the Main Person plus all Related Persons -- if there
are no related person, the query should return just the tblPeople
record of the Main Person.

P.S. This is not the real issue but simple analogy of the problem I am
trying to solve -- just trying the baby steps method!

PMFBI

I may be off base here, but (for me)...
queries provide data for forms and
reports.....

For a form or report,

the "main object" (form/report)
shows the main person

and the "sub object" (subform/subreport)
shows the related people

apologies if misunderstood...

 

[Guest]

In the tblPeopleGroups add each person as related to themselves.