Unanswered: Where to go next with a many-to-many relationship table?
I'm a self taught Excel junkie and I've been the go-to guy at work for the last couple years when it comes to fancy spreadsheets. I've recently been asked to develop an Access database because they have nobody else to ask, and I agreed to give it a shot. Prior to this project I've had no Access experience. One of the ways I gained proficiency in Excel was by taking apart and reverse engineering some fancy spreadsheets that were left by the previous resident Excel wizard; however I have no such references for Access. I've been googling a lot of beginner access stuff (took me a while to start thinking with a "database mindset" instead of my "spreadsheet mindset") and digging through these forums (you guys are really great and supportive community!) and I think I have the skeleton of the database I'm trying to create in place.
The database itself is to manage mandatory company training classes. I've created 3 basic tables: first is an employee table with id number, first and last name, and position; second is a positions table of all the positions our company has (relationship to the position field of the employee table); third is a training class table with the name of each class and how many days before the class must be taken again. Following advice from online, I created a 4th table to help create a many-to-many relationship, with an autonumber ID, employee ID number from the employee table, the class they need to take from the training class table, and a field to record the date completed.
What I'm struggling with now is how to create all the records in this 4th table. Some classes need to be taken by all employees, some classes by only employees in a few different positions, and some classes are specific to one position. I've been searching for a way to mass-create records for a class for every employee in specified positions. I've been trying to use the append query to achieve this but I haven't been able to get anywhere close.
So I pose the following questions to the Access savvy: Is my database structured improperly for the results I'm trying to achieve? Is there an easy way to achieve the type of record creation I have envisioned?
Any tips, tricks, or advice would be much appreciated. I've created a sample database and attached it. If there's anyone out there willing to build upon my sample database I would be beyond grateful, as taking something apart and putting it back together is really how I best learn.
Thanks to anyone taking the time to read and share advice!