Duplicate Query

[Rebeca]

Please help me find a solution so I don't have to manually go through 14,000
lines

I imported an excel spreadsheet into Access to find the duplicated records.

I created a duplicate query where I have identified all of the duplicates in
a table. What I now need to do is identify the rows that do not have the same
"rendering"

For example, Encounter 1 is duplicated with rendering of M.Jones and
C.Jones. I need to see those encounters. Those that have the same rendering,
I could care less.

Here is my SQL.

SELECT CVI.Encounter, CVI.Rendering
FROM CVI
WHERE (((CVI.Encounter) In (SELECT [Encounter] FROM [CVI] As Tmp GROUP BY
[Encounter] HAVING Count(*)>1 )))
ORDER BY CVI.Encounter;

Thank you in advance

 

[KARL DEWEY]

Please post sample data and desired results.

 

[Rebeca]

For example, this is parts of the results for my duplicate query. Encounter
numbers 12345 and 34512 are duplicate records, this is ok. However, Encounter
number 34512 has different persons listed as the rendering.

Encounter Rendering
12345 Michael Jones
12345 Michael Jones
34512 Michael Jones
34512 Chris Jones

I want to see the 2 records for Encounter 34512 and filter off the 12345
records.

Thanks!

Please post sample data and desired results.

--
Build a little, test a little.

Please help me find a solution so I don't have to manually go through 14,000
lines

I imported an excel spreadsheet into Access to find the duplicated records.

I created a duplicate query where I have identified all of the duplicates in
a table. What I now need to do is identify the rows that do not have the same
"rendering"

For example, Encounter 1 is duplicated with rendering of M.Jones and
C.Jones. I need to see those encounters. Those that have the same rendering,
I could care less.

Here is my SQL.

SELECT CVI.Encounter, CVI.Rendering
FROM CVI
WHERE (((CVI.Encounter) In (SELECT [Encounter] FROM [CVI] As Tmp GROUP BY
[Encounter] HAVING Count(*)>1 )))
ORDER BY CVI.Encounter;

Thank you in advance

 

[KARL DEWEY]

Try this --
SELECT CVI.Encounter, CVI.Rendering
FROM CVI
GROUP BY CVI.Encounter, CVI.Rendering
HAVING (((Count([Encounter] & [Rendering]))=1));

--
Build a little, test a little.

For example, this is parts of the results for my duplicate query. Encounter
numbers 12345 and 34512 are duplicate records, this is ok. However, Encounter
number 34512 has different persons listed as the rendering.

Encounter Rendering
12345 Michael Jones
12345 Michael Jones
34512 Michael Jones
34512 Chris Jones

I want to see the 2 records for Encounter 34512 and filter off the 12345
records.

Thanks!

Please post sample data and desired results.

--
Build a little, test a little.

Please help me find a solution so I don't have to manually go through 14,000
lines

I imported an excel spreadsheet into Access to find the duplicated records.

I created a duplicate query where I have identified all of the duplicates in
a table. What I now need to do is identify the rows that do not have the same
"rendering"

For example, Encounter 1 is duplicated with rendering of M.Jones and
C.Jones. I need to see those encounters. Those that have the same rendering,
I could care less.

Here is my SQL.

SELECT CVI.Encounter, CVI.Rendering
FROM CVI
WHERE (((CVI.Encounter) In (SELECT [Encounter] FROM [CVI] As Tmp GROUP BY
[Encounter] HAVING Count(*)>1 )))
ORDER BY CVI.Encounter;

Thank you in advance

 

[Sylvain Lafontaine]

Using a simple Exists() should do the trick:

select * from TheTable as t1
Where Exists (Select * from TheTable t2 where t1.Encounter = t2.Encounter
and t1.Rendering <> t2.Rendering)

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

For example, this is parts of the results for my duplicate query.
Encounter
numbers 12345 and 34512 are duplicate records, this is ok. However,
Encounter
number 34512 has different persons listed as the rendering.

Encounter Rendering
12345 Michael Jones
12345 Michael Jones
34512 Michael Jones
34512 Chris Jones

I want to see the 2 records for Encounter 34512 and filter off the 12345
records.

Thanks!

Please post sample data and desired results.

--
Build a little, test a little.

Please help me find a solution so I don't have to manually go through
14,000
lines

I imported an excel spreadsheet into Access to find the duplicated
records.

I created a duplicate query where I have identified all of the
duplicates in
a table. What I now need to do is identify the rows that do not have
the same
"rendering"

