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

In that case 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 And Y.MRN Not in(Select Z.MRN
>From qMembershipSelect As Z Where [THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC

I didn't try it, but I hope it worked.

--
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: 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)
  • 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: Problems after combining 2 queries
    ... FROM MEMBERSHIP WHERE [MemName] like 'Jones*' AS Y ... >FROM qMembershipSelect AS Y ... >My dilema now is that I will use this same string in ADO.NET for an ASP.NET ...
    (microsoft.public.access.queries)
  • RE: More help with A Not so simple - Max Function
    ... > FROM qMembershipSelect As Y ... >> If the member DOES NOT have a blank Thru-Dt ... >>> SELECT DISTINCT MRN, MemNAME AS Member, ... >>> Thank you and Good luck ...
    (microsoft.public.access.queries)