Same number, different description, same code 1

[laina222]

This is part 2 to a <a href = "

http://www.tek-tips.com/viewthread.cfm?qid=1046216&page=1">post

I had earlier</a>.

So now I have the records I want from my first query, and then I made another query to capture the duplicate Numbers from the first query. Now that I have that duplicates query, I want to take any "Number" records that have a new field, "Code" in common. For example:

ID Number Sequence Description Code
1 1000 1 Cat CD111
2 1000 3 Dog CD111
3 1080 1 Rabbit CD122
4 1080 2 Dog CD123

For my results I'd want ID#'s 1 and 2, but not 3 and 4. 1 and 2 would apply because "Number" is the same, "Sequence" is different, "Description" is different but "Code" is the same. 3 and 4 would not because the "Code" is different for each record.

Thanks for any help in advance

 

[ByteMyzer]

You might try something along the lines of:
[tt]
SELECT qdf1.ID, qdf1.Number, qdf1.Sequence, qdf1.Description, qdf1.Code
FROM MyQuery AS qdf1
WHERE qdf1.Code In (
SELECT qdf2.Code
FROM MyQuery AS qdf2
GROUP BY qdf2.Code
HAVING Count(qdf2.Code)>1;
);
[/tt]

See if this works for you.

 

[PHV]

Something like this ?
SELECT A.* FROM yourTable AS A INNER JOIN (
SELECT Number,Code FROM yourTable
GROUP BY Number,Code HAVING Count(*)>1
) AS B ON A.Number=B.Number AND A.Code=B.Code

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[laina222]

ByteMyzer - I got a few strange results, like Numbers that didn't match but with Codes that did. Some of the results are perfect, but too many extras that aren't.

PHV - perfect!

Thanks for all the help