Tables, Queries or Forms

[dichotomous]

I'm not quite sure where to post this question.

I have a table that records:

Department Tasks
Procedures for each task
Associated Tooling (this is a lookup)
Associated hazards


I want to be able to
Assign tasks to employee, pick out that task and print the related proceddures.

Obviously a query will be involved from which I'll run my report.

Am I asking too much from my table/query????

Basically, the end result is, I want to be able to assign series of tasks to employees as they've been trained on them.....

How do I structure my table, or, do I need two tables that are related?

thank you in advance.

 

[Beetle]

How do I structure my table, or, do I need two tables that are related?

How about three?

You are describing a many-to-many relationship. An Employee can be trained on many tasks and a given Task can have many Employees trained for it. A relationship of this nature needs a junction table. Example;

tblEmployees
EmpID (Autonumber Primary Key)
FirstName
LastName
'other fields specific to the Employee

tblTasks
TaskID (Autonumber PK)
TaskDescription
'other fields specific to a Task

tblEmpTasks (the junction table)
EmpID (Foreign Key to tblEmployees)
TaskID (FK to tblTasks)
TrainingDate
'other fields specific to the relationship

In the junction table EmpID and TaskID could be a compoundPK, or you could use a surrogate PK field.

 

[dichotomous]

cool

Many thanks for your insightful reply.

I'll read your notes and do some research on Junction tables for Many-Many relationships.

I wont ask any more daft questions till I've done so.

 

[dichotomous]

Just one more thing

So, the junction table would be where I pulled the query from, if, say, I wanted to see what training Joe Bloggs had had?

Or, if I wanted to see who had been trained on the Setting Procedures for Die # 105698 in Press No1?

Regards
D

 

[Beetle]

You would use a main form / sub form, where the main form is based on either the Employee table or the Tasks table (you could have one of each) and the sub form is based on the junction table. You can find more information on that, plus a small sample file in post #4, in this thread. However, get your table structure right before you start creating forms.

 

[dichotomous]

Beetle.

This is my first trial attempt at One-to many. Can you have a look?

I'm not sure im getting the right filter on employees/training modules.

What have I not done?

Cheers.

 

[dichotomous]

Here tis.

This is access 2007 btw.

 

[Beetle]

Looked at your db. There are a few problems here;

1) You have a Dept field in every table. This is incorrect. I'm not sure how the Dept relates to your other entities, so that needs to be determined so this can be corrected.

2) You should establish a Primary Key for your junction table

3) You have repeating fields in your Training Codes table. This is also incorrect. If a given Training operation can have multiple Procedures or Associated Hazards, then those should be stored in child tables.

It appears that you are going to need a couple more tables here, your junction table may even need a child table of its own, but I would need to know more detail about the nature of what your trying to model in order to be specific. Just state in plain english what you need to track, like;

"Employees need to take training once a month. Each training session will be specific to one department, but may cover multiple procedures related to that department..." and so on.

 

[dichotomous]

Ok Sean.

What I want is this:

Look up employee.

Say it's a galvanizing dept employee.

Have they had module Galv01? What modules have they had, I want to record that against the employee - do I need some sort of tick box??

Also, however, I want to be able to record procedures for Galv01 (and other modules of course) step by step. So that, I can print out Galv01, to give to a supervisor to train a new employee.

I think I"m being over ambitious for my skill level with Access in what I want, but I know it's possible. I've gotten this far by myself and on-line tutorials.

Many thanks in advance for your help.
Regards
Alastair.

 

[spikepl]

Is this an academic exercise? If not, then I have some comments for you:

1. Your company has not started in business today, has it? That means they already have a number of procedures in place for dealing with training. Survey those first. Some will be logical and based on company experience, some will be illogical and based on stuff that no one can explain any more ("this is the way we do it here since 1931"), and the remainder will be some mix.

2. You (or your boss) will have to ensure buy-in of the parties involved. The best system in the world cannot defeat organisational resistance by itself. Without that, your system can be killed by some key persons.

3. You want to "record procedures" ? Why? Probably piles of papers and files already exist -perhaps in some pre-approved format ...some may hold graphics, or external data .... You want to be able to refer to them, or even link to them, if these files are located on the server, but storing them in the db does not necessarily make much sense.

 

[dichotomous]

There are many things that are not Proceduralized here, i.e; written down, and, have just been handed on verbally from employee to employee.

And, you are right, there are a lot of pre-established written procedures.

What does not exist is a complete list of tasks on site and the associated hazards. I am in the process of recording these.

I have recorded these for some new procedures related to a new industrial process we have incorporated. I'm now retrospectively working my way through the rest of the operation.

Each employee will have a record set detailing what they've been trained on/in or for, specific to their department.

When I arrived, there were many disparate systems. How I chose to bring those together and record the results is largely up to me. Autocratically, resistence will be dealt with. Said with tounge firmly in cheek!

I've built:

