Very Slow Query

[chtullu]

Hello everyone,

I've created a query that finds duplicate records. It works fine and it is shown below

qryFindDuplicates

SELECT DISTINCTROW tblCurrentOptical.Group, tblCurrentOptical.Location, tblCurrentOptical.[Drawer #], tblCurrentOptical.Date, tblCurrentOptical.[Type of Payment], tblCurrentOptical.Stated, tblCurrentOptical.Verified, tblCurrentOptical.[Over/Short], tblCurrentOptical.[Deposit Number], tblCurrentOptical.[Unique Identifier]
FROM tblCurrentOptical
WHERE (((tblCurrentOptical.Location) In (SELECT [Location] FROM [tblCurrentOptical] As Tmp GROUP BY [Location],[Drawer #],[Date],[Type of Payment],[Stated],[Verified],[Over/Short],[Deposit Number],[Unique Identifier] HAVING Count(*)>1  And [Drawer #] = [tblCurrentOptical].[Drawer #] And [Date] = [tblCurrentOptical].[Date] And [Type of Payment] = [tblCurrentOptical].[Type of Payment] And [Stated] = [tblCurrentOptical].[Stated] And [Verified] = [tblCurrentOptical].[Verified] And [Over/Short] = [tblCurrentOptical].[Over/Short] And [Deposit Number] = [tblCurrentOptical].[Deposit Number] And [Unique Identifier] = [tblCurrentOptical].[Unique Identifier])))
ORDER BY tblCurrentOptical.Group, tblCurrentOptical.Location, tblCurrentOptical.[Drawer #], tblCurrentOptical.Date, tblCurrentOptical.[Type of Payment], tblCurrentOptical.Stated, tblCurrentOptical.Verified, tblCurrentOptical.[Over/Short], tblCurrentOptical.[Deposit Number], tblCurrentOptical.[Unique Identifier];

The problem is when I execute a select query on the above query as shown below. It executes but takes about ten minutes go execute. I'm trying to figure out why. When I remove the "DISTINCT" reserved word, it executes quickly enough, but I need to use it in order to get a list of only th unique groups. I wonder if I should try indexing. Any ideas

SELECT DISTINCT qryFindFullDupes.Group
FROM qryFindFullDupes;

 

[PHV]

And this ?
SELECT DISTINCT A.Group, A.Location, A.[Drawer #], A.Date, A.[Type of Payment], A.Stated, A.Verified, A.[Over/Short], A.[Deposit Number], A.[Unique Identifier]
FROM tblCurrentOptical AS A INNER JOIN (
SELECT [Location],[Drawer #],[Date],[Type of Payment],[Stated],[Verified],[Over/Short],[Deposit Number],[Unique Identifier]
FROM tblCurrentOptical
GROUP BY [Location],[Drawer #],[Date],[Type of Payment],[Stated],[Verified],[Over/Short],[Deposit Number],[Unique Identifier]
HAVING Count(*)>1
) AS D ON A.Location=D.Location AND A.[Drawer #]=D.[Drawer #] AND A.Date=D.Date AND A.[Type of Payment]=D.[Type of Payment] AND A.Stated=D.Stated AND A.Verified=D.Verified AND A.[Over/Short]=D.[Over/Short] AND A.[Deposit Number]=D.[Deposit Number] AND A.[Unique Identifier]=D.[Unique Identifier]
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chtullu]

Hello,

I tried it but I'm getting a syntax error in the second select clause,

 

[PHV]

Which version of access ?
Should works in ac2k and above.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chtullu]

Unfortunately, the users are using Access 97

 

[PHV]

So, create a save query named, say, qryGetDup:
SELECT [Location],[Drawer #],[Date],[Type of Payment],[Stated],[Verified],[Over/Short],[Deposit Number],[Unique Identifier]
FROM tblCurrentOptical
GROUP BY [Location],[Drawer #],[Date],[Type of Payment],[Stated],[Verified],[Over/Short],[Deposit Number],[Unique Identifier]
HAVING Count(*)>1

And now your query:
SELECT DISTINCT A.Group, A.Location, A.[Drawer #], A.Date, A.[Type of Payment], A.Stated, A.Verified, A.[Over/Short], A.[Deposit Number], A.[Unique Identifier]
FROM tblCurrentOptical AS A INNER JOIN qryGetDup AS D
ON A.Location=D.Location AND A.[Drawer #]=D.[Drawer #] AND A.Date=D.Date AND A.[Type of Payment]=D.[Type of Payment] AND A.Stated=D.Stated AND A.Verified=D.Verified AND A.[Over/Short]=D.[Over/Short] AND A.[Deposit Number]=D.[Deposit Number] AND A.[Unique Identifier]=D.[Unique Identifier]
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chtullu]

Hi thanks,
But your solution isn't what I was looking for. Thanks anyway.

Juan

 

[chtullu]

Hello again,
Basically I need to extract the groups ids of dataset tthat have duplicates in them. The problem is that the query execution is running very slow. Now what is the exact purpose of the following section of code

 ORDER BY 1,2,3,4,5,6,7,8,9,10

Is it used to set the order of columns. I'll check it out and include it in my code to see if it will speed up query execution. I'll keep you posted on my results.

 

[PHV]

I need to extract the groups ids of dataset tthat have duplicates in them
I think that is EXACTLY the purpose of my suggestion.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chtullu]

Hello,
It does what you say it does. I agree but I need only to extract the unique values of group id. For example if group Id 5 appears twice I need to see the 5 once. At any rate, I fiqured out a solution using a temp table that hold only unique occurances of group ids that are displayed in my qryFindDupes query. I can now use those group ID to identify duplicate datasets.
Thanks