Multiple Courses to Multiple Employees (1 Viewer)

[nashaz]

Hi all

Is there a way to bulk update training records? For example, let's say I want to update records of 50 employees, where they all received 13 trainings. Using continuous forms, I would usually navigate to an employee, then assign 13 trainings to them. But this is way too long to do for 50 employees. The structure of underlying table which needs updating is as follows:

EmployeeXCourse_JT:

• EmployeeID (FK)
• CourseID (FK)
• CertificationDate

What I was thinking is to have a listbox in a continuous form where I can select multiple courses, and underneath, I could have cbo to add employees. Is there a more efficient way of doing this?
Thank you in advance for your help!

 

[arnelgp]

use Insert query Cross joining your Employee table and the Training table.

Insert Into EmployeeXCourse_JT (EmployeeID, CourseID) Select A.EmployeeID, B.CourseID From
EmployeeTbl As A, CourseTbl As B;

 

[LarryE]

1. Are you adding new training records to all employees or just some employees and how are you identifying which employees?
2. Are you adding all training records to these employees or only some selected training records and how are you identifing which training records?
3. Create your INSERT INTO query accordingly.

 

[nashaz]

use Insert query Cross joining your Employee table and the Training table.

Insert Into EmployeeXCourse_JT (EmployeeID, CourseID) Select A.EmployeeID, B.CourseID From
EmployeeTbl As A, CourseTbl As B;

Pardon me for my lack of knowledge, do I write the above inside a SELECT query SQL?

 

[nashaz]

1. Are you adding new training records to all employees or just some employees and how are you identifying which employees?
2. Are you adding all training records to these employees or only some selected training records and how are you identifing which training records?
3. Create your INSERT INTO query accordingly.

1. Mostly, all employees, but in some instances it will be lesser than EVERYONE due to absences etc
2. Not all training records, but as an example, 13 out of 25 training records
3. Could you please explain this further (apologies)

 

[LarryE]

You are going to have to determine which specific CourseID's and which EmployeeID's need to be appended to your table.

1. Create a Select query of only those employees that require updating
2. Create a Select query of only those courses that were completed
3. Use those two queries as the source for your INSERT INTO query. The two source queries should NOT be connected together.

 

[Pat Hartman]

I'm pretty sure that this is not normal operation for the database. It sounds like you are playing catchup and loading past data. If you have a spreadsheet with the data, you can load from that. If not, you have to add all the people. Then you have to add all the courses. Then you can use a cross join of those two tables - a query with both tables listed but NO join line so that every person is matched to every course. Then you can make an append query to append the bulk data into the junction table.

However, this is pretty sloppy and probably means that you won't be able to set up validation rules such as requiring data for certain data fields like the course date and grade.

Is it actually your job to populate the data or are you just creating the database to store the data? SOMEONE has actual records. Let the person with the records do the data entry one at a time so that each record is actually complete. What you are loading with the bulk query is just garbage.

 

[nashaz]

I'm pretty sure that this is not normal operation for the database. It sounds like you are playing catchup and loading past data. If you have a spreadsheet with the data, you can load from that. If not, you have to add all the people. Then you have to add all the courses. Then you can use a cross join of those two tables - a query with both tables listed but NO join line so that every person is matched to every course. Then you can make an append query to append the bulk data into the junction table.

However, this is pretty sloppy and probably means that you won't be able to set up validation rules such as requiring data for certain data fields like the course date and grade.

Is it actually your job to populate the data or are you just creating the database to store the data? SOMEONE has actual records. Let the person with the records do the data entry one at a time so that each record is actually complete. What you are loading with the bulk query is just garbage.

Thank you for your reply. I am not playing catchup, as we just did an internal training day with all the staff, which included around 13 different trainings. When I actually got down to updating the db is when I thought maybe I should ask if there is another way of doing that.

 

[Pat Hartman]

Then since you know when the courses were taken, you should also include that in the append query. If there is a test/grade associated, you will need to update those manually.

Did you try to follow the directions yet? Have you created the database and loaded the tables with the employees and the first thirteen courses? Once you get that done, it will be easier for people to be more explicit in their append query suggestions.

 

[nashaz]

Then since you know when the courses were taken, you should also include that in the append query. If there is a test/grade associated, you will need to update those manually.

Did you try to follow the directions yet? Have you created the database and loaded the tables with the employees and the first thirteen courses? Once you get that done, it will be easier for people to be more explicit in their append query suggestions.

As I understand from your and LarryE's response, this would have to be an ad-hoc operation and not something which you can have a form for, am I right?

I will implement this solution tomorrow and update here with the results. As always, really appreciate everyone's help

 

[ebs17]

INSERT INTO
   EmployeeXCourse_JT(
      EmployeeID,
      CourseID,
      CertificationDate
   )
SELECT
   E.EmployeeID,
   C.CourseID,
   Date()
