Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationships (A2K)

    I have a Main Input form which uses as its source, a Parts Master file with the following three fields:

    Part_Mstr_SysKey
    Cheers,
    Andy

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

    Re: Relationships (A2K)

    What you describe is a many-to-many relationship: one part can be used in several notebooks, one notebook can use several parts. The way to implement such a relationship is to have three tables:
    - The Parts table (primary key Part_Mstr_SysKey)
    - A table listing all systems/computers/whatever, with its own primary key, say Computer_SysKey)
    - An intermediate table containing among others fields to be related to Part_Mstr_SysKey and Computer_SysKey; the combination of these two is the primary key of this table.

    See the thread starting at <post#=363,477>post 363,477</post: >. I posted a demo in the last reply in that thread.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2K)

    Many thanks, Hans. I'll jump to it immediately,
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2K)

    Hans,

    I had a look at your example and it doesn't quite do what it is that I want to do. Because both hard drives and notebooks are considered to be parts, they must both be within the same Parts master.

    I've attached what it is that I'm attempting to do. Splitting this into two forms, as per your example, is not the approach that I want to take if at all possible. The ideal situation is to have the two together.
    Cheers,
    Andy

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

    Re: Relationships (A2K)

    Sounds like a confusing setup. A hard disk can be a part of a notebook (sounds reasonable), and a notebook can be a part of a hard disk (huh?), and a notebook can be a part of itself <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2K)

    Hans,

    I have a knack of explaining the seemingly simple in the most convoluted of fashions. Let me try this again.

    Master Table has the following parts:

    #123 10 gb Hard Drive
    #456 Dell Notebook
    #789 25 gb Hard Drive
    #987 HP Notebook

    Individual Notebooks have Master Part Numbers

    Individual Hard Drives have Master Part Numbers

    I want to be able to identify in the Record #123
    Cheers,
    Andy

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

    Re: Relationships (A2K)

    But your setup will allow #123 10 GB Hard Drive to be used in #123 10 GB Hard Drive, and also allow #987 HP Notebook to be used in #456 Dell Notebook. Me seems there is something wrong here.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2K)

    When creating the master record, #123, the hard drive record, hopefully common sense will prevail and they won't attempt to use #123 in #123, but it could be easily used in either or both or any other notebook. This organization fixes stuff, so they use the same part number in any number of similar items.
    Cheers,
    Andy

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

    Re: Relationships (A2K)

    Check out the attached version. I changed the design of the UsedIn table. It now has two fields that link back to the primary key of the master table. If you look at the Relationships window, you'll see the links. (There are two copies of the master table in the Relationships window; this is done by Access to be able to display two different relationships between the same tables) The combination of the two fields is the primary key.
    The design of the subform has been adjusted accordingly.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (A2K)

    Hans,

    All I need now is the address of the Nobel Prize Committee so that I can submit your name. Thanks so very much. I'm quite certain the over the shoulder folks on this Forum are going to be as pleased as I am to have this nasty, little programming problem solved so easily. Again, many thanks,
    Cheers,
    Andy

Posting Permissions

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