Question about linking some tables together

[photogwa]

Hi.



I'm fairly new to database design and I have a question about relating some tables together.

Let me give you an example to help explain my question. Let's say we have a table that describes a whole process, in this instance, every time one our shop vehicles goes to our auto shop to have an inspection done. So we have a table called "VehicleInspections". It has fields like ID, vehicleID, inspectionStartTime, inspectionStopTime, which maintenance bay it's in, etc. Now, let's say we have another table called "MaintenanceActions" that list all the maintenance actions done on a particular vehicle (discovered during the vehicle inspections). So this has fields like ID, vehicle Inspection ID (foreign key from ID in VehicleInspections), vehicleID, status of the action, remarks, etc. Lastly, let's say we also have a table that lists the vehicle IDs for all of our vehicles. Now, if I setup a lookup in table VehicleInspections in the vehcileID field to the VehicleIDs table, should I do the same in the MaintenanceActions table? Or should I/can I link the vehicleID field in MaintenanceActions to the vehicleID field in VehicleInspections (which is a lookup of the field in VehicleIDs)?

Hopefully I didn't lose you.

Basically, if I have table with a list of actions that is attached to a list of processes, should I link the thing they have in common (vehicleID) in each table to the lookup list, or just one to the lookup list and the other one back to the one that refers to the lookup list.

Keep in mind, this is just an example to explain my question. Also, not sure if it matters, but I will be not using the Access program to read and write the data.

Thanks for your help,
Jason

[ranman256]

You shouldnt need a lookup for vehicle ID...it should be a PARENT/CHILD.
If you are in a vehicle (the parent) then all childs, inspection, actions become children and the Vehicle is automatically added via the form.\
Start inspection of vehicle A
vehA is parent
subform Inspection #001 begins using vehA
Actions are added to inspection. (or to a repair) (ok lookup happens here from the Action table)
stop inspection.
All data is recorded under the vehicle parent.

[orange]

Here is a draft data model based on your post that may help. You may have more fields and tables but this is my attempt to identify vehicle, inspection, maintenance actions.