Identify Duplicates (Group Records First)

G

Guest

Hello, I have an invoice table that includes the fields CustomerNumber and
DivisionNumber. Customers can deal with multiple divisions.

CustomerNumber DivisionNumber
ABC Co 10
ABC Co 10
ABC Co 10
Smith Inc 10
Smith Inc 10
Jones Corp 20
Jones Corp 20
Jones Corp 10
XYZ Inc 20
XYZ Inc 20

I am trying to come up with a query that will identify that Jones Corp has
records in both division 20 and 10. I would like the results to like the
below.

CustomerNumber DivisionNumber
Jones Corp 20
Jones Corp 10

I know that I can first do a select query that groups on the customernumber
and divisionnumber fields. AND, then do a find duplicates query on that
first query for records with duplicate customer numbers.

However, I am trying to see if I can do this with just one query (possibly
using select queries or something).

Does anyone know if this is possible using just one select query???

Thanks,
Bill
 
G

Guest

You can do it in a totals query. Substitute your table name for Horton.
SELECT Horton.CustomerNumber, Horton.DivisionNumber,
Count(Horton.DivisionNumber) AS CountOfDivisionNumber
FROM Horton
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;
 
G

Guest

If you know subqueries you can do it simpler but here it is with three queries.
Horton_1 ---
SELECT Horton.CustomerNumber, Horton.DivisionNumber
FROM Horton
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;

Horton_2 ---
SELECT Horton_1.CustomerNumber, Count(Horton_1.DivisionNumber) AS
CountOfDivisionNumber
FROM Horton_1
GROUP BY Horton_1.CustomerNumber
HAVING (((Count(Horton_1.DivisionNumber))>1));

Horton_3 ---
SELECT Horton.CustomerNumber, Horton.DivisionNumber
FROM Horton_2 INNER JOIN Horton ON Horton_2.CustomerNumber =
Horton.CustomerNumber
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;
 
G

Guest

Karl,

Yes, I am able to do it in 2 queries.

1st query:
SELECT Horton.CustomerNumber, Horton.DivisionNumber
FROM Horton
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;

2nd query:
SELECT DISTINCTROW [CustomerNumber], [DivisionNumber]
FROM Horton1
WHERE [CustomerNumber] In (SELECT [CustomerNumber] FROM [Horton1] As Tmp
GROUP BY [CustomerNumber] HAVING Count(*)>1 )
ORDER BY [CustomerNumber];

What I wanted to know was if anyone new a way that I could do this with 1
query as opposed to 2. I was thinking that perhaps it could be done using
subqueries but I have been unable to figure it out.

If anyone can give us a hand merging the above 2 queries into 1 using sub
queries or anything else I would appreciate it.

Thanks,
Bill
 
G

Guest

Thanks for your reply Karl.

However, when I create the suggested query on my test data I am receiving
the following result:

CustomerNumber Division Number CountOfDivision
ABC Co 10 3
Jones Corp 10 1
Jones Corp 20 2
Smith Inc 10 2
XYZ Inc 20 2

All that I want to appear on the result (for this test data) is the 2 Jones
Corp lines. Can I do this all in 1 query, or do I first need to do the query
you suggest here and then run a find duplicates query on the results of the
first select query?

Thanks for your help.

Bill
 
J

John Spencer

Perhaps the following query will work for you.

SELECT DISTINCT H.CustomerNumber, H.DivisionNumber
FROM Horton as H
WHERE Exists
(SELECT *
FROM Horton as H2
WHERE H2.CustomerNumber = H.CustomerNumber
AND H2.DivisionNumber <> H.DivisionNumber)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

William Horton said:
Karl,

Yes, I am able to do it in 2 queries.

1st query:
SELECT Horton.CustomerNumber, Horton.DivisionNumber
FROM Horton
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;

2nd query:
SELECT DISTINCTROW [CustomerNumber], [DivisionNumber]
FROM Horton1
WHERE [CustomerNumber] In (SELECT [CustomerNumber] FROM [Horton1] As Tmp
GROUP BY [CustomerNumber] HAVING Count(*)>1 )
ORDER BY [CustomerNumber];

What I wanted to know was if anyone new a way that I could do this with 1
query as opposed to 2. I was thinking that perhaps it could be done using
subqueries but I have been unable to figure it out.

If anyone can give us a hand merging the above 2 queries into 1 using sub
queries or anything else I would appreciate it.

Thanks,
Bill

KARL DEWEY said:
If you know subqueries you can do it simpler but here it is with three
queries.
Horton_1 ---
SELECT Horton.CustomerNumber, Horton.DivisionNumber
FROM Horton
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;

