Re: [MSAccessProfessionals] Summary Query Issue

Hi Sean,

Thanks for the clarification.

This is expected behavior in SQL since "blank" is actually Null which I like to think of as "unknown". You can't compare anything to unknown so it's unknown whether it equals "X" or not. 

You would need to change the WHERE clause in the query to:

 CatA & "" <>"X" 

Adding a zero length string to CatA changes it from unknown/null to "" which can be compared to "X".

Regards,

Duane

---

From: Sean Cooper 

 

I actually did that before asking the question to see if I can replicate the problem in a much easier framework and I can.

Consider the simple table

ID    CatA    Value

1                  1

2    X            2

3    Y            3

If you create a sum query with Criteria CatA of "X" you correctly get a 1 row query "X 2"

If you create a sum query with "where" Criteria CatA of "X" you correctly get a 1 row 1 cell query "2"

But

If you create a sum query with Criteria CatA of <>"X" you incorrectly get a 1 row query "Y 3", it drops row1 the value where "A" was blank.

Same If you create a sum query with "where" Criteria CatA <>"X" you incorrectly get a 1 row 1 cell query "3" not "4"

Sean

On Monday, May 17, 2021, 04:17:52 PM CDT, Duane Hookom <duanehookom@hotmail.com> wrote:

Hi Sean,

I typically simplify and test. Consider removing the Group By and check the results. If that isn't enlightening, then remove the criteria and test. If you still don't understand, copy some data to Excel and use the data tools there to check the values of XYZ.

Duane

---

From: Sean Cooper

 

I have a summary query which groups on several fields and then sums several other fields.

If I add another column XYZ and "Group By' that column everything looks good.

if I then change XYZ from a "Group By" to a "Where" column, and add criteria ="teststring", everything still looks good. ie it correctly only sums data where XYZ has the value "teststring"

if I keep XYZ as a "Where" column, but change the criteria to <>"teststring" though it now only sums columns of XYZ that are blank. ie it eliminates all data where XYZ has and value not just "teststring"

Any ideas?