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
> Automatically Copying Primary Key Into An Other Table, Access 2016    
 
   
manansafdar
post Feb 15 2020, 12:09 PM
Post#1



Posts: 14
Joined: 28-July 19



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,

Go to the top of the page
 
GroverParkGeorge
post Feb 15 2020, 12:34 PM
Post#2


UA Admin
Posts: 37,510
Joined: 20-June 02
From: Newcastle, WA


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?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Feb 15 2020, 12:51 PM
Post#3


UA Moderator
Posts: 78,487
Joined: 19-June 07
From: SunnySandyEggo


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?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
theDBguy
post Feb 15 2020, 12:55 PM
Post#4


UA Moderator
Posts: 78,487
Joined: 19-June 07
From: SunnySandyEggo


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...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
manansafdar
post Feb 15 2020, 12:59 PM
Post#5



Posts: 14
Joined: 28-July 19



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?
Go to the top of the page
 
manansafdar
post Feb 15 2020, 01:04 PM
Post#6



Posts: 14
Joined: 28-July 19



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.
Go to the top of the page
 
manansafdar
post Feb 15 2020, 01:06 PM
Post#7



Posts: 14
Joined: 28-July 19



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.
Go to the top of the page
 
theDBguy
post Feb 15 2020, 01:16 PM
Post#8


UA Moderator
Posts: 78,487
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
theDBguy
post Feb 15 2020, 01:27 PM
Post#9


UA Moderator
Posts: 78,487
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
manansafdar
post Feb 15 2020, 01:59 PM
Post#10



Posts: 14
Joined: 28-July 19



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.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th July 2020 - 03:31 PM