joining tables

  • Thread starter Thread starter Lori2836 via AccessMonster.com
  • Start date Start date
L

Lori2836 via AccessMonster.com

Hi. I'm confused and just can't seem to grasp how a 3rd table would result
from:

Table 1 = Employees and employee info
Table 2 = Procedures and dates completed(there are 500 different procedures
that any employee can be trained on at anytime....there is no schedule for
these)

I have a 3rd table I have created which has Employee ID (from table 1) and
Procedure ID (from table 2). I just can't understand what to do with it. I
need to create a form where I can click on the employee name at the top and
then be able to fill in the date(s) when a procedure has been completed.
And another form where I can click on the Procedure and have it show me which
employees have been trained. I wanted to do it all in one table, which is
too much data. That would make it much easier for me to understand.

Can someone clarify what I need to do? My mind is going in circles here
trying to put it all together!
Thanks,
Lori
 
Hi. I'm confused and just can't seem to grasp how a 3rd table would result
from:
Click to expand...

You've got it backwards. The third table doesn't "result from" the
relationship. It is how you IMPLEMENT the relationship.
Table 1 = Employees and employee info
Table 2 = Procedures and dates completed(there are 500 different procedures
that any employee can be trained on at anytime....there is no schedule for
these)

I have a 3rd table I have created which has Employee ID (from table 1) and
Procedure ID (from table 2). I just can't understand what to do with it. I
need to create a form where I can click on the employee name at the top and
then be able to fill in the date(s) when a procedure has been completed.
And another form where I can click on the Procedure and have it show me which
employees have been trained. I wanted to do it all in one table, which is
too much data. That would make it much easier for me to understand.
Click to expand...

Table3 should start out empty - nobody has had any procedures entered yet.
Note that the DateCompleted should be in Table3, not in Table2 - a Procedure
doesn't have a "date completed" by itself, only with regard to a particular
employee completing that particular procedure! Different employees will (I
presume) complete the same procedure on different dates.

Create a Form based on the Employee table (Table1).

Create a Subform on this form based on Table3. Use EmployeeID as the
Master/Child Link Field. Put a Combo Box on the subform bound to
Table3.ProcedureID.

You can find an employee on the mainform (you can put an unbound combo box to
locate an employee quickly if you wish). On the Subform you can then select
which Procedure that employee has taken (and enter any other data such as date
trained, outcome, etc). on the "new row" of the subform. If that employee has
taken additional training, use the combo box to select a procedure on a second
row.


John W. Vinson [MVP]
 
Back
Top