How do I compare values amongst rows on access?

Question

I would like to compare the difference in values between rows for example:

e.g. this is the original table

1 3 4 1 
2 5 6 2
3 6 7 4

I would want a result of

1 2 2 1
1 1 1 2

I know how to compare columns since it is a small number and i can directly reference them. However, my row entry will have thousands and would be impractical.

Do I need to create a for loop using the IIf function? I'm new to access and would appreciate all the help thank you.

Answer 1

Try if below workaround works for you

Added 2 columns ID1, ID2 as Number fields starting from 1 & 2 respectively.

Use below Query

SELECT a.N1-b.N1 As N1, a.N2-b.N2 As N2,  a.N3-b.N3 As N3,  a.N4-b.N4 As N4 from tbl as a
INNER JOIN tbl as b ON a.ID1 = b.ID2

Output