Showing only one record from many-to-one relationship

[JohnM77 via AccessMonster.com]

Consider the following scenario:

Two tables:
tParent
tParent.ID (Autonumber)
tChild
tChild.ID (Autonumber)
tParent.ID (Many-to-One relationship to tParent.ID)

tParent Records:
ID
1
2
3
4
5
6

tChild Records:
tChild.ID tParent.ID
1 1
2 1
3 2
4 3
5 4

I wish to create a Query that would show the following results:
tChild.ID tParent.ID
1 1
3 2
4 3
5 4
6 Null

where only one record is shown for each tParent.ID, and in cases where there
is more than one child record for a given tParent.ID (as is the case for
tParent.ID = 1), the query only includes the record for one of the tChild.ID
values (in this case, tChild.ID = 1).

Basically, I have a situation that is the same as this hypothetical scenario,
in which the vast majority of tParent.ID records have only one (if any)
reference in tChild. My query must only display each tParent record one time,
but it needs to show a corresponding tChild.ID for reference (accepting that,
on the rare occasion, some tChildID values will be omitted when there are
multiple tChild.ID's for a given tParent.ID).

Is there a simple way to acheive this result? Any thoughts/suggestions are
greatly appreciated.

Thanks,
John

 

[John Spencer]

SELECT TParent.ID, First(tChild.ChildID) as Child
FROM tParent LEFT JOIN TChild
ON TParent.Id = TChild.ParentID
GROUP BY TParent.ID

In Query Design view
== Add both tables
== Drag from Parent.ParentID to Child.ParentID
== Click on the join line and select show all from Parent and only matches
from child
== Add the Parent.ParentID and the Child.ChildID
== Select View: Totals from the menu
== Change Group by under Child.ChildID to First (or Max or Min or Last)

Run the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JohnM77 via AccessMonster.com]

Thanks for your reply, John. That works just as I was hoping.

I've seen some queries that result in parent records having a '+' symbol,
which the user can click to cause the child records to cascade below the
parent. Is there a way to cause my query above to show each tParent record a
single time, but having the '+' symbol on it to indicate multiple child
records?

Thanks!
-John

SELECT TParent.ID, First(tChild.ChildID) as Child
FROM tParent LEFT JOIN TChild
ON TParent.Id = TChild.ParentID
GROUP BY TParent.ID

In Query Design view
== Add both tables
== Drag from Parent.ParentID to Child.ParentID
== Click on the join line and select show all from Parent and only matches
from child
== Add the Parent.ParentID and the Child.ChildID
== Select View: Totals from the menu
== Change Group by under Child.ChildID to First (or Max or Min or Last)

Run the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Consider the following scenario:

[quoted text clipped - 47 lines]

Thanks,
John

 

[John Spencer]

Not that I am aware of. WHY bother - the query is either going to have one
child or no child displayed in the field?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks for your reply, John. That works just as I was hoping.

I've seen some queries that result in parent records having a '+' symbol,
which the user can click to cause the child records to cascade below the
parent. Is there a way to cause my query above to show each tParent record a
single time, but having the '+' symbol on it to indicate multiple child
records?

Thanks!
-John

SELECT TParent.ID, First(tChild.ChildID) as Child
FROM tParent LEFT JOIN TChild
ON TParent.Id = TChild.ParentID
GROUP BY TParent.ID

In Query Design view
== Add both tables
== Drag from Parent.ParentID to Child.ParentID
== Click on the join line and select show all from Parent and only matches
from child
== Add the Parent.ParentID and the Child.ChildID
== Select View: Totals from the menu
== Change Group by under Child.ChildID to First (or Max or Min or Last)

Run the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Consider the following scenario:

[quoted text clipped - 47 lines]

Thanks,
John

 

[JohnM77 via AccessMonster.com]

I was thinking, instead of only showing one Child, if Access would allow all
Child records to be grouped in a collection (probably wrong terminology) with
the corresponding parent, that would be an even better solution. It's not
critical, though. I just thought it would be nifty.

Thanks, again!

-John

Not that I am aware of. WHY bother - the query is either going to have one
child or no child displayed in the field?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks for your reply, John. That works just as I was hoping.

[quoted text clipped - 33 lines]