 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / June 2005Compare multiple values to reference table for scores
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. 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? 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 > >I have 30 columns of data values equal to S, M, or L. > [quoted text clipped - 16 lines] > > John W. Vinson[MVP] 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, > [quoted text clipped - 26 lines] > > > > 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. 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, > > [quoted text clipped - 15 lines] > > John W. Vinson[MVP] >John, > [quoted text clipped - 6 lines] >pointing me in the right direction toward normalization heaven? I appreciate >the help. 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 > >John, > > [quoted text clipped - 41 lines] > > John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.