NULLS Affecting Embedded Query Calculations

[LoopyNZ]

I'm sure this has something to do with NULLS, but I just can't get my
head around this behaviour. More importantly, I can't SOLVE this
behaviour, and until I do I can't have any confidence in my data!

- Specs: Access 2007, Windows XP, data in linked (mySQL) table.

- I have a text (varchar) field called [HighestEducation] that
contains NULL values.

- I have a calculated field [HighestEducation_Valid] to check the
quality of the contents of the text field. The formula is:

IIf(IsNull([HighestEducation]),"No
Response",IIf([HighestEducation]="","Invalid/Incomplete","Complete"))

In my query results (datasheet), the contents of
[HighestEducation_Valid] are:
--- 287 COMPLETE
--- 1 INVALID/INCOMPLETE
--- 301 NO RESPONSE.

My problem is that when I use this query in a *subsequent* query (e.g.
SELECT [HighestEducation_Valid], COUNT(myID) FROM myQuery GROUP BY
[HighestEducation_Valid], I get:
--- 287 COMPLETE
--- 302 INVALID/INCOMPLETE.

(Other notes:
- I've also tried IIf([HighestEducation] Is Null... but the behaviour
was unchanged.
- This is all happening in vanilla queries, no VBA involved at all.)

Any ideas?

Many thanks,
- Linda Patterson
- Dunedin, New Zealand

 

[Dale Fye]

Loopy,

Nested IIFs can be a problem, especially with data linked from other ODBC
data sources. I'm not familiar with MySQL so this will just be a guess.

Have you tried inverting the first two IIFs?

IIF([HighestEducation] = "", "Invalid/Incomplete", IIF([HighestEducation] IS
NULL, "No Response", "Complete"))

Other than that, I don't have a clue.

HTH
Dale

 

[LoopyNZ]

Loopy,

Nested IIFs can be a problem, especially with data linked from other ODBC
data sources. I'm not familiar with MySQL so this will just be a guess.

Have you tried inverting the first two IIFs?

IIF([HighestEducation] = "", "Invalid/Incomplete", IIF([HighestEducation] IS
NULL, "No Response", "Complete"))

Other than that, I don't have a clue.

HTH
Dale

I'm sure this has something to do with NULLS, but I just can't get my
head around this behaviour. More importantly, I can't SOLVE this
behaviour, and until I do I can't have any confidence in my data!

- Specs: Access 2007, Windows XP, data in linked (mySQL) table.

- I have a text (varchar) field called [HighestEducation] that
contains NULL values.

- I have a calculated field [HighestEducation_Valid] to check the
quality of the contents of the text field. The formula is:

IIf(IsNull([HighestEducation]),"No
Response",IIf([HighestEducation]="","Invalid/Incomplete","Complete"))

In my query results (datasheet), the contents of
[HighestEducation_Valid] are:
--- 287 COMPLETE
--- 1 INVALID/INCOMPLETE
--- 301 NO RESPONSE.

My problem is that when I use this query in a *subsequent* query (e.g.
SELECT [HighestEducation_Valid], COUNT(myID) FROM myQuery GROUP BY
[HighestEducation_Valid], I get:
--- 287 COMPLETE
--- 302 INVALID/INCOMPLETE.

(Other notes:
- I've also tried IIf([HighestEducation] Is Null... but the behaviour
was unchanged.
- This is all happening in vanilla queries, no VBA involved at all.)

Any ideas?

Many thanks,
- Linda Patterson
- Dunedin, New Zealand

Thanks for the suggestion, Dale. I've just tried it out and it doesn't
seem to have helped.

- Linda

 

[Sammi12]

The COUNT() function counts the occurrences of each distinct value separately. So, when you run the subsequent query, it correctly counts 287 occurrences of "COMPLETE." But, it also counts all the occurrences of "INVALID/INCOMPLETE," which includes the one instance from the original query and the additional 301 instances with "NO RESPONSE."

To resolve this issue, you can modify your subsequent query to include the original query as a subquery and count the records based on the values in [HighestEducation_Valid]. Here's an example of how you can achieve this:

SELECT [HighestEducation_Valid], COUNT(*) AS CountOfRecords
FROM (
    SELECT IIf(IsNull([HighestEducation]), "No Response", IIf([HighestEducation] = "", "Invalid/Incomplete", "Complete")) AS [HighestEducation_Valid]
    FROM myQuery
) AS SubQuery
GROUP BY [HighestEducation_Valid];

By using a subquery, you ensure that the count is based on the distinct values of [HighestEducation_Valid] from the original query. This way, you should get the correct counts for each category without duplication.