Unanswered: Assign numeric value to texts across multiple columns
I designed a database to monitor quality in my office, with no previous experience . The user checks the work of others and indicates where errors are made and what the errors are. So I have multiple columns representing each part of the process where an error can be made, and for each I have a combobox listing each error which could occur.
I would like to create a single query which shows the error rate for each employee in each column (representing the section of the process). By default, each error indicating column is set to either "correct" or "yes". So I think the way to go is to assign a 0 value to the texts "correct" and "yes", and 1 to all other texts. I've created a new table containing all unique texts in one column and either 1 or 0 in another column.
Could anyone please suggest how I can create a query which will convert the text values to numeric and then aggregate them?
A quick and dirty solution (I'm sure there must be a more elegant way to get the results), though a functional one.
As you did not provide information about the structure of the tables, I used these:
1. Tbl_Errors is the table where the results of the inspections are stored ("yes", "correct", etc.). Its structure is like this:
3. The query that yields the results. Its a bit strange because I used the query builder wich sometimes insert parentheses at unexpected places to create the relationships and I did not try to change what it provided. In this query, Null values in Tbl_Errors are counted as 1 (error: hence the use of the Nz() function). To ignore such Null values, change the Joins to INNER JOINS (and you can remove the Nz() functions). Here is the SQL:
SELECT Tbl_Errors.SysCounter, Nz(Tbl_Answers_6.NumericValue, 1)
+ Nz(Tbl_Answers_5.NumericValue, 1)
+ Nz(Tbl_Answers_4.NumericValue, 1)
+ Nz(Tbl_Answers_3.NumericValue, 1)
+ Nz(Tbl_Answers_2.NumericValue, 1)
+ Nz(Tbl_Answers_1.NumericValue, 1)
+ Nz(Tbl_Answers.NumericValue, 1) AS NumberOrErrors
FROM Tbl_Answers AS Tbl_Answers_6 RIGHT JOIN
(Tbl_Answers AS Tbl_Answers_5 RIGHT JOIN
(Tbl_Answers AS Tbl_Answers_4 RIGHT JOIN
((((Tbl_Answers RIGHT JOIN Tbl_Errors ON Tbl_Answers.StringValue = Tbl_Errors.Step1) LEFT JOIN
Tbl_Answers AS Tbl_Answers_1 ON Tbl_Errors.Step2 = Tbl_Answers_1.StringValue) LEFT JOIN
Tbl_Answers AS Tbl_Answers_2 ON Tbl_Errors.Step3 = Tbl_Answers_2.StringValue) LEFT JOIN
Tbl_Answers AS Tbl_Answers_3 ON Tbl_Errors.Step4 = Tbl_Answers_3.StringValue)
ON Tbl_Answers_4.StringValue = Tbl_Errors.Step5)
ON Tbl_Answers_5.StringValue = Tbl_Errors.Step7)
ON Tbl_Answers_6.StringValue = Tbl_Errors.Step8;