Updating Multiple Tables Via One Form

[JoshuaRogers]

Good evening folks.



I'm working on a database to keep track of wear and tear on parts, and while the majority of it is going great, I'm at a point where my vague recollections of database design courses back in 2005 are failing me. So I'm hoping you can help.

Right now I have a large number of tables, each of which is dedicated to a particular kind of part (such as calipers, tie rods, and batteries) and uses the serial number of the part as the primary key. These all are linked on a one-to-many relationship to another table that keeps track of dates, chassis, mileage, and which parts were on that chassis on that date (aka, the Events table).

What I'm trying to do is create a form that will allow someone to enter a record into the Events table, and at the same time, update the mileage totals for all of the parts used at that event.

So it'd be something along the lines of a button that does this (pardon my pseudocode):

1. Create record in Events table
2. Calipers.Mileage = Calipers.Mileage + Form.MileageTextbox where Calipers.Serial = CalipersCombobox
3. Brakes.Mileage = Brakes.Mileage + Form.MileageTextbox where Brakes.Serial = BrakesCombobox

And so on.

I'm fairly certain this can be done, via coding if nothing else. I just don't know the proper way to go about it.

[June7]

Not sure I would have separate tables for kinds of parts. Are the fields different in the tables?

Sounds like you need to run multiple UPDATE queries.

[JoshuaRogers]

Some are and some aren't; the design's for caution as much as anything else- should someone need to look at the raw data for a particular part type, they can go straight to a table and get it quickly without having to set up a query. Which is important as I'm the only one in the business who has any idea you'd even want to query a database.

I ended up using lots of UPDATE queries for this, as well as other parts of the database in similar veins (such as resetting current mileage on a part while updating the total mileage after the part is rebuilt, and putting the whole mess into a separate table for recordkeeping).