Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    relationships (2000)

    I've never been good at relationships!! I have a db with the main table being Applicants. Each applicant can have many income sources, many grant applications and many family members. Each family member can also have many income sources and many grant applications. I can't seem to figure out how to relate these. When trying to relate family members to income sources and grant applications, I get Indeterminate join.

    Tables: Applicant Tbl Family Member Tbl Income Source Tbl Grant Applications Tbl
    Fields: App SSN(p) Family SSN (p) SSN SSN
    App SSN Source Name Grant Name

    (p) stands for primary field.
    I'm linking App SSN (1:many) to Family Member App SSN.
    I'm linking App SSN (1:many) to Income Source.
    I'm linking App SSN (1:many) to Grant Applications

    When I try and link Family Member SSN to Income Source or Grant Applications I get Indeterminate relationship and would like 1:many.

    Help!

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

    Re: relationships (2000)

    Are you sure you want to like directly from the FamilyMember to IncomeSource or GrantApplications? It seems to me you would want to link to the Applicant table, which then links to the other two tables. Not sure that makes sense, but I'm also puzzling over what a Family SSN is - SSNs are normally associated with a person, not a family.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relationships (2000)

    Family Member SSN is social security number (unique ID) for additional members of the Applicant family. All of their income together is considered in determining eligibility.

    The Applicant would be the primary table with many family members associated with it.

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

    Re: relationships (2000)

    Then the only joins you should have are between Applicant and grants, etc. You would link individual family members through the Applicant table, not directly.
    Charlotte

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: relationships (2000)

    You said <<When I try and link Family Member SSN to Income Source or Grant Applications I get Indeterminate relationship and would like 1:many.>>

    In setting up the tables, you have applicant to grant, applicant to income, and applicant to family members as a one to many. Are you then trying to link family members back to the same income and grant table that applicant is linked too? This may cause confusion and errors based on how you are creating the primary field in the income and grant tables.

    It would be easier if under family members, link two new tables, income and grant, as a one to many such that each family member could have many incomes and grants. Queries could then be used to calculate the total income or grant by applicant across all of the tables.

    Ensure that you keep the applicants ssn in all tables as part of the primary key to aid in reporting and querying the data.

    The bottom line is you will have two income and grant tables, one set for applicant and one set for family members. If set up correctly, you can set the cascade functionality on to help maintain the data.

    HTH
    Regards,

    Gary
    (It's been a while!)

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

    Re: relationships (2000)

    Just another observation. There's no reason at all to name your tables with spaces in them, and it makes a lot more work since you have to always wrap those object names in square brackets when you refer to them in code or in SQL.
    Charlotte

Posting Permissions

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