Help with a "most recent of each" type of query?!

[Guest]

If I have two tables to keep track of members of a club and their children...
Members - includes the following fields: member_id, name, address, and phone
Children - includes the following fields: member_id, name, age
(These two tables are related via a "1 to many" relationship based on
member_id, )

Can I set up a query to find the oldest child of each member?

Thanks!!!!
Julie

 

[John Spencer (MVP)]

One query method:
SELECT A.[Name], A.Address, A.Phone, B.[Name], B.Age
FROM Table1 as A INNER JOIN Table2 as B
ON A.[MemberID] = B.MemberID
WHERE B.[Age] =
(SELECT Max(C.Age)
FROM Table2 as C
WHERE C.MemberID = A.MemberID)

Two query method:
Q1
SELECT MemberID, Max(Age) as MaxAge
FROM Table2

Q2 uses that saved query (Q1)

SELECT A.[Name], A.Address, A.Phone, B.[Name], B.Age
FROM (Table1 as A INNER JOIN Table2 as B
ON A.[MemberID] = B.MemberID )
Inner Join Q1 ON
Q1.MemberID = B.MemberID AND
Q1.MaxAge = B.Age

 

[Guest]

John,

Thank you a ton!!!! I had been struggling with that for WAY too long.

Thanks!
-Julie

One query method:
SELECT A.[Name], A.Address, A.Phone, B.[Name], B.Age
FROM Table1 as A INNER JOIN Table2 as B
ON A.[MemberID] = B.MemberID
WHERE B.[Age] =
(SELECT Max(C.Age)
FROM Table2 as C
WHERE C.MemberID = A.MemberID)

Two query method:
Q1
SELECT MemberID, Max(Age) as MaxAge
FROM Table2

Q2 uses that saved query (Q1)

SELECT A.[Name], A.Address, A.Phone, B.[Name], B.Age
FROM (Table1 as A INNER JOIN Table2 as B
ON A.[MemberID] = B.MemberID )
Inner Join Q1 ON
Q1.MemberID = B.MemberID AND
Q1.MaxAge = B.Age

If I have two tables to keep track of members of a club and their children...
Members - includes the following fields: member_id, name, address, and phone
Children - includes the following fields: member_id, name, age
(These two tables are related via a "1 to many" relationship based on
member_id, )

Can I set up a query to find the oldest child of each member?

Thanks!!!!
Julie

 

[Guest]

How would I do this if the member had no children? My example is complicated
by having several right joins to lookup tables. I tried changing the inner
join to the child table to a right join, but still was missing the records.
When I created a child record with the inner join, the results returned
correctly. So I think I must just be missing something in the complicated
joins.

Can you modify this example with the correct join to pull all member records
even if no child records exist for that member? Thanks so much?