locked
1 Form Multiple Tables Common ID link RRS feed

  • Question

  • I have a single form that will feed several tables. The intent is to hold contract data and a single table will not work as there are too many data elements.

    The question is this. I want to enter a unique contract ID to feed the first table but as I go through the other sections of the form I do not want the end user to have to enter that ID multiple times. Can I create a data point for each table that the form will auto-populate when contract data is being entered?

    At the end of the day I want to run a report that links all of the information for each contract to a single report allowing me to do analysis and reporting from the linked tables.

    Friday, November 9, 2018 7:18 PM

All replies

  • The main table has the primary key (contractID). The related tables have their own primary key plus a foreign key to the main table.

    Your form would have the main table as the recordsource. Then put sub forms on the main form and set the master/child links to the contractID. That way, Access will assign the contactID to the records in the related table.

    I strongly suggest you do a bit of research to learn how relational databases work. Otherwise you will be making so many mistakes as you go that by the time you want to pull reports you will be completely lost.

    I highly recommend the tutorials on YouTube by Crystal. She starts out with the basics and as you watch the series it goes into more detail.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, November 9, 2018 8:27 PM
  • It sounds like you have tables between which the relationship types are one-to-one, i.e. the tables are related on the primary keys of each table. This is legitimate where the tables are modelling a type hierarchy, in which the referencing tables each model a sub-type of the referenced table.  However, I don't think this is the case here.  Your reference to 'too many data elements' suggests that the subdivision of the data into separate tables is due primarily to the number of attributes involved.

    A legitimate model is one in which each table models a distinct real world entity type, with the columns of each table representing the attributes only of the entity type modelled by the table, and no attributes of any other entity types.  In addition to the resource to which Bill has referred you, you might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    As its name suggests this little demo file is an introduction to some of the basic principles and methodologies used in the design of a relational database.  For an example of a database in which one-to-one relationship types are valid see TypeHierarchy.zip in the same OneDrive folder.

    Ken Sheridan, Stafford, England

    Saturday, November 10, 2018 11:45 PM