Percentage in Totals Query

[Swatskeb]

Below is the SQL and the query results when I run my query:

SELECT DDRs_Table.PWC,
 Count([DDRs_Table]![PWC]) AS [NO DDRS LOADED],
 Count([DDRs_Table]![ERRORTYPE].[VALUE]) AS [TOTAL NO ERRORS]
FROM DDRs_Table
GROUP BY DDRs_Table.PWC
ORDER BY DDRs_Table.PWC;

I would like to add an error rate so I add it here is the SQL:

SELECT DDRs_Table.PWC,
 Count([DDRs_Table]![PWC]) AS [NO DDRS LOADED],
 Count([DDRs_Table]![ERRORTYPE].[VALUE]) AS [TOTAL NO ERRORS],
 Count([DDRs_Table]![ERRORTYPE]![Value])/Count([DDRs_Table]![PWC]) AS [ERROR RATE]
FROM DDRs_Table
GROUP BY DDRs_Table.PWC
ORDER BY DDRs_Table.PWC;

But when I do that is gives me the error:
Syntax error (missing operator) in query expression 'TOTAL NO ERRORS/NO DDRS LOADED'.

Can someone school me on doing percentages or see what I am doing wrong. If you need to see the table let me know if that would help.

[June7]

Why using .[Value] and ![Value]?

Remove them or change ! (bang) to . (dot).

I never reference Value property in queries.

[Swatskeb]

I changed it to "." I don't know why it came out with "!" when I had been messing with it, but either way even if I remove the value, I get the same syntax error. I don't like using them but in this case the Error Type field is a multi-valued field so the .value counts all instances even if there are multiple selections in the field and not just counting the field once. I still don't know why it says missing operator. My "/" divide by sign should be the operator right?

I also tried

Sum(Count([DDRs_Table]![ERRORTYPE].[Value])/Count([DDRs_Table]![PWC])) AS [ERROR RATE]

And I get a different error with this again doesn't matter if I remove the .value or use a diffrent field all together to count.
Cannot have aggregate function in WHERE clause (Count([DDRs_Table]![ERRORTYPE].[Value])).

What I noticed though is if you use a completely separate query based off of the first one if will work just by using the field names TOTAL NO ERRORS/NO DDRS LOADED but it seems improper to have a totals query base off of another totals query there has to be a better way to do it in the same query. I have attache a small example of the table with about 6 records in.

[June7]

I was afraid of that. I NEVER use multi-value field. The expression works for me with normal fields.

Maybe build a report on the original query and do the percentage calc in textbox.

[Swatskeb]

let me try it with a normal field and see if it works for me I don't generally use them I need it in this case.

[June7]

Your query does not show a WHERE clause.

[Swatskeb]

Yeah I guess your right It must be that multi valued field causing the issue cause I didn't have any issues with calculating these percentages which use a regular combo box. So I might just have to create a second query off of the first one because it seems like the multi-valued calculation has to be done prior to it being used like the new query need to be based off the old query with only one difference which is an added column to do the calculation. But how would I change my output format to 0.0% instead of the statndard percentage of 0.00% or would I be better off leaving the percentage as a decimal and changing the format in the report that is based off this query. What would be the formula I would need in the format block of the txt box like #.#%

SELECT DDRs_Table.PWC,
Count([DDRs_Table]![PWC]) AS [NO DDRS LOADED],
Count(IIf([DDRs_Table]![DDRSTATUS]="ERROR NOT CORRECTABLE",1,Null)) AS [NO UNCORRECTABLE ERRORS],
[NO UNCORRECTABLE ERRORS]/[NO DDRS LOADED] AS [AFTER CORRECTION ERROR RATE],
 Count(IIf([DDRs_Table]![DDRSTATUS]="NOT REVIEWED",1,Null)) AS [NOT REVIEWED],
 ([NO DDRS LOADED]-[NOT REVIEWED])/[NO DDRS LOADED] AS [PARTICIPATION RATE]
FROM DDRs_Table
GROUP BY DDRs_Table.PWC
ORDER BY DDRs_Table.PWC;

[June7]

I don't do formatting in query if I can help it. Format() function results in a string value, not a true number, and can cause issues if used in other calcs.

Set textbox Format to Percentage and DecimalPlaces to 1.

[Swatskeb]

I just decided to do all the percentages in the Report that query is based off of and it calculates the multivalued field with no issue. A little better than having two underlying queries for the report.