Hi Robin
Try adding this WHERE clause:
WHERE DateJoinedThisClub=(Select Max(DateJoinedThisClub)
from tMembersClubHistory as X
where X.MemberID = tMembersClubHistory.MemberID)
The bit in the outer parentheses is known as a "subquery". It returns the
maximum (latest) value of DateJoinedThisClub for the MemberID matching the
current record.
I'm a little bit puzzled though – you should be able to get this by
filtering for (Terminated is Null). Perhaps you mean you want the club
previous to the current one? In which case, use this:
WHERE DateJoinedThisClub=(Select Max(DateJoinedThisClub)
from tMembersClubHistory as X
where X.MemberID = tMembersClubHistory.MemberID)
AND (Terminated is not Null)
Best wishes,
Graham
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
> Sent: Thursday, 22 September 2011 17:04
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Most Recent Record
>
>
> I have a membership database where members sometimes change clubs. I
> record their history in a table called "tMembersClubHistory" which
> includes a field "DateJoinedThisClub" and another "Terminated".
>
> I identify current members by the fact that they do not have a
> terminated date. I am now building a history of the posts held by
> members to include terminated members. In the case of a member who
> has a history of membership over several clubs I need to identify the
> most recent "DateJoinedThisClub".
>
> Here is the SQL:
>
> SELECT tMembersClubHistory.MemberID,
tMembersClubHistory.DateJoinedThisClub
> FROM tMembersClubHistory;
>
> What criteria do I need to find the most recent?
>
> Many thanks,
>
> Robin Chapple
Tidak ada komentar:
Posting Komentar