By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
4 Replies


Rabbit
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
File Type: zip test3Query.zip (11.1 KB, 47 views)
May 17 '12 #3

P: 83
@Rabbit
Hi Rabbit,

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

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

Post your reply

Sign in to post your reply or Sign up for a free account.