Maxell
asked on
Select portions from a duplicate query dynaset
I have a duplicate query that selects customers that have a duplicate first name, last name and address1. Oftentimes, these duplicates originate for different store locations. I need to identify consumers from a specific store but also want to include it’s matching record that resides in another store. How do I do that?
For example I want to select the duplicate records from store 555:
John Doe, 123 Main Street, Store 555
John Doe, 123 Main Street, Store 789
If I create another query based on the dupe query and enter store 555 parameter, it will only bring up the first consumer record in the dynaset. But I need both records.
For example I want to select the duplicate records from store 555:
John Doe, 123 Main Street, Store 555
John Doe, 123 Main Street, Store 789
If I create another query based on the dupe query and enter store 555 parameter, it will only bring up the first consumer record in the dynaset. But I need both records.
ASKER
SELECT Customer.Lnm, Customer.FNM, Customer.Add1, Customer.City1, Customer.State1, Customer.ZIPCODE, Customer.Phone, Customer.Store, Customer.DeleteRecords
FROM Customer
WHERE (((Customer.Lnm) In (SELECT [Lnm] FROM [Customer] As Tmp GROUP BY [Lnm],[FNM],[Add1] HAVING Count(*)>1 And [FNM] = [Customer].[FNM] And [Add1] = [Customer].[Add1])))
ORDER BY Customer.Lnm, Customer.FNM, Customer.Add1;
FROM Customer
WHERE (((Customer.Lnm) In (SELECT [Lnm] FROM [Customer] As Tmp GROUP BY [Lnm],[FNM],[Add1] HAVING Count(*)>1 And [FNM] = [Customer].[FNM] And [Add1] = [Customer].[Add1])))
ORDER BY Customer.Lnm, Customer.FNM, Customer.Add1;
The problem is you are including the criteria in the sub-query that does not have a reference from the sub select.
You need this instead:
SELECT Customers.*
FROM Customers INNER JOIN [SELECT Customers.LNM, Customers.FNM, Customers.Add1
FROM Customers
GROUP BY Customers.LNM, Customers.FNM, Customers.Add1
HAVING (((Count(*))>1))]. AS X ON (Customers.Add1 = X.Add1) AND (Customers.LNM = X.LNM) AND (Customers.FNM = X.FNM);
You can modify this to return the columns in your original query. Please let me know if you need something else.
You need this instead:
SELECT Customers.*
FROM Customers INNER JOIN [SELECT Customers.LNM, Customers.FNM, Customers.Add1
FROM Customers
GROUP BY Customers.LNM, Customers.FNM, Customers.Add1
HAVING (((Count(*))>1))]. AS X ON (Customers.Add1 = X.Add1) AND (Customers.LNM = X.LNM) AND (Customers.FNM = X.FNM);
You can modify this to return the columns in your original query. Please let me know if you need something else.
ASKER
thanks dorbraxton! i am able to get all the dupe records if i use the lnm, fnm or add1 as criteria however i am having trouble adding the store. could you post the sql statement with the store field addition?
ASKER
I also wanted to mention, the store number is unique for each record unlike the fnm, lnm & add1.
ASKER
the query works splendid if i want to pull both records using the last name, first name or add1 criteria. but the store number varies and will be different in most cases. i can't get the query to work using the store as critera.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You might want to change the columns you select and also hide (uncheck show or remove from the first line of code) the Customers_1.Store column.
In fact, you can use any set of criteria on customers_1 table and if those records have duplicates, you will see them all.
ASKER
Thank you, thank you, THANK YOU!!!!! I love the parameter option!!!!!!!
ASKER
A++++
-Jim