Sedang Disiapkan

223194 Compare MS Access Database TBL

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 ([url removed, login to view] and [url removed, login to view]) 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 ([url removed, login to view] and [url removed, login to view]) 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 [url removed, login to view] 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 [url removed, login to view] to [url removed, login to view] when compared against the value in the UserID field.

3. Employees_Changed_EmpCode:

Value in EmpCode field that has changed from [url removed, login to view] to [url removed, login to view] when compared against the value in the UserID field.

4. Employees_Changed_FullName:

Value in FullName field that has changed from [url removed, login to view] to [url removed, login to view] 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 [url removed, login to view] to [url removed, login to view] when compared against the value in the UserID field.

6. Employees_Changed_Phone:

Value in the Phone field that has changed from [url removed, login to view] to [url removed, login to view] when compared against the value in the UserID field.

7. Employees_Changed_EmpType:

Value in EmpType field that has changed from [url removed, login to view] to [url removed, login to view] when compared against the value in the UserID field.

8. Employees_Changed_Status:

Value in Status field that has changed from [url removed, login to view] to [url removed, login to view] when compared against the value in the UserID field.

9. Employees_Changed_StatusCode:

Value in StatusCode field that has changed from [url removed, login to view] to [url removed, login to view] when compared against the value in the UserID field.

10. Employees_Changed_Title:

Value in Title field that has changed from [url removed, login to view] to [url removed, login to view] 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 [url removed, login to view] 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.

Kemahiran: Semua Boleh, Microsoft Access, SQL, Visual Basic

Lihat lebih lanjut: database tbl, key data structure, employee find, tbl database, sql access, ms access, human resources project, database building, convert txt files, compare spreadsheet, access database, database employee, sql update text, sql update field text, convert columns text files, create access database spreadsheet, compare exist, macro script file, write macro, access import txt, create file table access, visual basic access application, data compare, building project sql, compare update

Tentang Majikan:
( 6 ulasan ) Washington,

ID Projek: #1969430