Finding records with duplicates field entries

  • Thread starter Thread starter Pele
  • Start date Start date
P

Pele

I am working with a Brand Table and I need to ensure that
none of the entries in the Brand ID or Brand Name should
be repeated i.e. if any one of these fields are repeated,
I need to identify the records.

I used the "Find Duplicates Query Wizard" and below is the
SQL. The problem is that this query seems to only try to
identify records where the Brand ID and Brand Name are
duplicated for the records, but I am interested in whether
in situations where even one of these fields are
duplicated.

What do I need to do.


SELECT [Temp_1Brand Distribution].[Brand ID], [Temp_1Brand
Distribution].[Brand Name], [Temp_1Brand Distribution].
[Distribution Count]
FROM [Temp_1Brand Distribution]
WHERE ((([Temp_1Brand Distribution].[Brand ID]) In (SELECT
[Brand ID] FROM [Temp_1Brand Distribution] As Tmp GROUP BY
[Brand ID],[Brand Name] HAVING Count(*)>1 And [Brand
Name] = [Temp_1Brand Distribution].[Brand Name])))
ORDER BY [Temp_1Brand Distribution].[Brand ID],
[Temp_1Brand Distribution].[Brand Name];
 
If you are up to it, I would probably set up separate queries to identify
duplicate BrandID and Brand Name and then join them together in a Union
query.

Make sure these 2 queries have the same number of fields in the same
sequence: BrandID, BrandName and Comment. Comment could be set to
"Duplicate BrandID" for one query and "Duplicate BrandName" for the other.

Then create a Union query to join the two together. This will give you a
list of all the selected records with a comment as to why they were
selected.

Union queries have to be written in the SQL view, but it should be as simple
as this as long as the queries have the same # of fields & order of fields:

TABLE qryBrandIDDuplicates
UNION TABLE qryBrandNameDuplicates
ORDER BY BrandID, BrandName;

Hope this helps,
 
One Union query that is not updatable.

SELECT tmp.[Brand ID], "BrandID" as FldName, tmp.[Distribution Count]
FROM [Temp_1Brand Distribution] As Tmp
GROUP BY tmp.BrandID
WHERE ((([Temp_1Brand Distribution].[Brand ID]) In (SELECT
[Brand ID] FROM [Temp_1Brand Distribution] As Tmp GROUP BY
[Brand ID],[Brand Name] HAVING Count(*)>1 And [Brand
Name] = [Temp_1Brand Distribution].[Brand Name])))
ORDER BY [Temp_1Brand Distribution].[Brand ID],
[Temp_1Brand Distribution].[Brand Name];
 
AUGH!!!! Sorry about that. I see you have a suggested solution already.
Disregard the beginning of a response below.

John Spencer (MVP) said:
One Union query that is not updatable.

SELECT tmp.[Brand ID], "BrandID" as FldName, tmp.[Distribution Count]
FROM [Temp_1Brand Distribution] As Tmp
GROUP BY tmp.BrandID
WHERE ((([Temp_1Brand Distribution].[Brand ID]) In (SELECT
[Brand ID] FROM [Temp_1Brand Distribution] As Tmp GROUP BY
[Brand ID],[Brand Name] HAVING Count(*)>1 And [Brand
Name] = [Temp_1Brand Distribution].[Brand Name])))
ORDER BY [Temp_1Brand Distribution].[Brand ID],
[Temp_1Brand Distribution].[Brand Name];
I am working with a Brand Table and I need to ensure that
none of the entries in the Brand ID or Brand Name should
be repeated i.e. if any one of these fields are repeated,
I need to identify the records.

I used the "Find Duplicates Query Wizard" and below is the
SQL. The problem is that this query seems to only try to
identify records where the Brand ID and Brand Name are
duplicated for the records, but I am interested in whether
in situations where even one of these fields are
duplicated.

What do I need to do.

SELECT [Temp_1Brand Distribution].[Brand ID], [Temp_1Brand
Distribution].[Brand Name], [Temp_1Brand Distribution].
[Distribution Count]
FROM [Temp_1Brand Distribution]
WHERE ((([Temp_1Brand Distribution].[Brand ID]) In (SELECT
[Brand ID] FROM [Temp_1Brand Distribution] As Tmp GROUP BY
[Brand ID],[Brand Name] HAVING Count(*)>1 And [Brand
Name] = [Temp_1Brand Distribution].[Brand Name])))
ORDER BY [Temp_1Brand Distribution].[Brand ID],
[Temp_1Brand Distribution].[Brand Name];
Click to expand...
Click to expand...
 
John,

The SQL code I had shown in my initial email was what the
wizard had given me, but the code is not doing what I want
it to do i.e. the query has it stands is presently looking
for situations where BOTH Brand ID and Brand Name are
duplicated whereas, I want to identify cases where if
EITHER Brand ID or Brand Name are duplicated or where both
are duplicated.

Do you have any solution that can be done with one query.

