Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationships for Addresses - Simple Question??? (2000)

    I'm freaking out just a bit. I'm no dummy . . . have books and lots of info, have researched all of Access info here, and yet the answer is not clear to what seems a simple question (causing major problems) regarding relationships for an Address database with MULTIPLE addresses, phones, e-mails.

    1) I have a table: NAMES (with "NamesPID" primary field).
    2) I have 3 tables that should have "Many" relationship to NAMES: ADDRESSES, PHONES, E-MAILS (each with "NamesFK" foreign key field).
    3) I go to Relationships window and add all 4 tables.
    4) I create one-to-many relationships by dragging NamesPID and dropping on "NamesFK" in each table.
    5) I set Referential Integrity on for all.
    6) I set Joins to "2" setting so I can see all recards in query dataset (to make form).

    At this point I create query with all tables in order to produce form with all fields. They both show all records. But both query and form do not allow any entries or updating!!! (The option for new record is greyed out.)

    I pressume I'm missing something very simple. Please help if you can. Thanks, Mark

    PS: As an additional problem: I want to add and EXTRA_INFO table that can contain additional information to any record in any of the 4 tables. I believe that would be a one-to-many for all 4 tables mentioned (NAMES, ADDRESSES, PHONES, E-MAILS). How is relationship set for that?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Relationships for Addresses - Simple Question??? (2000)

    From what I can see, all the relationships are fine.
    But your form needs to be a Main form (NAMES) and a series of subforms (ADDRESSES, PHONES,E-MAILS).

    The problem as far as I can tell is you must create a NAMES record before trying to create records in any of the other tables.

    I'm not sure about your PS problem, perhaps one of the others can answer that one. What would be held in table EXTRA_INFO?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Relationships for Addresses - Simple Question??? (2000)

    As Patt says, you a need a names form with three separate subforms. You can't build that in one operation. Create a query with names and addresses, and build a form a form using form wizard, then build a phone numbers form and add that as a subform, then build an emails form and add that as well.
    To add a new subform to an existing form, use the subform tool in the toolbox (or just drag from the db window in design view).
    For your second question, why not do wither of these instead.
    Add an excta info field to each table, or simpler just add an extra info field to the names table.Depends on what you need to do with this info later.
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships for Addresses - Simple Question??? (2000)

    I appreciate the suggestion. I've already noticed that I can create useable (editable) queries and forms with just the NAMES and ADDRESS tables . . . the problem starts when I add a third table. Then query or form become "dead". They list information, but don't allow new input.

    Still the question begs. Why can't I just dump all info onto one form? Is there some conflict in establishing multiple one-to-many relationships (using a single primary key to multiple foreign keys)? From what I've read there is frequent mention of creating both forms and queries from multiple tables--with little or no mention of multiple subforms.

    The thing is, it makes such simple sense to dump this all onto one form AND GO. Isn't that the intuitive thing to do? Especially for quick testing. And I've suffered enough frustration to want to go beyond the basic fix. If anyone out there can make more sense of this, I'd be very thankful.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships for Addresses - Simple Question??? (2000)

    The use of a Main Form and subforms to represent the one to many relationships, as others have suggested, is your best approach. I understand that this may feel like a lot of work, but in order to represent those relationships and be able to add data, I can't think of a more straight-forward approach. Linking these tables together in a select query is one thing. You can "see" the data just fine, but being able to add records on the many side of a relationship is quite another. It will all display on one form in the end, as the subforms are linked to the main form. Why don't you do a quick test and see if this accomplishes your goal.

  6. #6
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships for Addresses - Simple Question??? (2000)

    Thanks. I'm headed in that direction. But just as you mentioned, it's a lot of work when it would be easier to drop it all onto one form. Just wondering if this is a true database issue (requirement), or just a MS Access screwup.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships for Addresses - Simple Question??? (2000)

    It seems natural and correct to me because of the need to add records to the many side of one to many relationships. I understood that it may feel like a lot of work at the onset, but it seems perfectly reasonable to me - probably an indication of how many times I've done it.

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

    Re: Relationships for Addresses - Simple Question??? (2000)

    What you are encountering is the rules for a query being updateable - they can be found in the Help file if you do a search on "updateable query" you should see a topic that is titled "When can I update data from a query" - it is not a bug. The issue has to do with the fact that you might be updating data that affects multiple records on the many side. Hope this makes the situation a little clearer - and do use subforms - they save lots of time compared to writing code to do the updating on your own.
    Wendell

Posting Permissions

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