comparing records

  • Thread starter Thread starter Ezekiël
  • Start date Start date
E

Ezekiël

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
 
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
 
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>
 
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 said:
Jeff,

But how about the fields that are not duplicate but has the same id.
Click to expand...

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 said:
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>
Click to expand...
 
Ezekiël said:
Can you give me an example?
Click to expand...

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
Click to expand...

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>
 
Back
Top