Unique problem with unique fields

[Clayman]

Every day I find a new reason why nobody wanted this job.

I have built a vacation-tracking system that worked quite well with "File
Number" as a key. I say "worked" because last week we found that a File
Number was duplicated. A whole new set of problems was illuminated.

These are the unique items to identify an employee:
Name
Employee Number (EmpNum)
Company Code/File Number (CoCode & FileNum) (FileNum is unique only within a
Co.Code)

Name and EmpNum remain constant. They follow an employee until they day they
die. If they are re-hired, they are assigned the same EmpNum. Well, I guess a
name can change, so even it is technically not a constant.

The CoCode and FileNum are not constant. If an employee is transferred, they
will be in a new company and assigned a new FileNum.

The payroll folks are not interested in the EmpNum. The ancient reports
which I have built this system to feed are concerned only with CoCode and
FileNum. EmpNum is nowhere in the reports. Nor, do I have a way to
continually update the EmpNum with the CoCode.

I guess my problem is that HR and Payroll are not talking to each other.
Payroll folks can log in to HRIS and find the EmpNum.

What I would like to do is keep my automated system that is based on
FileNum/CoCode but use EmpNum as a key. I'm thinking that, since I have no
access to the system that can match them, I will just have a popup in case of
a discrepancy.

I have this overwhelming urge to move to a cabin in Montana and write a
manifesto...

 

[Jeff Boyce]

Clay

"Name" is a reserved word in Access. If you tell Access to do something
with "Name", it may decide to do something with what IT thinks you mean.
Change that fieldname straight away!

?!"Name" is unique?! ?!"Name" doesn't change?!

(I know of two individuals named "Bill Smith". And when my daughter got
married, her last name changed.)

I'm not sure I saw your question in your post.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Clayman]

Clay

"Name" is a reserved word in Access. If you tell Access to do something
with "Name", it may decide to do something with what IT thinks you mean.
Change that fieldname straight away!

?!"Name" is unique?! ?!"Name" doesn't change?!

(I know of two individuals named "Bill Smith". And when my daughter got
married, her last name changed.)

I'm not sure I saw your question in your post.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Good points. That's why nobody wanted this job. I'm working with a branch of
a national company, building a payroll system. The only common link between
the data Payroll has and the Employee Number is the employee name (which I've
broken down into FirstName, LastName and Suffix). They're hoping that the
name and the company code will be unique. This is why we have "John A.
Smith", "John B. Smith" and "John B. Smith, Jr." I would much rather be at
the corporate office replacing their entire system.

My question is, what is the appropriate way to link the Employee Number
(only thing that is truly unique) with the data the payroll system has? The
payroll data is not constant. Their key is FileNum+CoCode.

It's a frustratin' mess (to quote Hendrix), and it would require a book to
explain the whole situation.

Thanks for your points - they are well taken.

 

[Klatuu]

Well, Hendrix took drugs and set his guitar on fire.
Maybe you could take drugns and set your computer on fire

As Jeff pointed out, names can't be considered consistent. I am suprised
payroll will only accept a name. If a woman changes her name, how does
payroll track the continuity there?
The one thing consistent and unique about a person (unless they are an
illegal alien) is their SSN.

Payroll has to have the employee's SSN; otherwise, they can't report taxes.
Talk to them and see if that will work for them. If not, set them on fire
and go for the cabin in Montana.

 

[Clayman]

Whether or not it's SSN or EmpNum (both unique to the employee for life), I
have no way of matching up their ancient reports with these numbers.

Fire sounds good, though. Back to the proverbial drawing board.

I sincerely appreciate the help, Jeff and Dave.

It was so much easier in college (20 years ago) where we were building stuff
from scratch. I figured a programming job would be more fun than network
administrator. There's boneheaded users and bullheaded bean-counters no
matter where you go.

 

[Klatuu]

So what is unique about an accident report?
Sometimes you have to get the users to bend a little other times:

Accident Report # 15055
Employee: Bully Bonehead
Injury: Severe burns
Description: Subject accountant set on fire by pyscotic programmer.

 

[Clayman]

Thanks for the laugh. Yeah, I can't make everyone happy. I also just found
out that there is no way to get an electronic copy of the EmpNum for these
users. I asked for the list, and was handed a 36-page report generated by
HRIS. I tried to scan it for OCR, but they don't even have that software here
(nor do I have it at home).

The new key: A number beginning with 000001 and incrementing to whatever the
last employee will be at the tribulation.

 

[Jeff Boyce]

Clay

You have my sympathies, partner! Is there a way you could keep all the
pieces they are throwing at you ([THEIR_EmpNum], [THEIR_CoCode], [THEIR...])
and add your own column to hold a unique identifier which you then use to
connect things as needed? They never need to know that you've taken their
mess and imposed order on it...as long as it still works!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John W. Vinson]

This is why we have "John A.
Smith", "John B. Smith" and "John B. Smith, Jr." I would much rather be at
the corporate office replacing their entire system.

Sounds like a good idea. I once worked with Dr. Lawrence David Wise, Ph.D. and
his colleague, Dr. Lawrence David Wise, Ph.D. Larry was tall, blond, outgoing
and friendly; L. David was stocky, dark, and taciturn. I'm sure they had
different SSN's.

John W. Vinson [MVP]