Meets 5 or more of 8

T

Tim Sullivan

I have a table that has a company name and 8 different
ratios for the fields. (nine fields in total) What I want
to do is run a query where the results will show me which
companies ratios exceed a certain level in 6 or more of
the ratio fields.
I can then say Company's A,c,r,w exceeded expectations in
6 of 8 categories.

Each ratio will have a different number it has to exceed
 
M

Matthias Klaey

I have a table that has a company name and 8 different
ratios for the fields. (nine fields in total) What I want
to do is run a query where the results will show me which
companies ratios exceed a certain level in 6 or more of
the ratio fields.
I can then say Company's A,c,r,w exceeded expectations in
6 of 8 categories.

Each ratio will have a different number it has to exceed
Click to expand...

Can you post your table structure?

First thing that comes to my mind is that you should rearganize this
into two tables

tblCompany
----------
CompanyName


tblRating
---------
CompanyName
Category
Rating

HTH
Matthias Kläy
 
E

Ed

I would write a query that uses and IIF statement to set a column value to 1
if the ratios exceed the value and a 0 if they do not. I would then write a
second query using the 1st as input and add up the number of 1's. Probably
could be done in a single query but I find nesting them keeps things simpler
sometimes.
 
Top