Need help with the next stage of this....

[Big Pat]

I’ve spent hours reading posts, here and elsewhere but I guess I don’t even know what to search for!

My database contains details of medical staff and the jobs they do.
- Each JOB requires several skills and this list may be updated at any time.
- Each PERSON may or may not currently have the required skills (i.e. they may be still in training)

New staff may be added at any time and based on the job selected, their REQUIRED skills should be immediately displayed.
So, when scrolling to a particular staff record, the skills they require are visible, but the user also needs to be able to quickly update the database to say whether that person HAS those skills.

This is where I’m stuck. When I agreed to take on this project for my employers, the concept sounded fairly simple and I’m sure it is really, but I’m having some kind of mental block. I just can’t get my head around what the next step should be.

I have attached an example database, with a small number of records and the comments in blue on the form explain what I’m trying to do.
I hope the format of the database is OK. We've just upgraded to Office 2007 (not liking it much so far!), but I have saved this copy in Access 2000 to ensure most people can open it. I’m not sure how it will look.

A word of warning: I’m not a programmer or designer and need to do nearly everything by wizards and property sheets. I don’t follow much coding except maybe some DoCmd stuff and basic IF statements to make things visible or invisible sometimes. So be gentle with me!!!

Thanks for your time and I hope someone can get me started on the right lines.

 

[The_Doc_Man]

Without looking at your design, I'll just expound on the theory here and hope it gives you focus.

You have employees. They have jobs (roles) and skills (abilities).

The jobs have REQUIRED skills. The employess have skills, required or not. I see not less than five tables involved here.

First things first. If a person only holds one job at a time, you can make a simplifying assumption. IF you recognize it as an ASSUMPTION. If the assumption ever breaks, your life gets much tougher. But... here is the assumption.

tblJobs - a desciption of the jobs that people can hold. Includes prime key of some number to act as a job code. The nature of the number doesn't matter. Uniqueness does.

tblPersons - all identifiying information about each person. HERE IS THE ASSUMPTION. If jobs are one//one with people, you can include the Job table's identifying code as a foreign key in this table. If the assumption is wrong, you need a junction table and that becomes really ambitious. Each person again has a unique identifying number.

tblSkills - a list of skill names, descriptions, qualifications, and an identifying code number. Again, unique code.

And two junction tables.

tblJobSkills - a list that contains one entry for every skill required for any job. You will have many skills for each job. The table MIGHT be as simple as the job code number and the skill code number. It is a list of skills you must hold for job X. You will put lists of skills in here,

tblPersonSkills - a list that contains one entry for every skill held by any person AND any skill REQUIRED for the job that person holds.

This table will have the person id number, skill id number, and a couple of other items, perhaps yes/no flags. First, does the person have this skill. Second, does the person's job require this skill. Other items are possible but this is the minimum.

The logic behind this table is simple. Each entry is a skill that relates to a person. If the person has a job, you append records to this table for every skill the person needs but doesn't have, and set those flags as Has: NO, Needs: YES. For every needed skill the person has already, set the flags as Has: YES, Needs: YES. For every skill the person got in a previous rotation, you would set the flags as Has: YES, Needs: NO.

Every time a person changes jobs, you would update this table to remove the Needs: YES flag if the new job doesn't need that skill. But once a skills entry is set to Has: YES, you never reset it to NO. You can only downgrade the Needs flag, not the Has flag.

Of course, the final case for those flags is Has: NO, Needs: NO - but there is no reason to store such a record for any person. Once you have your lists complete, you can assume that if there is no entry in the junction table, the person neither has nor needs the skill.

This is complex, but can be done through forms, form wizards, and proper join queries. Here is how you make JOIN queries. First, note that we talked about foreign keys. If you define a relationship between two tables such that the prime key on one is the foreign key on another, you have set up Access to automagically create JOIN queries when you create a query with two tables as data sources and you have defined the relationship between the tables.

As to forms, they don't give a rat's patoot whether you feed data from a table or a query or a JOIN query. The design wizards love anything that looks like a good recordset. (Yeah, I know... makes 'em sound dumb. Well {wink and conspiratorial whisper} they ARE dumb!)

The idea here is that for persons, skills can be held incidentally. So you need two flags. One to show requirements. One to show incidentals. For instance, my skills might include "boil lancing" and "carbuncle shaving" and "supraorbital depilation" - but only the latter skill is required for the (hypothetical) job of "medical cosmetologist" - that skill that helps doctors cover up their mistakes...

Seriously, this approach I have given you doesn't include history and doesn't include consideration of qualifying scores and exams and such. Those things are wrinkles on the problem that you can add yourself - or ask a separate question or browse the forum on the subject of historical tracking.

 

[Big Pat]

Wow Doc Man,

I understood most of that (and no-one is more surpised by that than me!)

I must be on the right track design-wise because I have those five tables you listed. Almost identical except I've used the term Staff instead of Person.

I need to go through your response in detail and try to understand more about relationships. I think I've got them though.

Just need to figure out how to get the stuff written to the table. Sounds like I might need both append and delete queries.

I may need to get back to you, but in the meantime, thank you VERY much for your input.

Pat.