5

Suppose I have a table named tbl_med, which has six fields: [nom_lab], [nom_desc], [nom_apres], [date_vig], [cod_med], [vr_pmc].

I want an MS Access SQL query that will:

  1. Find duplicated records in relation to four of the fields: [nom_lab], [nom_desc], [nom_apres], [date_vig].
  2. Show all six fields (not just the ones being used to check for duplicates).

I used the MS Access "Find Duplicates Query Wizard", which gave me the following SQL:

SELECT tbl_med.[nom_lab], tbl_med.[nom_desc], tbl_med.[nom_apres], tbl_med.[date_vig], tbl_med.[cod_med], tbl_med.[vr_pmc]
FROM tbl_med
WHERE tbl_med.[nom_lab] 
IN 
(
    SELECT [nom_lab] 
    FROM [tbl_med] As Tmp 
    GROUP BY [nom_lab], [nom_desc], [nom_apres],[date_vig] 
    HAVING Count(*)>1

    And [nom_desc] = [tbl_med].[nom_desc] 
    And [nom_apres] = [tbl_med].[nom_apres]
    And [date_vig] = [tbl_med].[date_vig]

)
ORDER BY tbl_med.[nom_lab], tbl_med.[nom_desc], tbl_med.[nom_apres], tbl_med.[date_vig];

Could anyone explain why the three And conditions between the horizontal rule lines above are necessary?

Does anyone have a more intuitive query which would be easier to understand?

Lokerim
  • 338
  • 1
  • 5
  • 21
NatBr
  • 53
  • 4
  • 4
    They are necessary because the query has to reference back to a sum version of itself in order to determine if the count of each record is greater than 1, hence is a duplicate. I would recommend sticking with the wizard for queires of this type. – Matt Donnan Nov 08 '12 at 12:34

1 Answers1

1

Essentially, the three And clauses are there because you told the query wizard that you wanted to check for duplicates not just on the [nom_lab] field, but also on the [nom_desc], [nom_apres], and [date_vig] fields (as you stated at the beginning of your question).

The SELECT [nom_lab] FROM [tbl_med] As Tmp ... HAVING Count(*)>1 part of the subquery tells it to look for records which have duplicate [nom_lab] values. The And clauses then fulfill the rest of your requested duplication criteria by saying in effect "in addition to having duplicate [nom_lab] values, I want to see only records that also have duplication in all three of these other fields ([nom_desc], [nom_apres], and [date_vig]) as well."

So to answer your second question, I really can't see how you could force it to be more intuitive. SQL is just a tricky language to get your head around sometimes, and like any language (whether it be a programming or spoken language) it takes time to learn its patterns and nuances before you become comfortable with reading it easily.

Lokerim
  • 338
  • 1
  • 5
  • 21