Toks
-----Original Message-----
AUGH!!!! Sorry about that. I see you have a suggested solution already.
Disregard the beginning of a response below.

John Spencer (MVP) said:
One Union query that is not updatable.

SELECT tmp.[Brand ID], "BrandID" as FldName, tmp. [Distribution Count]
FROM [Temp_1Brand Distribution] As Tmp
GROUP BY tmp.BrandID
WHERE ((([Temp_1Brand Distribution].[Brand ID]) In (SELECT
[Brand ID] FROM [Temp_1Brand Distribution] As Tmp GROUP BY
[Brand ID],[Brand Name] HAVING Count(*)>1 And [Brand
Name] = [Temp_1Brand Distribution].[Brand Name])))
ORDER BY [Temp_1Brand Distribution].[Brand ID],
[Temp_1Brand Distribution].[Brand Name];
I am working with a Brand Table and I need to ensure that
none of the entries in the Brand ID or Brand Name should
be repeated i.e. if any one of these fields are repeated,
I need to identify the records.

I used the "Find Duplicates Query Wizard" and below is the
SQL. The problem is that this query seems to only try to
identify records where the Brand ID and Brand Name are
duplicated for the records, but I am interested in whether
in situations where even one of these fields are
duplicated.

What do I need to do.

SELECT [Temp_1Brand Distribution].[Brand ID], [Temp_1Brand
Distribution].[Brand Name], [Temp_1Brand Distribution].
[Distribution Count]
FROM [Temp_1Brand Distribution]
WHERE ((([Temp_1Brand Distribution].[Brand ID]) In (SELECT
[Brand ID] FROM [Temp_1Brand Distribution] As Tmp GROUP BY
[Brand ID],[Brand Name] HAVING Count(*)>1 And [Brand
Name] = [Temp_1Brand Distribution].[Brand Name])))
ORDER BY [Temp_1Brand Distribution].[Brand ID],
[Temp_1Brand Distribution].[Brand Name];
Click to expand...
Click to expand...
.
Click to expand...
 
To do this on one query:

1) Create the 2 SELECT queries I suggested. One for BrandID, one for
BrandName.
2) Create the UNION query I described, based on those queries
3) Once you get the results you want from that UNION query you may be able
to replace the TABLE references in the UNION query with the SQL from the
SELECT queries:

SELECT (...remaining SELECT statement from query 1)
UNION SELECT (...remaining SELECT statement from query2)
ORDER BY x, y;

Depending on the complexity of the 2 SELECT statements, you may be able to
get the UNION query to do it all. If so, you can delete the 2 SELECT
queries once you have the revised UNION query working to your satisfaction.
--
George Nicholson

Remove 'Junk' from return address.


Pele said:
John,

The SQL code I had shown in my initial email was what the
wizard had given me, but the code is not doing what I want
it to do i.e. the query has it stands is presently looking
for situations where BOTH Brand ID and Brand Name are
duplicated whereas, I want to identify cases where if
EITHER Brand ID or Brand Name are duplicated or where both
are duplicated.

Do you have any solution that can be done with one query.

Toks
-----Original Message-----
AUGH!!!! Sorry about that. I see you have a suggested solution already.
Disregard the beginning of a response below.

John Spencer (MVP) said:
One Union query that is not updatable.

SELECT tmp.[Brand ID], "BrandID" as FldName, tmp. [Distribution Count]
FROM [Temp_1Brand Distribution] As Tmp
GROUP BY tmp.BrandID
WHERE ((([Temp_1Brand Distribution].[Brand ID]) In (SELECT
[Brand ID] FROM [Temp_1Brand Distribution] As Tmp GROUP BY
[Brand ID],[Brand Name] HAVING Count(*)>1 And [Brand
Name] = [Temp_1Brand Distribution].[Brand Name])))
ORDER BY [Temp_1Brand Distribution].[Brand ID],
[Temp_1Brand Distribution].[Brand Name];

Pele wrote:

I am working with a Brand Table and I need to ensure that
none of the entries in the Brand ID or Brand Name should
be repeated i.e. if any one of these fields are repeated,
I need to identify the records.

I used the "Find Duplicates Query Wizard" and below is the
SQL. The problem is that this query seems to only try to
identify records where the Brand ID and Brand Name are
duplicated for the records, but I am interested in whether
in situations where even one of these fields are
duplicated.

What do I need to do.

SELECT [Temp_1Brand Distribution].[Brand ID], [Temp_1Brand
Distribution].[Brand Name], [Temp_1Brand Distribution].
[Distribution Count]
FROM [Temp_1Brand Distribution]
WHERE ((([Temp_1Brand Distribution].[Brand ID]) In (SELECT
[Brand ID] FROM [Temp_1Brand Distribution] As Tmp GROUP BY
[Brand ID],[Brand Name] HAVING Count(*)>1 And [Brand
Name] = [Temp_1Brand Distribution].[Brand Name])))
ORDER BY [Temp_1Brand Distribution].[Brand ID],
[Temp_1Brand Distribution].[Brand Name];
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top