Query not returning all expected data

[slovell]

O.k. I have a query working, and it's calculating perfectly and I'm reporting on it fine. However I noticed that when I run the query it doesn't populate the equation for all the results. Let me explain further.

I have a main form for tracking company information, and a sub form that tracks departments for that company and waste breakdown information. We take measurements for the company in two ways. 1 - a total weight for the day, and 2 - we weigh out categories of waste (within the sub form that tracks the department stuff)..we then calculate what percentage of the daily waste a specific category is. This calculation is done in a query, and works fine for the first department of every company, however when it moves to the next department for the same company, all I get is #Error in the field. eg./

Company "X"
Total Daily Weight = 750 Kilograms
Department "Shipping"
Category - Plastic Bottles - 20 Kilograms

I then have a query that calculates what % 20 Kilograms is of 750, then a report based on the query. This works fine; However in my report I look at the next department, with the exact same informaton as above...I see #Error returned in the field.

Does all of that make sense?? Is there a way to make sure my query calculation flows through all of the departments??

Thanks.

 

[KeithG]

Do you get the error when you run the query or just in the report?

 

[slovell]

Both actuall

 

[KeithG]

Can you post your SQl? Are there any null values in the fields in the calculations?

 

[slovell]

There will for sure be null values in the specified fields, but I just noticed that it looks like I'm getting duplicate returns from my query...exact same information, except the second row is erroring??? ugh...sorry. Here is my SQL:
SELECT MainInfo.[Company Name], [Waste Information].Department, [Yearly Totals].[Total Daily Waste / KG], [Yearly Totals].[Yearly Total Weight KG], ([Total Daily Waste / KG]/[Yearly Total Weight KG]) AS [Percentage of Yearly Waste], [Waste Information].[Plastic Bottle - Weight Kilograms], ([Plastic Bottle - Weight Kilograms]/[Total Daily Waste / KG]) AS [Percentage of Daily Waste], [Waste Information].[Misc Plastic - Weight Kilograms], ([Misc Plastic - Weight Kilograms]/[Total Daily Waste / KG]) AS [Percentage of Daily Waste Misc Plastic], [Waste Information].[White Paper - Weight Kilograms], ([White Paper - Weight Kilograms]/[Total Daily Waste / KG]) AS [Percentage of Daily Waste White Paper], [Waste Information].[Cardboard - Weight Kilograms], ([Cardboard - Weight Kilograms]/[Total Daily Waste / KG]) AS [Percentage of Daily Waste Cardboard], [Waste Information].[Wood - Weight Kilograms], ([Wood - Weight Kilograms]/[Total Daily Waste / KG]) AS [Percentage of Daily Waste Wood], [Waste Information].[Glass - Weight Kilograms], ([Glass - Weight Kilograms]/[Total Daily Waste / KG]) AS [Percentage of Daily Waste Glass], [Waste Information].[Metal - Weight Kilograms], ([Metal - Weight Kilograms]/[Total Daily Waste / KG]) AS [Percentage of Daily Metal], [Waste Information].[Rubber - Weight Kilograms], ([Rubber - Weight Kilograms]/[Total Daily Waste / KG]) AS [Percentage of Daily Rubber], [Waste Information].[Organic Waste - Kilograms], ([Organic Waste - Kilograms]/[Total Daily Waste / KG]) AS [Percentage of Daily Waste Organic Waste]
FROM ([Yearly Totals] INNER JOIN MainInfo ON [Yearly Totals].AuditNumber = MainInfo.AuditNumber) INNER JOIN [Waste Information] ON MainInfo.AuditNumber = [Waste Information].AuditNumber
WHERE (((MainInfo.[Company Name])=[CompanyName]) AND (([Waste Information].Department)=[DepartmentName]));

Hope this makes sense...

 

[KeithG]

Suspect the Null values are causing the #Error. You can use the Nz function to change the nulls to a 0 value. Something like below

Nz([FieldName],0)

 

[slovell]

where do I put that statement?

 

[KeithG]

I would put it around every field in you calculation. Ofcourse replace [FieldName] with the name of you field.

 

[slovell]

O.k. Keith...so if I get you right...this is what it should look like
Yearly Total Weight KG is one of the fields right now.

it should look like
Nz([Total Weight KG],0)
Right?

Btw...thanks for all the help so far.

 

[KeithG]

Yep you got it. And I wanted to correct myself, I would use the Nz function anywhere there could be a possible null value, not nessarly on every field in you calcuations. So if you know for sure that there will not be a Null value in a field the Nz function would be a waste of resources, not too much of a waste but still every little thing adds up.