Update a Table when Changing Data in Another Table

Question

I have a training Database in Access 2010 that I am creating. I have ‘courseindex’ which is the table that has course titles and the course required frequency. I also have ‘trainingcompleted’ which is the table that contains all training my employees have completed. The form ‘training’ contains employee data and all training each employee has completed.

On the form ’training’, the training completed is selected from a combo box, which is pulled from the table ‘courseindex’. When I update the combo box, I would like the training frequency field that corresponds to the selected training to auto fill in my ‘trainingcompleted’ table. 

 Once completed I should be able to generate a report, where the user enters a date range and it will tell me who is due for training within that time frame. I have the forms, tables, and reports set up. I can enter the next due data in manually each time I update a record, but I would rather that data be updated automatically.  I have over forty courses that employees must take.  Some courses are required annually, some bi-annually, some every five years, etc….

 I'm learning Access as I go. Any help would be greatly appreciated.

Answer 1

Yes you are trying to do it the hard way. You are trying to store data redundantly when it can be done in a query. 

You could include the course table and your completed table in a query and pull the frequency from the courses table. No reason to store it twice. You then do your next due calc in your query.

Answer 2

Perhaps I am trying to do this the hard way.  The reason I need to add the frequency to the table is so that I can calculate the next due date. 

One of the Instructors would like to be able to generate a report where he enters a date range and it returns what employee needs training in any course within those dates.  We have over 1,000 employees and they are all requried to take a varying combination of the over 50 courses we provide.  We really need to be able to see 'at a glance' what employees are due for training during a given period of time.

Answer 3

PS:  There is of course a third possible scenario, which is that the frequency for any given course might change over time, but you would want the changed frequency applied to any existing training records.  In which case you again need do nothing as the frequency is determined solely by the course.

Answer 4

Before doing that you need to ask yourself whether this would be a correct approach or whether it would introduce redundancy and the consequent risk of update anomalies.  It all boils down to what is known in the language of the database relational model as 'functional dependency'.

1.  Let's look firstly at when it would be incorrect to do this.  If the course frequency will always be the same for a given course, and there is no possibility of the frequency being changed at any time in the future,  then it would be incorrect to store the value in the TrainingCompleted table.  This is because the frequency is functionally determined by the course, so once you know the course you know the frequency.  Storing it again in TrainingCompleted would introduce redundancy as the table would not be normalized to Third Normal Form (3NF), which requires that all non-key columns be functionally determined solely by the whole of the table's primary key.  In this case it would not be, but would be transitively determined via the non-key course column.   In this scenario you don't need to do anything as when returning training data the frequency for the course in question can be returned via the relationship between CourseIndex and TrainingCompleted.

2.  So when would it be legitimate to store the frequency in TrainingCompleted?  This would be the case if the frequency for a given course could be changed over time, i.e. it would not be 'time independently determined' by the key of CourseIndex.  In this case the current frequency value for a course would be determined by the key of CourseIndex, but the frequency at the time when a row is inserted into TrainingCompleted would be functionally determined by the key of that table.

If scenario 2 is the correct one in your case then it is directly analogous to the very common situation in an ordering or invoicing database where the current unit price of a product is assigned to a unit price column in an OrderDetails or InvoiceDetails table when a row is inserted (you'll find an example in Northwind, but it is an unnecessarily long-winded solution in my view and can be far simpler.)  In your case if we assume that your CourseIndex table is as follows:

CourseIndex

....CourseIndexID (PK)

....CourseName

....Frequency

then the TrainingCompleted table would include a CourseIndexID foreign key column and a Frequency column.  The combo box in the training form would be set up as follows:

Name:    cboCourse

ControlSource:    CourseIndexID

RowSource:  SELECT CourseIndexID, Frequency, CourseName FROM CourseIndex ORDER BY CourseName;

BoundColumn:    1

ColumnCount:     3

ColumnWidths:    0cm:0cm;8cm

Access will automatically change the last to inches if you are not using metric units.

Add a text box bound to the Frequency column to the form.  In the AfterUpdate event procedure of the cboCourse control assign the value of its hidden second column to the control with:

    Me.Frequency = Me.cboCourse.Column(1)

The Column property is zero-based, so Column(1) is the second column.

Answer 5

Hi JMe107

If you want to add a record to a table as a result of changes in an other table, you can use event macros.

see http://msdn.microsoft.com/en-us/library/ff973807(v=office.14).aspx