Candrope-
No, they're simply a filter on the subquery. The WHERE Clause of the outer
query has this subquery:
WHERE tbl_med.[nom_lab] IN
(SELECT [nom_lab]
FROM [tbl_med] As Tmp
' Defines the grouping
GROUP BY
[nom_lab],[nom_desc],[nom_apres],[data_vigencia]
' Restricts the output to multiple records of
' three fields matching
HAVING Count(*)>1
AND
[nom_desc] = [tbl_med].[nom_desc]
AND
[nom_apres] = [tbl_med].[nom_apres]
AND
[data_vigencia] = [tbl_med].[data_vigencia]
)
The lines that Clive added with ' are merely comments to help you understand
what is going on. I would have used WHERE instead of HAVING like this:
(SELECT [nom_lab]
FROM [tbl_med] As Tmp
WHERE Count(*)>1
AND
[Tmp].[nom_desc] = [tbl_med].[nom_desc]
AND
[Tmp].[nom_apres] = [tbl_med].[nom_apres]
AND
[Tmp].[data_vigencia] = [tbl_med].[data_vigencia]
GROUP BY
[nom_lab],[nom_desc],[nom_apres],[data_vigencia] )
The WHERE clause in the subquery references fields in the outer query that
contains tbl_med. It does that so that the result of the subquery is the result
only for each individual row returned by the outer query. Note that the
subquery also renames the copy of tbl_med that it is using to Tmp so that the
references are clear.
Does that help?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
------------------------------
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of candrope
Sent: Sunday, May 27, 2012 5:13 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Looking for a more intuitive SQL query...
Hy Clive
Thank you for your attention.
I can't understand these conditions:
"AND
[nom_desc] = [tbl_med].[nom_desc]
AND
[nom_apres] = [tbl_med].[nom_apres]
AND
[data_vigencia] = [tbl_med].[data_vigencia]"
Is it a kind of Join?
Candrope
--- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@...> wrote:
>
> Hi Candrope?,
>
> If you look for the keywords in the SQL you can
> get some idea of what how it is working out the
> result.
>
> ' Defines the Fields
> SELECT
> tbl_med.[nom_lab],
> tbl_med.[nom_desc],
> tbl_med.[nom_apres],
> tbl_med.[data_vigencia],
> tbl_med.[cod_med],
> tbl_med.[vr_pmc]
> ' Defines the Tablename
> FROM
> tbl_med
>
> ' Defines the conditions and call it 'Tmp'
> WHERE tbl_med.[nom_lab]
> IN
> (SELECT [nom_lab]
> FROM [tbl_med] As Tmp
>
> ' Defines the grouping
> GROUP BY
> [nom_lab],[nom_desc],[nom_apres],[data_vigencia]
>
> ' Restricts the output to multiple records of
> ' three fields matching
> HAVING Count(*)>1
> AND
> [nom_desc] = [tbl_med].[nom_desc]
> AND
> [nom_apres] = [tbl_med].[nom_apres]
> AND
> [data_vigencia] = [tbl_med].[data_vigencia]
> )
>
> ' Defines the Sort Order
> ORDER BY
> tbl_med.[nom_lab],
> tbl_med.[nom_desc],
> tbl_med.[nom_apres],
> tbl_med.[data_vigencia];
>
> I think that it is as simple as it can be. The query
> doing a lot of processing of the data.
>
> John Viescas has written a good book on SQL called
> "SQL Queries for Mere Mortals" which is worth getting
> if you want to improve your understanding. It includes
> a CD with all the examples too.
>
> Hope that helps.
>
> Regards, Clive.
>
> --- In MS_Access_Professionals@yahoogroups.com, "candrope" <candrope@> wrote:
> >
> > Hello everyone
> >
> > Suppose a table named tbl_med.
> > The tbl_med table has six fields: [nom_lab], [nom_desc], [nom_apres],
[data_vigencia], [cod_med], [vr_pmc].
> > I want a SQL query to find duplicated records in relation to these fields:
[nom_lab], [nom_desc], [nom_apres], [data_vigencia].
> > But besides this I want that query SHOWS ALL THE SIX FIELDS (not only the
four ones referenced above).
> > Access 2007 Help has created the following SQL query to solve this question:
> >
> > SELECT tbl_med.[nom_lab], tbl_med.[nom_desc], tbl_med.[nom_apres],
tbl_med.[data_vigencia], 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],[data_vigencia] HAVING Count(*)>1 And
[nom_desc] = [tbl_med].[nom_desc] And [nom_apres] = [tbl_med].[nom_apres] And
[data_vigencia] = [tbl_med].[data_vigencia])
> > ORDER BY tbl_med.[nom_lab], tbl_med.[nom_desc], tbl_med.[nom_apres],
tbl_med.[data_vigencia];
> >
> > Could anyone explain how this SQL query work?
> > Does anyone have a more intuitive query (and easier to understand)?
> >
> > Thank you in advance.
> >
>
Tidak ada komentar:
Posting Komentar