UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Proper Use Of Field To Identify Prior Employees, Any Version    
 
   
GrdnElf
post Sep 12 2017, 07:13 PM
Post#1



Posts: 23
Joined: 23-August 17



I imported data from an Excel spreadsheet to populate some tables while I created a prototype and figured out how Access really works. The People table was/is a kind of 'dumping ground' to collect bits of info about people. Now, I'm re-aligning fields and creating new tables to better categorize / chunk data. I'm trying to determine where the 'prior employee' info bits *should* go.

tblPeople is being subdivided into: Address, Phone, Email, Employee, Incumbent (names of people working on the contract for another company), GovernmentClearance. Applicant info is already in separate tblApplicDetails.
fields remaining in People: PersonID; P-Firstname; P-Lastname; PersonTyp (Applicant, Incumbent, Employee, Installation, Briefing Center, Support Center, Employment Verifier)


tblEmployee will have: EmplID (PK); PersonID* from tblPeople as FK, EmployeeNumber (assigned by corporate DB when Offer is accepted), contractRole, contractDistrict, WorkLocation, MilitaryStatus, EmploymentStatus (active/resigned/medical leave etc.), EmployeeNotes, ResignationDate, ResignationReason, ResignationNotes. (I'm considering moving the Resignation bits to a separate table.)

I'm not sure what to do with the PriorEmployee field (currently yes/no). In the tblPersonTyp that feeds the PersonTyp in tblPeople, there is a value for 'Employee-former'. I'm thinking then that PriorEmployee might not be needed, since tblPeople has PersonTyp which has a value that indicates the same thing as PriorEmployee,. PriorEmployee can apply to Incumbents, Applicants or Employees. However, if a person is a PriorEmployee, it's a prompt to get their EmployeeNumber (which goes in tblEmployee) so a new one isn't created by HR during the hiring process. And, if I pull it out of tblPeople, then I'd have to put it into the tables for Employee, Incumbent, Applicants. So, I'm thinking that leaving it in tblPeople is the right way to go, otherwise I'd be 'duplicating data' in Employee, Incumbent, Applicants tables.

I'm not sure what to do with the PriorEmployeeRole either. Since a Person who is an Employee, Incumbent or Applicant could have had a PriorEmployeeRole with my company, it makes sense to keep that info in tblPeople to avoid duplicate data.

Are there rules of thumb / other considerations for these kinds of situations?

btw: I plan to write a query to pull the data out of tblPeople and make a new table with the query results for each new table, so I don't have to rekey any information already there.



*on the data entry form PersonID will show with 'EmployeeFullname' (as concatenation of Lastname, Fullname using a query) on the select list so that the correct PersonID can be chosen
Go to the top of the page
 
Doug Steele
post Sep 12 2017, 08:20 PM
Post#2


UtterAccess VIP
Posts: 21,306
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Strikes me that you've got a many-to-many relationship. One person can have many roles, one role can apply to many people. Whenever you've got a many-to-many relationship, you need to introduce an intersection entity to resolve the many-to-many. This intersection entity would have a minimum of PersonID, RoleID, EffectiveDate and ExpiryDate.

--------------------
Go to the top of the page
 
projecttoday
post Sep 13 2017, 02:30 AM
Post#3


UtterAccess VIP
Posts: 8,376
Joined: 10-February 04
From: South Charleston, WV


So in the spreadsheets which you receive, are there people who are not employees mixed in with people who are employees?

--------------------
Robert Crouser
Go to the top of the page
 
GrdnElf
post Sep 13 2017, 07:40 AM
Post#4



Posts: 23
Joined: 23-August 17



Thanks for the feedback!

projecttoday: Two different spreadsheets - one with people, one with installations

Doug Steele:
Yep, I think you are right about the many to many relationship. I just couldn't 'see' it for the people. (LOL)
I know how to deal with that...another table coming up.
Go to the top of the page
 
projecttoday
post Sep 13 2017, 07:57 AM
Post#5


UtterAccess VIP
Posts: 8,376
Joined: 10-February 04
From: South Charleston, WV


How do you know which of them are employees?

I was wondering how you came up with the solution of an employees table with a pk to people.

Can an employee have more that 1 role at a time and do you need to maintain a history of their roles?

Can an employee resign (or otherwise leave) more than once and if so do you need to maintain a history of his/her resignations?

I recommend using dates instead of flags wherever possible.

--------------------
Robert Crouser
Go to the top of the page
 
GrdnElf
post Sep 13 2017, 09:17 AM
Post#6



Posts: 23
Joined: 23-August 17



project today:

How do you know which of them are employees?

Ans: Since I'm the project controller/administrator and help with/track the hiring process, I know who all the employees are, their start dates, where they work, etc.


I was wondering how you came up with the solution of an employees table with a pk to people.

Ans: I also track contact information for ALL People and the Installations on which they work (and info about entities/depts. within the Installations). So, using Crystal's approach of separating People and Installation info out from address, phone, email. People can be Employees, Incumbents (work on prior contract for a different company), Installation workers. I also need to track which Employees report to which SiteLead (Manager). People table is what I started with, is being morphed as I move things around.

Thinking about changing People table to the following:
Option 1: PersonID, LastName, FirstName, MidName, Suffix, Salut and use other table - Entity with EntID, EntTyp (values: people, installation, briefing center, support center) to use to direct which table to populate/form to use - People or Installation/BriefingCenter/SupportCenter. This maintains fieldnames that call things what they are, but presents challenges with how to put/get data in or out.
Option2: EntityID (replace PersonID), PrimaryName (person's Lastname or InstallationName), SecondaryName (person's Firstname or InstallationNickname), MidName, Suffix, Salut. This approach presents the problem of 'generic names' being used to identify things rather than calling things what they are.

Employee table fields will be: EmplID, PersonID, EmplNum (assigned by HR), EmplRole (on the contract), WorkLocation, Workdistrict, MilitaryStatus, EmployeeStatus (active, resigned).

Installation table will be: InstallationName, InstallationNickname (SS-abbreviated Installation Name or CCC-Abbreviated Installation Name, where SS=US state, CCC=foreign country to allow users to know where the installation is located via 'short name' used on forms/reports), InstallationType, ServiceBranch, InstallationDistrict, InstlnLocCat (CONUS/OCONUS - to allow for categorizing data and applying work authorization rules based on location), InstallationNotes.

Dept table fields are: DeptID, WorkLocID (FK from Installations), WorkLocName (InstallationNicknm), MaxStaff, DeptMgr.
Join Table EmplDept fields are: DeptID, EmplID, EmplID (Manager & Staff). Since EmplDept needs to have EmplID for both Employee & Manager, the EmplID will be 'aliases' - MgrID and StaffID.


Can an employee have more that 1 role at a time and do you need to maintain a history of their roles?

Ans: Employee will have only 1 role at a time. I need to track change in role (even though corporate DB does this, I need to do it at the contract level for reporting.)

Can an employee resign (or otherwise leave) more than once and if so do you need to maintain a history of his/her resignations?

Ans: Yes, employee can resign and come back. In fact, for this contract, I have several people who will be rehired. Yes, I plan to keep track of resignations.


I recommend using dates instead of flags wherever possible.

Ans: Agreed. Data was pulled in from spreadsheet, so that was the starting point. One of the goals of this restructuring process is to change the flags into dates or category designators.
Go to the top of the page
 
projecttoday
post Sep 13 2017, 11:59 AM
Post#7


UtterAccess VIP
Posts: 8,376
Joined: 10-February 04
From: South Charleston, WV


I think you are on the right track.

I like the employees table with a people id but the drawback to that is the 2-step entry process which I don't think most people find intuitive. So they usually just have separate tables for broad classes of otherwise-unrelated people.

You can test if a date is there with the Isdate() function although if the resignation date is in a subtable it will be more complicated than that.

Sounds like an ambitious undertaking.

--------------------
Robert Crouser
Go to the top of the page
 
GrdnElf
post Sep 13 2017, 05:13 PM
Post#8



Posts: 23
Joined: 23-August 17



projecttoday,

Yes, it's an ambitious project with a deadline that is rapidly approaching to go live. Yikes!!!

The data entry form for People will capture all the information for a Person; it will just be stored in different tables. I used tabs on the People table for key process steps: Application, Interview, Offer, OnBoarding, Employee. The Incumbent table serves as a source of Applicants, and the people in the People table that aren't Incumbents or Employees are by default Applicants. We have a need to keep a ready list of names we can contact to fill any of the positions that need to be filled due to resignations.

On open of the Frontend DB, the master form presents two options: enter People or Installation info. Clicking the button opens the appropriate form.
Go to the top of the page
 
projecttoday
post Sep 13 2017, 08:25 PM
Post#9


UtterAccess VIP
Posts: 8,376
Joined: 10-February 04
From: South Charleston, WV


So what are your table layouts at this point?

--------------------
Robert Crouser
Go to the top of the page
 
John Vinson
post Sep 14 2017, 06:39 PM
Post#10


UtterAccess VIP
Posts: 4,105
Joined: 6-January 07
From: Parma, Idaho, US


Ouch. You appear to be designing the table structure based on the layout of the data entry form; that is the cart before the horse putting! Table structures come FIRST - in this case, just one People table with either a field or data in a related table to categorize the people.

Otherwise you run a big risk of data redundancy (the same person in two or more tables... MAYBE with consistent data, maybe not!) or excess complexity (having to move data from the Applicants table into the Employees table on hire, making sure the data all gets across right, new data gets added, the record gets deleted or flagged as no longer available in the Applicants table...). Not fun!

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
GrdnElf
post Sep 14 2017, 07:34 PM
Post#11



Posts: 23
Joined: 23-August 17



Projecttoday,

I'm not sure I understand your question "what are your table layouts at this point?"
Since I'm 'breaking the relationships' to fix some things, I can't send you a table diagram that reflects the new plan. I started to draw one in PowerPoint and it got too complicated to finish it. I felt it was better to use the Excel file to track the details of what I need to do. I can't attach the Excel file since I don't have a way to zip it. (Coroporate IT doesn't include a tool in the standard profile and restricts what a user can do.)

I have an Excel file that I use to list the fields in a table and the field properties (using the form in 'Database Design for Mere Mortals' as a guide). There is a worksheet for each table in the DB. At the top of the sheet is the tablename and a description. Down the left, the first column are the field characteristics. Subsequent columns are used to describe the characteristics of each field in that table. I'm notating in each worksheet, the changes I need to make (red text = to do; blue text = change completed), characteristics I need to define (yel highlighted cells = to do; lt.grn highlighted cells = done).

These are the tables that are in the DB.
tblAddress, tblApplicDetail, tblAssetDetail, tblAssetRtnReason, tblAssetStatus, tblAssetTyp, tblContactMethod, tblEmail, tblEmplmtVerification, tblEmployee, tblEmplStatus, tblEmplRoleTrackr, tblGovSecurLvlStatus, tblIncumbent, tblIncumbentRole, tblInstallations, tblInterviewDetail, tblInterviewResult, tblJobPositionNumReq, tblJobReqs, tblMilitaryStatus, tblOfferDetail, tblOnBoardingDetail, tblPeople, tblPersonTyp, tblPerTypTrackr, tblPhone, tblReportList, tblResignReason, tblSalary, tblSercoLocDetail, tblTAPdistrct, tblTAProle, tblTrainingCourses, tblTrainingCrsRqmt, tblTrainingCrsTyp, tblTrainingTracker, tblUSstates.

tblInstallations will be divided into: Installation, BriefingCenter, SupportCenter (may be Web) with InstallationID as foreign key. The people who work at BC or SC or Installation will be in tblPeople. I'm debating if I need Address, Phone, Email for these entities separate from the above tables.
Go to the top of the page
 
MadPiet
post Sep 14 2017, 07:41 PM
Post#12



Posts: 2,143
Joined: 27-February 09



Is that "the sort of pedantry up with which you shall not put"?
Go to the top of the page
 
projecttoday
post Sep 14 2017, 08:11 PM
Post#13


UtterAccess VIP
Posts: 8,376
Joined: 10-February 04
From: South Charleston, WV


I thought you wanted commentary on your database. But since the subject of your post is about 1 field, maybe not.

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Sep 14 2017, 08:43 PM
Post#14



Posts: 2,143
Joined: 27-February 09



Why not

CREATE TABLE PersonRole (PersonID INT NOT NULL
, RoleID INT NOT NULL
, StartDate DATE NOT NULL
, EndDate DATE);

Anything with a NULL EndDate is still current. PRIMARY KEY IS either PersonRoleID or (PersonID, RoleID, StartDate). Depends if you need child records.
Go to the top of the page
 
Doug Steele
post Sep 14 2017, 09:16 PM
Post#15


UtterAccess VIP
Posts: 21,306
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Gee, MadPiet, that sounds surprisingly like what I suggested 2 days ago! smile.gif

QUOTE
One person can have many roles, one role can apply to many people. Whenever you've got a many-to-many relationship, you need to introduce an intersection entity to resolve the many-to-many. This intersection entity would have a minimum of PersonID, RoleID, EffectiveDate and ExpiryDate.

--------------------
Go to the top of the page
 
MadPiet
post Sep 14 2017, 09:46 PM
Post#16



Posts: 2,143
Joined: 27-February 09



<edit>
What Doug said!
=)
Go to the top of the page
 
GrdnElf
post Sep 15 2017, 01:47 PM
Post#17



Posts: 23
Joined: 23-August 17



JohnVinson,

Actually, the tables and relationships were designed first. Since the data came from spreadsheets, I did a quick dividing of data into tables. Since I wanted to see how things actually worked Access and I limited resources (references and time), I created some forms. I knew from the beginning that I would need to tweak things before the DB was called final.
Go to the top of the page
 
GrdnElf
post Sep 15 2017, 02:38 PM
Post#18



Posts: 23
Joined: 23-August 17



Doug Steele,

I followed your suggestion for the many to many with the tblPerRoleTrackr, and the 'People' contact info is split into 3 'contact' tables: tblAddress, tblPhone, tblEmail.

Now, I'm working on the Installations and will put the Installation's POC & Briefing Center POC for the contract in tblPeople and then their contact info into tblAddress, tblPhone, tblEmail.

However, the Installation itself has an address that is usually different than it's BriefingCenter (We decided only to track only the BC that receives course material shipments.) Even though a BC has only one Installation, however an Installation can have more than one BC, but the business rule makes it one to one.

However, if things change in the future, it might make sense to put BC info into a separate table (called tblInstltnDept). I'm thinking tblInstltnDept has InstltnDeptID, InstltnDeptName, InstltnDeptPOC (use PersonID as foreign key), InstallationID (foreign key-to associate the Dept to an Installation).

Also, in the 'contact' tables, all currently use PersonID as foreign key to link to tblPeople. But, that doesn't work for an Installation, so I thought about using InstltnID also.

I also thought about creating an 'entity' table with EntityID, EntTyp (person/installation), PersonID, InstltnID. Then, use EntityID in the 'contact' tables. It seems like a roundabout / adding a layer approach though.

I originally thought about putting 'installation' and 'people' info into the same table, but the characteristics are too different to come up with generic, but meaningful field names.

Does it make sense to have two foreign keys in the 'InstallationDept' (both would apply to the row/record) or 'contact' tables (where only one would apply for a given row/record)? The former case seems logical, the latter I'm not sure.

btw: With the tblEntity approach to tables, I can design a form so that the user selects the EntityTyp first, then depending on which Entity is chosen, either tblPeople or tblInstallation is searched after the user enters a search term in a data entry box to find the specific Entity (a Person or an Installation). I just don't how to make the 'action' happen for that - macro? module? (I'm not very familiar with VBA; SQL a little more.) I saw that Access has a tool to turn a macro into a module, but I haven't used it. Any thoughts on that tool?

Go to the top of the page
 
Doug Steele
post Sep 15 2017, 04:43 PM
Post#19


UtterAccess VIP
Posts: 21,306
Joined: 8-January 07
From: St. Catharines, ON (Canada)


There's certainly nothing wrong with having two foreign keys. (Think about it a minute: the PersonRole table has two foreign keys. Depending on how you created the table, the primary key may be the combination of those two fields)

Afraid your description is a little too abstract for me to give specific advice.

Good luck with your project! smile.gif

--------------------
Go to the top of the page
 
GrdnElf
post Sep 15 2017, 05:21 PM
Post#20



Posts: 23
Joined: 23-August 17



Doug Steele

Thanks!

It's really easy for me to get 'lost in the weeds' working on this database. It really simplifies things to use 2 foreign keys.

p.s. I really appreciate the time you've taken to respond to my questions.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th September 2017 - 12:08 PM