Results 1 to 5 of 5
  1. #1
    b82726272 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2014
    Posts
    21

    Multiple join duplication

    Hi,



    When I join three tables I get duplicated records in Access SQL. How can I solve this? Using distinct is no options. Thank you for the reaction.

    Kind regards,

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you get duplicate records, that means that you have some one-to-many or many-to-many relationships going on.
    That could be due to the fact that it is just the nature of the relationship between those tables, or perhaps you joined your table incorrectly (sometimes, you may have to join on more than one field).

    Why is "SELECT DISTINCT" not an option? If you just mean it is not working, that means that you records really are not duplicates. In order to be considered a duplicate, EVERY field you are returning must be the same. If even one is different, it is not defined to be a duplicate.

    It might be helpful if you provide an example of the data in your three tables, the relationships between the three tables, and what you want your expected results to look like.

  3. #3
    b82726272 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2014
    Posts
    21
    Thank you for the reaction. There is no hard relationship between the tables (stand alone tables with common fields as a key). The database is designed by someone else. SELECT DISTINCT is no options because I have a COUNT in SELECT. This is the FROM part of my query.

    FROM (TBL_A1 INNER JOIN TBL_A2 ON TBL_A1.A1 = TBL_A2.A1) INNER JOIN TBL_A3 ON TBL_A1.A2= TBL_A3.A2

    Kind regards,

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    When I say "relationship" between the tables, I am really referring to how they are joined together in your query (how they are being "related").
    Without seeing the data, and nature of your key fields, it is very hard to say.

    Obviously, the key fields to focus on are:
    TBL_A1.A1
    TBL_A2.A1
    TBL_A1.A2
    TBL_A3.A2

    One or more of these fields must not be unique. It isn't required for them to be unique, but if they aren't, you will often up with apparent "duplicates".

    If you really want detailed help, it would be best to post the SQL code of your query, samples of your data, and your expected results.
    Or, you can just upload a copy of your database for analysis (be sure to remove any private data). I won't be able to download it until tonight, but others may be able to before then.

  5. #5
    b82726272 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2014
    Posts
    21
    Hi,

    I have solved the problem by joining the tables with different fields. Thank you for the help.

    Kind regards,

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Query with multiple inner join
    By phineas629 in forum Queries
    Replies: 1
    Last Post: 04-28-2014, 06:56 PM
  2. Inner Join and multiple fields
    By mrr2 in forum Queries
    Replies: 25
    Last Post: 05-16-2012, 11:11 AM
  3. Join multiple tables to one
    By b6677862 in forum Queries
    Replies: 1
    Last Post: 02-08-2012, 06:59 AM
  4. Join for multiple tables?
    By Etownguy in forum Queries
    Replies: 3
    Last Post: 05-30-2011, 04:54 PM
  5. Error on Multiple Table Join & IIF
    By cmartin in forum Queries
    Replies: 1
    Last Post: 05-21-2010, 08:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums