Results 1 to 5 of 5
  1. #1
    Daniel_Z is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    2

    Removing duplicates in table produced by query

    Table 1 contains fields ID1 (number) and Text1.

    Table 3 contains fields ID3 (number) and Text3.

    Table 2 is used to link 1 to 3 and contains several pairs ID1-ID3. Here each ID1 may appear several times, each time with a different ID3.

    I run a query to match ID1 to ID3 via the link table, and some ID1's have several ID3's. Imagine this:

    -----
    ID1 - Text1 - ID3 - Text3
    -----
    1 - Client Blue - 478 - Order April (because in the link table ID1=1 appears only once, associated with ID3=478)
    2 - Client Green - 104 - Order January (because in the link table ID1=2 appears on three rows 2 - 104, 2 - 332, and 2 - 492)
    2 - Client Green - 332 - Order March (see above)


    2 - Client Green - 492 - Order April (see above)
    3 - Client Red etc.
    4 - Client Yellow etc.
    -----

    How do I display for each ID1 only its LAST occurrence? (In this case 1 - 478, 2 - 492, etc.)

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your post title and question don't seem to jive. Replace means one thing, display means another.
    for each ID1 only its LAST occurrence?
    I presume you just want to filter out everything but the max of ID3? You could just turn this into a Totals query and filter on the Max of ID3 if that's what your meaning is.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly do you mean by LAST? Last often is used to mean the latest entry based on Date/Time field.

    Tables have no inherent order.
    To select records from table(s) you use a query.
    To put selected records into an order, you use an ORDER BY clause with the query.

    see w3schools sql query order by

  4. #4
    Daniel_Z is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    2
    What I want is one row for each unique ID1, specifically the one with the max ID3. In the example I want it to show:

    -----
    ID1 - Text1 - ID3 - Text3
    -----
    1 - Client Blue - 478 - Order April
    2 - Client Green - 492 - Order April
    3 - Client Red etc.
    4 - Client Yellow etc.
    -----

    Each ID1 can appear only once.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    SELECT Table2.ID1, Table1.Text1, Max(Table3.ID3) AS MaxOfID3, First(Table3.Text3) AS FirstOfText3
    FROM (Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID1) INNER JOIN Table3 ON Table2.ID3 = Table3.ID3
    GROUP BY Table2.ID1, Table1.Text1;
    change the joins to outer joins if you want rows where there is no ID3 as this
    How do I display for each ID1 only its LAST occurrence? (In this case 1 - 478, 2 - 492, etc.)
    isn't the same as
    1 - Client Blue - 478 - Order April
    2 - Client Green - 492 - Order April
    3 - Client Red etc.
    4 - Client Yellow etc.
    since in since the last 2 rows don't have an ID3 value
    Last edited by Micron; 06-13-2018 at 07:24 PM. Reason: fixed quote blocks

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

Similar Threads

  1. Replies: 9
    Last Post: 05-25-2017, 11:43 AM
  2. Removing Duplicates
    By hefferwolfe in forum Queries
    Replies: 1
    Last Post: 04-30-2014, 04:31 AM
  3. Replies: 6
    Last Post: 03-29-2013, 11:05 AM
  4. Removing duplicates
    By DAVID W in forum Access
    Replies: 5
    Last Post: 12-21-2011, 03:15 PM
  5. Need help removing duplicates
    By warlock in forum Queries
    Replies: 1
    Last Post: 04-14-2011, 03:44 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