Moving and updating data with command button

SaraMegan

Starving Artist
Local time
Yesterday, 19:15
Joined
Jun 20, 2002
Messages
185
Hi (again)!

All right, I have something more I'd like to do that I think will make my users' lives easier (and allow for fewer mistakes, hopefully.) Problem is, I'm not sure how to do this...

I've got a database which holds all the employees in the agency, plus information on their positions and all that. The main table is tblEmployees and the primary key for this table is TDNo, which is a pre-assigned unique ID number for each employee.

There's a second part to the database which holds information on positions posted and applicants to that position. It tracks each application to each position. There's a tblApplicants which has a field for TDNo, in case the application came from within the agency.

What I'd like to do is have a command button on my application tracking form that hires the applicant to that position. This is where I get myself confused. This is what I'd like to happen:

First: a message box which asks "Are you sure you want to hire [applicant] to position [PositionNo] ([PositionTitle,Location])?"

Then: If the person clicks yes, it will check for the presence of a TDNo, and if there is one, it will change the position number for that employee in tblEmployees and ask for a start work date.

If there is no TDNo, it will ask the user for a TDNo, and start a new record for tblEmployees, taking the name fields (last and first) and position number from the application. It will also ask for a start work date.

Is this too complex for poor inexperienced little me? I think it would make things so much easier for the user, so I don't care if it takes me a while to grasp... I just have no idea even where to start.

Thanks again in advance. Y'all are great. (woo-hoo)

--Sara
 
Sarah,

Trivia questions

Does an "applicant" have to be an "employee" ?

If no, does an applicant become an employee after they are selected for an open position ?

It sounds to me like you want 3 tables:
1) Position
2) Applicant/Employee ?
3) PositionToApplicant

The third table, PositionToApplicant, would just be a "link" table that contains the PK of Position and Applicant/Employee.

So an open position would have a Null value in the AppEmp field and an Applicant/Employee who has no current position would not appear in the link table.

RichM
 
RichM,

Thanks for the reply!

Applicants do not have to be employees, but they become one if they are selected to an open position.

I currently have two separate tables for applicants and employees, though there can be duplicates if an employee applies for a job.

What I want is for an applicant who is not an employee to be entered into the employees table if they get hired (the "hire employee" button gets clicked and user verifies through a message box)

One of the main reasons I have two separate tables for employees and applicants is that the tables hold very different information. For example, the SSN and address of the applicant is in the applicant table, but not in the employee table. The employee tracking part of the database has no personal information, but the application section does.

Does that help? I'm not sure... I just downed a whole can of Coke so I'm kinda zippy... whee!

Thanks again!

--Sara
 
PS

I can post a picture of my relationships if you'd like to see them...

Oh, and I'm using Access97

--Sara
 
Sarah,

I think the whole process will be easier if Employees and Applicants can be combined in one table:
1) the additional information for applicants can be stored in a linked table and deleted after a "hire"
2) the table can contain a simple YesNo for "is an employee"

Also in the PositionToApplicant table add a YesNo field for "got the position".

So you can:
1) add an applicant to the person table and default to not an employee, fill in the applicant data as needed
2) indicate "applied for position" by creating a PositionToApplicant entry by selecting an employee or an applicant
3) list all the people, employee or not, who have applied for a position
4) click on the one you want to hire and then run a sub that puts the PersonID in the PositionToApplicant table (closing the open position), updates the person table to "is an employee", deletes the applicant data if neccessary.

Seems like the cleanest flow.

RichM
 
Thanks again, RichM.

The problem is, I kind of wanted to keep the two tables separate... Is that really bad? Is there a major downfall to doing it the way I posted originally? I've already done so much with the relationships as they are now, and changing that all around would really complicate things, I think. Plus, it feels like it would just be cleaner to keep separate things separate... The information that is stored in the employees table and the information stored in the applicants table is completely different and for different purposes.

Please let me know if I'm way off base here. Still trying to learn.

--Sara (without an "h" ;) )
 
Sara,

I think it's OK to keep Employees and Applicants in separate tables. You will want to make a list of all candidates for a Position and that would be easier if all the candidates were in one table. If not then a union query can select common fields from 2 or more tables.

RichM (with an h)
 
RichMh, ( :D )

