Comparing Employee Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am not a Access guru, hoping someone can help with this...

I have two databases that contain employee data. One is managed by HR, and
the other by the training department. There are some differences in the
records caused by updates being made in one database and not the other when
promotions, transfers, etc take place. I have been advised that the HR
database is 100% correct because errors would be caught during payroll
activities.

Here is what I would like to do. I have a primary key which is the employee
number. I need to validate/compare the information in the Training database
against what I have been told is correct in the HR database. Result would be
a list, or exception report, that shows me which employee records in the
Training database do not match those in the HR database. I would use these
results to update the training database.

There are three main criteria I want to compare:
Position Title
Location
Supervisor

I do not mind if this is done in one query or three.

Example would be like this:
HR Database shows employee #123 as the Sales Manager in Atlanta reporting
to Jane Doe...
while the Training Database shows employee #123 as the Sales Representative
in Atlanta reporting to John Doe.

Any suggestions?

Thanks,

Scott
 
Sounds to me like the Employee basic data should live in a database and be
shared by the two departments. Why duplicate the work?

Link the HR database to a database where the employee basic data lives.

Link the Training database to a database where the employee basic data
lives.

Then, you have all the data in one place, but both departments can access
it. To resolve your current problem, you'd simply delete the bad table and
start using the new layout above.


Rick B
 
Rick,

Thanks for the reply. I wish I could achieve what you mentioned, but both
database are necessary at this point. There is discussion about combining
both down the road, but at the rate organizations make these kinds of steps I
will be a long time with bad data. Hence the need to scrub what I currently
have.

Do you know how I can write a query to do what I described in my orignal note?
 
You don't need to do this manually!
1. Open the Training database to the database window and go to the tables
tab
2. Click on File - Get External Data - Link and link to the HR database.
Link specifically to the employee table in the HR database.
3. Create a query that includes the employee table from both databases.
4. Include the Position Title, Location and Supervisor fields from the
Training database in your query.
5. Click on the query types button on the toolbar at the top of the screen
and change your query to an update query.
6. Where it says "Update To" under each field, enter the corresponding
field in the HR database
7. Run your query and the three fields will automatically be updated to
the values in the HR database.
 
PC Datasheet,

Thanks!

I do want to produce a report before I correct so I can show HR how bad it
really is and get some support to change the process so this doesn't happen
again.

How would I produce the report?
 
Scott, I agree with Rick. This is just not worth your time or their
resources and money. The Employee database is obviously up to date
and therefore the basis for your core data.

You do not have to "combine" them, just have your database link to
their employee table for the name and details.

If they are worried about the privacy of certain data then that too
can be easily handled with a your database creating a local table on
startup via Make Table with only the fields that are non-personal.

Any way is better than what you are trying to do because it is never
going to be up to date and valuable time and resources will be
continually required to keep them aligned.

I bet we can all come up with an answer. What platform is the
employee database on?

Cheers

Brett


Rick,

Thanks for the reply. I wish I could achieve what you mentioned, but both
database are necessary at this point. There is discussion about combining
both down the road, but at the rate organizations make these kinds of steps I
will be a long time with bad data. Hence the need to scrub what I currently
have.

Do you know how I can write a query to do what I described in my orignal note?
Click to expand...

Cheers,
Brett
 
Do steps 1 to 4. You can add whatever other fields you need for your report.

Set the criteria for Position Title as:
<> NameOfPositionTitleFieldInHRDatabase
Go down one line in the criteria section, and set the criteria for Location
as:
<> NameOfLocationFieldInHRDatabase
Go down one line in the criteria section, and set the criteria for
Supervisor as:
<> NameOfSupervisorFieldInHRDatabase

If you have entered the criteria correctly, each criteria should be in a
separate row.

When you run the query, it will return all records in the Training database
where any one or more of the three fields in the Training database does not
match its corresponding field in the HR database.

Use the report wizard to create a report from this query.

Email me at my email address below for further discussion.
 
Thanks for the reply Brent.

The HR database is ADP HRPerspectives, and the training database is PC
Compliance. The HR database houses employee payroll info, performance
reviews, in addition to basic employee data. The training database is mainly
just training records and plans for the employees, but also houses course
resource data and scheduling info. There is discussion about moving to one
unified platform that will give us superior functionallity from both
perspectives, but I am not sure when that would happen.

There is a great deal off office politics involved as you can imagine. I
myself work in the training department. My concern is that if I wait for
what might come I will put at risk the accuracy our employee training
records. HR and IT have said they do not have the resources to make changes,
or correct the problems with the training database since this replacement is
down the road.... kind of a catch 22 really.

I sincerely appreciate your suggestions, and those Rick B offered of course,
but it may be a while before I can implement anything other than a manual
process. I will however prepare a plan based on your suggestions and see if
anyone will listen.

Thanks so much,

Scott
 
Back
Top