Query ...

[Laney]

Hello.

I am trying to figure something out but my brain isn't thinking right!

I have an Employee Data File which has my employee information in it.

I then have a query that pulls certain data from the Employee Data File that populates the Employee Number by certain things entered. (First Name, Middle Initial, Last Name, Social Security Number and it has formulas that generate the First, Middle and Last names together in one field and also generates the employee number in one field)

I then have an expense sheet which I would like to set up to where when they enter their employee number it automatically populates their Employee Name for example ...

If I typed in

First Name - John
Middle Initial - A
Last Name - Doe

I want it to automatically bring the employee number in from the Employee Data File.

Can anyone help? My brain is fried! lol

 

[Uncle Gizmo]

>>>>My brain is fried! lol<<<< Yes it is, and mine is Now! I just answered the first part of your question thus:

Have a combo box (call it "cboYourCombo") that just displays the employee number. It also has the other data you require available in its query, but the fields are hidden. You can hide them by setting the column widths property like this: 2cm;0cm;0cm;0cm Do this for as many fields as you require hidden.

Now you can have text boxes on your form that display the hidden info, set the control source of the text boxes to: =[cboYourCombo].[Column](2) change the(2) to the apropriate value. Note:- Value of 1st column is 0 not 1.

On re-reading your post prior to me posting I see your question is well!!! I better shut up

 

[Laney]

Hmmm ...

My question is well...what?

 

[Uncle Gizmo]

1) >>>>when they enter their employee number it automatically populates their Employee Name<<<<


2) >>>>>If I typed in

First Name - John
Middle Initial - A
Last Name - Doe

I want it to automatically bring the employee number<<<<<

My confusion is I answered part 1 of your question and just before posting re-read it and noticed the above conflict in your question... Do you want to automatically populate the Employee name or number?

 

[raskew]

Why are you attempting to create a calculated employee number when you have Social Security Number (SSN) (or its National equivalent) which is, in the US (and most other nations), the most unique identifier you could imagine?

Only one person has a particular SSN, which is far superior to most calculated Employee Numbers which typically consist of x letters from the last name, y letters from the first name, etc.., and some other stuff (e.g., Date of Birth, gender) These are not unique and, given enough time, will end up in duplications.

Consider Jane Sara Doe and Janet Sandra Doe, identical female twins born on the same day. You'll be hard-pressed to come up with a calculation which will differentiate between the two. And, if you do, a year later along comes Janine Sarah Doe, unrelated, but also female and also born on the same day as Jane and Janet. It becomes a coding nightmare. Stick with SSN or its national equivalent, you won't be disappointed!

Bob

 

[neileg]

Bob, in the UK, the equivalent to the SSN is the National Insurance Number, or NINo. Unfortunately, this is not guaranteed to be unique. Temporary numbers can be issued that are non-unique, and situations can airise where a new NINo is issued. This effectively prevents them being used as a unique key. As a second point, if you use the SSN, then if the employee leaves and then rejoins, you don't always want to reuse the original record. You would need to use a different primary key to achieve this.

Laney, the compound employee number has an inbuilt flaw. If someone changes their name, eg on marriage, then your number is going to change. You could either use the SSN, if this is unique in your location and subject to my comments above, or use an independantly generated number that is not derived from other data. The answer to your second query about entering the name and finding the Number could be done with a cascading combo. Search in this site for 'cascading combo'.

 

[Pat Hartman]

FYI, I believe that there are laws against the use of SSN as a primary key. You should check.

 

[raskew]

Pat-

Haven't checked recently. Relied (perhaps foolishly) on 26+ years with the US Department of Defense, where SSN was always the PK.

Bob

 

[raskew]

Neileg-

What you suggest is very prone to happen if using just two tables, e.g.:

-tblStaff: ('unchanging personal info', e.g. SSN, LName, FName, MName, Gender, DOB, Race)

-tblEmploymentHistory: HireDate, DprtDate, JobTitle, etc...

The problem with that approach is that no historical info is maintained. If Joe Sixpack starts off as the janitor and works his way up to CEO, we see only his HireDate and his current JobTitle. We haven't a clue of where he's been since the HireDate.

If Joe leaves the organization and comes back at a later date, we'll lose all of our previous historical data.

The solution is to handle it as we would if we were tracking a patient's visits to the doctor. Each visit would be a separate record. Thus, an intervening table (e.g., tblStaffAndEmployment), ala Northwind's StudentsAndClasses template serves as a fair example.

Bob

 

[raskew]

Pat-

Reference your "FYI, I believe that there are laws against the use of SSN...", you just may very well be correct!

In this era of political correctness, identify theft, etc., it's certainly conceivable that laws have been passed prohibiting the use of SSN as a PK.

But then I've got to ask: "Did the law include a workaround where each person was identified as an unique entity, without using the specific SSN?"

If so, do they (George W. and his gang of advisors) not think it possible that some of us folks could reverse-engineer that process and come up the SSN? "Duh-huh, they're doing what? How's a thing like that work?"

Pat, I know that if, for example, you knew my SSN, and also my AllNewIdentifier, and your SSN and AllNewIdentifier, you could programatically determine my SSN. And I could do the same for you. And so could probably tens if not hundreds of thousands of other folks out there! This is not brain-surgery!

I'm going to make some inquiries tomorrow since I'm concerned that you may be absolutely correct and, if so, I'm in violation of the law and have been for a number of years.

