More help with A Not so simple - Max Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Thank you! This proves that SQL is in a class by itself.
An "IF" condition handled totally by sets.


Ofer said:
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 said:
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 said:
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



:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problems after combining 2 queries 7
2nd layer of filtering? 6
something about stored procedure not returning the result 5
why code for datagrid doesn't work 1
DateTime parameter keeps seeing string even though not passed 5
MAX function is not picking right date in ms access 1
Please Help... KRISH 4
Using a Form to create Reports 1

Back
Top