Forcing Output in Query

[jshepar7]

I have a query that is pulling compliance for every department. The table I am querying uses a look-up table for the service name. My table does not have data for every department, but I want my query to return every department whether they have data or not and to mark them as zero.

Any ideas how to do this without writing in VBA?

 

[vbaInet]

When you say mark them as zero, what do you mean?

How are things related?

 

[jshepar7]

So I have a table "compliance" with field "department". The "department" field is linked to a look-up table "lkdepartment" using a numerical value.

I have my query below:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [compliance].department, Sum(IIf([compliance].[scrub]="Yes",1,0)) AS Compliant, Sum(IIf([compliance].[scrub]="No",1,0)) AS Not_Compliant
FROM [compliance]
WHERE ((([compliance].[Observation Date]) Between [Start Date] And [End Date]))
GROUP BY [compliance].department;

The problem is that I have more departments in my lookup table than I have data in my "Compliance" table. I want all the of the departments listed, regardless if they have data because I will be graphing all the departments compliance.

 

[vbaInet]

Ok, got you:

1. Go back to the design view of your query
2. Right click the line join between Department and Compliance
3. Change the join to include "ALL from Department..."

 

[jshepar7]

That didn't do it. Any other idea. I made the join changes but I am still getting the same results.

 

[vbaInet]

Can you post your db? Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[jshepar7]

I can't post the db, there is sensitive information. Is there a way to force the full output list besides using a join property?

 

[vbaInet]

Your parameters are limiting the values so you have to account for the null values as well. So under the parameter in the OR row you put Is Null

 

[MSAccessRookie]

I do not see why a LEFT JOIN of the Department Table to a Query of data from the other Tables would not give you what you want, since whenever there is no data for any Department, the related fields would all be NULL. You could then check for Null values and display them any way that you wanted to (in your case as 0).