Can't seem to find the answer to this already posted.
My db has about 15 tables for various product types. Several fields are the common throughout all 15 tables. If I decide to add another 'common' field, then I have to add it to all the tables and all the forms.
I would like to have one table that holds the common fields for all the other table. Essentially, that table would have: product_table_number, key_field, commonfield1, commonfield2, etc.
I want to create relationships between each product table and the common table with referential integrity for cascade deletes. If a product is deleted, delete all these additional common fields also.
The problem: a key_field from product table 1 might be the same as the key_field from product table 2 - since the key fields are just autonumbered.
So, I got the brilliant idea of creating a number of queries that would filter based on the product_table_number and select only the records for a particular table.
But when I try to create a relationship between the product table and this query, it won't allow me to select referential integrity.
Any ideas on why not? Or ideas on a better way of accomplishing my goal?