I have a master table with some lookup tables.

Master Table: ID Q1 Q2 Q3 etc
001 Good Bad Medium
etc


Lookup Table Q1: Criteria Q1_Value
Good 2
Medium 1
Bad 0

Lookup Table Q2: Criteria Q2_Value
Good 5
Medium 3
Bad 1

Lookup Table Q3: Criteria Q3_Value
Good 9
Medium 5
Bad 2

and so on



I am trying to create query that calculates my total value as well as percentage. So my query should look like:


ID Q1value Q2_value Q3_value Total Max Percentage
1 2 1 5 8 16 50
etc

This is just an illustration, I actually have much more lookup tables and calculation. So in the Max, I put = max(Q1_value) + max(Q2_value) + max(Q3_value). However, since max is aggregrate function, I need to group by ID, Q1_value and so on in order to be able to assign the same max value to each ID. This query really takes long to run and I think there should be a better way to do this. Does anyone have any idea ?

Thanks a lot.