Dział techniczny » Office applications »
Question for Access users - how to handle duplicates
Autor wątku: Daniel Frisano
Daniel Frisano
Daniel Frisano  Identity Verified
Włochy
Local time: 10:52
Członek ProZ.com
od 2008
angielski > włoski
+ ...
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: 04:52
hiszpański > angielski
+ ...
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:

Moderatorzy tego 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 Studio 2022 Freelance
The leading translation software used by over 270,000 translators.

Designed with your feedback in mind, Trados Studio 2022 delivers an unrivalled, powerful desktop and cloud solution, empowering you to work in the most efficient and cost-effective way.

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! »