Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    One to one issue (2003)

    I have two tables, tblStaff and tblStaff_Pers, with a one to one relationship via StaffID. I've created a form for each table and placed a button (using the wizard) on the staff form to show the relevant personal details. This works if there already exist some personal details, but how can I add new personal details? Thanks, Andy.

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

    Re: One to one issue (2003)

    Why have you created two tables with a one-to-one relationship? It's rarely necessary to use a one-to-one relationship, and you should avoid it if possible.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to one issue (2003)

    I'm trying to demonstrate a one-to-one relationship, by splitting general staff details from more personal details - such as salary, home address, etc. Andy.

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

    Re: One to one issue (2003)

    Here are some possible workarounds:

    - Use a single table. You can still create separate forms for general info and for personal details.

    - Use a subform instead of a stand-alone form for the personal details.

    - Use code to create a record in the personal details table when a new record is added to the general info form.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: One to one issue (2003)

    >>I'm trying to demonstrate a one-to-one relationship, by splitting general staff details from more personal details - such as salary, home address, etc<<

    I'm not sure that is the best reason. Having different groups of data isn't really enough of a reason to split it into another table. Generally speaking, there relatively few reasons to create one-to-one relationships like that. Some of the valid reasons might be:

    - Too much data for one record (but then I'd really suspect your data design).
    - Too many indexed or Foreign Key fields.
    - You are not automatically creating a record in 2nd table, only doing it when necessary.

    As Hans pointed out, you can probably accomplish most of what you want through your data entry form design.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to one issue (2003)

    Hi. All that yourself and Hans mention makes good sense. I was really thinking in terms of setting Permissions for the personal details so that only relevant staff could see it. I also thought that by having two tables, one of which is only viewed by certain people, the queries using just the main table would run faster?
    Anyway, as I say I wanted to demonstrate an example of such a relationship. I think I would resolve my original question using a form with a subform. Perhaps I could change the subform to Single Form view to make it look 'respectable'. Thanks, Andy.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: One to one issue (2003)

    I'd use a tab control with separate pages for personal vs. business info.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to one issue (2003)

    Hi. That seems a sensible solution. With linked forms it seems tricky to create a new record where necessary. I suppose even with code to start a new record (for the current staff member) I would end up with lots of blank records if users change their mind and just close the form? Andy.

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

    Re: One to one issue (2003)

    You could enforce referential integrity with cascade delete related records, so that a detail record is deleted automatically if the main record is deleted, but in general, handling a one-to-one relationship is a PITA, so unless you have *very* pressing reasons, you should stick with using one table.

Posting Permissions

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