Home | How it works | Projects archive | Contact Us
Air Compressor Bot 		 
The Career Path of Freelance Programming Jobs 

   Compare Ms Access Database Tbl
Bidding Time:
15/05/2008 18:13 - 29/05/2008 18:13
Budget:
N/A
Status:
 Pending


Job Type:
Visual Basic, MS SQL, MS Access
Description:
 Background:

I have an employee database in an MS SQL 2000 environment. Every week on a
Friday evening the database is updated with the latest information form the
Human Resources department in the form of a semi colon delimited text file. The
update overwrites the entire database and replaces all records with the most
current records. The update is done by means of a utility from within a third
party application which has “write” access to the employee database. The
intention is to import the raw “SOURCE” text files (LastWeek_date.txt and
ThisWeek_date.txt) into an MS Access database as tables and then do the
comparison from within MS Access. The objective of the comparison is to find
differences in the employee records from week to week. See next sheet for
spreadsheet details. The text files are identical in structure. The text file
has 18 columns/fields. The text file has between 8,000 and 12,000 rows.


Requirement:

I require an application that will import the “SOURCE” text files of employee
data into MS Access, compare the contents and output the results of the
comparison to new MS Access tables.

Part One:

Create a script/macro/engine to import the two (semi colon) delimited “SOURCE”
text files (LastWeek_date.txt and ThisWeek_date.txt) from within a folder into
MS Access and convert them into MS Access tables. The tables shall be renamed
tbl_LastWeek_date and tbl_ThisWeek_date.

Part Two:

Create a script/macro/engine to compare the records (rows/columns) of
tbl_LastWeek_date and tbl_ThisWeek_date using the eleven (11) criteria (see
below) I have selected as the trigger and then to output the changes or
differences to eleven (11) new MS Access tables within the same database.

New Table Names:

The names of the 11 new output tables shall be

1. tbl_Employees_Added
2. tbl_Employees_Changed_CostCenter
3. tbl_Employees_Changed_EmpCode
4. tbl_Employees_Changed_FullName
5. tbl_Employees_Changed_Location
6. tbl_Employees_Changed_Phone
7. tbl_Employees_Changed_EmpType
8. tbl_Employees_Changed_Status
9. tbl_Employees_Changed_StatusCode
10. tbl_Employees_Changed_Title
11. tbl_Employees_Removed

Criteria:

1. Employees_Added:
Values in UsedID, FirstName, MiddleName, LastName and FullName fields that exist
in the data file from ThisWeek_date.txt but do not exist in the data file for
LastWeek_date.txt.

2. Employees_Changed_CostCenter;
Value in Cost Center field that has changed from ThisWeek_date.txt to
LastWeek_date.txt when compared against the value in the UserID field.

3. Employees_Changed_EmpCode:
Value in EmpCode field that has changed from ThisWeek_date.txt to
LastWeek_date.txt when compared against the value in the UserID field.

4. Employees_Changed_FullName:
Value in FullName field that has changed from ThisWeek_date.txt to
LastWeek_date.txt when compared against the value in the UserID field.

5. Employees_Changed_Location:
Values in the Building, Floor and MailStop fields that have changed form
ThisWeek_date.txt to LastWeek_date.txt when compared against the value in the
UserID field.

6. Employees_Changed_Phone:
Value in the Phone field that has changed from ThisWeek_date.txt to
LastWeek_date.txt when compared against the value in the UserID field.

7. Employees_Changed_EmpType:
Value in EmpType field that has changed from ThisWeek_date.txt to
LastWeek_date.txt when compared against the value in the UserID field.

8. Employees_Changed_Status:
Value in Status field that has changed from ThisWeek_date.txt to
LastWeek_date.txt when compared against the value in the UserID field.

9. Employees_Changed_StatusCode:
Value in StatusCode field that has changed from ThisWeek_date.txt to
LastWeek_date.txt when compared against the value in the UserID field.

10. Employees_Changed_Title:
Value in Title field that has changed from ThisWeek_date.txt to
LastWeek_date.txt when compared against the value in the UserID field.

11. Employees_Removed:
Values in UsedID, FirstName, MiddleName, LastName and FullName fields that do
not exist in the data file for ThisWeek_date.txt but, exist in the data file
from LastWeek_date.txt.

Table Structure:

Fields

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

a. Please note that filed number 17, the field named UserID is the primary key
field.

b. Of specific interest are changes in the shaded fields.




A sample of the text file has been attached to this project.


Criteria and Triggers:

(See table structure and eleven criteria below)

Employees_Added Values in UsedID, FirstName, MiddleName, LastName and FullName
fields that exist in the data file from ThisWeek_date but do not exist in the
data file for LastWeek_date.
Employees_Changed_CostCenter Value in Cost Center field that has changed from
ThisWeek_date to LastWeek_date when compared against the value in the UserID
field.
Employees_Changed_EmpCode Value in EmpCode field that has changed from
ThisWeek_date to LastWeek_date when compared against the value in the UserID
field.
Employees_Changed_FullName Value in FullName field that has changed from
ThisWeek_date to LastWeek_date when compared against the value in the UserID
field.
Employees_Changed_Location Values in the Building, Floor and MailStop fields
that have changed form ThisWeek_date to LastWeek_date when compared against the
value in the UserID field.
Employees_Changed_Phone Value in the Phone field that has changed from
ThisWeek_date to LastWeek_date when compared against the value in the UserID
field.
Employees_Changed_EmpType Value in EmpType field that has changed from
ThisWeek_date to LastWeek_date when compared against the value in the UserID
field.
Employees_Changed_Status Value in Status field that has changed from
ThisWeek_date to LastWeek_date when compared against the value in the UserID
field.
Employees_Changed_StatusCode Value in StatusCode field that has changed from
ThisWeek_date to LastWeek_date when compared against the value in the UserID
field.
Employees_Changed_Title Value in Title field that has changed from ThisWeek_date
to LastWeek_date when compared against the value in the UserID field.
Employees_Removed Values in UsedID, FirstName, MiddleName, LastName and FullName
fields that do not exist in the data file for ThisWeek_date but, exist in the
data file from LastWeek_date.


Next:
Create 3 Web Pages from Custom Layout -

Mysql Database Transfers -

Tube style script -

Press Scores -

Finish Real Estate site -


This project is the proprietary information of . Click here to remove this project from OUR database.
Operating System:
 N/A
Database System:
 N/A

<<< back

Recent Projects Archive:

Sunday - Saturday - Friday - Thursday - Wednesday - Tuesday - Monday

View all freelance web projects
 
Home | Projects archive | RSS | Resources | Links | Contact Us © 2004-2007 ProjectsList.biz /0.467