Max function

[ocp&mcsd]

Hi all,
How are you?

I have two tables, Groups and Members.
Each Group has members.
I want a query to return the oldest in each group, as:
MemberName; GroupID ; Age
Jane ; 1 ; 52
John ; 2 ; 51

How can I do that.
Kindly, find attached the DB.

 

[Jon K]

You can first build a query to get the Max Age of each GroupID. Then link the query back to the table in a second query.

qryOne:-
SELECT Members.GroupID, Max(Members.Age) AS MaxOfAge
FROM Members
GROUP BY Members.GroupID;

qryTwo:-
SELECT Members.MemberID, Members.MemberName, Members.GroupID, Members.Age
FROM Members INNER JOIN qryOne ON (Members.Age = qryOne.MaxOfAge) AND (Members.GroupID = qryOne.GroupID);

Run the second query.
.

 

[ocp&mcsd]

Thanks alot my dear.