Duplicate Query
- Thread starter Humanski
- Start date
I take it that you mean you're trying to use the "Find Duplicates Query Wizard" to work out duplicate records. You can look for duplicates without using the wizard and using as many fields as you like: Make a new query using all the fields you need to compare. Use the Group By function which will only display unique combinations of those fields. Set up another field to count the occurrences of each group and restrict this in the criteria field as >1.
This will display only duplicates. The output isn't quite as slick as that produced by the wizard but it does the job.
This will display only duplicates. The output isn't quite as slick as that produced by the wizard but it does the job.
Hello
I have just run this Query to find duplicate values in my table and it has not found any this is not correct as I know there are duplicates, can anybody see what is wrong with this query
SELECT DISTINCTROW [08/02VA].Load_Date, [08/02VA].Status, [08/02VA].Pack, [08/02VA].Inv_No, [08/02VA].SI, [08/02VA].BR, [08/02VA].Cust, [08/02VA].DC, [08/02VA].Nat, [08/02VA].EU, [08/02VA].Ref_No, [08/02VA].NOI, [08/02VA].Exp_Lic, [08/02VA].ID_No, [08/02VA].Due_Date, [08/02VA].QTY, [08/02VA].Wt, [08/02VA].Val_EU, [08/02VA].Curr, [08/02VA].Val_Curr, [08/02VA].Con1, [08/02VA].Name, [08/02VA].Add1, [08/02VA].Add2, [08/02VA].Add3, [08/02VA].PostCode, [08/02VA].COMCO, [08/02VA].Rem, [08/02VA].Class, [08/02VA].Area, [08/02VA].WH, [08/02VA].Co, [08/02VA].VX_Date, [08/02VA].VZ_Date, [08/02VA].DM, [08/02VA].User_ID, [08/02VA].Name1, [08/02VA].Name2
FROM [08/02VA]
WHERE ((([08/02VA].Load_Date) In (SELECT [Load_Date] FROM [08/02VA] As Tmp GROUP BY [Load_Date],[Status],[Pack],[Inv_No],[SI],[BR],[Cust],[DC],[Nat],[EU],[REF_No],[NOI],[Exp_Lic],[ID_No],[Due_Date],[QTY],[Wt],[Val_EU],[Curr],[Val_Curr],[Con1],[Name],[Add1],[Add2],[Add3],[PostCode],[COMCO],[Rem],[Class],[Area],[WH],[Co],[VX_Date],[VZ_Date],[DM],[User_ID],[Name1],[Name2] HAVING Count(*)>1 And [Status] = [08/02VA].[Status] And [Pack] = [08/02VA].[Pack] And [Inv_No] = [08/02VA].[Inv_No] And [SI] = [08/02VA].[SI] And [BR] = [08/02VA].[BR] And [Cust] = [08/02VA].[Cust] And [DC] = [08/02VA].[DC] And [Nat] = [08/02VA].[Nat] And [EU] = [08/02VA].[EU] And [Ref_No] = [08/02VA].[Ref_No] And [NOI] = [08/02VA].[NOI] And [Exp_Lic] = [08/02VA].[Exp_Lic] And [ID_No] = [08/02VA].[ID_No] And [Due_Date] = [08/02VA].[Due_Date] And [QTY] = [08/02VA].[QTY] And [Wt] = [08/02VA].[Wt] And [Val_EU] = [08/02VA].[Val_EU] And [Curr] = [08/02VA].[Curr] And [Val_Curr] = [08/02VA].[Val_Curr] And [Con1] = [08/02VA].[Con1] And [Name] = [08/02VA].[Name] And [Add1] = [08/02VA].[Add1] And [Add2] = [08/02VA].[Add2] And [Add3] = [08/02VA].[Add3] And [PostCode] = [08/02VA].[PostCode] And [COMCO] = [08/02VA].[COMCO] And [Rem] = [08/02VA].[Rem] And [Class] = [08/02VA].[Class] And [Area] = [08/02VA].[Area] And [WH] = [08/02VA].[WH] And [Co] = [08/02VA].[Co] And [VX_Date] = [08/02VA].[VX_Date] And [VZ_Date] = [08/02VA].[VZ_Date] And [DM] = [08/02VA].[DM] And [User_ID] = [08/02VA].[User_ID] And [Name1] = [08/02VA].[Name1] And [Name2] = [08/02VA].[Name2])))
ORDER BY [08/02VA].Load_Date, [08/02VA].Status, [08/02VA].Pack, [08/02VA].Inv_No, [08/02VA].SI, [08/02VA].BR, [08/02VA].Cust, [08/02VA].DC, [08/02VA].Nat, [08/02VA].EU, [08/02VA].REF_No, [08/02VA].NOI, [08/02VA].Exp_Lic, [08/02VA].ID_No, [08/02VA].QTY, [08/02VA].Wt, [08/02VA].Val_EU, [08/02VA].Curr, [08/02VA].Val_Curr, [08/02VA].Con1, [08/02VA].Name, [08/02VA].Add1, [08/02VA].Add2, [08/02VA].Add3, [08/02VA].PostCode, [08/02VA].COMCO, [08/02VA].Rem, [08/02VA].Class, [08/02VA].Area, [08/02VA].WH, [08/02VA].Co, [08/02VA].VX_Date, [08/02VA].VZ_Date, [08/02VA].DM, [08/02VA].User_ID, [08/02VA].Name1, [08/02VA].Name2;
The other duplicate query I ran using the wizard worked but only allowed me to bring across 10 fields but I needed all of them this query looked like this:-
SELECT DISTINCTROW [08/02VA].Load_Date, [08/02VA].Inv_No, [08/02VA].Ref_No, [08/02VA].ID_No, [08/02VA].QTY, [08/02VA].Wt, [08/02VA].Val_EU, [08/02VA].VX_Date, [08/02VA].Last_Date
FROM [08/02VA]
WHERE ((([08/02VA].Load_Date) In (SELECT [Load_Date] FROM [08/02VA] As Tmp GROUP BY [Load_Date],[Inv_No],[Ref_No],[ID_No],[QTY],[Wt],[Val_EU],[VX_Date] HAVING Count(*)>1 And [Inv_No] = [08/02VA].[Inv_No] And [Ref_No] = [08/02VA].[Ref_No] And [ID_No] = [08/02VA].[ID_No] And [QTY] = [08/02VA].[QTY] And [Wt] = [08/02VA].[Wt] And [Val_EU] = [08/02VA].[Val_EU] And [VX_Date] = [08/02VA].[VX_Date])));
Can anybody tell me what I am doing wrong
I have just run this Query to find duplicate values in my table and it has not found any this is not correct as I know there are duplicates, can anybody see what is wrong with this query
SELECT DISTINCTROW [08/02VA].Load_Date, [08/02VA].Status, [08/02VA].Pack, [08/02VA].Inv_No, [08/02VA].SI, [08/02VA].BR, [08/02VA].Cust, [08/02VA].DC, [08/02VA].Nat, [08/02VA].EU, [08/02VA].Ref_No, [08/02VA].NOI, [08/02VA].Exp_Lic, [08/02VA].ID_No, [08/02VA].Due_Date, [08/02VA].QTY, [08/02VA].Wt, [08/02VA].Val_EU, [08/02VA].Curr, [08/02VA].Val_Curr, [08/02VA].Con1, [08/02VA].Name, [08/02VA].Add1, [08/02VA].Add2, [08/02VA].Add3, [08/02VA].PostCode, [08/02VA].COMCO, [08/02VA].Rem, [08/02VA].Class, [08/02VA].Area, [08/02VA].WH, [08/02VA].Co, [08/02VA].VX_Date, [08/02VA].VZ_Date, [08/02VA].DM, [08/02VA].User_ID, [08/02VA].Name1, [08/02VA].Name2
FROM [08/02VA]
WHERE ((([08/02VA].Load_Date) In (SELECT [Load_Date] FROM [08/02VA] As Tmp GROUP BY [Load_Date],[Status],[Pack],[Inv_No],[SI],[BR],[Cust],[DC],[Nat],[EU],[REF_No],[NOI],[Exp_Lic],[ID_No],[Due_Date],[QTY],[Wt],[Val_EU],[Curr],[Val_Curr],[Con1],[Name],[Add1],[Add2],[Add3],[PostCode],[COMCO],[Rem],[Class],[Area],[WH],[Co],[VX_Date],[VZ_Date],[DM],[User_ID],[Name1],[Name2] HAVING Count(*)>1 And [Status] = [08/02VA].[Status] And [Pack] = [08/02VA].[Pack] And [Inv_No] = [08/02VA].[Inv_No] And [SI] = [08/02VA].[SI] And [BR] = [08/02VA].[BR] And [Cust] = [08/02VA].[Cust] And [DC] = [08/02VA].[DC] And [Nat] = [08/02VA].[Nat] And [EU] = [08/02VA].[EU] And [Ref_No] = [08/02VA].[Ref_No] And [NOI] = [08/02VA].[NOI] And [Exp_Lic] = [08/02VA].[Exp_Lic] And [ID_No] = [08/02VA].[ID_No] And [Due_Date] = [08/02VA].[Due_Date] And [QTY] = [08/02VA].[QTY] And [Wt] = [08/02VA].[Wt] And [Val_EU] = [08/02VA].[Val_EU] And [Curr] = [08/02VA].[Curr] And [Val_Curr] = [08/02VA].[Val_Curr] And [Con1] = [08/02VA].[Con1] And [Name] = [08/02VA].[Name] And [Add1] = [08/02VA].[Add1] And [Add2] = [08/02VA].[Add2] And [Add3] = [08/02VA].[Add3] And [PostCode] = [08/02VA].[PostCode] And [COMCO] = [08/02VA].[COMCO] And [Rem] = [08/02VA].[Rem] And [Class] = [08/02VA].[Class] And [Area] = [08/02VA].[Area] And [WH] = [08/02VA].[WH] And [Co] = [08/02VA].[Co] And [VX_Date] = [08/02VA].[VX_Date] And [VZ_Date] = [08/02VA].[VZ_Date] And [DM] = [08/02VA].[DM] And [User_ID] = [08/02VA].[User_ID] And [Name1] = [08/02VA].[Name1] And [Name2] = [08/02VA].[Name2])))
ORDER BY [08/02VA].Load_Date, [08/02VA].Status, [08/02VA].Pack, [08/02VA].Inv_No, [08/02VA].SI, [08/02VA].BR, [08/02VA].Cust, [08/02VA].DC, [08/02VA].Nat, [08/02VA].EU, [08/02VA].REF_No, [08/02VA].NOI, [08/02VA].Exp_Lic, [08/02VA].ID_No, [08/02VA].QTY, [08/02VA].Wt, [08/02VA].Val_EU, [08/02VA].Curr, [08/02VA].Val_Curr, [08/02VA].Con1, [08/02VA].Name, [08/02VA].Add1, [08/02VA].Add2, [08/02VA].Add3, [08/02VA].PostCode, [08/02VA].COMCO, [08/02VA].Rem, [08/02VA].Class, [08/02VA].Area, [08/02VA].WH, [08/02VA].Co, [08/02VA].VX_Date, [08/02VA].VZ_Date, [08/02VA].DM, [08/02VA].User_ID, [08/02VA].Name1, [08/02VA].Name2;
The other duplicate query I ran using the wizard worked but only allowed me to bring across 10 fields but I needed all of them this query looked like this:-
SELECT DISTINCTROW [08/02VA].Load_Date, [08/02VA].Inv_No, [08/02VA].Ref_No, [08/02VA].ID_No, [08/02VA].QTY, [08/02VA].Wt, [08/02VA].Val_EU, [08/02VA].VX_Date, [08/02VA].Last_Date
FROM [08/02VA]
WHERE ((([08/02VA].Load_Date) In (SELECT [Load_Date] FROM [08/02VA] As Tmp GROUP BY [Load_Date],[Inv_No],[Ref_No],[ID_No],[QTY],[Wt],[Val_EU],[VX_Date] HAVING Count(*)>1 And [Inv_No] = [08/02VA].[Inv_No] And [Ref_No] = [08/02VA].[Ref_No] And [ID_No] = [08/02VA].[ID_No] And [QTY] = [08/02VA].[QTY] And [Wt] = [08/02VA].[Wt] And [Val_EU] = [08/02VA].[Val_EU] And [VX_Date] = [08/02VA].[VX_Date])));
Can anybody tell me what I am doing wrong
I haven't worked my way through your code to work out what is wrong but what I had in mind was a lot simpler than that!
Try something along the lines of:
SELECT Count(Oneofyourvariables) AS CountOfX, [08/02VA].Load_Date, [08/02VA].Status, etc etc etc
FROM [08/02VA]
GROUP BY [08/02VA].Load_Date, [08/02VA].Status, etc etc etc
HAVING (((Count(Oneofyourvariables))>1));
This just lists all those lines that are duplicated and as a bonus tells you how many of them there are.
Try something along the lines of:
SELECT Count(Oneofyourvariables) AS CountOfX, [08/02VA].Load_Date, [08/02VA].Status, etc etc etc
FROM [08/02VA]
GROUP BY [08/02VA].Load_Date, [08/02VA].Status, etc etc etc
HAVING (((Count(Oneofyourvariables))>1));
This just lists all those lines that are duplicated and as a bonus tells you how many of them there are.
Thanks this has worked,
One more question I would also like to display the field "Last_Date" in the query but I know this is the only field that will make these records not duplicates. I want to add this as an additional field. Would I be able to turn this query into a delete query?
This is complicated because the fields in the table are identical except for the last_Date because the record has been accessd twice somewhere along the way this is not supposed to happen thats why I want to delete the duplicates but I would also like to display the Last_Date field
One more question I would also like to display the field "Last_Date" in the query but I know this is the only field that will make these records not duplicates. I want to add this as an additional field. Would I be able to turn this query into a delete query?
This is complicated because the fields in the table are identical except for the last_Date because the record has been accessd twice somewhere along the way this is not supposed to happen thats why I want to delete the duplicates but I would also like to display the Last_Date field
After I ran the query using the count function as a delete query it has deleted way too many records as in 40,000 to many I guess that means I cant use this as a delete query. So I have gone back to my origional query which brings me no results, Is this because when I try to click on design view I get a box saying
" The expression you entered exceeds the 1024 character limit for query design grid"
Will this error have any effect on the way my Query looks for the results
" The expression you entered exceeds the 1024 character limit for query design grid"
Will this error have any effect on the way my Query looks for the results
What you need to do is save your "find duplicates" query and then make a new query joining the original table to your list of duplcate records. You will then be able to add in your date field to do the delete query.
You posted your last reply while I was composing mine! If your query actually succeeded in deleting loads of records then you won't find any duplicates now because they will all have gone....I hope you have a backup copy of your original table?
Similar threads
Solved
Left or Mid?