Should I use a lookup from the tblApplicants to the tblEmployees to fill in a TDNo? Then if there is a TDNo, can I get it to fill in the LastName and FrstName fields? And if there isn't a TDNo, the user can fill in the LastName and FrstName fields?

So there would be some of the same people in tblEmployees and tblApplicants, but I don't mind that. (Though it probably breaks some kind of rule...)

I'm attaching a picture of my relationships... maybe it'll make things clearer somehow...

Anyway, is there a way to do what I posted originally as I posted it, or should I find another way? Or maybe just accidentally set fire to my PC? "No, Commissioner, I don't know how it happened... One second it was up and running, the next it was in a fiery blaze. Aren't the colors pretty?"


--Sara

PS... um... how do I attach a picture to this post?
 
Sara,
<<
Should I use a lookup from the tblApplicants to the tblEmployees to fill in a TDNo? Then if there is a TDNo, can I get it to fill in the LastName and FrstName fields? And if there isn't a TDNo, the user can fill in the LastName and FrstName fields?

So there would be some of the same people in tblEmployees and tblApplicants, but I don't mind that. (Though it probably breaks some kind of rule...)
>>

Think I don't get it. It seems like there are 2 categories of people, employee and non-employee. An "applicant" could be either ?? If TDNo is the primary key of the Employee table then a non-employee would not have one. Right ? So a non-employee needs some other kind of unique ID number. That's because you need to somehow connect a non-employee to a possible Position.

The table I mentioned earlier, PositionToApplicant, is a "link" table which contains the primary key of other tables; Position and Employee/Applicant.

What that means to me is the you would not have "some of the same people in tblEmployee and tblApplicants.

Does that make sense ?

RichM
 
I'm afraid I don't understand...

here is a picture of my relationships. I think I've got that figured out, at least. (I hope)

Isn't your plan going back to the one table for all employees and non-employees?

What I've got is a table with employees and a different table with applicants. the applicants have their SSN as a primary key. The employees have their TDNo as PK.

Sorry this is so difficult. I work much better with pictures than words... So here's my picture. Hopefully it'll help clarify... I don't know... I'm frustrated. Thanks for your patience.

--Sara
 

Attachments

  • relationsips.jpg
    relationsips.jpg
    70.2 KB · Views: 194
I think I understand, but you have not mentioned some things that perhaps have obscured the problem. Let me see if this next explanation is right. (If not, CORRECT me, don't just say "no that's not right.")

------------------------------------------------------------------------------------

Your company posts a new opening or position.

Someone can apply for the position. You have a command button on one of your forms. You want to use that button to help you update your company information.

The person who gets selected for that position can either be

1. A totally new hire, or

2. An employee who wishes to accomplish an internal shift of position within the company.

Your problem is that when you fill the position, you have more than one possible source for the information.

-----------------------------------------------------------------------------------

If you have confusion over data sources, I think it is because you are perhaps not thinking about what it means data-wise to do what you are trying to do. Your data model (inside the database) does not really match the real flow of your company's business.

Your data model confuses you because you are trying to do several things at once sometimes, but not always.

The things are:

1. Make someone a new employee, and this new person is not yet posted to a position (sometimes).

2. Unpost a current employee from a position (sometimes, and this "sometimes" is the exact complement of the other case's "sometimes." I.e. the "sometimes" from cases 1 and 2 total 100%.)

3. Post an unposted employee to a position (always).

It is the division of activity from the two "sometimes" cases that confuses you, I think.

Solve this by dividing the problem into "hiring" and "opening a position" and "filling a position."

In the NEW HIRE case: Hire the person FIRST, so that person gets a new number and whatever else is required. Append the new employee data which you can obtain from whatever sources are available, such as your applicants table.

In the INTERNAL TRANSFER case: Unpost the person FIRST, and break the position's link to that person even as you break the person's link to the position. The existing employee data not related to current position does not need to be changed.

If you do that, then when you fill the position, it is always done with an employee who has a number and other data in the employees table.

I would also suggest that by doing this, you would make it far easier to maintain an employee's position history. Because how the changes of position have been isolated as distinct events.
 
Sara,

The picture looks good. A few comments.

1) The big one.
I think that tblJunction needs to be linked to both tblEmplolyees and tblApplicants. That's because both employees and non-employees can apply. So you need a common link field OR you have to fake it by putting Employees into Applicants as you stated previously.
Sorry, bad idea.
You "may not mind" but the person who has to maintain the phony Applicants will mind.

IMO, you need to rethink this part of the data model.
You could make an Applicant table that is similar to the Employee table (but not identical) and give Applicants a temporary TDNo. That lets you link both tblEmployee and tblApplicant to tblJunction, with separate queries of course.


2) Do you really need tblApplicants and tblApplication ? Since there is a 1-to-1 relationship the two tables can be combined. Not a big deal but fewer tables are generally better than more.


