Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    subform links (2003)

    i have attached my relationships window for clarification. i have a form based on tbl2Date. On this form i have a subform based on tbl3SetInfo. I am needing to link more than 3 fields to make this work properly. I need to link on the following fields:

    JobNumber
    Date
    Set
    ReqStrength
    Location

    Is this possible? How do I accomplish this?

    thanks,
    Jackal

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

    Re: subform links (2003)

    You can enter the Link Master Fields and Link Child Fields properties manually:

    JobNumber;Date;Set;ReqStrength;Location

    Note: in the first table, the field appears to be named Loaction !

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform links (2003)

    When you bring up the properties of the subform from within the main form, go to the Data tab.

    Are you able to directly edit the "Link Child fields" and "Link Master fields"? You should be able to add the other two links?

    Out of curiosity, why are these fields in different tables? If five fields comprise a unique record, are they indexed?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: subform links (2003)

    Thanks to both of you. Hans, thanks for the spelling error. Mike, I could have all the first 4 fields the same, thus the "Location" being the only different one. And for every one in tbl2Date there are at least 4 records in the tbl3SetInfo, sometimes more. Finally, the first 3 are indexed, not the ReqStrength or Location. Do they need to be?

    thanks,
    jackal

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

    Re: subform links (2003)

    If the combination of the 5 fields is (or should be) unique in tbl2Date, it might be a good idea to make this combination the primary key of the table.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform links (2003)

    Taking a look at the table structure

    <pre>tbl2Date tbl3SetInfo
    ---------------- ----------------
    JobNumber JobNumber
    Date Date
    Set Set
    ReqStrength ReqStrength
    Location Location
    Slump Slump
    Air Air
    ID
    Strength
    </pre>


    I'm not entirely sure why you repeat the ReqStrength, Slump, and Air. If tbl2Date is a table of tests, or something....one could make the key fields a combination of JobNumber, Date, and Location. Theoretically, I would not do the same job more than once at a location on a given date.

    Assuming this is correct (and I am not sure what slump and air are), the data that relates to a given job, at a location, on a date would also reside in tbl2Date. I would not repeat these fields in tbl3SetInfo.

    Apparently table 3 stores different strengths? Any idea what purpose the ID field serves?


    Proposed new table tbl2Date
    JobNumber
    Date
    Location
    Set
    ReqStrength
    Slump
    Air

    Proposed new table tbl3SetInfo
    JobNumber
    Date
    Location
    ID
    Strength

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: subform links (2003)

    Let me try to strip down some of my data and post my db. This will be easier than trying to explain it.

    thanks,
    jackal

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: subform links (2003)

    Upon further review, I am going to change my tables to what you have suggested and give this a try. Yes this db is used to collect concrete strength tests. The different cities have different requirements for concrete; 1-strength, 2-slump(the denseness of wet concrete), 3-air(% of air content in concrete). Each set has an ID, so for set 1, you might have IDs A, B, C, D that correlate to how long after the sample was taken that the concrete gets tested. Until reciently I have not had the same job with same date with same set #. So changing to Location should fix my problems. Again thanks for the help.

    thanks,
    jackal

Posting Permissions

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