Hi, I am trying to compare two sets of data: Test 1 Scores and Test 2 Scores. The output result would be a table that shows the difference in scores between Test 1 and 2 for each individual.
The problem I am having is that on Test 2, a new person joined the class which means the person (Gene) did not take Test 1, which is ok. However, I needed the output results to show Gene's difference in test results is NULL (or blank).
I tried doing a Join on field "Name", "Age", and "Full time?" but it didn't work.
Here are my tables:
Test 1:
Test 2:
Output Result Needed:
Solved! Go to Solution.
Hey jcyc,
Joining on Name, Age, and [Full Time?] is the correct thing to do, but then you need to union the L, J, and R exits from the join in order to bring in the people that didn't take test 1 or test 2.
See attached. The formula tool is looking to see if a person missed either test 1 or test 2 and then puts a NULL in the test difference column. Else it subtracts Test 2 Score from Test 1 Score.
Thanks,
Ken
Yes!!! Union was exactly what I needed. Thank you so much!!!!
What if I want the return of NULL to be replaced by a score? say the score of 2nd test instead of NULL? How to achieve that?
Hi Annzhu,
It might get a little confusing to have a test score in a column that has test score differences, but this is how I would do it (assuming the person took at least 1 test):
For the [Test Score Difference] column formula:
if isnull([Test 1 Score])
then [Test 2 Score]
elseif isnull([Test 2 Score])
then [Test 1 Score]
else [Test 2 Score] - [Test 1 Score]
endif
This would put their test score into the Test Score Difference field. In other words:
If the first test score is missing --> Use the Second Test Score
If the second test score is missing --> Use the First Test Score
Else take their difference.
Hope this helps,
Ken
Thank Ken. Don't know how to apply the IF functions so I used JOIN and treat L,R,J output differently, after that, UNION the result together. It might be a longer way? It could get the result though.