• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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.
0
Maxell
Asked:
Maxell
  • 6
  • 4
1 Solution
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now