Running a Find duplicates query and unmatched query

[srm7028]

I need to run a find duplicates query and unmatched query without the use of the wizards. I do not have it installed and do not have the office cd anymore to install it. I have Access2000.
Any help is much appreciated again.

Thx Sean

 

[Jon K]

You can use IN and NOT IN with a subquery to find duplicate or unmatched records.


(1) To Find Duplicates
The following query (typed in the SQL View of a query) will find those records with duplicate "PartNumber plus CreationDate" in table tblParts:-

SELECT *
FROM tblParts
WHERE PartNumber & CreationDate IN (select PartNumber & CreationDate from tblParts group by PartNumber, CreationDate having count(*) >1)
ORDER BY PartNumber;


Notice that "group by... having count(*) >1" is used in the subquery (the select statement in brackets.)

To find duplicates in only the PartNumber field, just remove CreateionDate from the query.


(2) To Find Unmatched Records
Assuming tables tblCustomer and tblOrder are related by CustomerID,
the following will find those customers who have not placed any orders this year:-

SELECT *
FROM tblCustomer
WHERE CustomerID NOT IN (select CustomerID from tblOrder where year(OrderDate)=2002)


A subquery must be a query that returns ONLY ONE field or expression, so "(select PartNumber & CreationDate ...)" is used in the first example.

Hope you can adapt the examples to suit your needs.

 

[srm7028]

Syntax Error

Didnt work I found something that did work by typing in it the criteria area but you have to know the CustomerNum an input box comes up prompting for a CustomerNum. I need it to do the same as the wizard would do it. I searched and found many ways that didnt work. If I could just see a DB that had one run with the wizard I could figure it myself.

Thx for trying, Sean

 

[Jon K]

I installed the Find Duplicates Query Wizard and the Find Unmatched Query Wizard from the Office 97 CD, ran them on the tables in my examples above, and obtained these two SQL statements:

by Wizard - Find duplicates for tblParts:-
SELECT DISTINCTROW tblParts.PartNumber, tblParts.CreationDate, tblParts.Description
FROM tblParts
WHERE (((tblParts.PartNumber) In (SELECT [PartNumber] FROM [tblParts] As Tmp GROUP BY [PartNumber],[CreationDate] HAVING Count(*)>1 And [CreationDate] = [tblParts].[CreationDate])))
ORDER BY tblParts.PartNumber, tblParts.CreationDate;

by Wizard - tblCustomer Without Matching tblOrder:-
SELECT DISTINCTROW tblCustomer.CustomerID, tblCustomer.CustomerName
FROM tblCustomer LEFT JOIN tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID
WHERE (((tblOrder.CustomerID) Is Null));

However, the Find Unmatched Query Wizard won't let me set any criteria, so it can only find "CustomerID in tblCustomer Without Matching CutomererID in tblOrder", but not "customers who have not placed any orders this year" as in my example.


I attach the DB for you (find_dup_and_unmatch.zip in Access97, size 16KB). It contains the two queries produced by the wizards and my two examples. They have all been tested in Access 2000. No syntax errors at all.

 

[srm7028]

Ok first I want to thank you

for all your help. I got the find duplicates to work by modifying part of what you had in the SQL view In (SELECT [PartNumber] FROM [tblParts] As Tmp GROUP BY [PartNumber],[CreationDate] HAVING Count(*)>1 And [CreationDate] = [tblParts].[CreationDate])))
and putting it in the criteria for CustomerNum.

The unmatched I couldnt get to work whatever I did to it but I will try more tommorow.


Thx Again for your help

Sean