Count Query displays no records, want it to display Zero 1

[kirstenlargent]

I currently have multiple queries that use the Count function. I have them all combined into one main query. My problem is that some of the Count queries do not find any records (and that is correct, there are currently no records meeting the criteria). But when the queries find no records, they display nothing, so the main query that combines them all displays nothing, even when some of the other queries have actual number results for their count.

Example: Query1 count is 6. Query2 count is 34. Query3 count is nothing, no records found. One main query to join Query1 and Query2 displays counts of 6 and 34. But if I add Query3, no results are displayed for ANY of the queries.

I'm trying to find a way to make the queries that do not find any records display Zero. Is that possible? Or any way you can think of where the actual valid counts would display, even if some of the other counts returned no records? THANKS!

 

[JonFer]

It would be helpful to see an example of your queries with the counts and the query that combines the other queries. Are you grouping by some fields? I don't know if you are joining the queries or just showing a single row with the counts.

Depending on your setup, the answer may be a LEFT JOIN, changing your count to Count(*), or using subselects.

 

[kirstenlargent]

Thanks for the speedy reply. My base queries only have two fields - one is a Yes/No field to indicate if a user has taken a class. I have select criteria of "Yes" to count only users that have taken the class. The other field is the Username, and has a count. This works fine for the few queries where at least one user has taken a class. But the query that queries the table where NO users have taken the class displays Nothing.
Then when I join them together and include the query with Nothing, I get Nothing.

As far as the main query, I just have the three other queries added and i'm showing just a single row with three counts (one from each query). There is no join in the main query.

I haven't used the Count(*) function or Subselects before, so if you think that is my option, you may need to explain a lot! THANKS!!

 

[JonFer]

That is what I needed. You need to make sure you get a single row per query even if there are no "Yes" records so ...

Option 1: Remove the Yes/No criteria from your queries. Instead of Count, use the Sum function on this field:

UsersTakenClass: -1*[yes/no fieldname]

The Yes/No value is -1 if Yes so the -1 converts it to a positive 1 which will count the users who have taken the class. No's are 0 so will not affect the count.

Option 2: The subselect option lets you do all of the counting in one query. It can be very useful but is more complicated. The syntax for the count fields would be:

UsersTakenClass1: (Select Count(*) from table1 where Yes_No
UsersTakenClass2: (Select Count(*) from table2 where Yes_No

For your situation, the main query needs to be one that returns a single row. The subselects are then added as fields to that.

 

[kirstenlargent]

Awesome! I tried Option 1 and it worked PERFECTLY! THANKS!!!

 

[kirstenlargent]

JonFer - I need a bit more help now that I've had time to really dig in.

I'm using your Option 1, but I'm still running into problems. I need to be able to select criteria from the table. Group field = ZDG, EDG, or LDG. Then the Yes/No field for a certain class taken, all in the same table.
My table currently only has ZDG in it, so when I select on criteria for EDG and Yes for the class, I still get nothing, even using the Sum, since there are no EDG records at all in the table.

Splitting the table out into different tables is not an option at this point, I have way too many queries, forms, reports, etc, that I would have to revise.

I can't quite figure out the Option 2 - where would I put in the field name for the yes/no?

Any advice?!!

 

[JonFer]

I'd be better able to help if you posted some examples. Show me the SQL for each of the initial queries and what a few rows of the output look like. Then show an example of what you want in the final query results. Provide the table structure, too, with a little explanation of the fields, what the primary key is, etc.

 

[kirstenlargent]

OK, thanks. Here it goes...
Two main tables:
1. tEmployee - has employee data, primary key is EmployeeID. The field I need to use criteria on is "EmployeeType" which can be "LDG", "ZDG", or "EDG".
2. tCurriculum - has data about which classes taken. Relates to tEmployee by EmployeeID. Main fields I need to select from there are "LDG_Complete", "ZDG_Complete", or "EDG_Complete".
All employees are listed in tEmployee, but not all of them have entries in tCurriculum yet. Also, there are no LDG employees currently.

Queries were pulling based on EmployeeType (LDG, ZDG, EDG) from tEmployee and “Yes” for certain classes in tCurriculum (LDG_Complete, ZDG_Complete, EDG_Complete).
I have two queries for each employee type to count Complete and Not COmplete.
Example - LDG employees. There is a query for any LDG employee types that have "Yes" checked for LDG_Complete. Then I would count the Yes's to get total LDG_Complete. Also another query just like that one for "NO" in LDG_Complete, and I would count to get the not completes.

This works when there are LDG employees in the tEmployee and tCurriculum, but they are not in there yet. So they are returning blank queries. My main query linked all 6 queries (LDG Complete, LDG Not Complete, ZDG Complete, ZDG Not Complete, EDG Complete, and EDG Not Complete) with just the count field of each of the 6 queries. This way, I could create a report with a grid to show counts of complete and not complete.

Also, I can't just select off of the yes/no field of LDG_Complete without also having the EmployeeType of "LDG" criteria, since eventually, there will be employees that will have LDG_Complete marked as "YES" but they will move up to the ZDG or EDG group. So I wouldn't want to count those. So I have to have that EmployeeType Criteria in the queries.

Here’s the SQL of the LDG_Complete query that returns NO records. If there was just a way to have it show 0, I would be ok! (Alias = Employee ID Group= Employee Type)

SELECT Count(tEmployee.Alias) AS CountOfAlias, tEmployee.Group, tCurriculum.[LDG: Completed All LDG Curriculum]
FROM tEmployee LEFT JOIN tCurriculum ON tEmployee.Alias = tCurriculum.Alias
GROUP BY tEmployee.Group, tCurriculum.[LDG: Completed All LDG Curriculum]
HAVING (((tEmployee.Group)="LDG&quot AND ((tCurriculum.[LDG: Completed All LDG Curriculum])=Yes));

 

[JonFer]

What do you want your final result to be? Something like this?

Code Employees TakenClass(appropriate for code)
ZDG 14 10
LDG 0 0
EDG 10 8

Is this what you are looking for? What if a ZDG employee has taken the EDG class? Do you want to know that?

What about this?

Code Employees TakenZDG TakenEDG TakenLDG
ZDG 14 10 5 4
LDG 0 0 0 0
EDG 10 0 8 0

 

[kirstenlargent]

Your second scenario would be good. So far, my query to combine all those totals end up being blank since there are no LDG's. If I leave the LDG's off, all the other querys pull in ok and the class counts are all on one row.
Any thoughts on how to get my blank query to show zeroes?

 

[JonFer]

First create a table that lists the 3 codes, one per row. It just needs the one field in it.

Create a query that joins the Employee table with the Curriculum table. Grab the Employee Code from the Employee table and everything from the Curriculum table.

For the final query, add in the table and the query. Create a Left Join on the Code field (make sure the arrow points to the query). Grab the Code from the table (not the query) and then create these fields:

  ZDG:  -1*Sum(nz(ZDG_Taken,0))
  EDG:  -1*Sum(nz(EDG_Taken,0))
  LDG:  -1*Sum(nz(LDG_Taken,0))

GROUP BY the Code field and that should do it. I added the nz function to change NULLS to zero. You'll have NULLs for rows where there are no employees for that Code. You will always get three rows in the result, regardless of what employees there are. If you want to see the total employees by Code, you'll need to do another step before the final query.

 

[kirstenlargent]

HALLELUJAH!!! That did it! Sorry it took me so long to get time to try it, but that extra table with the three codes did the trick. Once I added that into my final query, it returned the totals, even if they were zero! You are a LIFESAVER!!!!!!!!!

 

[JonFer]

I was wondering what happened - glad it worked.