Updating a field in one table based on the updates in the field of another table

[D-Angle]

I'll try and explain this as best I can.

I am building a database for archiving paper records. The records are kept in boxes, each of which has a unique box number. I have created a database with 2 main tables of information, 1 for the details of the box and 1 for the details of each individual record. I have created a one-to-many relationship between the tables using the box number as the common field.

If we need to bring records back from storage, we can request individual records be pulled and sent back to us, we don't have to have the whole box. We can request whole boxes as well if we need to. So in each table there is a 'status' field, which will have the following options to select from: In Storage, Pre-Storage, Pickup Requested, Retrieval Requested, Retrieved, and Lost.

So what I want to do, is if I update the status of a box, that update is applied to all records therein as well. But if I update the status of an individual record, the status of the box itself and the rest of its contents remains unaffected. For example, if a box was lost, I could change its status to Lost and all the records contained in the box would be updated to Lost as well. But if we went to a box and couldn't find a record, I could mark that individual record as Lost without changing the status of the box or the other records contained in it.

Does that make sense?

 

[Micron]

You need a status table to manage those values and a field in tblRecords RecStatus. I would NOT use a lookup field to set any status directly in a table. Using a form to manipulate data (always should) you status a record individually as required. On that same form, you also have a control (probably combo box) that if set according to the status' that only boxes can have (if they're different from records) will automatically set all the records status' to whatever you want it to be, based on the boxID in the record table. You should not be editing tables directly.

If you want the order of status to be other than alphabetical ascending or descending, then add a SortOrder field (integer) and apply the sort to that field. This will also enable you to control the choices better so that they make sense to you (e.g. using the position of your examples, a user could not choose value 3 for a record if the current box value is > 3.