Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2014

    Unanswered: Assign numeric value to texts across multiple columns

    Hi all,

    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?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    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:
    CREATE TABLE [Tbl_Errors]
        ( [SysCounter] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,
          [Step1] TEXT(50) NULL,
          [Step2] TEXT(50) NULL,
          [Step3] TEXT(50) NULL,
          [Step4] TEXT(50) NULL,
          [Step5] TEXT(50) NULL,
          [Step6] TEXT(50) NULL,
          [Step7] TEXT(50) NULL,
          [Step8] TEXT(50) NULL
    2. Tbl_Answers is the translation table (i.e. the one that associates each possible answer to a numeric value, either 0 or 1). Its structure is like this:
    CREATE TABLE [Tbl_Answers]
        ( [SysCounter] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,
          [StringValue] TEXT(50) NULL,
          [NumericValue] INTEGER NULL
    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;
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts