Divide-by-Zero Issue (based on record count)

[skydivetom]

Hello Experts:

I need some assistance with generating a query that outputs 100% even if the denominator = 0.

Attached are two (2) samples files illustrating the process when using a) "number of records" vs. b) "field data type = NUM".

Version 01:
- Attached DB file includes table with 10 sample records where [CONCURRENCE] includes 2 values {Concur, Non-Concur}.
- Query "Q01" and "Q02" calculate the numerator and denominator, respectively.


- Lastly, "Q03" outputs the desired percentages for both "Concur" and "Non-Concur".

Version 02:
- FYI purposes only, I've included table/query "T_DivideByZero" and "Q_DivideByZero" that demonstrate the work around using an expression when "field data type = NUM".
- However, just like in version 01, I need to output the percentage breakdown based on a query that returns # of records.
- So, in this DB, all 10 sample records have [CONCURRENCE] = "Concur".
- Thus, query "Q02" does NOT return any records.
- And, consequently, "Q03" also does NOT return the desired output of, e.g., "100%", "0%".

My question:
- Just like with the "After: IIf([Denominator]=0,1,[Numerator]/[Denominator])" expression in query "Q_DivideByZero", does anyone know of a work-around for the denominator query "Q02" so that the "divide by zero" won't be an issue for "Q03"?

[Bulzie]

Might be missing the issue totally but can't you just make it static 100% or make denom value same a numerator (or Q1) if denom (Q2) is 0? IIF (Q2=0, Q1/Q1x100, Q2/Q1)?

[skydivetom]

Bulzie:

I'm ok to make it static (just like I did in query "DivideByZero" example). However, I'm not entirely clear on how to make it static if Q02 doesn't return any records whatsoever.

My goal is to return "100%" and "0%" (in Q03) based on Version02's current data set (T_Research_Status). At the same time, if the underlying dataset changes to a different distribution (see Version01's data set), then I must calculate the % distribution accordingly in Q03.

I hope that makes sense. Thanks.

[davegri]

Try this for Q002

SELECT T_Research_Status.RESEARCH_STATUS, T_Research_Status.CONCURRENCE, Nz(DCount("CONCURRENCE","T_Research_Status","CONCURRENCE='non-concur' AND CONCURRENCE ='Complete'"),0) AS [COUNT]
FROM T_Research_Status
GROUP BY T_Research_Status.RESEARCH_STATUS, T_Research_Status.CONCURRENCE;

[skydivetom]

Davegri:

Ok... this is definitely going in the right direction! When replacing Q02 w/ your SQL, Q03 outputs the desired results.

HOWEVER, now let's go back and change, e.g., one or more records in field [T_Research_Status].[CONCURRENCE] to "Non-Concur" again. If I change 1 record, the distribution should be calculated as "90%" | "10%". Unfortunately, it returns it as "100%" | "0%" as well.

So, while your "overwrite" works great, it should only be used when all records in CONCURRENCE="Concur".

Is there a way to do both a) use original calculation if I have multiple values for concurrence and/or b) use your 'overwrite' SQL if all concurrence are the same?

[xps35]

Life would be easier if you had a table with the allowed values ​​of concurrence. One query is sufficient.

Version 03.accdb

[skydivetom]

xps35 -- thank you for chiming in... I very much appreciate it.

First of all, I LOVE THIS METHOD!! It certainly gives me the desired results.

Allow me to follow w/ a few observations though:
- I noticed the new table [T_CONCURRENCE]. When changing n records in table [T_Research_Status], it automatically update its data set. How is that working? I noticed the sub-select in Q1?
- Also, my example table was extremely reduced to just the basic data. In my actual version, I may have several values (above and beyond the "concur" and "non-concur"). So, I'll have to see to this approach works out when plugging it into my actual solution.
- Lastly... out of curiosity only. Why isn't the sub-select (including the T_Concurrence) not shown when in DesignView of the query. I only see it in SQL view?

Again, thank you for thinking outside the box and providing another alternative. Again, I will have to see how to mimic this process in my actual DB given I have additional record values. I'll keep you posted.

Cheers!

[xps35]

- I noticed the new table [T_CONCURRENCE]. When changing n records in table [T_Research_Status], it automatically update its data set. How is that working?
Sorry, I do not understand this question. What do you mean with "its data set"?

- Also, my example table was extremely reduced to just the basic data. In my actual version, I may have several values (above and beyond the "concur" and "non-concur"). So, I'll have to see to this approach works out when plugging it into my actual solution.
Should not make a difference. All values must be in T_CONCURRENCE of course.

- Lastly... out of curiosity only. Why isn't the sub-select (including the T_Concurrence) not shown when in DesignView of the query. I only see it in SQL view?
It is to complex to show in design view.

[skydivetom]

Thanks for the additional info.

I further reviewed the SQL. It contains 3 SELECT statements. I'm not clear whether or not this means I have the SELECT plus two sub-select. I need to further break this down given that, e.g., I don't have the ID number field in my data set. Not an easy query to makes sense of... urgh.

//

Update... I made some progress applying the changes to my actual DB. However, this query appears to very extremely "sensitive". For example, if I were to change the query's field format to "Percent" (and remove the *100), I regularly get the "Record is deleted" error. Then, upon clicking "compress/repair", it generates the MSysCompactError table.

Why would I constantly get the "Record is deleted" error even though it produces the correct calculation?

[skydivetom]

xps35 -- pls allow me to follow up on your proposed method.

I was able to apply your process to my actual DB. I was simpler than I first thought it would be.

Unfortunately, I constantly get the "Record is deleted" error. It particularly happens when I open the query, select the field and then change the format/property to, e.g., Standard (to display only 2 decimals).

And each time I click on "Repair", I get the MSysCompactError. See attached the version I'm currently using.

Do you know how the displaying of only 2 decimals could be build into the SQL statement so that I don't need to change the format property? It appears that's causing the error.

[skydivetom]

Below solved the "Record is deleted" issue.

PERCENT: Round(First([COUNT_CONCUR]/DCount("*","Q_Test","RESEARCH_STATUS='Complete'")*100),2)