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

Daniel Frisano
Monaco
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
Chile
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.

HTH
RL


 


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






TM-Town
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 ProZ.com

Join this translator’s group buy brought to you by ProZ.com 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 »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search