Results 1 to 4 of 4
  1. #1
    CorinadeJong is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015

    Relationships between 9 tables, the easy way?

    I have created 9 tables all holding information about a certain set of people. Each table has the same variable identifying the individual the data belongs to ('participant ID'). That variable has been given key status. Now I would like to combine each table with each other table based on that key variable. But that would mean that each table must be linked to each of the 8 other tables, if I remember correctly from math class this would give 9! lines which is a lot. Can this be executed automatically?

    Thanks a lot!

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    One can only answer in general terms. If the tables have a 1:1 relationship, which is somewhat rare - meaning just 1 record always links to just 1 record - - then one could consider consolidating them into a single table; presuming the total count of fields is not excessive.

    But generally tables are 1:Many. For instance 1 person has multiple telephone numbers. In which case the easiest way to display the data really isn't an issue of figuring out joins between tables - but rather create a main form and then inset a sub form so one can see the data coherently. There would be really no issue in having 8 sub forms though maybe challenging in terms of layout on the screen.

    If you are really just interested in query design; I don't know what you mean in terms of 'executed automatically' - in query design view one just selects the tables that are part of the query. In a complicated design having 8 tables is not out of the question.

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Large database may have many tables and therefore many relationships. There is nothing inherently wrong about that.
    What you should focus on is the design of your database. Are your tables all set up so it is a normalized database?

    Here are some signs that maybe you have too many tables and some should possibly be designed:

    - Do you have multiple tables where the design of the table (fields) are identical (or nearly identical) to other tables? If so, maybe they should be combined. You need to ask yourself why they are separate to start.

    - Do you have multiple tables where the the number of records in each table is almost identical to another one? If so, maybe they should be combined. An example of something like this is if you have a table that contains address information and another table that contains phone numbers. So each table has essentially the same number of records (one for each client). You could combine these two tables into a single table called something like "Client Information".

    Note that the first example is something that you would probably want to combine (where you have multiple tables with the same fields). The second example can reduce the number of tables, but it is fine to leave it like that too. I have seen some databases where they like to split a lot of information like that out into its own tables.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  4. #4
    CorinadeJong is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Thanks JoeM and NTC. They are similar records but different information (in this case info from different questionnaires). I followed your advise and combined the tables into 1 large table. Much better! Thanks
    Last edited by CorinadeJong; 03-30-2015 at 05:24 AM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with tables and relationships
    By DCarrollUSMC in forum Access
    Replies: 2
    Last Post: 10-15-2012, 02:33 PM
  2. Replies: 0
    Last Post: 03-29-2011, 09:37 AM
  3. Many to Many Relationships among 8 tables
    By Pilotwings_64 in forum Database Design
    Replies: 9
    Last Post: 10-30-2010, 03:12 AM
  4. 8 Tables with Relationships
    By bigdogxv in forum Access
    Replies: 1
    Last Post: 12-09-2009, 09:37 PM
  5. Tables Vs Relationships
    By Bruzer in forum Database Design
    Replies: 8
    Last Post: 09-06-2009, 04:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums