How do I design the queries????

[hrprabhu]

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

[Rabbit]

For the first query, join the table to itself on the pay period to the next pay period.

On the second query, do the same thing except using an right outer join and where the left side is null.

On the third query, do the same thing as the second query except reverse the right and left.

[hrprabhu]

Thanks will try out the suggestion and get back to you re the out come.

18 May 2012

Hi Rabbit,

Attached a sample database. Was able to do the second and third queries but not the first one.

Cheers
Raghu

[hrprabhu]

For the first query, join the table to itself on the pay period to the next pay period.

On the second query, do the same thing except using an right outer join and where the left side is null.

On the third query, do the same thing as the second query except reverse the right and left.

Hi Rabbit,

Solved the problem.

[Rabbit]

Glad you got it working. Good luck.