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

    Table relationships (Access 97 SR2)

    Ok, I'm going to show my stupidity here. But, I trust you guys. I have a database to track fish. I have one main data entry table and then some other tables: fish type, tag type (we tag the fish), location (where the fish is caught). To get the relationships right, to I include the data entry primary key as a foreign key in the other tables and create the relationship that way; or do I include the primary keys of the other tables in the data entry table and link that way?

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table relationships (Access 97 SR2)

    Your data entry table should have the fields that contain the primary keys of (links to) the other tables. It will look like your data entry table has just a bunch of numbers (all the key values), but you can define lookups (combo boxes) for your data entry table so that "meaningful" descriptions from your other tables will be displayed (and can be chosen from).

    Holler if you need more guidance on this and I'm sure someone here will be glad to help!

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Table relationships (Access 97 SR2)

    What you normally want is the latter - your type tables work as lookup values for records in your main "fish" table. There are occasional exceptions, but that's generally the way things work. Those kind of relationships are know as one-to-many relationships when you use the relationships window. And more often than not you use a combo box on the data entry form where you hide the primary key of the lookup table and show the text instead. Use the combo box wizard to create one of those and you'll begin to get the idea. Good luck!
    Wendell

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table relationships (Access 97 SR2)

    I repeat thisad nauseam to my students:-
    One-to-Many needs Primary Key at the 'one end'; Foreign Key at the 'many end'
    HTH
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: Table relationships (Access 97 SR2)

    Ok. I have my table relations set as you both told me to do. I am getting an error message, though, when I try to enter information into my form. It says "You can't add or change a record because a related record is required in table tbl_ExamDates." The table changes depending on what field I am updating.

    This database is 284k in size, so I can't attach it, but would someone be willing to let me email it to you to look at. I'm sure it's something with the way I've related the tables... or maybe not. But, it's a mess right now and I'm really struggling with this.

    Any help would be appreciated.
    mmcmanis@email.com is my email address.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Table relationships (Access 97 SR2)

    The bottom line is you have to add records in the lookup tables before you can add records in the main table. I presume you are doing this from the table view - if not are you using a form? If you want, feel free to email me the .mdb file - you might want to zip it first, and before that you should try a compact and repair. I probably won't be able to get back to you until late tomorrow (Mountain Std Time), but I'm willing to give it a go.
    Wendell

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

    Re: Table relationships (Access 97 SR2)

    Well, I finally figured it out. I had the relationships set right (after you guys told me what to do, of course), but I don't know where I went wrong with the form. So, I deleted it and re-created it with the wizard, and it works beautifully. Thank you very much for offering help and for offering to look at it. But, I think I'm ok now. You guys did point me in the right direction, and I learned from you. Thanks!

Posting Permissions

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