I would like to understand a Microsoft Access wizard-generated query

Question

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?

Answer 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.