Update 'old' and 'new' records when Join field is updated.

[DocBrown]

I have two tables related via an ID field. I have a logging form that is
based on a query on the two tables. This volunteerLog is the login/logout
data each time the volunteer works a shift. The volunteers table keeps track
of all the info on a volunteer including total hours of volunteering. The
volunteerId is displayed on the logging form as a multi-column combo box that
displays the volunteer's name.

For new records of each log entry, the shift hours are added to the total
hours. This works.

I'm thinking of allowing 'oops'es and if the user changes the volunteer by
selecting a different name I subtract the shift hours from the Totalhours on
the 'old' volunteer and add the hours to the newly selected volunteer. Can I
do this and if so, how?

Here's the query:

SELECT VolunteerLog.RecordID, VolunteerLog.VolunteerID,
VolunteerLog.LogDate, VolunteerLog.TimeIn, VolunteerLog.TimeOut,
Volunteers.FirstName, Volunteers.LastName, Volunteers.SchoolName,
Volunteers.TotalHours
FROM Volunteers INNER JOIN VolunteerLog ON Volunteers.VolunteerID =
VolunteerLog.VolunteerID;

Thanks. You guys have been great help my other questions.
John

 

[Douglas J. Steele]

Why store the total hours anywhere? Calculate them (in a Totals query) when
you need them. Then you won't have the issue of needing procedures to handle
corrections.

 

[DocBrown]

Because I want the total hours displayed on the Volunteer form along with all
the other data for the volunteer. To me it doesn't make sense to need to
query the VolunteerLog table when working with the Volunteers data. Which
would mean (I think) that everytime I go to a different Volunteer record, the
log table would be requeried and total hours recalculated from ALL the log
records for the selected volunteer.

Why would that be better?

John

 

[Douglas J. Steele]

As fellow MVP John Vinson likes to say "Storing derived data such as this in
your table accomplishes three things: it wastes disk space; it wastes time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields is
subsequently edited, you will have data in your table WHICH IS WRONG, and no
automatic way to detect that fact."

SImply add a computed field in a query that figures out the date, and use
the query wherever you would otherwise have used the table. With proper
indexes on your tables, unless you've got a huge database (i.e. tens of
thousands of rows), you should see acceptable performance.