Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Unanswered: Duplicates in Multiple join tables

    I have 3 tables in my database
    Applicants
    Finance
    Dependents

    relationships
    Applicants - Finance - one-to-one
    Applicants - Dependents - one-to-many

    Problem
    When I run a query for example "Balance", if all 3 tables are in the relationship, the data is duplicated based on the number of dependents in the Dependents table.

    However, in i remove the Dependents table and use the relationship between Applicants and Finance only, the answer given is correct.

    Is this how it should be? Or should i be able to get the correct answer without removing one table from the relationship?

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    This is working exactly as it should. The whole principal of a relational database is that you store something once, and they show it as many times as required by virtue of relationships.

    In the example you give, are there any fields from the Dependants table that a required in the Balance query's output? If not, feel free to remove it from the query definition. If there are, consider using a subform/subreport model to display the information from Applicants and Finance once, and the Dependants information as many times as is required.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2012
    Posts
    2

    Duplicate items in data base Thank You

    I want to thank you very much for your advice; it is very much appreciated.

    Thanks again

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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