Find Dup Wizard Query Not Working

[Basshopper]

I used the Wizard to do find dups and it displays all records. Have tried this several diff ways no success. Any suggestions

Here is the code

SELECT DISTINCTROW [q_quote Bom Excess].[Customer ID], [q_quote Bom Excess].[Assembly #], [q_quote Bom Excess].

, [q_quote Bom Excess].[Line #], [q_quote Bom Excess].[MFG #]
FROM [q_quote Bom Excess]
WHERE ((([q_quote Bom Excess].

) Like "T7224") AND (([q_quote Bom Excess].[MFG #]) In (SELECT [MFG #] FROM [q_quote Bom Excess] As Tmp GROUP BY [MFG #] HAVING Count(*)>1 )))
ORDER BY [q_quote Bom Excess].[Line #], [q_quote Bom Excess].[MFG #];

thanks in advance

 

[SonOfEmidec1100]

I think you just need to change DISTINCTROW to DISTINCT.
DISTINCTROW will only suppress dups if all fields are the same, DISTINCT suppress dups based on the fields selected.

Another point you are using LIKE "T7224" if you want an exact match use = not LIKE, much faster, otherwise if T7224 can be part of a longer string you should use "*T7224*

 

[SonOfEmidec1100]

Also I think you have made it more complex (and slower)than need be. I think this gives the same results

SELECT  [q_quote Bom Excess].[Customer ID], [q_quote Bom Excess].[Assembly #], [q_quote Bom Excess].[quote=#], [q_quote Bom Excess].[Line #], [q_quote Bom Excess].[MFG #]
FROM [q_quote Bom Excess]
WHERE ([q_quote Bom Excess].[quote=#] Like "*T7224*") 
GROUP BY [q_quote Bom Excess].[Customer ID], [q_quote Bom Excess].[Assembly #], [q_quote Bom Excess].[quote=#], [q_quote Bom Excess].[Line #], [q_quote Bom Excess].[MFG #]
HAVING Count(*)>1 
ORDER BY [q_quote Bom Excess].[Line #], [q_quote Bom Excess].[MFG #];

 

[Basshopper]

What would I add to have your code find the dup for Line # and Mfg # other than that It works great.

thanks

 

[SonOfEmidec1100]

You should just be able to remove references to the other fields in the select and group by

SELECT  [q_quote Bom Excess].[Line #], [q_quote Bom Excess].[MFG #]
FROM [q_quote Bom Excess]
WHERE ([q_quote Bom Excess].[quote=#] Like "*T7224*") 
GROUP BY  [q_quote Bom Excess].[Line #], [q_quote Bom Excess].[MFG #]
HAVING Count(*)>1 
ORDER BY [q_quote Bom Excess].[Line #], [q_quote Bom Excess].[MFG #];

If you wanted to retrieve additional data for all the rows you could save the above query and join to it (query3 in the example)

SELECT [q_quote Bom Excess].* FROM [q_quote Bom Excess] 
INNER JOIN Query3 Q3 ON ([q_quote Bom Excess].[Line #] = [Q3].[Line #]) AND  ([q_quote Bom Excess].[MFG #] =  [Q3].[MFG #])
ORDER BY [q_quote Bom Excess].[Line #], [q_quote Bom Excess].[MFG #]

 

[Basshopper]

It worked great got it just the way I wanted.Thanks for your help on this.