Files and Status/Location Keys

I am working on a project for a law firm where the different files are either boxed or unboxed. Unboxed files can be on a lawyer's desk, on the secretary's desk, or on a shelf in the back room.

A file which is Boxed must have the same status and location of its assigned box. If the box’s status or location changes, the status or location of any boxed files must be changed as well. This should happen automatically. A file which is not boxed may have a different status and location from that of its assigned box; however the status and location must not be offsite. All Offsite files must be boxed.

Box Table

Box Key PK
Box ID
Box Destroy Yes/no (has the box been destroyed)
Box Status FK
Box Location FK

Location Table

Location ID FK
Location Description Text

Status Table

Status_ID_FK
Status Name Text
Status Description Text

File Table (refers to items within a clients file folder)

File ID
File Number
File Description
File Boxed Y/N
File Destroy Y/N
Client Number (linked to number in client table)
File Status FK
File Location FK
Box Key

Any help y'all can give me would be appreciated.

steve

re: Files and Status/Location Keys

So what is the problem exactly?

re: Files and Status/Location Keys

My first thought is to turn on cascade updates. Though without fully understanding the system this may causes issues...

From my understanding all files will at one time or another be in a box. If the file has been removed from the box it is flagged as such. A file is removed because it is in use. Upon moving a box any files that are currently in the box should have their location and status updated to match that of the box that they are in.

Because of this condition I dont believe you'd be able to cascade updates but rather you'd need a trigger to fire off on the afterupdate of the box table. If you're doing direct table manipulation I'm not sure if this is possible. If you're using forms I would suggest coding an after update event on the box form that will update the files table for those files in the moved box.

re: Files and Status/Location Keys

Elegant. Thank you. This is my first Access project since getting my MS certification and I'm learning just how much stuff was not covered in the course.