Increase, decrease or no change

[Orangecat53]

If I have the following fields [UNIQUE ID],[SURNAME],[PAY CODE],[EFF
DATE],[PAY AMT] how do I get a RESULT of either INCREASE,DECREASE or NO
CHANGE in a new column? Bearing in mind this list could have pay for several
people, but each person would only be compared to themself.

1 JONES 300 01/01/2006 £500 No Change
2 JONES 300 01/02/2006 £500 No Change
3 JONES 300 01/03/2006 £300 Decrease
4 JONES 300 01/04/2006 £800 Increase
5 SMITH 300 01/01/2006 £350 No Change
6 SMITH 300 01/02/2006 £300 Decrease
7 SMITH 300 01/03/2006 £375 Increase

Help, got brain ache...

 

[Allen Browne]

Use a subquery to retrieve the previous value.

For an example, see:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

That example is for a meter reading (difference between current reading and
previous one), so the principle is the same for your case.