Compare records in different pay periods and ...

[raghuprabhu]

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

 

[plog]

You are going to need to create a sub-query that determines the next pay period for each record. Then, to do what you want you would create a new query with tblOffset linked to the sub-query which in turn is linked to another instance of tblOffset. This would be a lot easier to do with a properly structured database.

You've got a few issues with your structure--Surname should be an id that links to a table of people, you shouldn't have numerated field names (Code1, Code2, Code3, etc.) instead this data should be held in another table. However those aren't really issues with what you want to do now. The structuring issue with your database that you have is that payPeriod doesn't easily lend itself to calculating the next pay period. I'm guessing its a text field--it shoudl either be one date field or a numeric field.

What you need to do with the sub-query is produce results like this:

Surname, CurrentPeriod, NextPeriod
Prabhu, 2012/21, 2012/22
Jain, 2012/21, 2012/22
...
Prabhu, 2012/22, 2012/23
Jain, 2012/22, 2012/23

Once you have a query that can produce that you will be able to use it to link two instances of tblOffset to each other and get a pay periods data as well as the following one. With a numeric payPeriod field its as easy as adding 1 to it, with a date you could use DateAdd, with what you have now, its going to be more difficult--either a VBA function or some complex caluclating code within the query itself.

 

[raghuprabhu]

Thanks Plog will try out your suggestion and see how it works.

18 May 2012

Hi Plog,

I have attached a small database. Please have a look at it.

Thanks

Raghu

PS 20 May 2012

Have been able to solve.
Cheers