RE: More help with A Not so simple - Max Function

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thank you! This proves that SQL is in a class by itself.
An "IF" condition handled totally by sets.


"Ofer" wrote:

> Correction
>
> SELECT DISTINCT MRN, MemNAME AS Member,
> DOB,
> SEX,
> SSN,
> GROUP,
> SGR,
> [FROM-DT],
> [THRU-DT]
> FROM qMembershipSelect As Y
> GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
> HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
> qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
> From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
> ORDER BY MemNAME, [FROM-DT] DESC
>
>
> --
> Please respond to the group if your question been answered or not, so other
> can refer to it.
> Thank you and Good luck
>
>
>
> "jonefer" wrote:
>
> > That was Excellent!
> > Forgot to add - that if that member HAS a blank Thru-Dt
> > That's the only row I want to show
> >
> > If the member DOES NOT have a blank Thru-Dt
> > Show the latest one
> >
> > Thanks again so much.
> >
> > "Ofer" wrote:
> >
> > > Try this
> > >
> > > SELECT DISTINCT MRN, MemNAME AS Member,
> > > DOB,
> > > SEX,
> > > SSN,
> > > GROUP,
> > > SGR,
> > > [FROM-DT],
> > > [THRU-DT]
> > > FROM qMembershipSelect As Y
> > > GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
> > > HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
> > > qMembershipSelect As X Where X.MRN = Y.MRN)
> > > ORDER BY MemNAME, [FROM-DT] DESC
> > >
> > > --
> > > Please respond to the group if your question been answered or not, so other
> > > can refer to it.
> > > Thank you and Good luck
> > >
> > >
> > >
> > > "jonefer" wrote:
> > >
> > > > In reference to the recent post "Need help with a Max function" - I'm glad
> > > > you confirmed that that works.
> > > >
> > > > That is how I intended to run a MAX date query (but I'm not having any such
> > > > luck)
> > > >
> > > > (Dealing with Membership) In addition to finding the LATEST date of a field,
> > > > I also want to find any NULL field for that date So I want to find current
> > > > members either with no THRU-Date (the expiration date) OR Just the LATEST
> > > > Thru-date that member incurred.
> > > >
> > > > Here is my unsuccessful query:
> > > > SELECT DISTINCT MRN, MemNAME AS Member,
> > > > DOB,
> > > > SEX,
> > > > SSN,
> > > > GROUP,
> > > > SGR,
> > > > [FROM-DT],
> > > > [THRU-DT]
> > > > FROM qMembershipSelect
> > > > GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
> > > > HAVING ((([THRU-DT])=Max([Thru-Dt]))) OR ((([THRU-DT]) Is Null))
> > > > ORDER BY MemNAME, [FROM-DT] DESC;
> > > >
> > > > I will greatly appreciate and archive the solution to this! Thanks.
.

Relevant Pages

  • Re: 2nd layer of filtering?
    ... With DAO, I can rewrite a query using VBA and a querydef object. ... It basically says in psuedo SQL, Select the Member if the member has a ... >> FROM qMembershipSelect AS Y ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: More help with A Not so simple - Max Function
    ... SELECT DISTINCT MRN, MemNAME AS Member, ... FROM qMembershipSelect As Y ... > Forgot to add - that if that member HAS a blank Thru-Dt ...
    (microsoft.public.access.queries)
  • RE: More help with A Not so simple - Max Function
    ... SELECT DISTINCT MRN, MemNAME AS Member, ... FROM qMembershipSelect As Y ... > Forgot to add - that if that member HAS a blank Thru-Dt ...
    (microsoft.public.access.queries)
  • RE: More help with A Not so simple - Max Function
    ... Forgot to add - that if that member HAS a blank Thru-Dt ... > FROM qMembershipSelect As Y ... >> Here is my unsuccessful query: ...
    (microsoft.public.access.queries)