| Description: |
This is an urgent project. A bonus will be paid for quick delivery.
Background:
I have an MS Access 2003 database with two (2) (MS Access 2003) tables in it.
A new table is imported every week.
The structure of both tables is identical. (Please see below).
ThisWeek_date LastWeek_date
Table 2
1 EmpNumber
2 FirstName
3 MiddleName
4 LastName
5 FullName
6 EmpType
7 Status
8 StatusCode
9 CostCenter
10 Title
11 Phone
12 MailStop
13 Building
14 Floor
15 Office
16 EmpCode
17 UserID
18 TimeStamp
1 EmpNumber
2 FirstName
3 MiddleName
4 LastName
5 FullName
6 EmpType
7 Status
8 StatusCode
9 CostCenter
10 Title
11 Phone
12 MailStop
13 Building
14 Floor
15 Office
16 EmpCode
17 UserID
18 TimeStamp
I need queries that will compare both tables and look for changes in the tables form one week (or table) to another.
The changes being sought are in the shaded fields (Field 1 to Field 17)
The changes found must then be “written” directly to an MS Access 2003 database report in the same database.
The preference is to develop an individual query and report for each camparison.
Please see below for details of changes, queries and reports on page 2..
Please see below for query and reports names page 3.
Queries/Changes:
Employees_Added:
Values in FullName fields that exist in table ThisWeek_date but do not exist in table LastWeek_date.
Employees_Changed CostCenter:
Value in CostCenter field that has changed in table ThisWeek_date to table LastWeek_date when compared against the value in the UserID fields for both tables.
Employees_Changed EmpCode:
Value in EmpCode field that has changed in table ThisWeek_date to table LastWeek_date when compared against the values in the UserID fields for both tables.
Employees_Changed FullName:
Value in FullName field that has changed in table ThisWeek_date to table LastWeek_date when compared against the value in the UserID fields for both tables.
Employees_Changed Location:
Values in the Building, Floor and MailStop fields that have changed form table ThisWeek_date to table LastWeek_date when compared against the value in the UserID fields for both tables.
Employees_Changed Phone:
Value in the Phone field that has changed in table ThisWeek_date to table LastWeek_date when compared against the value in the UserID fields for both tables.
Employees_Changed EmpType:
Value in EmpType field that has changed in the table ThisWeek_date to Table LastWeek_date when compared against the value in the UserID fields for both tables.
Employees_Changed Status:
Value in Status field that has changed in the table ThisWeek_date to Table LastWeek_date when compared against the value in the UserID fields for both tables.
Employees_Changed StatusCode:
Value in StatusCode field that has changed in the table ThisWeek_date to table LastWeek_date when compared against the value in the UserID fields for both tables.
Employees_Changed Title:
Value in Title field that has changed in the table ThisWeek_date to Table LastWeek_date when compared against the value in the UserID fields for both tables.
Employees_Removed:
Values in UsedID, FirstName, MiddleName, LastName and FullName fields that do not exist in the table for ThisWeek_date but, exist in the table from LastWeek_date.
Query Names:
Employees Added
Employees Removed
Employees Changed CostCenter
Employees Changed EmpCode
Employees Changed FullName
Employees Changed Location
Employees Changed Phone
Employees Changed EmpType
Employees Changed Status
Employees Changed StatusCode
Employees Changed Title
Report Names:
Employees Added
Employees Removed
Employees Changed CostCenter
Employees Changed EmpCode
Employees Changed FullName
Employees Changed Location
Employees Changed Phone
Employees Changed EmpType
Employees Changed Status
Employees Changed StatusCode
Employees Changed Title
Additional Info (Added 5/20/2008 at 14:55 EST)...Attached file: MS Access Table Comparison DB.zip
File info: Sample MS Access Database
Additional Info (Added 5/22/2008 at 13:44 EST)...Attached file: ThisWeek_date.txt
Additional Info (Added 5/22/2008 at 13:53 EST)...Removed file: ThisWeek_date.txt
Additional Info (Added 5/22/2008 at 13:57 EST)...Attached file: Data Files.zip
File info: Raw Text Files
|
(2 reviews)
