Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: Help with database relationships

    Hi everybody

    am pretty new to Access and am struggling a bit with what I am sure is easy stuff to do. I am creating a database for a scientific review paper. I need to store data regarding about 200 scientific articles.

    Here are the issues:

    I have tables that list the parameters of interest for each paper I read(some physical, some statistical). Some of the paper test multiple things or use different lab settings.

    I have a main table with ArticleIDs Titles and all basic info and would like for each record to be able to assign more than a value. For instance if two animals were used for testing I would like to be able to have two experimental setups with all the relative data.

    Tried to link the main info table with the other using a one-to-many relationship using the ArticleID as primary key and ExperimentalID as a foreign key, but data is not stored properly or errors appear.\

    Hope I made myself clear and someone is there to help

    Thanks a lot!

    B.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    What error appear? Please explain.

    As a general guideline, ArticleID is the primary key of the table Article, ExperimentalID is the primary key of the table Experiment (or Experimental). This second table also have a column that stores the Foreign key for the table Article (i.e. a copy of the primary key ArticleID ). This allow the creation of a one (Article) to many (Experiment) relationship.

    If you need a many to many relationship (i.e. several articles referencing the same experiment and several experiment referenced in the same article), you need a third table (a junction table) which has (at least) two columns: 1 foreign key to the table Article and 1 foreign key to the table Experiment.
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    5
    Thanks for your reply Sinndho!

    Let me better explain it. Some of the papers I have in list have conducted several experiments with different setups with various exposure values.

    So to semplify Table1 has ArticleID Title Author

    Table2 ArticleID ExperimentID Setup FrequencyinHz Power etc

    What I would like to do is being able for each of the articles to indicate when needed (perhaps with a form and a subform) several entries for each column.

    When I tried to link the two table setting up a one-to-many relationship between ArticleID and ExperimentID, I end up not being able to add more than one line because it automatically updates the value of ArticleID in Table2 duplicating it, which gives me an error.

    That's why I need to add a third junction table. But how does that work?How is that related to the others?

    And is ExperimentID an AutoNumber?

    Thanks again.

    B.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    As I wrote formerly:
    ArticleID is the primary key of the table Article, ExperimentalID is the primary key of the table Experiment (or Experimental). This second table also have a column that stores the Foreign key for the table Article (i.e. a copy of the primary key ArticleID ). This allow the creation of a one (Article) to many (Experiment) relationship.
    So, for a One (Table1) To Many (Table2) relationship:
    Table1: ArticleID - Primary Key (AutoNumber)
    Table2: ExperimentalID - Primary Key (AutoNumber)
    Table2: ArticleID - Foreign Key (for Table1)

    If you need a many to many relationship, you need a third junction table:
    Table3: ArticleID - Foreign Key (for Table1)
    Table3: ExperimentalID - Foreign Key (for Table2)

    Quote Originally Posted by Bobus View Post
    What I would like to do is being able for each of the articles to indicate when needed (perhaps with a form and a subform) several entries for each column.
    Not perhaps: You should never allow users (even yourself) to have a direct access to the table when manipulating data.

    See the attached screen snapshot for how to build the relationship.
    Attached Thumbnails Attached Thumbnails OneToManyRelationship.jpg  
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    5
    Thanks a lot S. !Will work on it asap.

    Great hints

    Have a great day

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Sep 2011
    Posts
    5
    All worked fine!

    I now have a main table (Table1)with general info on the articles and then another (Table2) with the experiments. Just as you suggested.

    Would the procedure be completely different if instead of only one table I would like to link to Table1 more than one table?

    For instance what if:

    Table1 General info
    Table3 Experimental Setup
    Table4 Physical Parameters

    They are all related to the ArticleID but they provide different information.

    In this way I could have an article in which similar features are present in the Experimental setup but different parameters (e.g. different frequency values)used.

    Is that too complex to do and/or explain?

    Cheers
    B.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The principles remain the same. You can create one-to-one or one-to-many relationships and joins among Table1 (General info) and Table3 (Experimental Setup) and Table4 (Physical Parameters) Using the primary key (ArticleID) in Table1 and foreign keys (ArticleID) in Table3 and Table4.
    Have a nice day!

  9. #9
    Join Date
    Sep 2011
    Posts
    5
    Done!Works fine.

    thanks mate

    Cheers

  10. #10
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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