Results 1 to 8 of 8
  1. #1
    johnong is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2020
    Posts
    1

    Need help to eliminate duplicate records from joining four tables

    I have four tables that I'd joined together:


    Click image for larger version. 

Name:	1.GIF 
Views:	21 
Size:	17.3 KB 
ID:	42602

    I was hoping to get this result:
    Click image for larger version. 

Name:	2.JPG 
Views:	21 
Size:	30.6 KB 
ID:	42603

    But I ended up with this massive duplicates:
    Click image for larger version. 

Name:	3.JPG 
Views:	23 
Size:	73.4 KB 
ID:	42604

    I really appreciate any advice to solve this!!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That's to be expected when query involves multiple 1-to-many or many-to-many relationships.

    Linking is on Deal_ID fields but there is nothing to define that Oil1 and Brake1 should associate with Engine1 so the query associates all Oil and all Brake with all Engine. What you end up with is a Cartesian product query: 1 x 2 x 3 x 4 = 24 records in query.

    Is this what data really looks like?

    Is this intended for report? Perhaps should build report/subreport(s) arrangement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    I would suggest that your table structure is wrong.

    From what you have described you need a Form that allows you to do the following:

    Enter a Deal Date
    Then select an OilType using a Combobox
    Then select an Brake Type using a Combobox
    Then select an Engine Type using a Combobox

    So these values would be stored in a table with the following structure:

    tblDealDetails
    -DealDetailID - PK - Autonumber
    -DealDate
    -OilID - FK - Number DataType (Linked to the PK from tbluOilTypes - List of Oil Types)
    -BrakeID - FK - Number DataType (Linked to the PK from tbluBrakeTypes - List of Brake Types)
    -EngineID - FK - Number DataType (Linked to the PK from tbluEngineTypes - List of Engine Types)
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree with earlier comments about table structure.
    However, with the tables you currently have, you can use a union query for this with 3 parts, each of which joins T1 to one of the other 3 tables.
    As union queries must have identical fields in each part, you need to add 'dummy fields' to make this work.

    See e.g. https://access-excel.tips/union-and-...-access-query/
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Colin, perhaps you could show the UNION query you envision because I don't see it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi June
    Sorry I've been offline almost all day.
    You were right to question my comments. It is possible to do this with a union query though not the way I suggested!

    The following explains how I did this using several intermediary queries.
    However all of those could be omitted if preferred with the union doing all the work

    First of all I created qryT2 as follows adding an extra field to get the last character in the OilTitle field
    Code:
    SELECT T2.*, Right([OilTitle],1) AS OilTNo FROM T2;
    Similarly qryT3:
    Code:
    SELECT T3.*, Right(BrakeTitle,1) AS BrakeTNo FROM T3;
    and qryT4
    Code:
    SELECT T4.*, Right([EngineTitle],1) AS EngineTNo FROM T4;
    Then I created qryMatchedRecords as follows:
    Code:
    SELECT T1.Deal_ID, T1.DealDate, qryT2.OilTitle, qryT3.BrakeTitle, qryT4.EngineTitle 
    FROM ((T1 INNER JOIN qryT2 ON T1.Deal_ID = qryT2.Deal_ID) INNER JOIN qryT4 ON (qryT2.OilTNo = qryT4.EngineTNo) AND (qryT2.Deal_ID = qryT4.Deal_ID)) 
    INNER JOIN qryT3 ON (qryT2.OilTNo = qryT3.BrakeTNo) AND (qryT2.Deal_ID = qryT3.Deal_ID)
    This gives the first 2 records:
    Deal_ID DealDate OilTitle BrakeTitle EngineTitle
    3 03/08/2020 Oil1 Brake1 Engine1
    3 03/08/2020 Oil2 Brake2 Engine2

    Next I created qryUnmatchedRecords to get the other 2 records

    Code:
    SELECT DISTINCT T1.Deal_ID, T1.DealDate, qryT2.OilTitle, qryT3.BrakeTitle, qryT4.EngineTitle 
    FROM ((T1 INNER JOIN qryT4 ON T1.Deal_ID = qryT4.Deal_ID) LEFT JOIN qryT2 ON (qryT4.EngineTNo = qryT2.OilTNo) AND (qryT4.Deal_ID = qryT2.Deal_ID)) 
    LEFT JOIN qryT3 ON (qryT4.EngineTNo = qryT3.BrakeTNo) AND (qryT4.Deal_ID = qryT3.Deal_ID)
    WHERE (((qryT3.BrakeTNo) Is Null))
    ORDER BY qryT4.EngineTitle;
    Deal_ID DealDate OilTitle BrakeTitle EngineTitle
    3 03/08/2020 Oil3
    Engine3
    3 03/08/2020

    Engine4

    Finally union those two queries:
    Code:
    SELECT T1.Deal_ID, T1.DealDate, qryT2.OilTitle, qryT3.BrakeTitle, qryT4.EngineTitle 
    FROM ((T1 INNER JOIN qryT2 ON T1.Deal_ID = qryT2.Deal_ID) INNER JOIN qryT4 ON (qryT2.OilTNo = qryT4.EngineTNo) AND (qryT2.Deal_ID = qryT4.Deal_ID)) 
    INNER JOIN qryT3 ON (qryT2.OilTNo = qryT3.BrakeTNo) AND (qryT2.Deal_ID = qryT3.Deal_ID)
    ORDER BY qryT4.EngineTitle;
    UNION
    SELECT DISTINCT T1.Deal_ID, T1.DealDate, qryT2.OilTitle, qryT3.BrakeTitle, qryT4.EngineTitle
    FROM ((T1 INNER JOIN qryT4 ON T1.Deal_ID = qryT4.Deal_ID) LEFT JOIN qryT2 ON (qryT4.Deal_ID = qryT2.Deal_ID) AND (qryT4.EngineTNo = qryT2.OilTNo)) LEFT JOIN qryT3 ON (qryT4.Deal_ID = qryT3.Deal_ID) AND (qryT4.EngineTNo = qryT3.BrakeTNo)
    WHERE (((qryT3.BrakeTNo) Is Null))
    ORDER BY qryT4.EngineTitle;
    Deal_ID DealDate OilTitle BrakeTitle EngineTitle
    3 03/08/2020 Oil1 Brake1 Engine1
    3 03/08/2020 Oil2 Brake2 Engine2
    3 03/08/2020 Oil3
    Engine3
    3 03/08/2020

    Engine4

    See attached database.

    However, whilst it does what the OP wanted, I'm not suggesting this is a good approach for the OP to use.
    As previously stated the tables should be redesigned
    Attached Files Attached Files
    Last edited by isladogs; 08-11-2020 at 12:57 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This assumes data in tables is truly as presented in sample (sequential suffix). Somehow I doubt it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That was one of the reasons I said I don't recommend this approach
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 8
    Last Post: 12-16-2018, 06:45 AM
  2. Replies: 7
    Last Post: 04-11-2015, 10:19 AM
  3. Replies: 4
    Last Post: 12-18-2013, 06:52 PM
  4. Replies: 4
    Last Post: 04-03-2013, 08:49 PM
  5. Eliminate Duplicate (mirrored) Results
    By fauowls in forum Access
    Replies: 3
    Last Post: 03-21-2013, 03:39 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