If Null

[Mike Hughes]

When I run this query I get the results of the number of cases in each District that are [CASE TYPE]="A" AND [EST TYPE]="P"

If the District has no cases that meet this criteria the the District is not included in the results.

What I want is to have that District included in the results with the case count of 0.

Would someone please tell me how to do this, if it is possible?


SELECT
[CASE TYPE AND EST].district,
Count([CASE TYPE AND EST].CASE) AS CASES INTO [P]
FROM [CASE TYPE AND EST]
WHERE [CASE TYPE AND EST].[CASE TYPE]="A" AND
[CASE TYPE AND EST].[EST TYPE]="P"
GROUP BY [CASE TYPE AND EST].district, [CASE TYPE AND EST].[CASE TYPE], [CASE TYPE AND EST].[EST TYPE]
ORDER BY [CASE TYPE AND EST].district;

 

[vbaInet]

You have put criteria to restrict the results and I think you're after the Is Null case type.

WHERE ([CASE TYPE AND EST].[CASE TYPE]="A" AND
[CASE TYPE AND EST].[EST TYPE]="P") OR [CASE TYPE] Is Null

 

[Mike Hughes]

I tried this and got the same results, still not what I'm looking for.

SELECT [CASE TYPE AND EST].district, Count([CASE TYPE AND EST].CASE) AS CASES INTO [TANF P]
FROM [CASE TYPE AND EST]
WHERE [CASE TYPE AND EST].[CASE TYPE]="A" AND [CASE TYPE AND EST].[EST TYPE]="P" OR [CASE TYPE AND EST].[EST TYPE] Is Null
GROUP BY [CASE TYPE AND EST].district, [CASE TYPE AND EST].[CASE TYPE], [CASE TYPE AND EST].[EST TYPE]

ORDER BY [CASE TYPE AND EST].district;

 

[boblarson]

Actually, a way to get the count even if there are zero for the district is to create a separate query that pulls the districts. Then you bring that query into your other one and link the two with an OUTER JOIN where you include all of the districts from that first query and only the records that match in the other. So then it will give you nulls for the values but the districts will show up. So, to get the zeroes, you would change your field you are counting on to be this:

Count(IIf(IsNull([CASE TYPE AND EST].CASE),0,[CASE TYPE AND EST].CASE)) AS CASES

And there you go - you should have zeroes where for districts without any cases.

 

[Mike Hughes]

Something is still wrong - still not giving me all the districts
SELECT
[DISTRICT OFFICE].DO,
Count(IIf(IsNull([CASE TYPE AND EST].CASE),0,[CASE TYPE AND EST].CASE)) AS CASES
INTO [TANF P]

FROM [DISTRICT OFFICE] LEFT JOIN [CASE TYPE AND EST] ON [DISTRICT OFFICE].DO = [CASE TYPE AND EST].DO

GROUP BY [DISTRICT OFFICE].DO, [CASE TYPE AND EST].[CASE TYPE], [CASE TYPE AND EST].[EST TYPE]

HAVING ((([CASE TYPE AND EST].[CASE TYPE])="A") AND (([CASE TYPE AND EST].[EST TYPE])="P"))

ORDER BY [DISTRICT OFFICE].DO;

 

[vbaInet]

I've taken out the INTO part. You really shouldn't be making a table out of this query, that's bad practice. If you want to use it in report or a form set that report's/form's record source to this query.

For those Null Cases, they will not have a Case Type and an Est Type so we can use either:

SELECT [DISTRICT OFFICE].DO, Count(*) AS CASES
FROM [DISTRICT OFFICE] LEFT JOIN [CASE TYPE AND EST] ON [DISTRICT OFFICE].DO = [CASE TYPE AND EST].DO
GROUP BY [DISTRICT OFFICE].DO, [CASE TYPE AND EST].[CASE TYPE], [CASE TYPE AND EST].[EST TYPE]
HAVING ([CASE TYPE AND EST].[CASE TYPE] = "A" AND [CASE TYPE AND EST].[EST TYPE])="P") OR ([CASE TYPE AND EST].[CASE TYPE] Is Null)
ORDER BY [DISTRICT OFFICE].DO;

 

[Mike Hughes]

I tried running that code and got an error "Data type mismatch in criteria expression"

This won't work either
SELECT [DISTRICT OFFICE].DO, Count(*) AS CASES
FROM [DISTRICT OFFICE] LEFT JOIN [CASE TYPE AND EST] ON [DISTRICT OFFICE].DO = [CASE TYPE AND EST].DO
GROUP BY [DISTRICT OFFICE].DO, [CASE TYPE AND EST].[CASE TYPE], [CASE TYPE AND EST].[EST TYPE]
HAVING ([CASE TYPE AND EST].[CASE TYPE] = "A" AND [CASE TYPE AND EST].[EST TYPE])="P") OR ([CASE TYPE AND EST].[CASE TYPE] Is Null)
ORDER BY [DISTRICT OFFICE].DO;

 

[vbaInet]

Extra parentheses <-- Bob will be happy to see this

Use this bit:
HAVING ([CASE TYPE AND EST].[CASE TYPE] = "A" AND [CASE TYPE AND EST].[EST TYPE]="P") OR ([CASE TYPE AND EST].[CASE TYPE] Is Null)

 

[Mike Hughes]

Yea, I caught that also, but still doesn't work. Time to go home for the day. I'm going to start everthing over tomorrow and see if I can figure it out.
Thanks for everyones help.
Mike

 

[boblarson]

Yea, I caught that also, but still doesn't work. Time to go home for the day. I'm going to start everthing over tomorrow and see if I can figure it out.
Thanks for everyones help.
Mike

Tomorrow, post a copy of your database (with bogus data of course) and we may get it taken care of faster.

 

[Mike Hughes]

I'm sorry, before I saw your request for a copy of the DB I had deleted it and dumped the trash. I've started all over and I'll let you know if I run into problems with this one.

Thanks to everyone for all your attention to this. Mike

 

[Mike Hughes]

OK I'm attaching a DB with about the same data and fields

 

[boblarson]

You didn't include the departments table. It isn't going to work right in the sample if we don't have a complete list of departments to work with as the only ones I have are those in the NOLDBA_INT_CASE_STATUS table and since those in there have cases, it makes it impossible to show you what I'm talking about unless we have the full department list.

 

[Mike Hughes]

Sorry, here you go. Mike

 

[boblarson]

Okay, so it would appear that all of your departments have cases in there at the moment. So, just for an example, I put in two new departments (OO and PP) so you can see how they are handled.

Check out your Query1 which I modified. Also, you really shouldn't need to have it a make table query because you can use that query wherever the table would be used.

So, in the query1 check out the [CASE TYPE] and [EST TYPE] fields to see how I put in NONE for the two that were missing data, so your crosstab query and/or report could work.