Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2010
    Posts
    49

    Unanswered: Forms, Primary Keys, and Foreign Keys

    So when I'm entering new records in through my forms, should the keys be included in the front end? or is that back end work?

    Since i have so many junctions, I curious as to whether or not i link the entries somehow through the form or in the tables...


    Sorry, I just realized I should have placed this in the general database question section :P
    Last edited by .:RoKsTaR:.; 10-13-10 at 15:18.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Primary Keys and foreign keys are data. The must end up in the tables to define to create a relationship between the records.

    Are you using a form in Access?
    Are you using a Form with a sub form on it?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Apr 2010
    Posts
    49
    I'm trying to build my forms right now and am wondering for design purposes I'll try access first, since I'm new to this and maybe mess around with VBA later

  4. #4
    Join Date
    May 2010
    Posts
    601
    If you are using Access to create your forms then Access will do a lot of the work for you. If you set the properties for the sub form control's master and child linking field properties then Access will automatically set the foreign key for you.

    I have some sample datasets on my site that might help. Here is one:

    Document text stored in tables

    Also check out the other examples and templates.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Apr 2010
    Posts
    49
    Cool, so your saying use a sub-form to manage key linking?

  6. #6
    Join Date
    May 2010
    Posts
    601
    Yes. That is correct.

    IMHO this is one of Access's strengths.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Apr 2010
    Posts
    49
    now that being said, I have 8 tables and 7 junctions. How are you supposed to wrap your head around that planning? I see the potential for me to make so many mistakes in data entry and linking alone..

  8. #8
    Join Date
    May 2010
    Posts
    601
    Forms are the key to data entry in a relational database.

    Normally a junction table is used to create a many-to-many relationship. Is that why you are using junction tables.

    Do all your tables only have many-to-many relationships?

    Curios. What information are you modeling?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    Apr 2010
    Posts
    49
    Well access shows them all as one-to-many, but essentially yes. I'm building a database of songs for my guitar students that lists a variety of teaching topics related to each song.

    i.e. key, tempo, techniques, harmonic concepts, tuning, etc..

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in your case Im not so sure you need sub forms
    you could quite happily use combo/list boxes instead

    ferinstance
    a song is classified by its genre (lets say Rock, Metal, Acid, Jazz, other)
    because all you are interested in is the classification you could use a list box to display to the user only the choices that are relevant. so a list box works.

    if you needed to store more information that just that classification then you would need a subform.

    you would use a parent form and sub form approach if there was data that was relevant to a sub table
    eg you could have an order form and a sub form which handled the details of the order
    the order form would have everything relevant to the order (eg the customer, the invoice address, the delivery address, instructions and so on) the sub form would have everyhting pertinent to a specific items/product eg the quantity, the product code, the price and so on.

    in the music world it could be, say a CD as the parent form, the individual tracks the sub form.
    because the artist would be FK to another table, you could consider using a combo/list box for the artist on the track sub form. but you would need to use a subform to record stuff such as the duration.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2010
    Posts
    49
    i definitely want to use want to use combo boxes in my form for things like, techniques, harmonic concepts, key, genre, level and i guess some lookup tables for some of those. But what really boggles me is how to link all the relevant info through my junctions.

    Performer to song name to song details (tuning, capo, genre, level) to each of my five other tables (harmony, technique, meter, tempo, key)

    And some of those can have up to 12 records related back to one song! i.e. tbltechnique has 12 techniques in it that could be present in a particular song.

    So going back to my first post, how do I link all that in the front end when it's probably divided up between multiple entry forms?

Posting Permissions

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