Assist with results

[GregRemaly]

The query I put together isn't returning the results I expected. Here is the query:

SELECT DISTINCT [Followup Grid].REGION, [Followup Grid].ENTITY, [Followup Grid].[CITY/STATE] AS LOCATION, [Followup Grid].[ISSUE ID], [Followup Grid].[FOLLOW UP STATUS]
FROM [Followup Grid]
WHERE ((([Followup Grid].[FOLLOW UP STATUS])="NOT DUE") AND (([Followup Grid].SYSTEM)="XYZ Health") AND (([Followup Grid].[REPORT STATUS])="Final report signed"))
GROUP BY [Followup Grid].REGION, [Followup Grid].ENTITY, [Followup Grid].[CITY/STATE], [Followup Grid].[ISSUE ID], [Followup Grid].[FOLLOW UP STATUS]
HAVING ((([Followup Grid].[FOLLOW UP STATUS])="NOT DUE"))
ORDER BY [Followup Grid].REGION, [Followup Grid].ENTITY, [Followup Grid].[CITY/STATE];

My expectation is to get a listing of all unique "NOT DUE" issues. Instead, I'm getting more. Issues "ISSUE ID" can have more than one action plan. I have two issues that have an action plan that have a "FOLLOW UP STATUS" of "NOT DUE" and "PAST DUE". If an issue has a "PAST DUE" status, then it is a past due issue and shouldn't be included.

How can I get the query to exclude "ISSUE IDs" that have a "FOLLOW UP STATUS" of "PAST DUE" and "NOT DUE"?

I appreciate any help with this.

[ranman256]

If you are not adding numbers, dont use the GROUP BY. TURN OFF SUMMATION.
Make sure if you dont want the field to show, then use WHERE, not GROUP.

[GregRemaly]

I am adding numbers in the next query I'm using to total up all of the NOT DUEs. My numbers are wrong because the there are two issues that have two follow up statuses, NOT DUE and PAST DUE.

[ranman256]

You have [FOLLOW UP STATUS] as a GROUP and a WHERE. You only need 1.

did you want:
"PAST DUE" or "NOT DUE"

[ssanfu]

I am confused...

You have

WHERE [Followup Grid].[FOLLOW UP STATUS] = "NOT DUE" AND 
[Followup  Grid].SYSTEM = "XYZ Health" AND 
[Followup Grid].[REPORT  STATUS] = "Final report signed"

But then you ask

How can I get the query to exclude "ISSUE IDs" that have a "FOLLOW UP STATUS" of "PAST DUE" and "NOT DUE"?

Does "Issue IDs" also have "PAST DUE" and "NOT DUE" as values?
A few examples of the data would help...


I would start out like ranman256 suggested:

SELECT DISTINCT [Followup Grid].REGION, [Followup Grid].ENTITY,  [Followup Grid].[CITY/STATE] AS LOCATION, [Followup Grid].[ISSUE ID],  [Followup Grid].[FOLLOW UP STATUS]
FROM [Followup Grid]
WHERE [Followup Grid].[FOLLOW UP STATUS] = "NOT DUE" AND [Followup  Grid].SYSTEM = "XYZ Health" AND [Followup Grid].[REPORT  STATUS] = "Final report signed"

When this first query returns the correct (expected) records, use the 1st query as the source for the 2nd query - the totals query.




A couple of other suggestions:
Do not use punctuation or special characters (underscore is the exception) in object names
Don't use all caps.
Do not use spaces in object names. If you must separate words, use the underscore.

Examples:
[FollowupGrid].[FollowUpStatus]
[FollowupGrid].[Follow_Up_Status]

[FollowupGrid].[CityState]
[Followup Grid].[City_State]

[GregRemaly]

Sorry, this is hard to explain.

I attached some sample data. I scrubbed it a little so that you can see enough to maybe get me where I need to be.

Long story, but I put together a Follow Up report monthly and I'm trying to improve the summary. I'm from an internal audit firm where audits generate audit issues. Issues require action plans to correct the issue identified. Our automated system generates a data dump that I import into Access to run reports.

In the sample data, you will see that the Issues with ISSUE ID 13081 and 13082 (highlighted yellow) had two action plans each. Neither action plan is complete while the action plans with the ACTION PLAN ID 14981 and 14984 are past due.

The summary report I'm running includes several regions, several departments and needless to say, several issues and action plans. The summary report I am putting together is on the second tab. I am running individual queries for counting the number of issues, number of NOT DUEs, PAST DUEs, WNBI and COMPLETE. My summary works fine the way I have it except for this once circumstance where there is a past due and a not due action plan for an individual issue. I highlighted where the count returns a value of 4 NOT DUE issues when I need it to return a value of 2.

I apologize for having spaces and capital letters and all of the other formatting issues you describe. Maybe that will be the next thing I tackle when I get the summary put together.

Again, I appreciate the help on this. If I can get this one thing fixed, then the Summary report I need to complete will be finished.

[ssanfu]

Excel sheet helps, but what about the dB?

[GregRemaly]

The DB is pretty big and includes confidential info that I just cannot include.

Is there another way?

[ssanfu]

I tried to create a table using your data from the spreadsheet to test the query, but data/fields are missing.

It looks like the record source for the query is a single table. Create a copy of the dB, with the one table and query. Cchange sensitive data, leave only enough records for testing (like your example).

Can't see anything major wrong with the query..... (but then again, I'm not a query Jedi )