I have a database that tracks:
Product Type > Product Number > Serial Numbers > and Service History (for each serial number).
I am struggling with "Service History". It has some fields that would be required regardless of what is done (Date, tech etc.), but most of the fields are only applicable for a certain "type" of product / service.
I know I could just stick all those fields in the "Service History" table, but that's a huge amount of fields, most of which would be blank for any given record. So I created a different table/form for each type of "Service History Detail". Say "Car breaks", "Car exhaust", "Boat exhaust", "Lawnmower".
Can anyone give me some advice on how to connect these "Service History Detail" tables, so I ultimately can get a form that displays:
ENTER: Product Type > Product Number > Serial Numbers
RETURN: The applicable values from the "Service History Detail" table(s) that are applicable. Note for a Particular Car #123, The history could be in multiple tables (breaks, exhaust, tires, body etc.)
Any guidance would be appreciated!