I am trying to run an expression in a query to get the sum of certain values. When one of those is zero I get a divide by zero error. I tried doing the NZ function but cant figure it out. I am fairly new to working with numbers in databases, I am used to working with text type dbs and am struggling and would like some help if possible.
Here are the items I am working with:
CR00 Current Std Cost
CR00 12 Mo Usage (cost)
CR00 Avg Mo Annual Usage (cost)
TA01 Proposed Std Cost
The sums are being developed from a two queries below QY01 ($Query_CROO-Sum Cost Data) and QY02 ($Query_CR00 - Saving Cost Data)
QY01 No. Purchased per Year = Current Std Cost – 12 Mo Usage
Query Expression: NoPurAnnually: Sum([TA_CR00 - Cost Reduction Main Table]![12 Mo Annual Usage]/[TA_CR00 - Cost Reduction Main Table]![Current STD Cost])
QY01 No. Purchased per Month = Current Std Cost – Avg Mo Annual Usage
Query Expression: NoPurMonthly: Sum([TA_CR00 - Cost Reduction Main Table]![Avg Mo Annual Usage]/[TA_CR00 - Cost Reduction Main Table]![Current STD Cost])
QY01 Estimated New Year Cost = Proposed Std Cost – 12 Mo Usage
Query Expression: EstNewYrCost: Sum([12 Mo Annual Usage]/[Proposed STD Cost])
QY01 Estimated New Mo Cost = Avg Mo Annual Usage – Proposed Std Cost
Query Expression: EstNewMoCost: Sum([Avg Mo Annual Usage]/[Proposed STD Cost])
QY02 Estimated Annual Savings = pulls from the QY01 query above titled: $Query CROO-Sum Cost Data
Query Expression: EstAnnualSvg: Sum([$Query_CR00 - Sum Cost Data]![12 Mo Annual Usage]-[$Query_CR00 - Sum Cost Data]![EstNewYrCost])
QY02 Estimated Monthly Savings = pulls from the QY01 query above titled: $Query CROO-Sum Cost Data
Query Expression: EstMonthlySvg: Sum([$Query_CR00 - Sum Cost Data]![Avg Mo Annual Usage]-[$Query_CR00 - Sum Cost Data]![EstNewMoCost])
The problem is that Proposed Std Cost could be zero (default in the table sets it to zero in the field) while people are working on it OR it could be blank if it was deleted (which can be corrected with a filter) however I would rather it see the all the data. I cant get reports to work without it. The current above queries cant work unless data in that field is not zero.
· Can someone please give me an example of a NZ query and tell me where it needs to go in the queries above in order for the reports to work.
· Is there a way to combine these queries into one single query?
· Is there a way to get the query data back into the main table once the query generates the numbers?
"Is there a way to get the query data back into the main table once the query generates the numbers?"
why would you want to do this when you can generate the calcs any time with the query?
you would run the risk of someone changing (e.g.) a [Proposed Standard Cost], but your [EstNewYrCost] staying the same.
yes of course you could re-run the query to update [EstNewYrCost], but to be CERTAIN you would have to do it every time you look at the table = even more of a hassle than generating the calcs with the query when you need the calcs.
to answer your question strictly: yes. (almost?) anything you can do in a query can find it's way into a table (experiment with update queries after modifying the table structure to have fields for a calc). but do it for curiosity, not for real.
Thanks to all for your previous help. I was able to get the divide by zero error corrected. I’m fairly new to this so the naming convention using spacing is wrong, but I will need to go with what I have for now and change naming convention as I get the opportunity and for the future stuff. Curious, why is name spacing not suggested?