Hi everyone,

Can someone tell me how to compare records where one field may have double
values, but in the other fields not.

Greetings,

Ezekiel

Ezekiël

Could you provide an example?

Jeff Boyce
<Access MVP>

Jeff,

For example i have in field1 clientnrs, field2 kwantities, field 3 amounts.
field1 must be unique.

I've got duplicate records but i want to compare which one i can drop.

e.g. record 1
field1 = 1
field2 = 10
field3 = 100

record 2
field1 = 1
field2 = 10
field3 = 101

"Jeff Boyce" <(E-Mail Removed)-DISCARD_HYPHEN_TO_END> wrote in message
news:%(E-Mail Removed)...
> Ezekiël
>
> Could you provide an example?
>
> Jeff Boyce
> <Access MVP>
>

Are you saying that you consider the two records you gave as an example to
be equal? I see that the first two fields are equal, but not the entire
records...

Jeff Boyce
<Access MVP>

Yes, that is what i mean

One approach might be to build a query that only returns the fields for
which you wish to find duplicates (i.e., the set of fields that you think
may match). Then, using the first query as your source, build a second
query using the Query Wizard that finds duplicates.

Good luck

Jeff Boyce
<Access MVP>

Jeff,

But how about the fields that are not duplicate but has the same id.
"Jeff Boyce" <(E-Mail Removed)-DISCARD_HYPHEN_TO_END> wrote in message
news:%(E-Mail Removed)...
> One approach might be to build a query that only returns the fields for
> which you wish to find duplicates (i.e., the set of fields that you think
> may match). Then, using the first query as your source, build a second
> query using the Query Wizard that finds duplicates.
>
> Good luck
>
> Jeff Boyce
> <Access MVP>
>

"Ezekiël" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Jeff,
>
> But how about the fields that are not duplicate but has the same id.

Perhaps I'm still not understanding. I thought you were looking for the
records that DO match... Wouldn't that mean they have the same ID?

My suggestion was to leave out the non-duplicate fields in the first query,
so as to find those that DO match by ID. Then, in the second query, based
on the first, join back to the table to inspect the non-duplicate values.

Good luck

Jeff Boyce
<Access MVP>

Can you give me an example?
"Jeff Boyce" <(E-Mail Removed)-DISCARD_HYPHEN_TO_END> wrote in message
news:(E-Mail Removed)...
>
> "Ezekiël" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Jeff,
> >
> > But how about the fields that are not duplicate but has the same id.
>
> Perhaps I'm still not understanding. I thought you were looking for the
> records that DO match... Wouldn't that mean they have the same ID?
>
> My suggestion was to leave out the non-duplicate fields in the first
query,
> so as to find those that DO match by ID. Then, in the second query, based
> on the first, join back to the table to inspect the non-duplicate values.
>
> Good luck
>
> Jeff Boyce
> <Access MVP>
>

"Ezekiël" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Can you give me an example?

Here's the example you gave...

> I've got duplicate records but i want to compare which one i can drop.
>
> e.g. record 1
> field1 = 1
> field2 = 10
> field3 = 100
>
> record 2
> field1 = 1
> field2 = 10
> field3 = 101

In this thread, I believe you've described these two records as "matching".
If the only field you are considering as "matching" is field1, then use the
Query Wizard, select "find duplicate...", select the table, select only
field1, and run it. Given the two records you gave, the query should return
a row saying that field1 is duplicated.

If you are considering field2 as also part of what "matches", then also
include that field in the above query.

When this "find duplicates..." query is working, you will know which records
have more than one "match" on the field(s) you have selected.

Of the two records you gave, which one do you wish to eliminate? How did
you decide? How do you propose to tell Access how to decide?

If this doesn't work for you, respond back. There are other approaches we
can try.

Good luck

Jeff Boyce
<Access MVP>