I have designed and have been using MS Access as a case management system in my law office for some time now. I am working with a software product called "AutoFill" to use MS Access data to populate some on-line forms. Now I have several pieces of data I need to store for each item that relates to the name of the particular form it belongs in, and the name that data item has in that form. In other words, every item of data has these 2 variables. Presently, for example, I have a tables ClientWorkHistory with fields like Job Title, Company, Payrate, Start Date, End Date, etc, and table ClientIllnesses with fields like IllnessName, Treating Doctor, DiagnosisDate, etc. Looking at these two tables every single date record could be linked to a new table, tblForms, with fields FormName and DataItemName. In other words tblForms would have to link to every single table in my DB. This seems like a bit much. Any ideas?
If I understand you correctly, you should be able to do this using Primary and Foreign Keys in your tables.
For each table, add a PK as an AutoNumber. In the tblForms table, add a Foreign Key for each external table. E.g., you would have a column FKHist that equals the PK in the ClientWorkHistory table, a column FKIllness that equals the PK in the ClientIllnesses table, etc.
Be careful, however. A true PK demands that each key is unique; no duplicates. If you have duplicates, you will need to either delete a lot of records, normalize the data, or use multiple-field PKs (which is not at all unusual).