For example, Encounter 1 is duplicated with rendering of M.Jones and
C.Jones. I need to see those encounters. Those that have the same
rendering,
I could care less.

Here is my SQL.

SELECT CVI.Encounter, CVI.Rendering
FROM CVI
WHERE (((CVI.Encounter) In (SELECT [Encounter] FROM [CVI] As Tmp GROUP
BY
[Encounter] HAVING Count(*)>1 )))
ORDER BY CVI.Encounter;

Thank you in advance

 

[Sylvain Lafontaine]

This one won't exclude unique records. Maybe there isn't any in the table
CVI but I won't be surprised if there is.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Try this --
SELECT CVI.Encounter, CVI.Rendering
FROM CVI
GROUP BY CVI.Encounter, CVI.Rendering
HAVING (((Count([Encounter] & [Rendering]))=1));

--
Build a little, test a little.

For example, this is parts of the results for my duplicate query.
Encounter
numbers 12345 and 34512 are duplicate records, this is ok. However,
Encounter
number 34512 has different persons listed as the rendering.

Encounter Rendering
12345 Michael Jones
12345 Michael Jones
34512 Michael Jones
34512 Chris Jones

I want to see the 2 records for Encounter 34512 and filter off the 12345
records.

Thanks!

Please post sample data and desired results.

--
Build a little, test a little.


:

Please help me find a solution so I don't have to manually go through
14,000
lines

I imported an excel spreadsheet into Access to find the duplicated
records.

I created a duplicate query where I have identified all of the
duplicates in
a table. What I now need to do is identify the rows that do not have
the same
"rendering"

For example, Encounter 1 is duplicated with rendering of M.Jones and
C.Jones. I need to see those encounters. Those that have the same
rendering,
I could care less.

Here is my SQL.

SELECT CVI.Encounter, CVI.Rendering
FROM CVI
WHERE (((CVI.Encounter) In (SELECT [Encounter] FROM [CVI] As Tmp
GROUP BY
[Encounter] HAVING Count(*)>1 )))
ORDER BY CVI.Encounter;

Thank you in advance

 

[John W. Vinson]

Please help me find a solution so I don't have to manually go through 14,000
lines

I imported an excel spreadsheet into Access to find the duplicated records.

I created a duplicate query where I have identified all of the duplicates in
a table. What I now need to do is identify the rows that do not have the same
"rendering"

For example, Encounter 1 is duplicated with rendering of M.Jones and
C.Jones. I need to see those encounters. Those that have the same rendering,
I could care less.

Here is my SQL.

SELECT CVI.Encounter, CVI.Rendering
FROM CVI
WHERE (((CVI.Encounter) In (SELECT [Encounter] FROM [CVI] As Tmp GROUP BY
[Encounter] HAVING Count(*)>1 )))
ORDER BY CVI.Encounter;

Thank you in advance

There are several ways to do this - another is a "self join":

SELECT A.Encounter, A.Rendering, B.Rendering
FROM CVI As A INNER JOIN CVI As B
ON A.Encounter = B.Encounter
WHERE A.Rendering < B.Rendering
ORDER BY A.Encounter;

This should be quite efficient if there is an Index on Encounter.

NOte that I used < (less than) rather than <> - the latter will find each
mismatch twice.

 

[Rebeca]

Thank you, both of the last examples worked perfectly!

Please help me find a solution so I don't have to manually go through 14,000
lines

I imported an excel spreadsheet into Access to find the duplicated records.

I created a duplicate query where I have identified all of the duplicates in
a table. What I now need to do is identify the rows that do not have the same
"rendering"

For example, Encounter 1 is duplicated with rendering of M.Jones and
C.Jones. I need to see those encounters. Those that have the same rendering,
I could care less.

Here is my SQL.

SELECT CVI.Encounter, CVI.Rendering
FROM CVI
WHERE (((CVI.Encounter) In (SELECT [Encounter] FROM [CVI] As Tmp GROUP BY
[Encounter] HAVING Count(*)>1 )))
ORDER BY CVI.Encounter;

Thank you in advance

There are several ways to do this - another is a "self join":

SELECT A.Encounter, A.Rendering, B.Rendering
FROM CVI As A INNER JOIN CVI As B
ON A.Encounter = B.Encounter
WHERE A.Rendering < B.Rendering
ORDER BY A.Encounter;

This should be quite efficient if there is an Index on Encounter.

NOte that I used < (less than) rather than <> - the latter will find each
mismatch twice.