Hi I have a table called tblOffset. It has the following data
ID Surname Code1 Code2 Code3 Code4 Amount payPeriod
1 Prabhu aaa bbb ccc ddd $1,234.00 2012/21
2 Jain ccc ddd eee fff $1,233.00 2012/21
3 Kumar ddd eee fff ggg $1,245.00 2012/21
4 Singh aaa ddd eee ddd $1,236.00 2012/12
5 Reddy aaa ddd fff fff $1,122.00 2012/12
6 Prabhu ggg ddd ddd ccc $2,222.00 2012/22
7 Jain ccc ddd eee fff $1,233.00 2012/22
8 Kumar ddd eee fff ggg $1,245.00 2012/22
9 Singh aaa ddd eee ddd $1,236.00 2012/22
10 Reddy aaa ddd fff fff $1,122.00 2012/22
11 Arora ddd ddd ddd ddd $2,212.00 2012/22
12 Prabhu ggg ddd ddd ccc $2,222.00 2012/23
13 Kumar ddd eee fff ggg $1,245.00 2012/23
14 Singh aaa ddd eee ddd $1,236.00 2012/23
15 Reddy aaa ddd fff fff $1,122.00 2012/23
16 Arora ddd ddd ddd ddd $2,212.00 2012/23
I want to design 3 queries. All the queries have to compare records two consecutive pay periods at a time.
The first 5 records are in pay period 2012/21, 6 in pay period 2012/22 and 5 in pay period 2012/23.
First query: Here record 1 and 6 are for the same person but the fields Code1, Code2, code3, code 4 and amount have changed in pay period 2012/22. How do I compare records in pay period 2012/21 and 2012/22 and show these two records as the query out put?
Second query: Record 11 is a new record input in pay period 2012/22. It was not there in pay period 2012/21. How do I compare records in pay period 2012/21 and 2012/22 and show this record as the query out put?
Third query: Record 7 was input in pay period 2012/22 but it was omitted in pay period 2012/23. How do I compare records in pay period 2012/22 and 2012/23 and show this record as the query out put?
Thanks in advance
Raghu Prabhu