Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Huntington, IN

    Unanswered: Referential integrity: table to query

    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?

    Thank you.
    Matt Wilcox
    Casters & Wheels at

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Queries aren't tables and therefore cannot participate in relationships.

    Try one table for your core fields and either an additional table that stores metadata about other fields, or separate tables to contain item-specific attributes.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts