Crosstab query with calculation on two (or more) fields?

[CubsFan]

How do I perform a calculation on the values of two fields (or more) in a crosstab query? Sample table contains: [State], [County], [CountofTrials], [CountofSuccessful_Trials], [ActivityDate]. I need to create a crosstab query with row = State or County; column = ActivityDate (grouped by month); value = ratio of Successful Trials/Trials. I can create a crosstab to give me trials and a crosstab to give me successful trials, but I cannot figure out how to get a crosstab to calculate the ratio of success trials to trials.

[June7]

So divide CountofSuccessful_Trials by CountofTrials and that will be the Value field for CROSSTAB.

Post your query attempt for analysis.

[CubsFan]

I do have the calculation in the value field, but what is happening is that it is performing the ratio calculation at the daily level and then summing the ratio across days to provide an incorrect ratio for the month. If I try to change the VALUE field to EXPRESSION, then i get an error that "your query does not include the specified expression 'iif....." as part of an aggregate

TRANSFORM Sum(iif([CountofTrials]<=0,0, [CountofSuccessful_Trials]/[CountofTrials])) AS [State Ratio]
SELECT tblDailyActivityImport.State
FROM tblDailyActivityImport
GROUP BY tblDailyActivityImport.State
PIVOT Month([ActivityDate]);

[June7]

Provide sample data and desired result.

If you want to provide db for analysis, follow instructions at bottom of my post.

[CubsFan]

Database1.accdb

Thank you for your help. Attached is a sample of the DB with the table and query. The query as it stands is providing a ratio of 3,117.99% for March and 2,520,.23% for April, which are incorrect. The expected ratios for Florida for March and April are 49.58% and 53.25% respectively. Thanks again for any guidance toward how to make this work.

[June7]

Consider:

TRANSFORM Sum(IIf([tblDailyActivityImport]![CountofTrials]<=0,0,[tblDailyActivityImport]![CountofSuccessful_Trials]))/Sum([tblDailyActivityImport]![CountofTrials]) AS [State Ratio]
SELECT tblDailyActivityImport.State
FROM tblDailyActivityImport
GROUP BY tblDailyActivityImport.State
PIVOT Month([ActivityDate]);

If this is multi-year data, will want to have year as another Row Header.

[CubsFan]

Thank you. That did the trick.