Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need SQL to pull back only duplicate location records

Posted on 2011-05-13
4
Medium Priority
?
274 Views
Last Modified: 2012-08-13
Hi,

I have a table that has duplicate location # for each code. How can I only select records where the code has more than one location? In the following example, on the records ACT1,ACC1, 1 & ACT1,ACC2,2 & ACT1,ACC1,5 should be displayed?

Database

Code,Group,Location
ACT1,ACC1, 1
ACT1,ACC2, 2
ACT1.ACC1,5
ACT9,BCC2,1
ACT5,DCC2,1
ACT4,BCC2,3

Thanks.
0
Comment
Question by:mlong219
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35754811
use the query wizard when creating the query

queries > new > Find duplicates query wizard

0
 
LVL 77

Expert Comment

by:peter57r
ID: 35754833
This is a bit of a messy thing to do.

Can we first clarify whether the Group has any role in this at all?  You are saying that only the Code and Location should determine which codes are to be selected, but can you confirm that differences in the group value don't matter.

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35754857
try this query

SELECT x.Code, x.Group, x.Location
FROM x
WHERE (((x.Code) In (SELECT [Code] FROM [x] As Tmp GROUP BY [Code] HAVING Count(*)>1 )))
ORDER BY x.Code, x.Location;

Open in new window

0
 

Author Comment

by:mlong219
ID: 35755699
@capricorn1

Using the following data how could I just pull the locations that are different (unique) from the previous query you presented? The only result that would come back would be ACT1,ACC1,1 & ACT1,ACC1,2 because there is a duplicate location value for the code, but the location value is different.

ACT1,ACC1, 1
ACT1,ACC2, 2
ACT1.ACC1,5
ACT9,BCC2,1
ACT9,DCC2,1
ACT4,BCC2,3
ACT4,BCC2,3
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Implementing simple internal controls in the Microsoft Access application.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question