Autopopulate a table with blank fields every time another table adds a field RRS feed

  • Question

  • I'm new to access and am wondering if this is possible. Basically, I have a main table where the majority of info goes. But then there needs to be a second table with additional information. I want to make it so every time a new record is added to Table A, a blank record also gets added to Table B, so that when editing in a form, the user can have the option to click a button and see that additional info.

    That, or is it possible to match only specific records in Table A that get populated with the additional information in Table B without all the blank records?
    Friday, November 8, 2019 3:36 PM

All replies

  • To do what you want the relationship type between the two tables would need to be one-to-one, with the table holding the main data as the referenced table in the relationship type.  However, a one-to-one relationship type is used when modelling type hierarchy.  This is where an entity can be a member of two or more entity types, one being the (super) type, the others being sub-types.  Chris Date gives the example of Programmers as a (super type) with System Programmers and Application programmers as sub-types.  A sub-type is characterized by sharing all attributes of its (super) type, but not those of other sub-types.

    In your case this does not seem to be the case, with the two tables not modelling different entity types, but merely two subsets of the attributes of a single entity type, in which case all of the attributes can be represented by columns in a single table.  An appropriate interface for this would be a form in which the subset of main attributes is represented by controls in one page of a tab control, with the auxiliary subset of auxiliary attributes represented by controls on another page of the tab control.

    However, if the total number of columns in both tables is high, I would strongly suspect that the table would not be correctly normalized, and consequently in need of decomposition into a set of related tables, such that each table models a discrete entity type, and each column in each table models an attribute of the entity type modelled by the table, and of that entity type only.  In the formal language of normalization, each column should be functionally determined solely by the whole of the table's primary key.

    With the information available we obviously cannot be more categorical about how the existing tables should be decomposed, but if you would care to provide a more detailed description of what real world entity types are being modelled, and what attributes are represented by the columns in the tables, we'd then be in a better position to advise you further.

    Ken Sheridan, Stafford, England

    Friday, November 8, 2019 5:39 PM