Set up a single table as the source of values for your value list. You can link different detail tables to that unified table to record data specific to a given type.
Is it better to have multiple fields that are usually empty, or have data in a single field that can represent different things?
Are these my only options?
1 of 1 people found this helpful
Neither. Read my last post again.
Set up these relationshps::
"Unified table" holds the fields common to all three types and serves as the source of values for your value list. The other three "detail tables" hold fields unique to that type. Note that these are one to one or one to none relationships. An entry in UnifiedTable for a "Labor" item would link to one record in Labor and would not link to any record in Equipment or Miscellaneous.
Also note that you can set up a layout for entering Labor records that combines fields from UnifiedTable and Labor and the user will not be able to tell if any given field comes from UnifiedTable or from Labor as they will look and act as though they are fields from a single table for the most part.