FROM
   (
      SELECT
         EmployeeID
      FROM
         tblEmployees
      WHERE
         selected = True
   ) AS E,
   (
      SELECT
         CourseID
      FROM
         tblCourses
      WHERE
         selected = True
   ) AS E

A very simple variant of a mass append looks something like this.
But: If you want to do everything at once, all the necessary information must be available.
- Which of the 200 employees should be taken into account?
selected = True is only to be considered as an example.
- Which of the 50 courses should be taken into account?
- Which date should be entered? Does EVERYTHING take place on one day, or are there different courses on different days? Such an assignment would have to be available as a table and included here.

 

[Pat Hartman]

As I understand from your and LarryE's response, this would have to be an ad-hoc operation and not something which you can have a form for, am I right?

You would create a form with a subform to do the normal data entry. This mass entry is done with an append query. Access can't just make up the data. For the initial load, you would enter only the employees who took the classes and only the specific classes they all took. Then the append works fine. Going forward, there won't be any "automatic" way to generate the data to do the append query so you may as well just use the class main form and the employee subform. Access needs criteria to select data. When you have 200 employees but only 20 take a class, how do you select the employees? One at a time? Maybe you can do something with job category so that only welders take the class on welding safty. We have no idea how you need this to work. All we see is your initial request which is not how this application will work going forward unless - every employee takes every class, all on the same day.

I'll attach a link to a database that shows how to work with a many-many relationship from both sides of the relationship. In some cases you would use a main form with a subform but in others you would use a list form with a double click to open the separate child form. Both views are valid but one will make more sense in any particular situation than the other.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[nashaz]

INSERT INTO
   EmployeeXCourse_JT(
      EmployeeID,
      CourseID,
      CertificationDate
   )
SELECT
   E.EmployeeID,
   C.CourseID,
   Date()
FROM
   (
      SELECT
         EmployeeID
      FROM
         tblEmployees
      WHERE
         selected = True
   ) AS E,
   (
      SELECT
         CourseID
      FROM
         tblCourses
      WHERE
         selected = True
   ) AS E

A very simple variant of a mass append looks something like this.
But: If you want to do everything at once, all the necessary information must be available.
- Which of the 200 employees should be taken into account?
selected = True is only to be considered as an example.
- Which of the 50 courses should be taken into account?
- Which date should be entered? Does EVERYTHING take place on one day, or are there different courses on different days? Such an assignment would have to be available as a table and included here.

Precisely my concerns, and thus this thread. If I was to open individual employee record, and update the training subform within that (which is currently what I have), it would take ages. Just wanted to see if I could simplify this operation by having all employees in a list where I could select the ones whose record I would need to update, and same for the list of courses. I have a form where similar operation happens but thats for one course to multiple employees. Was not sure how to expand that to multiple courses to multiple employees.

From what I have understood, if I have two tables (desired employees, and desired courses), and they sit in a query without any joins, then each course will be assigned to each employee. Will implement that and let you all know how it goes

 

[nashaz]

You would create a form with a subform to do the normal data entry. This mass entry is done with an append query. Access can't just make up the data. For the initial load, you would enter only the employees who took the classes and only the specific classes they all took. Then the append works fine. Going forward, there won't be any "automatic" way to generate the data to do the append query so you may as well just use the class main form and the employee subform. Access needs criteria to select data. When you have 200 employees but only 20 take a class, how do you select the employees? One at a time? Maybe you can do something with job category so that only welders take the class on welding safty. We have no idea how you need this to work. All we see is your initial request which is not how this application will work going forward unless - every employee takes every class, all on the same day.

I'll attach a link to a database that shows how to work with a many-many relationship from both sides of the relationship. In some cases you would use a main form with a subform but in others you would use a list form with a double click to open the separate child form. Both views are valid but one will make more sense in any particular situation than the other.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

I would have done job category but thats not possible in this instance unfortunately. I also have to think about absences, as well as some employees were partially present. All of this data I have a physical record of. Just need to translate that into db.

Thanks for the attached db. I will go through it and let you know the outcome

 

[arnelgp]

If I was to open individual employee record, and update the training subform within that (which is currently what I have), it would take ages.

if you go on and started the task(since Monday), you'd be finish by now.

 

[ebs17]

Just wanted to see if I could simplify this operation by having all employees in a list where I could select the ones whose record I would need to update

Of course, that's task for a form. You have a ListBox with multiple selection set. The selection in the ListBox is evaluated using VBA and a filter for the query is created and incorporated into the query. This filter then looks different than the selected = True used in the example.
You will find many examples of this because this task has already been solved many times and has been discussed many times.

 

[Pat Hartman]

The first sample I sent is how to create a many-many app. Here is one that shows how to use a multi-select listbox to control a query which may be what you were looking for. Sample #4 may get you started.

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

But, I agree, you need to get started at some point and create the three tables and populate two of them by hand or using some import. Then you can get to the process of populating the junction table.