Question for Access users - how to handle duplicates
Thread poster: Daniel Frisano

Daniel Frisano
Local time: 02:55
Member (2008)
English to Italian
+ ...
Jun 13

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.)


Robin Levey
Local time: 20:55
Spanish to English
+ ...
Sub-query or Join statement Jun 14

Strictly speaking, the problem as posed is not about handling ‘duplicates’ – it's about filtering (eventual) one-to-many matches between tables.

One solution (untested!) would be to build a query incorporating a sub-query.

The ‘outer’ query would assemble all the Clients (‘Blue’, ‘Green’, ‘Red’, ...) and the sub-query would get a single record for each client based on their ‘last order date’. This could be something like: “SELECT TOP 1 Order FROM WhichEverTable .... WHERE ClientID = (from outer query) ORDER BY OrderDate DESC”.

'TOP1' returns the first record in the sub-query recordset and 'OrderDate DESC' ensures that that first record is the most recent order (for a given clientID).

A similar result could be obtained using a JOIN statement.



To report site rules violations or get help, contact a site moderator:

You can also contact site staff by submitting a support request »

Question for Access users - how to handle duplicates

Advanced search

Manage your TMs and Terms ... and boost your translation business

Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.

More info »
SDL Trados Studio 2017 only €435 / $519
Get the cheapest prices for SDL Trados Studio 2017 on

Join this translator’s group buy brought to you by and buy SDL Trados Studio 2017 Freelance for only €435 / $519 / £345 / ¥63000 You will also receive FREE access to Studio 2019 when released.

More info »

  • All of
  • Term search
  • Jobs
  • Forums
  • Multiple search