Calculating a ratio (1 Viewer)

[Zorkmid]

Hi there,

I have a field in a table that only has 2 possibilities: Near Miss or Actual Event. How can I create a ratio of one to the other, i.e. count the number of Actual Events by the number of near misses?

-Matt

 

[Brianwarnock]

I don't understand this

count the number of Actual Events by the number of near misses?

Isn't the ratio near misses/actual events or even
near misses/actual events + near misses

totmisses: sum(iif[field]="near misses",1,0)
totact:sum(iif([field]="actual event",1,0)
ratio: totmisses/totact

Or something like that

Brian

 

[Zorkmid]

I don't understand this

Isn't the ratio near misses/actual events or even
near misses/actual events + near misses

totmisses: sum(iif[field]="near misses",1,0)
totact:sum(iif([field]="actual event",1,0)
ratio: totmisses/totact

Or something like that

Brian

Yea sorry, I meant divided by.

 

[Zorkmid]

Tried this idea. I keep getting the same error I did before.

"You tried to execute a query that does not include the specified expression "Near Miss" as a part of an aggregate function"

-Z

 

[Brianwarnock]

Tried this idea. I keep getting the same error I did before.

"You tried to execute a query that does not include the specified expression "Near Miss" as a part of an aggregate function"

-Z

We will not be able to help until you post your SQL

Brian

 

[Zorkmid]

I don't use SQL, I'm just trying to do it in a design view query

 

[Brianwarnock]

The Design grid generates the SQL, go to the icon and click the down arrow instead of view or design select SQL and copy and paste it into a post.

Brian

 

[Zorkmid]

Oh, I see thanks

Here you go:

SELECT Risk_Data.[Near Miss], Sum(IIf([Risk_Data]![Near Miss]="Near Miss",1,0)) AS totmisses, Sum(IIf([Risk_Data]![Near Miss]="Actual Event",1,0)) AS totact, [totmisses]/[totact] AS ratio
FROM Risk_Data;

Im still getting the error
"You tried to execute a quesry thyat does not include the specified expression 'near miss' as part of an aggregate function."

Thanks

-Z

 

[Brianwarnock]

The sum function forces this to be a totals query and therefore all fields must have an aggregate function, you need to group on near miss if you require to show that field. Either simply add after the Fromand before the ;
Group By Risk_Data.[Near Miss]

In the DG you will see the effect

Brian

 

[Zorkmid]

SELECT Risk_Data.[Near Miss], Sum(IIf(Risk_Data![Near Miss]="Near Miss",1,0)) AS totmisses, Count(IIf(Risk_Data![Near Miss]="Actual Event",1,0)) AS totact, [totmisses]/[totact] AS ratio
FROM Group By Risk_Data.[Near Miss];

Now I'm getting a syntax error in FROM clause.

 

[Brianwarnock]

Sorry I wasn't clearer

SELECT Risk_Data.[Near Miss], Sum(IIf([Risk_Data]![Near Miss]="Near Miss",1,0)) AS totmisses, Sum(IIf([Risk_Data]![Near Miss]="Actual Event",1,0)) AS totact, [totmisses]/[totact] AS ratio
FROM Risk_Data
Group by Risk_Data.[Near Miss];

Brian