434,660 Members | 1,939 Online
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

# How do I design the queries????

 P: 83 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 May 16 '12 #1
4 Replies

 Expert Mod 10K+ P: 12,365 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. May 16 '12 #2

P: 83
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
Attached Files
 test3Query.zip (11.1 KB, 47 views)
May 17 '12 #3

P: 83
@Rabbit
Hi Rabbit,

Solved the problem.
Attached Files
 test3Query Solved.zip (57.8 KB, 68 views)
May 20 '12 #4

 Expert Mod 10K+ P: 12,365 Glad you got it working. Good luck. May 20 '12 #5