I have a list of resistors which I want to ensure that each unique value has only one corresponding part number. Here, for example:

Reference Value PartNumber
R1 100 Z1
R10 800 Z5
R2 120 Z2
R3 100 Z11
R4 300 Z3
R5 150 Z4
R6 800 Z5
R7 600 Z6
R8 100 Z1
R9 100 Z1

we see that the value of 100 has two part numbers (Z1 and Z11) and I want to catch that. In otherwords I want a query to show me just the following two rows:
100 Z1
100 Z11

I used the Find Duplicates wizard but got this:
Value PartNumber
100 Z1
100 Z1
100 Z11
100 Z1
800 Z5
800 Z5

On the above query I ran the wizard again but got a long list still.
How can I do all of this in a single query?

Harry,

You could do this in one query using nested selects, but in general
Access doesn't like that. It can seriously degrade performance.

I'd use 3 queries:

Query1: (Get all unique Ref/Part combinations)


Select Distinct [Value], PartNumber
From YourTable



Query2: (Get All Ref numbers with multiple PartNumbers)


Select [Value], Count(*)
From Query1
Group By [Value]
Having Count(*) > 1


Query3: (Get ONLY the unique Ref/Part combinations with more than 1 PartNumber)


Select Query1.[Value],
Query1.PartNumber
From Query1 Inner Join Query2 ON
Query1.[Value] = Query2.[Value]
Order By Query1.[Value]



hth,
Wayne

The following nested-query scenario is actually quite efficient (substitute highlighted text with actual table/field names:

SELECT DISTINCT T1.Value, T1.PartNumber
FROM MyTable AS T1
WHERE EXISTS (
SELECT T2.Value
FROM MyTable AS T2
WHERE T2.Value = T1.Value
AND T2.PartNumber <> T1.PartNumber);

ByteMyzer,

Using SQL Server, I wouldn't hesitate to use your example.

Maybe Access handles the "Where exists ..." better than it
does "Where T1.Value In (Select ...)".

Won't he still have the "I ran the wizard again but got a long list still." problem?

100 Z1 <-- MUltiple occurrences
100 Z1 <-- MUltiple occurrences
100 Z11
100 Z1 <-- MUltiple occurrences
800 Z5
800 Z5

Wayne

WayneRyan (http://www.access-programmers.co.uk/forums/member.php?u=11281),

It turns out that the solution is actually simpler. I have modified my SQL syntax to reflect the simpler solution, and it should work efficiently providing that the [Value] and [PartNumber] fields are indexed.

Thanks to both of you for your replies.
WayneRyan - I did this method successfully at first, however my end goal is to have a single query that I can use in Excel, and this method calls another query.

ByteMyzer - very cool. Thanks for the idea. It works.
1) What is your modified SQL that you said you have?
2) I now added a join with a Vendors table and I don't know how to use your trick. My basic query is:

SELECT Resistors.PartNumber, Resistors.Value, Vendors.VendorName
FROM Vendors INNER JOIN Resistors ON Vendors.VendorID=Resistors.Vendor;

If I try to add the table alias at the end of the FROM statement:

SELECT Resistors.PartNumber, Resistors.Value, Vendors.VendorName
FROM Vendors INNER JOIN Resistors ON Vendors.VendorID=Resistors.Vendor as T2;

I get a syntax error.
Do you mind adding your trick to the above query?
3) I'm looking for a good source of learning material on sql. Can you recommend a web site?
Thanks again. You guys are great.

Your query should be more like the following:

SELECT R1.PartNumber, R1.Value, V.VendorName
FROM Vendors AS V INNER JOIN Resistors AS R1 ON V.VendorID = R1.Vendor
WHERE EXISTS (
SELECT R2.Value
FROM Resistors AS R2
WHERE R2.Value = R1.Value
AND R2.PartNumber <> R1.PartNumber);

You left out the DISTINCT keyword in the first line but once added it works.
Thanks!

Whoops, you're right, I did. Good catch, though, and I'm glad it works for you.

There are certain methods you need to follow; you should refer to the web to be able to understand more on how you can do it better next time.

Hello, rickysymo (http://www.access-programmers.co.uk/forums/member.php?u=65567), and welcome to Access World Forums.

Your comment is rather vague. To whom were you addressing your response? Certain methods pertaining to what? Do you have something more specific to contribute that would be more constructive in working toward a working solution?