Those are my thoughts for today.
RichM
 
Doc,

Thanks for your response. Forgive me for being slow to understand. I have explained the best I could and I am still new to this.

Let me try to explain better what I was trying to do:

My intention was to put all applicants, employee or not in one table. That way I only have one source for my applicant/application information. Much of the data in the table for applicants cannot currently be found in the tblEmployees, therefore if I took my data from two places, (the employees table and the applicants table,) there would not be enough information. (is this what you mean about the data sources?)

My intention was to have a field in the tblApplicants to store TDNo, which if null would show that the applicant was not an employee.

If an Employee were to get hired, then their position number would change in tblEmployees, as well as ProbDate. If a non-employee were to get hired, then a new record would have to be put in tblEmployees.

What do you mean by "unpost the person FIRST, and break the position's link to that person even as you break the person's link to the position"? Wouldn't just changing the PositionNo break the old position's link and create a new one? All of the data for every position, (whether someone is assigned to that position or it is vacant,) is already in the database, so changing the position number would automatically change all that is related to that position. (Title, location, supervisor, etc.) I was not thinking that other employee data, with the exception of Probation Date, would have to be changed. (an employee starts a probationary period any time they get a new position)

RichM,

1) The database needs to store the SSN and Address of every applicant, whether they are currently employees or not. So should I add these fields to the Employees table? Not every employee applies for other positions, so many records would hold blank fields.

I'm not sure what you mean by "phony Applicants..." Everyone who applies for a position has to go through the same exact process every time they apply for a position.

What if, instead of linking the tblEmployees to tblJunction, I link tblEmployees to tblApplication, after adding SSN and address fields?

2) Actually, tblApplicants have a one-to-many relationship with tblApplication. One applicant can apply for many positions, and they need a different application for each.

Again, both of you, I'm sorry for being slow to get this. It's frustrating and upsetting for me, but I don't mean it to cause frustration or upset in others.

Please ask if I'm still not clear enough. I thought I had it figured out, but it seems I may never get it.

--Sara
 
Sara,

OK, on with today. You wrote
<<
1) The database needs to store the SSN and Address of every applicant, whether they are currently employees or not. So should I add these fields to the Employees table? Not every employee applies for other positions, so many records would hold blank fields.

I'm not sure what you mean by "phony Applicants..." Everyone who applies for a position has to go through the same exact process every time they apply for a position.

2) Actually, tblApplicants have a one-to-many relationship with tblApplication. One applicant can apply for many positions, and they need a different application for each.
>>

1) What I meant by "phony Applicants" was a person who really IS an employee but is entered into tblApplicant along with people who ARE NOT employees. I thought you said information is collected for non-employee applicants that is different than info for employee applicants.

2) On the diagram, it looked to me like tblApplicants had a 1-to-1 join to tblApplication. Other joins had "many to 1" symbols but this one did not.

BTW, I like Doc_Man's approach with a "script" for business actions. That's a good way to present and validate requirements. Piece of cake for an actess.

RichM
 
RichM,

Sorry for the confusion.

The information for all applicants needs to be the same, whether they are employees or not. tblEmployees simply holds a list of all the employees and their positions. It was the original part of the database, before the postings and applications and all that were added, it was simlpy a database to track employees and their positions, supervisors, dates of hire/probation, and other stuff like that.

The project that was assigned to me was to take this database (which was very poorly constructed) and re-construct it, which I did (and I thought I did it pretty well...) Then, my next task was to re-create another database which had been designed for another state agency and given to our agency, but since it was not designed for our agency, the needs of our agency were not fulfilled by this database.

So, my next job was to re-create this database from scratch. Since it would pull a lot of the position information, I was advised to put both parts into one database, and then divide it into a BE/FE structure.

Does this help explain the purpose of the tables? Originally, the database we received from the other agency had nothing to do with the one which tracks employees and their positions, so there were people who ended up in both databases.

So, in response to your part One, what I meant was that the information contained in the Applicants table is different from the information contained in the Employees table, however, all applicants to a position, be they employees or not, need to go through the exact same application process every time they apply for a position.

--Sara
 
Sara,
<<
The information for all applicants needs to be the same, whether they are employees or not.
>>

OK, I understand. So for the process of managing job postings and applicants it looks like you don't need to care much about the tblEmployee and its relationships to other tables. That makes things easier.

It looks like the major entities are:
Positions,
Postings,
Applicants,
Applications.
plus a link table for ApplicationToPosting.

I'm still not clear why there is a table for both Applicants and Applications. This seems like one conceptual entity to me. If the person who is an Applicant wants to apply for 2 or more Postings then the person's unique ID (whatever it is) would occur 2 or more times in the junction table. Right ?

Off Topic
======
Here is another comment I forgot to mention yesterday. In tblEmployee there is a "position" field that is a foreign key to tblPosition. I would remove this field from tblEmployee and make yet another "link" table for EmployeeToPosition. That lets you handle situations where an employee could work in 2 or more positions, perhaps 50% in each.

So where are we now? :)
Do you have any open questions ?

RichM
 
RichM,

Some (more) clarifications:

First, you said:
<<I'm still not clear why there is a table for both Applicants and Applications. This seems like one conceptual entity to me. If the person who is an Applicant wants to apply for 2 or more Postings then the person's unique ID (whatever it is) would occur 2 or more times in the junction table. Right ? >>

One applicant can have many applications, yes. Each application can apply to one or many postings (although it can only apply to many postings if the job title is the same, but that's a different thread altogether...) So if I've applied for three positions this year, I would have three different applications in the junction table, yes. But I would appear with three different postings. There are 2 primary keys in that table. The Applicant table is like a lookup table from the Application table.

Does that answer your question there?

Okay, secondly, you said:
<<In tblEmployee there is a "position" field that is a foreign key to tblPosition. I would remove this field from tblEmployee and make yet another "link" table for EmployeeToPosition. That lets you handle situations where an employee could work in 2 or more positions, perhaps 50% in each. >>

That's a good suggestion, only it doesn't work that way. If a person were to split their time between two jobs, they would be assigned a second TDNo. At least, that's how they did it the only time someone held two part-time positions. Probably not the best way to do it, but that's how they do it as an agency...

So are there other things you'd like to point out or ask? Are we back to my original method of proceeding?

Again, Rich, thanks for all your help.

--Sara
 
Sara,
<<
One applicant can have many applications, yes.
>>
Yes that's what the diagram shows. Still don't see what the purpose is.

<<
Each application can apply to one or many postings So if I've applied for three positions this year, I would have three different applications in the junction table, yes. But I would appear with three different postings. There are 2 primary keys in that table.
>>

Yes I see that.

If one Applicant wants to apply for 2+ Postings then the tblJunction contains:
AnnouncementID = 1
ApplicantID = 1,

AnnouncementID = 2
ApplicantID = 1 (same person)
etc.

The question is still why have tblApplication AND tblApplicants ? Particularly because your diagram seems to show a 1-to-1 relationship based on SSN.

That's my only open question.

off to Hartford CT tonight,
RichM
 
Okay, let's see:

If I want to track the positions a person has applied for, and don't want to enter the whole name and ssn every time a person applies for a job, isn't it better to have a table with that information?

I currently have a form that shows a posting, with a sub-form listing applicants to that position. I have the subform set up so that you can add a person who is already in the database (tblApplicant) as an applicant to that position, creating a new record in tblApplication. Isn't my database more normalized that way? I really thought I was doing this right, based on all the stuff I read on normalization in the forum and through web searches and stuff...

I can get the diagram to show the one to many relationships just be enforcing referential integrity, but I haven't done that yet, as I was experimenting with test data and adding and deleting data that I didn't think would work with referential integrity.

I hope that helps. Thanks again. Have fun in Hartford, and welcome to New England! :) I spent a year of college in Hartford, but then decided to come back to NH.

--Sara
 
Ooh! just realized something! ApplicationID (in tblJunction) is different every time a person has an application. So one SSN can have many ApplicationIDs... therefore, the person will not be in the tblJunction more than once.
 

Users who are viewing this thread

Back
Top Bottom