Find Duplicates with Different Yes/No values

[Guest]

I have merged two tables into one. I have many duplicate values for the name
and address fields. These duplicates do not nessasarily have the same values
in the yes/no fields. In any one of these yes/no fields, if there are
duplicates, and both say "yes" I want them to stay that way. If both
duplicate fields say "no" I want them to stay that way. If one says "yes and
the other says "no" I want them both to say "yes".

Is it possible to make a find duplicates query that only shows duplicates
where both duplicates have different values in one yes/no collumn?

The plan is to run such a query for each yes/no field and paste "yes" values
over all the "no" values. I will then repeat that query on each yes/no
collumn.

Once all the duplicates are truly duplicates even in the "yes" and "no"
fields I will run a duplicate report and manually remove duplicates from
there.

Again, here is the question: Is it possible to make a find duplicates query
that only shows duplicates where both duplicates have different values in one
yes/no field?

Thank you very much.

 

[Crystal]

Hi Charles,

make a query and put your table up there twice

assign an ALIAS to each table to make them easier to keep
track of (Properties window -- press ENTER after you assign
the alias)
link the name and address fields

on the grid:

field --> fieldname
table --> table1

field --> fieldname
table --> table2
criteria --> <> table1.fieldname

and then whatever other fields you want to show

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Guest]

Crystal,
That didn't work for me. After assigning to different alias' to the two
fields this is what I did on the grid:

field --> Last
table --> table1

field --> Last
table --> table2
criteria --> <> [table1].[Last]

field --> Address
table --> table1

field --> Address
table --> table2
criteria --> <> [table1].[Address]

field --> YesNO
table --> table1

field --> YesNO
table --> table2
criteria --> <> [table1].[YesNO]

This returned back numerous results including non duplicate results.
I then tried this on the grid:

field --> Last
table --> table1

field --> Last
table --> table2
criteria --> = [table1].[Last]

field --> Address
table --> table1

field --> Address
table --> table2
criteria --> = [table1].[Address]

field --> YesNO
table --> table1

field --> YesNO
table --> table2
criteria --> <> [table1].[YesNO]

In this I changed the first two fields criteria to = instead of <>. That
worked partially I think because the values for table 2 were different from
those of table 1 and brought up a fraction of the duplicates I was dealing
with before. I figured this was because it was only showing duplicates with
different values which is what I wanted. It would not let me change the
values in the yes/no collumn which is what I wanted. So that didn't really
work either.

Any ideas?

Thanks,
Charles

 

[Crystal]

Charles,

you will not be able to use this query to CHANGE values...
only identify them...

put this field in one of your tables

Flag, yes/no, default value = no

change the query to show them into an UPDATE query
from the menu: Query, Update

in the UpdateTo cell under flag
TRUE

then, you can use a form/subform to show the records that
need to be changed, or use an UPDATE query to change them

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com