Technical forums » Office applications »
Question for Access users - how to handle duplicates
Thread poster: Daniel Frisano
Daniel Frisano
Daniel Frisano  Identity Verified
Italy
Local time: 21:02
Member (2008)
English to Italian
+ ...
Jun 13, 2018

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 li... See more
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.) ▲ Collapse
 
Jennifer Levey
Jennifer Levey  Identity Verified
Chile
Local time: 15:02
Spanish to English
+ ...
Sub-query or Join statement Jun 14, 2018

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... See more
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 ▲ Collapse


Terry Richards
Roisin Ni Cheallaigh (X)
 


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

Moderator(s) of this forum
Maya Gorgoshidze[Call to this topic]
Prachya Mruetusatorn[Call to this topic]

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

Question for Access users - how to handle duplicates






Trados Business Manager Lite
Create customer quotes and invoices from within Trados Studio

Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.

More info »
CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

Buy now! »