Request to explain AUTO duplicate record query

  • Thread starter Thread starter Rocky
  • Start date Start date
R

Rocky

I'm hoping someone can shed some light on the code generated by the "find
duplicates" wizard. Here's what it spit out and
I've inserted my *** questions below.***
----------------------------
SELECT [tbl-ClientServicesListingData].[Case Number],
[tbl-ClientServicesListingData].[Service Code],
[tbl-ClientServicesListingData].[Begin Date],
[tbl-ClientServicesListingData].[Begin Time],
[tbl-ClientServicesListingData].[End Time],
[tbl-ClientServicesListingData].[Form Number]
*** Got this part... it's selecting the various fields ***

FROM [tbl-ClientServicesListingData]
*** Again... I understand this defines what table it's looking at. ***

WHERE ((([tbl-ClientServicesListingData].[Service Code])=34 Or
([tbl-ClientServicesListingData].[Service Code])=41)
*** This too makes sense - it's to select the criteria of 34 or 41 ***

AND ((NZ((([tbl-ClientServicesListingData].[Case Number]) In
*** Please explain NZ and why the placement is in the outter ( ) position ***

(SELECT [Case Number] FROM [tbl-ClientServicesListingData] As Tmp GROUP BY
[Case Number],[Service Code],[Begin Date],[Begin Time] HAVING Count(*)>1
*** Please explain the tmp GROUP, AND what's going on with HAVING Count... ***

And [Service Code] = [tbl-ClientServicesListingData].[Service Code]
And [Begin Date] = [tbl-ClientServicesListingData].[Begin Date]
And [Begin Time] = [tbl-ClientServicesListingData].[Begin Time]))))=False))
*** Please explain the =False part. This seems counter intuitive as I'm
trying to find duplicates here. So you'd think you'd want it to find A=A,
B=B, C=C and the end results would be what you're hoping for. Having a False
seems to me to be asking to return the opposite after finding the A=A, B=B,
C=C. So False would then present the opposite meaning all the non duplicate
records. Any clarification on this would obviously be helpful. Thank you

ORDER BY [tbl-ClientServicesListingData].[Case Number],
[tbl-ClientServicesListingData].[Service Code],
[tbl-ClientServicesListingData].[Begin Date],
[tbl-ClientServicesListingData].[Begin Time];
*** this last part is obvious too ***

Rocky''''''''s Curious Coding
 
Embedded answers.


Rocky said:
I'm hoping someone can shed some light on the code generated by the "find
duplicates" wizard. Here's what it spit out and
I've inserted my *** questions below.***
----------------------------
SELECT [tbl-ClientServicesListingData].[Case Number],
[tbl-ClientServicesListingData].[Service Code],
[tbl-ClientServicesListingData].[Begin Date],
[tbl-ClientServicesListingData].[Begin Time],
[tbl-ClientServicesListingData].[End Time],
[tbl-ClientServicesListingData].[Form Number]
*** Got this part... it's selecting the various fields ***

FROM [tbl-ClientServicesListingData]
*** Again... I understand this defines what table it's looking at. ***

WHERE ((([tbl-ClientServicesListingData].[Service Code])=34 Or
([tbl-ClientServicesListingData].[Service Code])=41)
*** This too makes sense - it's to select the criteria of 34 or 41 ***

AND ((NZ((([tbl-ClientServicesListingData].[Case Number]) In
*** Please explain NZ and why the placement is in the outter ( ) position
***
Click to expand...

It changes any NULL value in the field Case Number to a zero
(SELECT [Case Number] FROM [tbl-ClientServicesListingData] As Tmp GROUP BY
[Case Number],[Service Code],[Begin Date],[Begin Time] HAVING Count(*)>1
*** Please explain the tmp GROUP, AND what's going on with HAVING Count...
***
Click to expand...

The count ... counts the number of record in the GROUP. Here, if there
is more than ONE record in the said group, HAVING COUNT(*)>1, the case
number value is kept. Note that the group is made not only of the case
number, so, basically, it tells:

return the case number if there is more than one record having a given
[Case Number], a given [Service Code], a given [Begin Date] AND a given
[Begin Time]
And [Service Code] = [tbl-ClientServicesListingData].[Service Code]
And [Begin Date] = [tbl-ClientServicesListingData].[Begin Date]
And [Begin Time] = [tbl-ClientServicesListingData].[Begin
Time]))))=False))
*** Please explain the =False part. This seems counter intuitive as I'm
Click to expand...

= False is applied to the first WHERE clause, if you follow the ( ) . That
would be the same as saying: WHERE NOT (... conditions ...)


trying to find duplicates here. So you'd think you'd want it to find A=A,
B=B, C=C and the end results would be what you're hoping for. Having a
False
seems to me to be asking to return the opposite after finding the A=A,
B=B,
C=C. So False would then present the opposite meaning all the non
duplicate
records. Any clarification on this would obviously be helpful. Thank you

ORDER BY [tbl-ClientServicesListingData].[Case Number],
[tbl-ClientServicesListingData].[Service Code],
[tbl-ClientServicesListingData].[Begin Date],
[tbl-ClientServicesListingData].[Begin Time];
*** this last part is obvious too ***

Rocky''''''''s Curious Coding
Click to expand...




Vanderghast, Access MVP
 
Back
Top