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.
MaxellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Paste your existing query SQL in this question, and we'll modify it to suit your needs.

-Jim
0
MaxellAuthor Commented:
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;
0
dorbraxtonCommented:
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.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MaxellAuthor Commented:
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?
0
MaxellAuthor Commented:
I also wanted to mention, the store number is unique for each record unlike the fnm, lnm & add1.
0
MaxellAuthor Commented:
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.
0
dorbraxtonCommented:
Ok, try this:

SELECT Customers.*, Customers_1.Store
FROM Customers AS Customers_1 INNER JOIN (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.FNM = X.FNM) AND (Customers.LNM = X.LNM) AND (Customers.Add1 = X.Add1)) ON (Customers_1.Add1 = X.Add1) AND (Customers_1.FNM = X.FNM) AND (Customers_1.LNM = X.LNM)
WHERE (((Customers_1.Store)=[Enter Store Value]));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dorbraxtonCommented:
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.
0
dorbraxtonCommented:
In fact, you can use any set of criteria on customers_1 table and if those records have duplicates, you will see them all.
0
MaxellAuthor Commented:
Thank you, thank you, THANK YOU!!!!!  I love the parameter option!!!!!!!
0
MaxellAuthor Commented:
A++++
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.