Get Duplicated Records

[Extension]

I have a table with two columns. The first column contains the ID and second one contains the number. I'm trying to find the duplicated records (numbers) and display the numbers with the IDs.

Data

ID       NUMBER
D4999    BIN9003
D3955    BIN9003
D5666    BIN9003
B2022    BIN0007

Output

NUMBER     ID1     ID2     ID3
BIN9003    D4999   D3955   D5666

Current query

SELECT NUMBER, COUNT(ID) AS Dupes
FROM TABLE
GROUP BY NUMBER
HAVING (COUNT(ID)>1);

I'm obviously only getting the duplicated numbers with the number of times it's duplicated with the query above.

 

[jaydeebetoo]

why not use the find duplicates query wizard?

 

[Extension]

The "find duplicates query wizard" will come up will the same output because it's about the same query as mine. So I only get one ID and not all the IDs sharing the same number.

 

[traingamer]

This

SELECT t1.ID, t1.Num
FROM TABLE t1
WHERE t1.Num IN [COLOR=red](SELECT t2.Num
FROM TABLE t2
GROUP BY t2.Num
HAVING (((Count(t2.ID))>1))[/color]);

will return:
ID Num
D4999 BIN9003
D3995 BIN9003
D5666 BIN9003
You can probably turn it into a crosstab query without too much trouble.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill