Query to exclude criteria assistance

[Michael Abrams]

I have tried many variations on this query, but cannot obtain the desired result.
The following gives me what I originally wanted - all records matching the criteria.

Code: Select all

SELECT ISSUES.RECORDNUM, ISSUES.MEDICAL_GROUP, ISSUES.HMO, ISSUES.SPECIFIC_ISSUE, ISSUES.MEMBER_LAST_NAME, ISSUES.MEMBER_FIRST_NAME, ISSUES.DOB, ISSUES.MEMBER_IDNUM, ISSUES.MEMBER_CODE, ISSUES.MRN, ISSUES.OPENING_NOTES, ISSUES.HMO_NOTIFIED1
FROM ISSUES
WHERE (((ISSUES.DATE_ISSUE_OPENED)<#2/11/2010#))
GROUP BY ISSUES.RECORDNUM, ISSUES.MEDICAL_GROUP, ISSUES.HMO, ISSUES.SPECIFIC_ISSUE, ISSUES.MEMBER_LAST_NAME, ISSUES.MEMBER_FIRST_NAME, ISSUES.DOB, ISSUES.MEMBER_IDNUM, ISSUES.MEMBER_CODE, ISSUES.MRN, ISSUES.OPENING_NOTES, ISSUES.HMO_NOTIFIED1, ISSUES.DATE_RESOLVED
HAVING (((ISSUES.DATE_RESOLVED) Is Null))
ORDER BY ISSUES.MEDICAL_GROUP, ISSUES.HMO;

What I would like it to do, is exclude records where
HMO = AE
and
Specific_Issue = Closed Practice

Thank you very much for your help.

Michael Abrams

[Wendell]

Try this change to the HAVING and ORDER BY lines:

Code: Select all

HAVING ISSUES.DATE_RESOLVED Is Null AND ISSUES.HMO = "AE" AND ISSUES.SPECIFIC_ISSUE = "Closed Practice"
ORDER BY ISSUES.MEDICAL_GROUP;

Note that I've taken the parentheses out to make it a bit more readable - Access will put them back in if you switch to the query grid view. Also note that there is no point in sorting on the HMO field since you have restricted it in the HAVING clause.

[HansV]

If you want to exclude records with ISSUES.HMO = "AE" AND ISSUES.SPECIFIC_ISSUE = "Closed Practice", change Wendell's suggestion to

HAVING ISSUES.DATE_RESOLVED Is Null AND Not (ISSUES.HMO = "AE" AND ISSUES.SPECIFIC_ISSUE = "Closed Practice")
ORDER BY ISSUES.MEDICAL_GROUP;

[Wendell]

Sorry that's what I get for creating air-code in a hurry. I spaced the fact that you wanted to exclude them. And you want to keep the ORDER BY field ISSUES.HMO in that case.

[Michael Abrams]

Hi Guys,

Of course it works perfectly, and no sooner than I showed it to my boss, I was asked to add one more exclusion.
I will show you what I tried, but it doesn't produce the correct results. There is a third "and":
HMO = AE
and
Specific_Issue = Closed Practice
and
HMO_Notified1 is not null

I have tried:

HAVING ISSUES.DATE_RESOLVED Is Null AND Not (ISSUES.HMO = "AE" AND ISSUES.SPECIFIC_ISSUE = "Closed Practice") And Is Null ([HMO_Notified1])

HAVING ISSUES.DATE_RESOLVED Is Null AND Not (ISSUES.HMO = "AE" AND ISSUES.SPECIFIC_ISSUE = "Closed Practice") And ([HMO_Notified1]) = " "

I am sorry that I didn't include this in the OP.

Thanks again.

Michael

[HansV]

Try

HAVING ISSUES.DATE_RESOLVED Is Null AND Not (ISSUES.HMO = "AE" AND ISSUES.SPECIFIC_ISSUE = "Closed Practice" AND ISSUES.HMO_Notified1 Is Not Null)

Note that the condition HMO_Notified1 is not null is within the parentheses after Not.

[Michael Abrams]

Try

HAVING ISSUES.DATE_RESOLVED Is Null AND Not (ISSUES.HMO = "AE" AND ISSUES.SPECIFIC_ISSUE = "Closed Practice" AND ISSUES.HMO_Notified1 Is Not Null)

Note that the condition HMO_Notified1 is not null is within the parentheses after Not.

Works great ! Thank you very much.

Michael