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

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.yxmd