Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking tables after data entry (97 SR2)

    I created a database with about 8 tables that are related. I created a form to update the information in the tables.
    But, the person who entered the data into the database decided it would be easier to enter in separate Excel tables and copy the data into Access.. one table at a time. He was careful to be sure he had the same number of records for each table. But, now I can't get them to relate so I can do any queries or forms on the data. I have a junction table that contains foreign keys for each of the table's primary keys. But, when I look at the junction table, there is no data. Each separate table has data though. Hmmm.. how do I get the relationships to work AFTER data has been entered into the tables separately?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Linking tables after data entry (97 SR2)

    Did anyone actually enter any data into the junction table? Access is not going to automatically fill it for you.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking tables after data entry (97 SR2)

    I don't understand this remark:
    <hr>I have a junction table that contains foreign keys for each of the table's primary keys.<hr>
    Are you trying to relate all the tables to one another at once, or what? Normally, a join table is only necessary to relate tables with a many to many relationship. Each of the individual tables has a one to many relationship with the join table. If all you tables relate that way, it sounds like a nightmare, unless perhaps you're building a data warehouse.
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables after data entry (97 SR2)

    Well, Charlotte. I am still unsure of how to set relationships. I set this up according to advice from an online class I took. Hans helped me with a different problem with this database a couple of weeks ago. As I told him then, I am open to any advice you have regarding the design. The data can always be re-entered if necessary. I can attach the whole db if that would be easier for you.
    Attached Images Attached Images

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking tables after data entry (97 SR2)

    As Charlotte wrote, a junction table is necessary in some circumstances, but not in all situations.

    Example 1.
    You have two tables: students and states. Several students can come from the same state, but each student comes from a single state. There is a one-to-many relationship between stated and students. This is implemented by including a state ID field in the students table. No junction table is necessary.

    Example 2.
    You have two tables: students and courses. One student can take several courses, and one course can be taken by several students. There is a many-to-many relationship between students and courses. This is implemented by creating a junction table whose records consist of a student ID and a course ID, plus (if necessary) information that depends on the specific combination of a student and a course.

    In your database, I would guess that there is a many-to-one relationship between species and fish: several fishes can belong to the same species, but each fish belongs to one species only. So you would include a species ID field in the fish table.

    I don't know enough about the meaning of the other tables and relationships in your database; the database you posted in your other recent thread doesn't make that clearer, since the data have been scrambled.

Posting Permissions

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