How to Exclude Duplicates

[Lisa W.]

I am trying to get query results that will exclude all duplicate records. I
only want to see results of records that are not dups.

Is this possible?

Example: below is what the table includes with all records, including
duplicates.

Rev_Entity_ID
50001-c4011Roseb
50005-c3017WestP
50008-c1001Brook
50008-c3065Brook
50016-c1002Phila
50016-c1002Phila

The results I need are shown in the example below. It only gives results
for records which do not have a dup.

Rev_Entity_ID
50001-c4011Roseb
50005-c3017WestP

Thanks in Advance,
Lisa W.

 

[KARL DEWEY]

What are you looking at to define duplicate as I do not see 50008-c1001Brook
and 50008-c3065Brook being duplicate.

 

[Lisa W.]

Karl you are correct. That was my mistake. 5008-c1001Brook and
5008-c3065Brook would be in my results also because it is not a dup.

Can you help me with this?

Thanks in Advance,
Lisa W.

 

[John W. Vinson]

I am trying to get query results that will exclude all duplicate records. I
only want to see results of records that are not dups.

Is this possible?

Example: below is what the table includes with all records, including
duplicates.

Rev_Entity_ID
50001-c4011Roseb
50005-c3017WestP
50008-c1001Brook
50008-c3065Brook
50016-c1002Phila
50016-c1002Phila

The results I need are shown in the example below. It only gives results
for records which do not have a dup.

Rev_Entity_ID
50001-c4011Roseb
50005-c3017WestP

Thanks in Advance,
Lisa W.

To exclude *exact* duplicates in all fields, open the query in design view;
right click the grey background behind the table icons; select Properties; and
set the Unique Values property to Yes.

However this does not appear to be what you want, since the two "Brook" lines
appear to be unique and not duplicates. Why should they be excluded? Do only
the first five digits "count"?

 

[KARL DEWEY]

As I do not know subqueries then you must use two queries --
Rev_Entity_ID_Count ---
SELECT [Rev_Entity_ID], Count([Rev_Entity_ID]) AS [Rev_Entity_ID_Count]
FROM [YourTable]
GROUP BY [Rev_Entity_ID]
HAVING (Count([Rev_Entity_ID]) = 1);

SELECT [YourTable].*
FROM [YourTable] INNER JOIN [Rev_Entity_ID_Count] ON
[Rev_Entity_ID_Count].[Rev_Entity_ID] = [YourTable].[Rev_Entity_ID];

 

[Lisa W.]

Thanks Karl! I now have the results I needed!!

As I do not know subqueries then you must use two queries --
Rev_Entity_ID_Count ---
SELECT [Rev_Entity_ID], Count([Rev_Entity_ID]) AS [Rev_Entity_ID_Count]
FROM [YourTable]
GROUP BY [Rev_Entity_ID]
HAVING (Count([Rev_Entity_ID]) = 1);

SELECT [YourTable].*
FROM [YourTable] INNER JOIN [Rev_Entity_ID_Count] ON
[Rev_Entity_ID_Count].[Rev_Entity_ID] = [YourTable].[Rev_Entity_ID];

--
KARL DEWEY
Build a little - Test a little

Karl you are correct. That was my mistake. 5008-c1001Brook and
5008-c3065Brook would be in my results also because it is not a dup.

Can you help me with this?

Thanks in Advance,
Lisa W.

 

[John W. Vinson]

I am trying to get query results that will exclude all duplicate records. I
only want to see results of records that are not dups.

Is this possible?

Example: below is what the table includes with all records, including
duplicates.

Rev_Entity_ID
50001-c4011Roseb
50005-c3017WestP
50008-c1001Brook
50008-c3065Brook
50016-c1002Phila
50016-c1002Phila

The results I need are shown in the example below. It only gives results
for records which do not have a dup.

Rev_Entity_ID
50001-c4011Roseb
50005-c3017WestP

Thanks in Advance,
Lisa W.

aha... sorry, I did misunderstand (confused by the 50008 Brook issue).

You can do this as Karl suggests, or with one totals query:

SELECT [Rev_Entity_ID] FROM yourtable
GROUP BY [Rev_Entity_ID]
HAVING Count(*) = 1;

 

[Lisa W.]

Thanks to you as well John!

I am trying to get query results that will exclude all duplicate records. I
only want to see results of records that are not dups.

Is this possible?

Example: below is what the table includes with all records, including
duplicates.

Rev_Entity_ID
50001-c4011Roseb
50005-c3017WestP
50008-c1001Brook
50008-c3065Brook
50016-c1002Phila
50016-c1002Phila

The results I need are shown in the example below. It only gives results
for records which do not have a dup.

Rev_Entity_ID
50001-c4011Roseb
50005-c3017WestP

Thanks in Advance,
Lisa W.

aha... sorry, I did misunderstand (confused by the 50008 Brook issue).

You can do this as Karl suggests, or with one totals query:

SELECT [Rev_Entity_ID] FROM yourtable
GROUP BY [Rev_Entity_ID]
HAVING Count(*) = 1;