I got a TABLE in Excel format like: enter image description here

This is a STEN TABLE containing FACTORS (A,B,C,D,E....etc)

Each Factor is like a separate test.

Each Factor i.e. Factor A has a [Raw Score] and a [Sten]

Supposing Factor A, had a question like:


Why do humans have Eyes?

The answer options could be like:

a) To Watch movies = [Raw Score] -> 10,

b) To Read Novels = [Raw Score] -> 5,

c) To close them while sleeping = [Raw Score] ->0

So if they Chose a) then, the system will go to the STEN TABLE to get the STEN Equivalent under Factor A, in this case, the sten equivalent will be 4. (See Factor A->Row score 10-> Sten column)

What could be the most practical way to have this STEN TABLE with Factors and their Raw Scores and Stens created?

Smothing like

              **STEN TABLE**
                **FACTORS (A,B,C,D...)**
                      /     \
                     /       \
                    /         \
                   /           \
                  /             \
            **[Raw Score]      [Sten]**


To See a larger image, please click here: http://ctrlv.in/459785

Please note that, the stens are not equal for all Factors, though the Raw Scores are the same. i.e. in Factor A, [Raw Score]->3 = [sten]->2 but in Factor C [Raw Score]->3 = [sten]->1 and in Factor F [Raw Score]->1 [sten]->2 whereas in Factor E [Raw Score]->1 = [sten]->1.

Thank you.

Any Suggestion is highly honored.


I'd pull this data into two tables: Factor's and STEN Lookup's.

Your Factors table would look like:

Factor_ID(PK) | Factor_Name
  1           |      A
  2           |      B
  3           |      C
  4           |      D

Your `STEN_Lookup'

Factor_ID(FK) | Score_Value | STEN_Value
  1           |       0     |       1
  1           |       1     |       1
  1           |       2     |       1
  1           |       3     |       2
  1           |       4     |       2
  2           |       0     |       8
  2           |       1     |       9
  2           |       2     |       11

I'd suggest you do some reading on normalization, as it helps massivly when designing your tables. A great Video Resource is MVA, (Video 2 on this hyperlink).

  • Thanks for the Idea, but please not that the sten values are not equal for all Factors. Please see EDIT 1... Thank you – – Universal Grasp Nov 11 '14 at 10:09
  • PLease click here to see a larger image: ctrlv.in/459785 – Universal Grasp Nov 11 '14 at 10:10
  • @UniversalGrasp Please look in detail to STEN_Lookup table- you can set different values for each score AND factor. His solution is ok with your reqirements. It is just that the PK is (Factor_ID, Score_Value). – jynus Nov 11 '14 at 10:12
  • I've just made it a little clearer so you can see. In cases where the STEN_Value differs from the previous factor score, you just need to change the data. So for Factor 2 students a score of one gives a STEN of 9, but for Factor 1 Students a score of one gives a STEN of one. – KidCode Nov 11 '14 at 10:15
  • @KidCode Thank You... So if I understand your suggestion well, you mean, Fact_ID->1=Score_Value->[0 OR 1 OR 2 OR..4] = STEN_Value->[1 OR ...2]... This is Super great, however, following this, then we might have an issue with FactorB because, it's STEN_Value will start from 8instead of 1: i.e Fact_ID->2=Score_Value->[0] = STEN_Value->[8] which is meant to be Fact_ID->2=Score_Value->[0] = STEN_Value->[1].... Thanks again though. – Universal Grasp Nov 11 '14 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.