Enforce all possible records in a many to many relationship

[timeck]

Hello. I've been trying my best to find a solution for what I would have assumed a common issue.

I am scratching my head to figure out a way to populate a junction table with all possible connections in a many to many relationship.

The background story is I need to track a set of skills common to several workers. We practice a 3 iteration sign off to become fully certified to do something. In the junction table, I have (simplified for example purposes):

ID (PK: autonumber)
Worker (FK to tbl_workers : text)
skill (FK to tbl_skills : text)
iteration1 (date)
iteration2 (date)
iteration3 (date)
skillScore (calculated: iif( [iteration3] > 0, 1, iif( [iteration2] > 0 , .66, iif( [iteration1] > 0, .33, 0)))


When this is done, I would like to do the following:

Let's assume there are 50 skills to measure per worker. If I wanted to add a new worker, then 50 records will be appended programatically to represent the new worker and the 50 skills he/she will be measured on. I must be able to populate a subform for an evaluator to find a specific skill for the trainee even if they have not had any iterations yet.



I would like to make this go the other way around. If we identify a new skill, then a new record for each worker will be appended to the junction table.

This seems like something I could pull off with multipass queries but I can't wrap my head around how to pull this off. If you haven't figured this out yet, I am rather new to database development.

[orange]

How about 3 tables for the general approach

tblEmployee --info about employee
tblSkill --info about a Skill
tblJNCEmployeeHasSkill --info about this Employee and this Skill (perhaps on this Date)

WHERE tblJNCEmployeeHasSkill is intended to show which Skill(s) is/are "possessed" by which Employee(s).

tblEmployee
1 - John Smith
2 - Jihad Alzerez
3 - MaryLou Jones

tblSkill
1 - Java Programming Level 1
2 - Computer Diagnostics level 3
3 - Access - Introductory
4 - Access - Intermediate
5 - Access - Advanced

tblJNCEmployeeHasSkill

1 1 2 (John Smith, Computer Diagnostics level 3)
2 1 3 (John Smith, Access -Introductory)
3 3 1 (MaryLou Jones, Java Programming Level 1)


You add a new Skill to the tblSkill when the Skill is identified.
You add a new Employee to tblEmployee when you hire/add a new Employee to your org
You add a record for EmployeeHasSkill when an existing Employee has been qualified with that Skill.

With database you don't (normally) create records in anticipation of somebody receiving/attaining something.

Iteration may be a separate table depending on exactly what its business purpose is.
You may be able to count the records in tblJNCEmployeeHasSkill, and if an Employee has 3 successful records for Skill X (within some time period), then that Employee is "certified or whatever your term is".
May not need a separate table for iteration, it can be calculated by means of a query.

[ItsMe]

...If I wanted to add a new worker, then 50 records will be appended programatically to represent the new worker and the 50 skills he/she will be measured on...

Glossing over your post, I find this to be problematic. I would be appending records to the junction table. Add the EmployeeID and the SkillID values to the junction table only after it has been verified the employee has the skill. It will be unlikely a given employee would have 50 records in the junction table. Maybe an average would be 7 records to represent their 7 verified skills.

[timeck]

I appreciate the input. I needed a budge in perspective.
I was introduced to this dataset from someone else through a matrix in Excel and I volunteered to beef it up a bit to easily allow additions to it. All along I was trying to roughly emulate their format but as long as I structure the db properly, then data entry shouldn't be very difficult.
Eventually I'll have categories where the skills will weigh in. Some skills will apply to multiple categories (but not necessarily all categories).
The goal is to allow a shift lead to divvy out jobs based on work priorities. Measuring a shifts abilities by categories should help them get a better grasp of who's capable of what. Higher priorities go to the experts while the lesser ones are more open to training opportunities.
This will be my first Access project that have an audience other than myself; which as you likely already know encompasses more facets of Access than one would if the db had an audience of one (myself). Wish me luck as I know there is much to cover and I'm likely looking at months before I'm done.

I'm going to proceed with creating a single record for 1 evaluation iteration. Once 3 records are entered, then that person will be considered "certified" for the individual task.

Thank you very much!

[ItsMe]

One of the hardest things for me to do is move operations from Excel to Access. I would rather move from a file cabinet filled with paper.

[orange]

The goal is to allow a shift lead to divvy out jobs based on work priorities. Measuring a shifts abilities by categories should help them get a better grasp of who's capable of what. Higher priorities go to the experts while the lesser ones are more open to training opportunities.
This will be my first Access project that have an audience other than myself; which as you likely already know encompasses more facets of Access than one would if the db had an audience of one (myself). Wish me luck as I know there is much to cover and I'm likely looking at months before I'm done.

I realize you said this is your first Access project, but let me give you an analogy. This is hypothetical, but trying to make a point.

Suppose instead of starting with the Excel matrix, you got an overview of a potential task/project. Suppose the project was to build a 3 storey office tower. (forget any additional details at this time).
What would you need to start?

Perhaps a description/overview of the purpose of the office tower. And some dimensions of the physical building? What about existing or new infrastructure -- water/ electricity/ sewage...? I'm hoping you may have said -- perhaps a blueprint or plan.

From my perspective as reader, I see these things

Employees
Shifts
ShiftLead
EmployeeSkills and Degree of Certification
Skills
Abilities (expert, lesser??)
Categories
Work/Jobs
WorkPriorities
Opportunities
TrainingOpportunities

and you were the one who identified them. My guess is these would be in a "big picture" of what you foresee. If you build the big picture view, you can build various pieces as resources permit, and you will know where and how the pieces fit in the big picture.

Just something to ponder as you start your first multi-user database application.
Also, Access is interesting, but database generally involves more than Access.
Here's a tutorial to help with building the big picture and learning about database design.
You have to work through it (1/2 to 1 hour), but you will learn some critical concepts.

Good luck with your project.

[keviny04]

When this is done, I would like to do the following:

Let's assume there are 50 skills to measure per worker. If I wanted to add a new worker, then 50 records will be appended programatically to represent the new worker and the 50 skills he/she will be measured on. I must be able to populate a subform for an evaluator to find a specific skill for the trainee even if they have not had any iterations yet.

I would like to make this go the other way around. If we identify a new skill, then a new record for each worker will be appended to the junction table.

This seems like something I could pull off with multipass queries but I can't wrap my head around how to pull this off. If you haven't figured this out yet, I am rather new to database development.

You DON'T have to populate records back and forth; that would go squarely against the nature of a relational database, not to mention leading to A LOT of duplicate data.

This is how a many-to-many relationship is usually handled in a form:



You link the subforms together by the tables' key fields. Here, the Evaluation subform's WorkerID and SkillID fields are linked to the Workers table's WorkerID field and the Skills table's SkillID field. Here is the database: workers.zip

The way the form works is that you select a worker in the Worker subform, then select a skill in the Skills subform, and the Evaluations subform will show the evaluation for that particular worker and skill.

You said you want your evaluator to see all 50 skills even without iteration. The goal is already achieved with the Skills subform, which shows all 50 skills at all times.

If you have new workers, just add them to the Workers subform; no need to add them anywhere else. If you have new skills, just add them to the Skills subform; no need to add them anywhere else. The Evaluations subform will automatically "use" the worker and skill info every time you enter a record. This is the way of relational database.

[timeck]

Bravo! This is fantastic. Any tips on where to go to learn how you did this (preferably YouTube)? My efforts in reverse engineering that haven't come close.
I would like to learn how to apply some sort of filter controls for the worker and skills subforms after adding a workshift and "skill category" table respectively.

[keviny04]

Bravo! This is fantastic. Any tips on where to go to learn how you did this (preferably YouTube)? My efforts in reverse engineering that haven't come close.
I would like to learn how to apply some sort of filter controls for the worker and skills subforms after adding a workshift and "skill category" table respectively.

There are guides out there, such as this online guide and this Youtube video, but none as well-written as I would like. And some books don't cover it at all. And yet, this is probably one of the most asked-about topics. I joined this forum barely a week ago and you are the third person I have helped with a similar query. This is under form design, of course, and not really about database design. But let me outline it briefly here. If you need additional help you may need to post in the Forms section. Make sure you ask about "linking multiple subforms on a form". You may look at this past thread where I assisted on a similar problem.

Let me just describe how I created the form I attached in my last post:

1. The 3 subforms were all made with automatic form generation. In Access 2007/2010, it is as simple as clicking on a table, clicking "Create" at the top, then clicking the "Form" button. Instant form creation. I did this for the Workers, Skills, and Evaluations subforms.

2. Then I created a blank form called "Main form". I put the 3 subforms on the main form as you see.

3. Above the Workers subform, I added an invisible textbox (name: WorkerID; control source: =[fsubWorkers]![WorkerID] ). The purpose of this is to pull out the current WorkerID value on the Workers subform.

4. Above the Skills subform, I added an invisible textbox (name: SkillID; control source: =[fsubSkills]![SkillID] ). The purpose of this is to pull out the current SkillID value on the Skills subform.

5. On the Evaluations subform's property sheet, I set "Link Master Fields" and "Link Child Fields" to both WorkerID;SkillID .


And that is all you need to get it working.

The link master fields WorkerID;SkillID refer to the two textboxes created in (3) and (4) above.

The link child fields WorkerID;SkillID refer the values of WorkerID and SkillID inside the Evaluations subform. This is how the link is established among the 3 subforms.

[orange]

There are some very good free videos re Access tips and techniques at Datapig.
They use an older version of Access but the concepts are same.
For subforms see

subform1
subform2

[timeck]

Bear with me here. This becomes a major redirect in the subject but, is there an easy way to retain the functionality of the workers and skills SUBFORMS but have them in a cascading combobox format?

Our group is known to have fat fingers.

[keviny04]

Bear with me here. This becomes a major redirect in the subject but, is there an easy way to retain the functionality of the workers and skills SUBFORMS but have them in a cascading combobox format?

Our group is known to have fat fingers.

The way you set up in your picture is fine, except that there is no means for you to enter new workers and skills, which is what the subforms let you do. Other than that, it should serve your purpose of linking the various tables together.

Also, the two fields you added, Shift and Category, do you want them to be linked to the Evaluations subform? If you do, you must have a DIRECT link to the Evaluations subform for this to work. That means the Evaluations subform must have Shift and Category in it.

[timeck]

Adding Skills and Workers would be more of an admistrative task so I'd have some other form(s) to set that up. My ultimate vision is to add user login with permission levels to allow recording who evaluated the worker (a la SharePoint's "last modified by" field in many of their lists/libraries). Admin permissions will allow the manipulation of the outlying tables (worker, skills, etc.). Another pipe dream is to have evaluator permission controls based on the skill category (so only inviduals who are 100% certified in a category and with supervisor approval can evaluate on that specific category). I'm nowhere near that level of experience so it will be an ongoing project.

