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
> Help Understanding Relationships Between Employees, Access 2010    
 
   
rosscortb
post May 17 2019, 05:08 AM
Post#1



Posts: 9
Joined: 11-September 14



Hello,

Hoping you can help.

I have created a database for success planning so an employee can nominate one to many employee(s) to succeed them.
At first I thought I would need two tables the nominator and the nominees. However, basically these are the same employees as an employee could be a nominator or a nominee. Therefore, I just created one table and try to work out the relationship but doesn't seem to be working. Any ideas? hoping screenshots may help. I was trying to create two relationships between the nominations table Employee id to Nominee ID and Nominator ID. The reason I thought I should just use one table is when an employee leaves they wouldnt need to be deleted from two tables

Thanks
Ross
Attached File(s)
Attached File  ssssss.PNG ( 55.47K )Number of downloads: 21
 
Go to the top of the page
 
gemmathehusky
post May 17 2019, 05:42 AM
Post#2


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


But it's different.

A nominating B is not the same as B nominating A.


So given an employee table, and a nomination table, you need a relationship defining both parts of the nomination.
employee to nominatOR and employee to nominatEE (if you will)





--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
nvogel
post May 17 2019, 07:16 AM
Post#3



Posts: 977
Joined: 26-January 14
From: London, UK


You definitely only need one table but two foreign keys: nominator_id, nominee_id. Unfortunately if you try to add two foreign keys between Nominations and Employee then the Access relationships window will show two copies of the Employee table: Employee and Employee_1. This is just a lousy/silly/faulty "feature" of the relationships window - ignore it.
Go to the top of the page
 
orange999
post May 17 2019, 07:27 AM
Post#4



Posts: 1,934
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Ross,

For consideration--
I think it would help you and readers if you described a few examples and an overview of your work environment.
Is this a real project or an idea to be considered?
For example, it seems you are "seeking nominations for people to replace/succeed an employee who occupies a certain position".
In your set up can someone nominate more than 1 person?
If so, is there some sort of limit on the number?
Can any employee make a nomination(s)? Or is there a restriction based on department/area or seniority etc?
Many positions have required certifications/qualifications --does this apply?
Are there any details of nominations that are recorded? Dates?

It seems you are dealing with:

Position
CurrentOccupantOfPosition
NominatorID
NomineeID

after the nomination process, there would be a procedure to evaluate nominees and select the successor.

Good luck with your project.

--------------------
Good luck with your project!
Go to the top of the page
 
rosscortb
post May 20 2019, 02:53 AM
Post#5



Posts: 9
Joined: 11-September 14



Good Morning,

I putting this together for someone at work who was using two spreadsheets to do the administration of our succession planning at work.

This is three categories, Short, Mid, Long, meaning an employee(s) can fall in anyone of these categories to succeed another employee(usually a line manager but not always the case) if they leave or retire.

Not all employees can make nominations as its normally a head of a department/sector.
Someone can nominate more than 1 person and there is no limit.
No required certifications/qualifications.

If have created a position table( correct me if I am wrong) as if a nominator leaves the position stays and the nominee(s) to replace that position would also remain.

If have done the relationships as advised, so when I look at category I can see that Employee 73 is the short term for Employee 126 and 74 & 81 are the mid for employee 126. This seems to be displaying correctly.

But, in the employee table, under employee 73, I don't see anything?

Thanks for all your help
Ross

Attached File(s)
Attached File  ss14.PNG ( 6.95K )Number of downloads: 3
Attached File  ss12.PNG ( 25.2K )Number of downloads: 4
Attached File  ss13.PNG ( 29.58K )Number of downloads: 7
 
Go to the top of the page
 
rosscortb
post May 20 2019, 05:14 AM
Post#6



Posts: 9
Joined: 11-September 14



Actually I think that's correct I shouldn't see anything as that employee is not nominated.
Go to the top of the page
 
rosscortb
post May 24 2019, 06:11 AM
Post#7



Posts: 9
Joined: 11-September 14



Hello,
Managed to get a little further with my project.

Although struggling with the Position field.

In my Nominations Form I have combox box which populate the ID's of Nominator, Nominee and Category into the Nomination Table. The bit I'm struggling with is setting the Nominated Position to populate automatically, any ideas? How do I get it where when Nominator is selected in the combo, its returns the nominator position in the nominated position field(Not the Nominators ID). Also not sure got relationships correct with position. Any help would be appreciated.
Thanks

Ross
Attached File(s)
Attached File  SS4.PNG ( 53.8K )Number of downloads: 8
 
Go to the top of the page
 
mike60smart
post May 24 2019, 06:40 AM
Post#8


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi Ross

Before going any further I would recommend that you Enforce Referential Integrity between Tables.

Can you upload a zipped copy of the Db?

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
rosscortb
post May 27 2019, 05:45 AM
Post#9



Posts: 9
Joined: 11-September 14



Hi,

Yeah I was going to that once it was all set up.

See attached.
Thanks

Ross
Attached File(s)
Attached File  SP.zip ( 257.94K )Number of downloads: 2
 
Go to the top of the page
 
mike60smart
post May 27 2019, 06:27 AM
Post#10


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi Ross

Attached File  RIError.PNG ( 39.49K )Number of downloads: 1


Your Employee table has 2 Records with an ID of 1 & 2

However your Nominations table shows only one instance of ID 2

and your Nominations table shows the following unrelated ID No's

Attached File  Nominations.PNG ( 15.16K )Number of downloads: 0


This means you have created a number of Unrelated (Orphaned Child) Records in your Nominations table


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
rosscortb
post May 27 2019, 06:41 AM
Post#11



Posts: 9
Joined: 11-September 14



Sorry should have mentioned that I had to make a copy of that database cause it had a lot of our employee details in it, so deleted the employees.

In the example I sent I was trying to work out how to populate the Nominator Position ID in the Nominations form, which should be 77, from selecting the name in the combox box(nominator) automatically. Hope that makes sense.

Thanks

Ross
Go to the top of the page
 
mike60smart
post May 27 2019, 07:37 AM
Post#12


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi Ross

Ok Then I added an After Update to the cboNominator which populates the required Control

See the attached

Attached File  SP.zip ( 251.62K )Number of downloads: 1

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
rosscortb
post May 27 2019, 08:06 AM
Post#13



Posts: 9
Joined: 11-September 14



Hi Mike,

Thanks for your help. That's looks like what I need but now the Nominations Form is locked and wouldn't let me add another new nomination.

Thanks

Ross
Go to the top of the page
 
mike60smart
post May 27 2019, 08:09 AM
Post#14


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi

It allows me

See attached

Attached File  SP.zip ( 254.22K )Number of downloads: 2

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
rosscortb
post May 27 2019, 09:01 AM
Post#15



Posts: 9
Joined: 11-September 14



Sorry I'm an idiot, I didn't save first as was keen to see what you had done so it opened in read only.

Thanks again for your help.

Ross
Go to the top of the page
 
mike60smart
post May 27 2019, 09:33 AM
Post#16


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi Ross

We were glad to help

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 06:48 AM