Access 2010 - SQL Query - Count Iif Union All Error

Question

Edit: Please see new information below, under Update.

I have an Access database with 4 tables: Table 1, 2, 3, and 4. Each table has 4 columns: ID, Date, Name, and Item.

I am trying to design a Query that will produce a count of a unique value within the Item column, grouped by the Date and Name. For example, when I run the Query, it should display the date, name of the individual, and a total count of how many times the word "Apple" occurs in the Item column for the applicable associate.

I am using Count Iif to count the occurrences and Union All to combine my tables. (Sample code below)

Problem: ~~My Query is only providing a count from two of the tables, instead of all four. I am unable to determine why and need help trying to isolate the problem.~~ I am now able to get the information to count the data from all four tables (by just recreating it from scratch, sigh). However, it's now providing me a total per table and I want it to provide a grand total from all 4 tables. For example: It's showing me a total of 1 apple for table 1, 2 apples for table 2, 3 apples for table 3 when I would like a Query/Report that just gives me the grand total of apples (6). I'm probably overlooking something here.

Update: I decided to create a brand new database with dummy information and manually retyped the code (to appease the old gods) and... it works. the issue is technically resolved at this point. I just now have to figure out why it's not giving me a 'total' number.

Here is an example of the SQL:

SELECT Table1.Date, Table1.Name, Count(IIf([Table1]![Item]="Apples",1,Null)) AS CountApples, Count(IIf([Table1]![Item]="Oranges",1,Null)) AS CountOranges, Count(IIf([Table1]![Item]="Pears",1,Null)) AS CountPears
FROM Table1
GROUP BY Table1.Date, Table1.Name
HAVING (((Table1.Date) Between [SS/SS/SS] And [EE/EE/EE]) AND ((Table1.Name) Like [Name]))
UNION ALL
SELECT Table2.Date, Table2.Name, Count(IIf([Table2]![Item]="Apples",1,Null)) AS CountApples, Count(IIf([Table2]![Item]="Oranges",1,Null)) AS CountOranges, Count(IIf([Table2]![Item]="Pears",1,Null)) AS CountPears
FROM Table2
GROUP BY Table2.Date, Table2.Name
HAVING (((Table2.Date) Between [SS/SS/SS] And [EE/EE/EE]) AND ((Table2.Name) Like [Name]))
UNION ALL
SELECT Table3.Date, Table3.Name, Count(IIf([Table3]![Item]="Apples",1,Null)) AS CountApples, Count(IIf([Table3]![Item]="Oranges",1,Null)) AS CountOranges, Count(IIf([Table3]![Item]="Pears",1,Null)) AS CountPears
FROM Table3
GROUP BY Table3.Date, Table3.Name
HAVING (((Table3.Date) Between [SS/SS/SS] And [EE/EE/EE]) AND ((Table3.Name) Like [Name]))
UNION ALL
SELECT Table4.Date, Table4.Name, Count(IIf([Table4]![Item]="Apples",1,Null)) AS CountApples, Count(IIf([Table4]![Item]="Oranges",1,Null)) AS CountOranges, Count(IIf([Table4]![Item]="Pears",1,Null)) AS CountPears
FROM Table4
GROUP BY Table4.Date, Table4.Name
HAVING (((Table4.Date) Between [SS/SS/SS] And [EE/EE/EE]) AND ((Table4.Name) Like [Name]));

Your "Group By" in the sample for Table2 has "Table1.Name". That should have an effect, but I'm not seeing any other errors. I would check your actual code for similar issues. — PKatona
Thanks PKatona. I have carefully reviewed the original syntax and found no errors. I have also deleted and re-created the database and query to see if this was just an Access quirk. Did not resolve the issue unfortunately. — Dee Wolf
Yes. I ran a query for each table, verified that the results display accurately. But for some reason, when I Union All, I get accurate counts for the first two tables and get zero results for Table 3 and 4. — Dee Wolf
What happens if you run only the code for tables 3 and 4 without tables 1 and 2 included in the union? — Greg Viers

kismert kismert · Accepted Answer · 2016-01-08T01:38:52

Oops!

I did some more research, and it turns out your syntax works just fine. So:

SUM(IIf([Table4]![Item]="Apples",1,0)) AS CountApples

Is equivalent to:

Count(IIf([Table4]![Item]="Apples",1,Null)) AS CountApples

Either way works on Access as well as SQL Server, and over field data that contains nulls. The only reason to choose one over the other is readability.

So, this wasn't a helpful answer, and you should uncheck it.

(Old answer below)

~~Appealing as it may be, using COUNT() in this situation will not work. Instead, use:~~

SUM(IIf([Table4]![Item]="Apples",1,0)) AS CountApples

This will return the proper results you are after.

PS: Using COUNT([Field]) will count only the non-null values in [Field], so your general idea was right. But, as you have shown, COUNT() isn't smart enough to do the same with an IIf() statement.

Access 2010 - SQL Query - Count Iif Union All Error

1 Answers