What's this world coming too? Bill Clinton, where are you when we need you?

Bob

 

[neileg]

Bob, I understand your comments to me, but I did before you made them. The point I was making, though not clearly, was that you need to decide if you want to link periods of employment together, or not. There can be reasons for doing either.

It can also be important to distinguish between a series of posts help by an individual without a break in employment, and a series of periods of employment that are punctuated by absences from the company.

I'm just pointing out that there is no universal solution to this situation.

 

[Laney]

Re: Re: Query ...

My confusion is I answered part 1 of your question and just before posting re-read it and noticed the above conflict in your question... Do you want to automatically populate the Employee name or number?

I have a query that automatically populates that with a formula.

Thank you for your help.

 

[Laney]

Why are you attempting to create a calculated employee number when you have Social Security Number (SSN) (or its National equivalent) which is, in the US (and most other nations), the most unique identifier you could imagine?
Bob

I understand that completely and if it was my choice it'd be like that. The company has already established their own rules on Employee Numbers. I would have loved to just make them SSN's hehe that'd be a LOT easier.

Their system is as follows:

The employee numbers are 4 digits.
The first 2 digits are the last two digits of the year hired so say I was hired in 2003 my employee numbers start with 03.

The last two numbers are the number hired. It is automatically populated in a field that employee number 01 is the first person hired for that year. Employee number 02 is the second person hired for that year.

This way the employee numbers will never be identical.

So I just put in a query to take the last 2 digits of the year and the two digits of the number hired and combined them to make the employee number.

Like for instance I was hired in 2003 and I was the 2nd person hired. My employee number is: 0302.

 

[Laney]

Pat-

Haven't checked recently. Relied (perhaps foolishly) on 26+ years with the US Department of Defense, where SSN was always the PK.

Bob

hehehe I've always used it in every database I've made too ... My work has all been in Aerospace DOD contract work.

 

[Laney]

Neileg-
If Joe leaves the organization and comes back at a later date, we'll lose all of our previous historical data.


What I've always done is when I create any type of employee table with their information wether it be employee data, training, reports, etc. I've always added a column that is employee status. In this field they can chose full time, part time, contract, temporary, terminated, leave of absence and sometimes a few more.

Had a big issue with this on an aerospace company I worked for. There were two aerospace companies that the employees would get layed off from one and go work at the other 'till they were hired back.

So I would just put them as terminated or layed off status. Then when they came back I would be able to change them to Full Time again and still have all of their records for training etc. Didn't want to have to retrain someone for 60 different OSHA training classes when they'd already had the training. That's a lot of hours to pay someone over again. hehe

Anyway if an employee gets married or comes back at a later time you could always set your employee numbers up like I have on this database and the employee would just get a new employee number each time they're hired.

 

[Laney]

I guess I should have clearly stated that I wanted to do this in a query not a form.

hehe woops !

It's so easy to say something wrong on here and completely get something different isn't it? heheheh

I have several different queries.

I want to pull information from each of these queries but when I put in one thing out of each query it'll need to pull different information into the other fields in this query as well.

So if I have the following queries: (Note I don't have multiple tables with all of this information it's all pulled into queries off of the main tables)

Employee Information File
*****************
First Name, Middle Initial, Last Name, Social Security Number, Employee Number

Payroll Information File
*****************
First Name, Middle Initial, Last Name, Employee Number, Employee Status, Position, Salary Rate

Billing Information File
*****************
First Name, Middle Initial, Last Name, Employee Number, Employee Status, Position, Billing Rate

I would like to be able to make a query to do the following:

I will have another query with the following fields in it:
Employee Number
Full Name
Salary Rate
Billing Rate

I want to be able to just put in the employee number and it will automatically populate Full Name, Salary Rate and Billing rate in my query for me.

This will make it easier for forms and reports down the road. I "THINK" anyway. hehehe Any suggestions would be greatly appreciated!

Thank you all for your help !!!!

 

[Pat Hartman]

Bob,
Carrying SSN in a file is different from using it as a PK. Obviously, there is a need in certain cases to carry SSN as data. The fact that the DOD frequently uses SSN as a pk just goes to show ya' that our government is our own worst enemy and it doesn't need to follow the rules.

 

[raskew]

Pat-

Not that I disbelieve you (although I have to admit to being skeptical), but can you point to something definitive that says using SSN as the primary key is illegal?

It sounds good when you say it fast, but don't make us take it on faith -- where's the law and what does it say?

Best wishes,

Bob

 

[raskew]

Laney-

Different employee identifiers for the same person at different times?! Can't buy that at all. A person is a person is a person--they're one unique individual and need to be identified as such, with all other actions stemming from that unique identifier. That's why, in the US at least, SSN is about the most unique identifier you can find.

Waiting to hear from Pat about the legality / illegality of using SSN as the primary key. She may very well be correct, but it just boggles my mind that legislators could have crafted a law that was so technically precise as to single-out and eliminate a particular data element as being used as the primary key (PK) in a table.

These folks are neither that focussed, nor technically oriented.

Best wishes,

Bob

 

[raskew]

Pat????

Pat-

Just spotted you lurking out there--24 hours later. Is there an answer? You've advised folks that what they may be currently using is illegal, which is going to cause a lot of folks to rethrink and perhaps revise their applications.

Think it's only fair that you provide facts or, conversely, let them know that this is your personal, unsubstantiated opinion!

Bob