Compare and show differences ...

[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

[orange]

What did you try? What results did you get? Please show us your work.

[raghuprabhu]

What did you try? What results did you get? Please show us your work.

Will try out your suggestion and post it tomorrow.

Raghu

[raghuprabhu]

test3Query.zipHi Orange,

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

Raghu

[orange]

You should review Normalization and 3rd Normal form. Then structure your tables accordingly.
See the first few topics at http://www.rogersaccesslibrary.com/forum/topic238.html

[raghuprabhu]

test3Query Solved.zipThanks will do.

Cheers

20 May 2012

Here it is solved