I have an Access 2007 backended DB with that I want to have the functionality to add multiple lines of data (in a datasheet form) that involves 3 different tables.
The tables are set out as follows:
Asset Table: ID_Asset (PK), ID_AssetType, AssetNumber, OurAssetDesc
ML Table: LineID (PK), US_MH_ID, DS_MH_ID, Diameter, Length
MH Table: ManholeNo (PK), Type, MHDepth, Material
The Asset.AssetNumber field can be either an ML.LineID or an MH.ManholeNo so I'm wondering the best way to design the form?
Would I need 2 forms or can I create a query to update either table based on the ID_AssetType?
What query would be best or would SQL be a better option?