Duplicate Queries

[Tom]

I am using Access 2007 query wizards to do the following, but seem to be
unable to take it one step further.

In the Wizard, I identify 3 columns if they are duplicated to give me the
results No problem with this. But I would like to add a fourth column to
this query ONLY if this fourth column does not Match itself.

Hope I am making this clear enough.

Thanks

 

[John W. Vinson]

I am using Access 2007 query wizards to do the following, but seem to be
unable to take it one step further.

In the Wizard, I identify 3 columns if they are duplicated to give me the
results No problem with this. But I would like to add a fourth column to
this query ONLY if this fourth column does not Match itself.

Hope I am making this clear enough.

Not really. I've read it three times now and I'm not sure what you're getting
at.

You want to find records which are duplicates with respect to three fields,
but if they are also duplicates at the fourth field you DON'T want to see
them?

Could you post some sample data and the desired result?

 

[Tom]

Ok---Here is a try:

Columns
1 2 3 4 5 etc
a c e f
a c e f
a c e g
a d e f
a d e f

Results Wanted

a c e f
a c e g

Does this help?

Thanks

 

[John W. Vinson]

Ok---Here is a try:

Columns
1 2 3 4 5 etc
a c e f
a c e f
a c e g
a d e f
a d e f

Results Wanted

a c e f
a c e g

Does this help?

Thanks

Not unless you meant to say

a d e f

instead of

a c e g

since a c e g is NOT duplicated and a d e f is. In fact the situation for a d
e f appears to be absolutely identical to that for a c e f. Why should one be
selected and not the other?

 

[Tom]

I want the result to be If Col 1, 2 and 3 are duplicated then only want to
see the ones that Col 4 are Not duplicated.

Columns
1 2 3 4 5 etc
a c b h----changed-- this may take out the confusion
a c e f
a c e g
a d e f
a d e f

Results Wanted: The a c e match in two rows but only want when the 4th col
does not match

a c e f
a c e g

 

[Sylvain Lafontaine]

The easiest way to solve these kind of problem is always to use the EXISTS
statement. Later, you can use more advanced queries by using things HAVING
and ORDER BY to retrieve/filter your duplicates but these require a deeper
experience of SQL.

The problem with EXISTS is that it require a primary key in order to detect
the duplicate but I don't think that the fact it is missing in your design
is really important.

Here an example that could fill the bill for you; based on T-SQL for
SQL-Server for creating the sample data:

Select * into #t from
(select 1 as idt, 'a' c1, 'c' c2, 'e' c3, 'f' c4
union all select 2, 'a', 'c', 'e', 'f'
union all select 3, 'a', 'c', 'e', 'g'
union all select 4, 'a', 'd', 'e', 'f'
union all select 5, 'a', 'd', 'e', 'f'
) as SQ

Select c1, c2, c3, c4 from #t t1
Where Exists (select * from #t t2 where t1.idt<>t2.idt and t1.c1=t2.c1
and t1.c2=t2.c2 and t1.c3=t2.c3 and t1.c4 <> t2.c4)
Group by c1, c2, c3, c4

drop table #t

I have added a Group By in order to filter the duplicate between 1 & 2 as
you had in your very first example.

--
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)