ACCESS FEED: RE: [MS_AccessPros] Most Recent Record

Kamis, 22 September 2011

RE: [MS_AccessPros] Most Recent Record

Thanks Graham,

I need some records for members without a
terminated date so I am unable to find records where terminated "Is Not Null".

As soon as I saw "SelectMax" I knew that I had
used it years ago. It provides the records that I need.

Many thanks,

Regards,

Robin

At 22/09/2011 09:38 PM, you wrote:
>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
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>