Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Lakeland, FL, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    one-to-many-to-many relationship (Access 97)

    <P ID="edit" class=small>(Edited by dezBar on 23-Sep-02 13:02. the format changed, tabs do not work.)</P>Hello,

    I have a few questions about the one-to-many-to-many relationship. First, please look at the design and tell me if it is correct. Since the junction table has a composite primary key derived from tables 2 and 4, is it necessary to have a primary key rather than a foreign key in tables 2 and 4? Reason being, when I try to save a record I get an error message saying i cannot have duplicate values in the primary key field. Should it be indexed w/duplicates ok?

    Also, should data be stored through queries instead of storing directly into the tables when using subforms?

    and, should there be an unbound field from each subform (primary key) on the main form?

    This is my design.

    1:M
    TABLE1
    PatientID (p)
    Name
    Age
    Dob

    1:M
    TABLE2
    ExternalID(p)
    PatientID(f)
    externalDx
    PhyId

    1:M
    junctionTable
    ExternalID(cp)
    InternalID(cp)
    type
    code

    M:1
    TABLE4 (M:1)
    InternalID(p)
    dateFinallDx
    internalDx


    In advance, thanks for your help.
    lbrown

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: one-to-many-to-many relationship (Access 97)

    Sorry, but I couldn't follow your design notes. Which table contains which fields? If the join table contains keys from the other two (which it must), then I don't understand the question. In general, every table needs a primary key, and primary keys can't be anything but unique by definition. If you (or need) have duplicate keys in the table, then that field is not eligible to serve as the primary key unless it is part of a composite key. Many-to-many relationships have been discussed frequently in this forum, so a lounge search may turn up threads that would be helpful to you.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: one-to-many-to-many relationship (Access 97)

    The age field should be a calculated field as age changes over time. You determine age by running a query.
    Try the following code in a query to calculate age. I picked up the code in this forum. Format the Dob field as short date.

    Age: Int(DateDiff("y",[Dob], Date())/365.25)

Posting Permissions

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