Hello Experts,
I would like to do calculation based on a query. Let me give you an example of the table structure. Let call it Project. Each officer will score the project like following example below. There are some null values in the record.
ProjectID Officers Item1 Item2 Item3 Item4 Item5 Item6 Item7
50 Mr. A 1 2 3 1 0 1 2
50 Mr. B 1 0 3 0 1 2
50 Mr. C 1 2 3 0 0 0 1
50 Mr. D 0 2 3 1 0 1 0
50 Ms. E 1 3 2 2
50 Ms. F 2 1 2 2 1 3 3
50 Ms. G 3 1 2 2
50 Ms. H 0 1 3 2 2 3
50 Ms. I 1 2 2 3 3 3 1
50 Mr. J
50 Mr. K 3 2 1 1 2 2
52 Mr. A 0 1 0 0 3 1 1
Each project will have 12 officers to record scoring from item1 to item7.
Conditions:
a.Score on each item will be null or less than 3
b.Each item has its own weight. For example, Item1 weights 2 points. Item2 = 4; Item3 =4; Item4 = 3; Item5 = 2; Item6 = 1; and Item7 = 1.
What we need to do:
1. Create a query that will sum Item1 to Item7, even with Null value. I will call ScoreTotal. For example, total score for Project # 50 of Mr. A, will be equal to [1*2]+[2*4]+[3*4]+[1*3]+Null+[1*1]+[2*1], which give us the result of: 29 ; Mr. B will be equal to 18 and so on..
2. After getting the ScoreTotal for all 12 Officers, I need to average them into one number for each project. We have to ensure that the null value will not be calculated. There will always be 12 officers for the project. We can name this field or function: AverageProjectScore. However, some officers might decide to skip in giving the score, such as officer J.
I do appreciate your help. I am stucking with these null value. I cannot use a simple average because some officers might skipping the score. I cannot just divide by 12.
Thanks again.
I would like to do calculation based on a query. Let me give you an example of the table structure. Let call it Project. Each officer will score the project like following example below. There are some null values in the record.
ProjectID Officers Item1 Item2 Item3 Item4 Item5 Item6 Item7
50 Mr. A 1 2 3 1 0 1 2
50 Mr. B 1 0 3 0 1 2
50 Mr. C 1 2 3 0 0 0 1
50 Mr. D 0 2 3 1 0 1 0
50 Ms. E 1 3 2 2
50 Ms. F 2 1 2 2 1 3 3
50 Ms. G 3 1 2 2
50 Ms. H 0 1 3 2 2 3
50 Ms. I 1 2 2 3 3 3 1
50 Mr. J
50 Mr. K 3 2 1 1 2 2
52 Mr. A 0 1 0 0 3 1 1
Each project will have 12 officers to record scoring from item1 to item7.
Conditions:
a.Score on each item will be null or less than 3
b.Each item has its own weight. For example, Item1 weights 2 points. Item2 = 4; Item3 =4; Item4 = 3; Item5 = 2; Item6 = 1; and Item7 = 1.
What we need to do:
1. Create a query that will sum Item1 to Item7, even with Null value. I will call ScoreTotal. For example, total score for Project # 50 of Mr. A, will be equal to [1*2]+[2*4]+[3*4]+[1*3]+Null+[1*1]+[2*1], which give us the result of: 29 ; Mr. B will be equal to 18 and so on..
2. After getting the ScoreTotal for all 12 Officers, I need to average them into one number for each project. We have to ensure that the null value will not be calculated. There will always be 12 officers for the project. We can name this field or function: AverageProjectScore. However, some officers might decide to skip in giving the score, such as officer J.
I do appreciate your help. I am stucking with these null value. I cannot use a simple average because some officers might skipping the score. I cannot just divide by 12.
Thanks again.
