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

Daniel Frisano
Local time: 16:28
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: 11:28
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.


Terry Richards
Roisin Ni Cheallaigh

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

Anycount & Translation Office 3000
Translation Office 3000

Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.

More info »
SDL Trados Studio 2019 Freelance
The leading translation software used by over 250,000 translators.

SDL Trados Studio 2019 has evolved to bring translators a brand new experience. Designed with user experience at its core, Studio 2019 transforms how new users get up and running and helps experienced users make the most of the powerful features.

More info »

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