Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Tables + Relationships _ Automatically Copying Primary Key Into An Other Table

Posted by: manansafdar Feb 15 2020, 12:09 PM

Hi!

Greetings,

I have a Database for HR Management, I have a "tblEmployees" with PK "EmployeeID".
Another table named "tbluSchools" has list of schools in which these employee work with PK "SchoolID".
I have a junction table named "tblschoolEmployees" in which "EmployeeID" is linked with "SchoolID".

I want that when I add an employee in tblEmployees it automatically get added into tblSchoolEmployees as well as I want to add the schoolID for this employee.

Any support will be much appreciated.

I have attached here the Database for reference.
[attachment=93169:HR_Management_V19.zip]
Thanks,


Posted by: GroverParkGeorge Feb 15 2020, 12:34 PM

Welcome to UtterAccess.

"when I add an employee in tblEmployees it automatically get added into tblSchoolEmployees as well as I want to add the schoolID for this employee."

If this is automatic, how do you plan to identify the correct SchoolID? I assume that this junction table was set up so that you can assign each employee to one or more schools and that each school can have one or more employees (that's the purpose of a junction table). So if it is possible to assign an employee to one or more schools, how would this "automatic" assignment happen?

Posted by: theDBguy Feb 15 2020, 12:51 PM

Hi. Welcome to UtterAccess! welcome2UA.gif

After looking at your forms, I don't see any need to do this. Your school assignment form include a list of employees to select. So, after you add an employee and want to assign them a school, they should be available from the selections anyway. Unless, did you want all new employees to be assigned a "default" school automatically?

Posted by: theDBguy Feb 15 2020, 12:55 PM

Hi. I reviewed the "Associate School" form and it seems you're only assigning one school per employee? Is this correct? If so, then you don't even need the junction table and the associate school form. You should be able to just add a foreign key to the schools table to the employees table and then add a dropdown in the employee form to assign the school. Just a thought...

Posted by: manansafdar Feb 15 2020, 12:59 PM

Hi.. Thanks for the feedback and sparing your time.

I want to assign a schoolID to every newly added employee but I am unble to do it in the for frmNewEmployees.

Can you please help with it?

Posted by: manansafdar Feb 15 2020, 01:04 PM

Hi.. Thanks for response.

No, an employee can be associated with more than one school.

The problem is how to bring EmployeeID and SchoolID in tblSchoolEmployees.

Posted by: manansafdar Feb 15 2020, 01:06 PM

So, after you add an employee and want to assign them a school, they should be available from the selections anyway.

No, they are not availabe for selection.

Posted by: theDBguy Feb 15 2020, 01:16 PM

QUOTE (manansafdar)
No, they are not availabe for selection.

So, how come when I did the following, I see the new employee in the dropdown available for selection?

1. From the main menu, I clicked Employee Management
2. Clicked Add a New Employee
3. Entered my name
4. Clicked Employee Management button on top
5. Clicked Associate School
6. Clicked the Select Employee dropdown and searched for my name, and it was there

Can you please verify if I did not do the correct steps? Thanks.

Posted by: theDBguy Feb 15 2020, 01:27 PM

QUOTE (manansafdar)
No, an employee can be associated with more than one school.

The problem is how to bring EmployeeID and SchoolID in tblSchoolEmployees.

Okay, that means using the junction table is necessary, but how come the Associate School form is not showing all the schools associated with one employee? It only displays one school. How do you add the other schools to the same employee?

To bring the EmployeeID and SchoolID in tblSchoolEmployees should be the function of the Associate School form. If you want to associate a school to a new employee from the New Employee form, then you could add a Schools tab to it with a subform based on tblSchoolEmployees with a dropdown to select the schools you want to assign to the new employee.

Posted by: manansafdar Feb 15 2020, 01:59 PM

Hi,

I have checked, it is as you are saying.

What this means is, my problem is resolved.. I dont know how because I tried alot to fix it before posting here.

Thanks alot anyways.