Results 1 to 5 of 5
  1. #1
    Man_Over_Game is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    12

    Difficulties utilizing "junction" or "Many-To-Many" table in form, Round 2

    I originally posted a long time ago on this thread (https://www.accessforums.net/showthread.php?t=72692), and I wanted to followup more on the concept, rather than that thread's specific solution (which turned out to be quite simple, using a Date field as a primary key along with a person's name).



    Below, I've posted an example of the situation I'm having:

    Click image for larger version. 

Name:	dtb_Picture.png 
Views:	45 
Size:	49.6 KB 
ID:	39610

    Or, to put in writing:

    • Each Person has a Position.
    • Each Position has a list of Training Types they're assigned (for example, everyone has the "Universal" training type, but "Administrative" is specific to certain Positions).
    • Each Training Type has a list of Trainings that are under that type. ("Sexual Harassment" is a "Universal" training type, "Record Keeping" is an "Administrative" training type).
    • Each unique combination of Persons and Trainings need to be tracked to determine when they completed their Training, to track how soon they need to renew their Training (from the Training table).



    My questions are:

    1. What's the best way of putting this in a user-friendly form?
    2. How would I effectively do reports with this kind of database?
    3. Are there any major mistakes I've made when setting up the basics for this database (like the fact that I mostly use surrogate keys)?


    Or, could you supply resources where I could find information on this topic? I've found a decent amount of basic level stuff related to Many-to-Many tables (like how to properly join them), but hardly any information is available for actually utilizing them in forms and reports. Dor example, finding out what Trainings someone is responsible for based on their Position, then marking the day that Training was complete. Or scanning specific areas to count what kind of trainings they have a major deficit in to schedule a training day.

    I'm expecting it has to use a number of varying sub forms to get it done, but I'm not very familiar with the intricacies of using multiple super/sub forms together (like gathering information from two parent sources to find a specific child record), and experimenting hasn't worked out.

    In my current version, I've gotten most of it to work (80% or so) but it's not updating the dates correctly, and I'd rather start from scratch to make sure it's made properly from the ground up rather than building on a weak foundation. I also want to learn how to do it right, rather than utilizing something that feels like it "just happens" to work.
    Last edited by Man_Over_Game; 08-29-2019 at 05:26 PM.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    how robust do you intend this to be? With your current structure a client can have 1 and only one position, in other words what if someone gets an internal promotion? you might update the tests required of them but what happens if some of the testing overlaps, would they be required to redo the training or would it carry over? Does your system have any overlapping tests? for instance does the sexual harassment training appear in any training group other than 'universal'? If the testing items are unique to each group that makes things a bit easier but you would still have to account for things like internal promotions.

    Secondly, I would tend to approach this a bit differently

    Employee Table ----> Position Table (including start and end dates)

    This would allow you to mark a person's history within the company and might make it easier to terminate any training groups no longer required in the new position.

    As for the individual training items, I would tend to approach it like:

    Postion ----> Training Group -----> Individual Training

    so for any given position you could bring up a list of training groups and the individual training sessions under those groups. Then, whenever you had a new hire or an internal promotion you can look up all the individual training items they need, add those to the employee test results table and simply record the date completed. Further, for each individual training you could record the number of days (or years or months) the training was good for and run any reminders you needed via a calculation.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,704
    I don't know how relevant this older model may be to you, but I'm attaching it for reference.
    It has been posted previously. Good luck.
    Click image for larger version. 

Name:	EmployeePositionTraining.jpg 
Views:	30 
Size:	40.9 KB 
ID:	39669

  4. #4
    Man_Over_Game is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    12
    Quote Originally Posted by rpeare View Post
    how robust do you intend this to be? With your current structure a client can have 1 and only one position, in other words what if someone gets an internal promotion? you might update the tests required of them but what happens if some of the testing overlaps, would they be required to redo the training or would it carry over? Does your system have any overlapping tests? for instance does the sexual harassment training appear in any training group other than 'universal'? If the testing items are unique to each group that makes things a bit easier but you would still have to account for things like internal promotions.

    Secondly, I would tend to approach this a bit differently

    Employee Table ----> Position Table (including start and end dates)

    This would allow you to mark a person's history within the company and might make it easier to terminate any training groups no longer required in the new position.

    As for the individual training items, I would tend to approach it like:

    Postion ----> Training Group -----> Individual Training

    so for any given position you could bring up a list of training groups and the individual training sessions under those groups. Then, whenever you had a new hire or an internal promotion you can look up all the individual training items they need, add those to the employee test results table and simply record the date completed. Further, for each individual training you could record the number of days (or years or months) the training was good for and run any reminders you needed via a calculation.
    Very valid point regarding the end/being dates. As for your suggestion for the position-training, those are some pretty important components for what I hope to be is a final version.

    Quote Originally Posted by orange View Post
    I don't know how relevant this older model may be to you, but I'm attaching it for reference.
    It has been posted previously. Good luck.
    Click image for larger version. 

Name:	EmployeePositionTraining.jpg 
Views:	30 
Size:	40.9 KB 
ID:	39669
    Thanks, orange. Your design does incorporate having a single employee cover multiple positions, which is a lot more flexible than the original design. It does need some modification to include things like training categories, but that's a relatively easy change.

    My major concern is, though, how do I actually use this information in forms to add/populate data?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,704
    I recommend you start with a description of the business in simple, plain English. Getting that on paper and accepted by others who know the business or who are trying to help you design a database to support that business is key to getting your tables and relationships set up. This is basic to building a data model. And that data model cn be tested with some test data and test scenarios.
    Often helps if you can identify or even mock up the output(s) you expect. From the outputs, you can determine the inputs and manipulations/processing required to present the output(s).

    It seems you have
    -Employees
    -Positions
    -Employees occupy Positions
    -Some Positions may require successful completion of specific Training Course(s)
    -Some Employees may not have necessary Training Course(s)
    -TrainingCourses and Sessions
    -TrainingTypes (Admin, Universal....)
    -Employees are assigned a TrainingCourse in a particular Session
    -Employee could pass/fail/not attend/withdraw..... from a TrainingCourse/Session
    -Some TrainingCourses may have a "duration/good until date" after which the Employee must be recertified( or similar concept)

    Various reports/queries to be determined.

    These are the things readers can only guess. We need details and examples to offer more focused info/advice.

    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 08-29-2019, 04:52 PM
  2. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  5. Replies: 1
    Last Post: 09-03-2014, 03:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums