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