Compare two tables and take difference of matches

[jcyc]

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:

[kgalbert · Accepted 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

[jcyc]

Yes!!! Union was exactly what I needed. Thank you so much!!!!

[tcroberts]

Here's a solution using a similar join as you're trying right now.

[annzhu]

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?

[kgalbert]

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

[annzhu]

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.