As far as the shift and category fields, they have outer/satellite/inpertinent (don't know the lingo) relations that don't affect the functionality you brought up. They will mainly help the evaluator find the worker and skills.



I will have some skills that apply to multiple categories, just from the nature of our work, and bucketing those into a "general" category won't suffice. What matters is that the junction tblEvaluations just lists out the worker, skill, iterations and score. How all of that will pivot out should work from my perspective, however shortsighted that tends to be!

I plan to link this data into a few Pivot Tables in Excel and give an overall score by Category on worker, shift, and overall summary levels. This is mainly due to me being 2 of around 60 people who lightly touch Access. Everyone else is intermediate (at best) in Excel. Plus from my experience, Excel seems to be better at displaying information.

[timeck]

Ahh, skills and categories should be many to many based on what I said.

[keviny04]

timeck, your table design as shown in your diagrams looks sound to me, except for one thing. You said you wanted to record who evaluated the workers and when. Right now, your Evaluations table is not structured in a way to accommodate that. Even though you said this goal was for the distant future, sometimes a particular objective needs to be taken into account at the very beginning when you design tables. And this is one of those objectives, because it involves how your tables are designed and related.

Your Evaluations table has three columns for iteration 1, 2, and 3, and that in itself is questionable design: you DON'T add columns if you have many of the same type of data; you put them in ROWS instead. It works now only because you don't have much data yet. But If you add evaluators and, who knows, other info in the future, you will find it difficult without restructuring your table. Better do it at the beginning than later.

Precisely, you need a one-to-many relationship here. The "one" table is the Evaluations table. Add a "many" table that we may call EvaluationDetail that comprises of iterations and evaluators.



Note that I use a generic line number field (line_no) as the co-primary key for the table. This is done in case you have duplicate evaluators for the same evaluation, e.g. one evaluator working on the same evaluation more than once.

This "many" table makes possible for each evaluation to have multiple evaluators and multiple iterations. This is the kind of table relationship and design that make sense in a relational database.

Say you have an evaluation with an EvalID of 4, the EvaluationDetail table may contain:



The next thing is, of course, adding a normalized Evaluators table, e.g. fields: EvaluatorID (primary key), EvaluatorName