Access SQL query with mathematical logic question

[Rachel]

I have a table where each row represents a certain
company. The columns represent different performance
categories. The column entries are percentage values
identifying the company's performance in that category.
How can I run an Access SQL query that will display the
list of companies that have failed in more than 4
categories (marks less than 50%)?

 

[Dejan]

It is very difficult to achieve what you want because of
the way your table is set up. Lots of IIF functions etc.
Your table is not normalized.

You can redesign the table the following way. Create a
table with only 3 fields
tblNewTable = {CompanyID, Category, CategPerformance}.
Your present table has 1 record per company. The new
table will have X records per company, X being number of
categories. X can even be different from company to
company ;-).Now, a simple query, qryQ1, like this

SELECT CompanyID,Category, CategPerformance WHERE
CategPerformance <0.5

will list all companies with categories with preformace
less than 50%. All you need to know is which companies
are listed 4 or more times in the output of qryQ1. For
that purpose, you can use qryQ2:
SELECT CompanyID, Count(Category) AS FailedCategCnt
FROM qryQ1
GROUP BY CompanyID
HAVING Count(Category)>=4

Benefits of normalization, eh?