simplyfy query

[Bunce]

Here is the scenario:

The teacher is evaluating math, science and writing scores of his students and award them with prices. Mark got A's in all three subjects will receive $50, Martha got 2 A's and 1 B will receive $40 and Megan got all B's will receive $20 etc. I have one table for each subject.

Step 1: pull in all three scores T1 & T2 & T3

The result from step 1 is as follows:
A&A&A
A&A&B
B&B&B

Step 2: import a table look like this:
Score Award
A&A&A 50
A&A&B 40
B&B&B 20

Step 3: look at score column and return the value from the award column from step 2.

I've only written queries in design view and not an experienced Access user. What is the best approach to go about this, does it need more than 1 query to do step 1 and step 3?. I have never done a make table query before. Your guidance would be greatly appreciated.

 

[Galaxiom]

It would be easier if the teacher used numbers for Scores. Instead of A; B and C use 3; 2 and 1.

Then you would Union query the subject tables, GROUP BY Student and SUM the Scores field. This would give a total score for each student.

Build a Reward table with two fields, TotalScore (format Integer) and Prize (format Currency).
Enter records 9,50; 8,40 and 7,20

In another query join this table to the TotalScores query on the field TotalScore. Report on Student, Prize.

If you must use Letter Scores:
Make a table with two fields Score and Points. Make three records A,3; B,2 and C,1

Join this table to each of the Subject tables and modify the TotalScores query to Sum the Points field instead of the Score field.