Horton_2 ---
SELECT Horton_1.CustomerNumber, Count(Horton_1.DivisionNumber) AS
CountOfDivisionNumber
FROM Horton_1
GROUP BY Horton_1.CustomerNumber
HAVING (((Count(Horton_1.DivisionNumber))>1));

Horton_3 ---
SELECT Horton.CustomerNumber, Horton.DivisionNumber
FROM Horton_2 INNER JOIN Horton ON Horton_2.CustomerNumber =
Horton.CustomerNumber
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;
Click to expand...
Click to expand...
 
G

Guest

John,

Thanks very much, this works Great! I'll have to study your query a little
more because I am sure that I can use it in other things I am doing. I
appreciate your help.

Bill

John Spencer said:
Perhaps the following query will work for you.

SELECT DISTINCT H.CustomerNumber, H.DivisionNumber
FROM Horton as H
WHERE Exists
(SELECT *
FROM Horton as H2
WHERE H2.CustomerNumber = H.CustomerNumber
AND H2.DivisionNumber <> H.DivisionNumber)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

William Horton said:
Karl,

Yes, I am able to do it in 2 queries.

1st query:
SELECT Horton.CustomerNumber, Horton.DivisionNumber
FROM Horton
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;

2nd query:
SELECT DISTINCTROW [CustomerNumber], [DivisionNumber]
FROM Horton1
WHERE [CustomerNumber] In (SELECT [CustomerNumber] FROM [Horton1] As Tmp
GROUP BY [CustomerNumber] HAVING Count(*)>1 )
ORDER BY [CustomerNumber];

What I wanted to know was if anyone new a way that I could do this with 1
query as opposed to 2. I was thinking that perhaps it could be done using
subqueries but I have been unable to figure it out.

If anyone can give us a hand merging the above 2 queries into 1 using sub
queries or anything else I would appreciate it.

Thanks,
Bill

KARL DEWEY said:
If you know subqueries you can do it simpler but here it is with three
queries.
Horton_1 ---
SELECT Horton.CustomerNumber, Horton.DivisionNumber
FROM Horton
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;

Horton_2 ---
SELECT Horton_1.CustomerNumber, Count(Horton_1.DivisionNumber) AS
CountOfDivisionNumber
FROM Horton_1
GROUP BY Horton_1.CustomerNumber
HAVING (((Count(Horton_1.DivisionNumber))>1));

Horton_3 ---
SELECT Horton.CustomerNumber, Horton.DivisionNumber
FROM Horton_2 INNER JOIN Horton ON Horton_2.CustomerNumber =
Horton.CustomerNumber
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;

--
KARL DEWEY
Build a little - Test a little


:

Thanks for your reply Karl.

However, when I create the suggested query on my test data I am
receiving
the following result:

CustomerNumber Division Number CountOfDivision
ABC Co 10 3
Jones Corp 10 1
Jones Corp 20 2
Smith Inc 10 2
XYZ Inc 20 2

All that I want to appear on the result (for this test data) is the 2
Jones
Corp lines. Can I do this all in 1 query, or do I first need to do the
query
you suggest here and then run a find duplicates query on the results of
the
first select query?

Thanks for your help.

Bill

:

You can do it in a totals query. Substitute your table name for
Horton.
SELECT Horton.CustomerNumber, Horton.DivisionNumber,
Count(Horton.DivisionNumber) AS CountOfDivisionNumber
FROM Horton
GROUP BY Horton.CustomerNumber, Horton.DivisionNumber;

--
KARL DEWEY
Build a little - Test a little


:

Hello, I have an invoice table that includes the fields
CustomerNumber and
DivisionNumber. Customers can deal with multiple divisions.

CustomerNumber DivisionNumber
ABC Co 10
ABC Co 10
ABC Co 10
Smith Inc 10
Smith Inc 10
Jones Corp 20
Jones Corp 20
Jones Corp 10
XYZ Inc 20
XYZ Inc 20

I am trying to come up with a query that will identify that Jones
Corp has
records in both division 20 and 10. I would like the results to
like the
below.

CustomerNumber DivisionNumber
Jones Corp 20
Jones Corp 10

I know that I can first do a select query that groups on the
customernumber
and divisionnumber fields. AND, then do a find duplicates query on
that
first query for records with duplicate customer numbers.

However, I am trying to see if I can do this with just one query
(possibly
using select queries or something).

Does anyone know if this is possible using just one select query???

Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting first record from a sorted query 6
Problem with query output 1
DISTINCT or GROUP BY? 4
How to add sequence number to groups of records within a table 2
Update Query for only a certain number of records 1
Selecting unique record from duplicate 2
Restricting Records 1
Sorting by first letter, but not really 5

Top