A Die Register; that records maintenance details for each Die and prints reports on this, as well as procedures for set up/shut height etc.
Accident Register
Hazard Register
And a pile of other registers.

Access has given me a lot of control over data through reporting functions we didn't have before. Trend analysis is the most valuable tool in managing on-site safety performance.

Alastair.

 

[spikepl]

Autocratically, resistence will be dealt with.
(Hehe, that reminds me of "Resistance is futile - you will be assimilated" )

Hmmm. That seldom works effectively, outside of the military that is. Since your system will change the normal order of things and the power that various people hold, then the customary thing to do would be a so-called stakeholder analysis: who is affected, what are their interests (i.e. stated positions and true interest), what power do they hold to aid/scupper the system ..., how to make the system attractive to each and one ...

Just because your system is obviously brilliant or better or more efficient or cheaper or (whatever superlative), it does not mean it will be accepted and supported by the ones affected. It may well be that you are lucky, though...

 

[dichotomous]

Several things are in my favour (and this is my last post on the subject of; benefits or non for implementing organizational change).

1) My boss is an autocrat
2) My job is to change the HSE culture with the implementation of a system
3) I have complete autonomy in the development of a functioning system (provideded it is within the parameters of the HS&E Act 1992)

I'm aware the 1 and 2 are in conflict, however, the fear of huge fines for failure to comply is a good motivator.

Moving right along, I'm waiting for Beetle's reply.

thank you for your input (or not).

Cheers.

 

[Beetle]

Have they had module Galv01? What modules have they had, I want to record that against the employee

You started out using the term Task, now your using the term Module. Are we still talking about the same thing?

Also, however, I want to be able to record procedures for Galv01

Do the procedures for a module have any relationship to the employee? In other words, do employees get trained on each specific procedure of a module, or do they just get trained on a module and the procedures are a separate entity, related to the module but not to the employee?

 

[Beetle]

@ OP

I think you had a reply here that got lost when the site crashed. Post back if you are still looking for help on this.

 

[dichotomous]

Sean

many thanks for your input thus far. I've attached what I've done so far. If you could have a looksie, that'd be great.

What do you think of my relationship structure - am I on the right track???

AliG

 

[dichotomous]

Here you go Sean.


Thanks for pointing me in the right direction.

Alastair.

 

[Beetle]

OK dichotomous, sorry it took me a bit to get back to this but here is some food for thought.

You're close, but your Procedures table still has repeating fields. When designing tables in a relational database, whenever you find yourself creating fields in a table and you're naming them -

Something1
Something2
Something3
Something4

- and so on, that's usually a clue that you're doing something wrong and you should stop. You should just have one field to store the Procedure plus a Foreign Key field to relate it to the Training Code in the parent table (you got the FK part right, just not the Procedure field). Training Codes that have multiple Procedures will then have multiple rows in the Procedures table, not multiple columns. Each Training Code can then have as many, or as few, related Procedures as are necessary.

The way you're doing it now, for each Training Code that has fewer than 20 related Procedures you have empty fields in your table. What's worse though, is that if in the future you ever need to have more than 20 related Procedures for a Training Code you're screwed (that's a technical term ). You have to add more fields to the Procedures table and subsequently go back and redesign every object (query, form, report) that is based on that table. You likely designed it this way on the premise that you will never need more than 20 Procedures. All I can say to that is the database landscape is littered with the remains of projects who's designers were positive that they would never need more than x number of fields to store an attribute (a couple of my projects from when I first started are in that graveyard as a matter of fact). Also, you should not need to store the Training Description in the Procedures table, just the PK value (in this case Code) as a foreign key.

I'm still curious about the nature of the training as well. This was discussed a bit previously but I don't think I ever got a clear understanding of it. In essence, the question is;

Do employees get trained on the individual Procedures of a Code, possibly at different times? So they might get trained on Procedures 1 through 5 of Training Code X during one session, Procedures 6 through 10 in another session, etc.

-Or-

Is each training session specific to a single Code and encompasses all Procedures of that Code during that one session?

-Or-

Something else?

The answer to this is important in getting the correct table structure.

 

[dichotomous]

Hi

Yes. Generally a training session would be to cover a Code (say) Galv01 for instance.

Each step would be covered in that session (each procedure). However, in saying that, competence may not be reached from that training session and the training may be repeated until supervisor is happy they are competent. I would only tick complete in my database when supervisor advsed me that Competence in Code/Module Galv01 had been reached.

Alastair.

 

[dichotomous]

And yes

A Code (don't like that word - but i've used it for the DB) or module may have quite a number of procedures that need to be broken down into more than one training session.

Some of our refrigeration parts we make have three or four separate phases, each with procedural steps.

1) Setting the press - which includes selection of the tool, putting tool into press, setting shut height (doing lead test)
2) Reading the Job Instruction sheet for manufacture of that part, comprehension of the quality check document, etc
3) press operation - press operation is a (Code/Module) on its own...........

Hope this gives you a clearer picture.


Alastair.