query is excluding an entire group

[pbaldy]

I think you can attach a zipped file; did you try that?

[MLangendorf]

thanks,
I'll try that.

[MLangendorf]

Joe
did this work?
I can't tell if it attached or not.
I have created a zip file and uploaded it.

[JoeM]

Sorry, I have been away for a few days.

I was able to successfully download your database. Can you let me know which query exactly is the issue, and what record is missing exactly?

[MLangendorf]

The query is called:
Qry_AggregateResultsAllAgencies. It's the second one down in the query's

[MLangendorf]

I'm not sure what all records are missing but I do know that I am missing records from the agency US Marshals (San Antonio. I know for a fact that there were zero records that met the CCS>0 criteria.

[JoeM]

OK, in looking at your query, I think I see your problem, and it goes back to something I said back in post #6.
You have multiple data sources in this query, and they all seem to be linked to each other by INNER JOINS. When you do this, the only UNITS that will show up are UNITS found in EVERY single one of your sources that are joined like this.

What I recommend doing is creating a query (or using a table) that has all our your units listed (and listed exactly once). Then, from this source, do a LEFT OUTER join to all of your other data sources.
See this link for details on how to set-up left joing: http://www.techonthenet.com/access/queries/joins2.php
When you read the explanations of each of the different join options, it makes more sense.

[MLangendorf]

Thanks again Joe.
I believe that I have tried this approach but I will go back and try again. I already have a table only for Units.
I will get back to you in a day or so and let you know how it works out.

[JoeM]

I believe that I have tried this approach but I will go back and try again.

Yes, all the joins in that query are Inner Joins (no Left Joins).
How I typically like to approach ones like these is to create a query that lists each record that I want to see, and then use this as the main data source of my query, and then link all the other tables/queries to this one using Left Joins (so no records from that "main" data source are dropped.

You can then use the NZ function to return zeroes instead of nulls for any fields that may not have matching records in the other tables/queries.
See: http://www.techonthenet.com/access/f...dvanced/nz.php

So if you have a numeric field, the formula would look something like:
NZ([FieldName],0)

[MLangendorf]

Sorry, I missed this posting. will investigate my ability to make this work also.
I will get back to you

[MLangendorf]

this worked nicely Joe.
However, is it possible to use the NZ function to return zeros for a calculated expression. For example, I have a field that I am calculating % based on two other fields within the query. Here is the syntax for the expr.

expr: Round((([CCS >0]+[CountOfID11])/[SumOfCountOfID1])*100,2)
I have tried a number of ways to include the NZ function but it keeps dinging at me. Of note, I have used the NZ function on both of the fields named in this calculation {CCSS>0 and [CountOfID11].

[MLangendorf]

Never mind Joe, I figured it out. Was trying to use the brackets when I didn't need to.

[JoeM]

Excellent! Glad you got it all working now.

[MLangendorf]

Using the Nz function resolved my issues.