Aggregate query - Nz function not working. I need to show a 0 for blank records

[Josha]

Hi all,



It's been a while since I have posted.

I have an Access database that manages case notes for my student's examinations. They use it to create case notes, and then export them as 'reports' to PDF for me to review. However, the beauty of it, is that I have set up some queries so the students can run them to get the data they need from other student examinations and use that information for discussion points and conclusions.

Recently, I created a query that would give the students a list of 'case numbers' and how many times a chemical batch was used - this information is stored in a separate linked table.

I have noticed though that if a student doesn't use a chemical throughout their examination, the query doesn't even show case number at all. I want it to show the case number and a 0 for the chemical batch.

So I thought I would design a simple aggregate query and use the Nz( ,0) function to do it. But when I run the query it still doesn't show me the case number and the 0 ! Its just ignored... Am I doing something wrong? The images below have some information blurred for privacy reasons...

This is my query.....


This is when I run it, its still not showing examinations with 0 they are just ignored!?


I'm pretty sure Nz is the fuction /expression I need to use but its just not working

Any help will be appreciated !

[orange]

Your query is showing a Count of Chemicals Used---the column colored blue in your graphic.

What exactly do you want to display?

[Josha]

Yes you are correct it is showing a count of chemicals used - which is what I want.... HOWEVER, if in the case, the student (caseworker) didn't use a chemical throughout the examination (ie they didn't record anything in the linked the table tbl_32_BatchNo), then in my query above I want it to show the case number and then a 0 in the Chemical Used column..... That's not what is happening right now, Access ignores that case number completely and doesn't show it.

Does that make sense?

[June7]

Case is ignored because there is no data for it. Can't aggregate data that doesn't exist. Build the aggregate query then join it to a dataset of all possible case numbers. And if you want the aggregation by case and worker, that will be a compound join to a dataset of all possible case and worker pairs.

[Josha]

Hey June7,

I'm not sure what you mean build aggregate then join it to a dataset of all possible case numbers???

Why can't access just see that there is no data for it and then tell me 0 instead of ignoring it? Is Nz ( ,) not the right function to use?

THanks

[June7]

No, Nz() cannot do what you want. As stated, there is no data to summarize so Nz() has no purpose in the aggregate query. No database can simply see what does not exist.

Build the aggregate query like: SELECT CaseNum, WorkerID, Count(*) AS CntCaseByWorker FROM table GROUP BY CaseNum, WorkerID;

Now you need a dataset of all possible case and worker pairs. How that dataset is generated is another topic, could be a Cartesian product: SELECT CaseNum, WorkerID, WorkerName FROM Cases, Workers;

JOIN those two queries with compound link on CaseNum and WorkerID, possibly a LEFT JOIN on each link. Now Nz() can be used to show 0 for records where CntCaseByWorker field is null. Instead of using Nz() in query, could be expression in textbox on report.

Be aware that queries using Cartesian product can perform slowly.

This is a very common question in various forums.

[Josha]

Hmmmm I don't know how to do that??

Is there a step by step guide? ����

[June7]

I gave you the steps:

1. build aggregate query

2. build Cartesian query

3. build query that joins first two

Use query designer. View generated SQL in SQLView.