Designing the tables and relations

[Enzym]

Hi! I am pretty rusty at designing access databases, haven't done it since school at least 15 years ago.
I am now trying to design a database for handling personnel. I need to have personnel data, their position/rank, and then the courses/training required for each position, and what courses they have taken.

Tried starting out with the classic order management design, but cannot wrap my head around it.

I want to end up with a form (with a subform, i guess), that shows all the persons details (name, phone, mail, etc), the persons position/rank, what courses that are mandatory, and what courses that are completed. If the person changes position, the mandatory courses should change to match correspondingly, but the ones already taken should be listed.



I split the data into a table with personal details, one with rank, one with positions (with relations and foreign keys, so far so good), but ran into trouble when trying to figure out how to solve the course-problem.

Any input and tips are greatly appreciated, so I can keep the rest of my hair..

Thanks, Enzym

[June7]

Suggest a table that relates positions with courses. Then another table that documents courses taken by each person.

[Enzym]

Something like this, or am I lost ?

[orange]

See if the diagram here is helpful.

[Enzym]

It seems pretty helpful, but I am still a bit lost what to do next, in order to have a form with "employee" data, with subforms showing what courses are required for the current selected position for that person, and which ones he/she has completed. (And later on a report showing all required courses not taken, for all "employees"..)

[orange]

Start with your tables and relationships. Don't have embedded spaces in your field names.
Forms will come later. The key is to get your tables designed to meet your needs.