Compare multiple values to reference table for scores

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 30 columns of data values equal to S, M, or L. I would like to
compare these values to a reference table and return the corresponding scores
5, 3, or 1, and if no value is found, return a 0. How do I set up my query
to do this?

Thanks,
Melanie
 
I have 30 columns of data values equal to S, M, or L.
Click to expand...

Then your table is incorrectly normalized. It sounds like you have a
one-to-many relationship embedded in each row. Consider changing your
table structure so that you have thirty *records* rather than 30
*fields*!
I would like to
compare these values to a reference table and return the corresponding scores
5, 3, or 1, and if no value is found, return a 0. How do I set up my query
to do this?
Click to expand...

You can create a Query with thirty calculated fields such as:

Val1: Switch(Field1 = "S", 5, Field1 = "M", 3, Field1 = "L", 1, True,
0)

changing the fieldname for each such field.

John W. Vinson[MVP]
 
John,

Thanks for the reply. I tried what you wrote, and it worked. I want to
know if there is any way to reference a table for the values S, M, and L in
the Val1 field, so if I have to make changes, I can update the table rather
than the query.

Thanks,
Melanie
 
You could write a small function to do this

Function ChangeToNumber(valIN)

Select Case Trim(ValIn & vbnullstring)
Case "S"
ChangeToNumber = 5
Case "M"
ChangeToNumber = 3
Case "L"
ChangeToNumber = 1
Case ""
ChangeToNumber = 0
End Function

Save that function in a module and call the function in your query. By the way
make sure that the function and the module do NOT have the same name. If you
name the module ChangeToNumber when you save it, Access will give you an error message.

SELECT ChangeToNumber(Field1) as FldSize1,
ChangeToNumber(Field2) as FldSize2,
ChangeToNumber(Field3) as FldSize3,
....

You could do this in a table and either use the DLookup function or join to the
table once for every one of the thirty fields.

All this extra work comes from the fact that your table structure is not correct
for a relational database.
 
John,

Thanks for the reply. I tried what you wrote, and it worked. I want to
know if there is any way to reference a table for the values S, M, and L in
the Val1 field, so if I have to make changes, I can update the table rather
than the query.
Click to expand...

Yes, you could have a four-row table with the correspondence.

But you would have to create a query joining this table to your
"spreadsheet" THIRTY TIMES - it'll be a monstrous query, probably not
updateable, possibly not even legal.

Do yourself a favor. Normalize your table. You have a one to many
(thirty in fact) relationship; model it as a one to many relationship,
rather than storing data in fieldnames. You'll find that things will
be MUCH easier.

John W. Vinson[MVP]
 
John,

Thanks for the assistance. I normally am pretty good at normalization, but
this one has me stumped. As you can see, I'm recording values for 1100+
processes across 30 categories, with the values equalling severe, moderate,
or low. My thought was to mirror the sort of query that looks at a numeric
value, compares it between a low and high numeric value, and then assigns it
a rating, or score. Apparently, my approach isn't working. Would you mind
pointing me in the right direction toward normalization heaven? I appreciate
the help.

Thanks,
Melanie
 
John,

Thanks for the assistance. I normally am pretty good at normalization, but
this one has me stumped. As you can see, I'm recording values for 1100+
processes across 30 categories, with the values equalling severe, moderate,
or low. My thought was to mirror the sort of query that looks at a numeric
value, compares it between a low and high numeric value, and then assigns it
a rating, or score. Apparently, my approach isn't working. Would you mind
pointing me in the right direction toward normalization heaven? I appreciate
the help.
Click to expand...

Sure. You have a perfectly normal many to many relationship between
Processes and Categories; the proper way to build a many to many
relationship is with three tables. In your case you'll need a tiny
little fourth "lookup" table for the severities. Try something like:

Processes
ProcessID Primary Key
<information about the process>

Categories
Category Primary Key (your current 30 fieldnames would be 30 rows
here; you might or might not want to have a numeric CategoryID. If the
categories are pretty stable I'd just use the category name as the
only field, and Primary Key).

Ratings
ProcessID < link to Processes, what's being rated
Category < link to Categories, what category are you rating
Severity < link to Severities, how bad is it

Severities
Severity Text <L, M, H>
Score Integer

Thus rather than having 30 *FIELDS* in your Processes table - with
categories embedded in the fieldname - you would have 30 *RECORDS* in
the Ratings table. If you decide to drop a category, or add a new
category, it's now very easy; just add or delete a record in the
Categories table. With your current design you will need to alter your
table, all queries involving the table, your form, and all your
reports; with the normalized design - you add a record and you're
done.

John W. Vinson[MVP]
 
John,

Thanks for being so descriptive. I can rest easier this weekend. Luckily,
I haven't built any queries, reports, or forms, so I can focus on just the
table structure now. I greatly appreciate the help!

Melanie
 
Back
Top