You are here:

Using MS Access/Switch Function using multiple criteria

Advertisement


Question
QUESTION: Hi Scott,
I am struggling in writing a query that can transform raw scores into standardized scores -- its complexity is that the standardized scores are determined based on multiple criteria. Let me provide you the brief intro and a couple of examples to give you a better idea.
Check the table below. The first 3 variables (Age, Gender, and Raw Scores) determine what standardized scores this student will receive.  
[Age] has three categories: 1, 2, 3
[Gender] has two categories: 1, 2
Age          Gender          Raw Scores          Standardized Scores
1          1          63+          160
1          1          62          159
and so on….
1          1          0          77
____________________________________________________
2          1          64+          160
2          1          63          159
and so on….
2          1          0          81
_____________________________________________________
3          1          58+          160
3          1          57          159
and so on….
3          1          0          82
_______________________________________________________
1          2          76+          160
1          2          75          159
and so on….
1          2          0          77
________________________________________________________
2          2          69+          160
2          2          68          159
and so on….
2          2          0          80
_________________________________________________________
3          2          69+          160
3          2          68          159
and so on….
3          2          0          85

For example, if a student is in the category of “1” in [Age] AND  in the category of “1” in [Gender] AND  has [Raw Scores] of “63”, then her standardized scores is 160.
Another example: If another student is in the category of “2” in [Age]  AND in the category of “1” in [Gender] AND  has [Raw Scores] of “63”, then her standardized scores is 159.
After searching for hours, I think SWITCH function might be the proper one for me to use in this case, but I am not positive. Scott, could you please provide some guidance regarding what is the best ACCESS function to meet my need? I never got exposure to Switch function before, so I’d appreciate if you can help me to understand your SQL better by using simple direct language.  
Thank you so much! I very much forward to hearing from you!
Melody

ANSWER: How are the standardized scores arrived at? Do you have a table of scores like this:

Age
Gender
Raw
Standard

I think while Switch may be viable, it would be too complex and hard to debug. I think a custom function might be better, but I need to know how you arrive at the standardized score.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

Table PrtSc Part2
Table PrtSc Part2  
Table PrtSc Part1
Table PrtSc Part1  
QUESTION: Hi Scott,
Yes, I was given a table that contains four columns like you listed. Given the limitation of file format on this site, I was only allowed to attach you two images (print screen), which is partial of the table. I can send you the whole table (in excel file) if you prefer.  
But there is no pattern existing among those data based on my observation. To be more specific, person A and person B both have the same raw scores, but their standardized scores are very likely different due to their age and gender.
Let me know if you need more information. I appreciate your help and your precious time!

ANSWER: OK, in this case, its easy. Just create a query that joins your data table to the score table on all three fields (Age, Gender and Raw score) and pull the Standardized score from the score table.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Scott, I had hard time to follow you (sorry! I am a novice at Access). Do you mean that Switch function is proper solution? Could you please kindly provide a more detailed guidance? Actually, a step-by-step procedure would be even more helpful for me to catch up if you don't mind.
Again, I am very grateful at receiving your valuable advice!

Best,
M.

Answer
No, Switch is not necessary or viable. The solution is simple. Open Query Design Mode and add the two tables (the data table and the code table). Join the two tables on the three fields (drag the Age, Gender and Raw Score fields from the data table onto the corresponding fields in the Code table). Then add the columns you want including the Standardized score field from the Code table.

The resulting query should show the standardized score from the code table.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.