Comparing records within a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to create a query that will list records with an 80% or
greater match to other records within the same table. For example:
1 | BACD | DGEA | YAK | AZ | UAS |
2 | AZ | AKA | DGEA | BACD | YAK | IOAA|
3 | YAK | UAS | BACD | AZ |
4 | JALL | NXSS | ELAK | AZ | UAS
5 | AZ | YAK | BACD | UAS |
Record 2 is an 80% match with record 1, record 3 is an 80% match with record
1, record 5 is a 100% match with record 3, etc.
Any help would be appreciated.
Thanks
Mark
 
Mark

It wasn't intuitively obvious to me what criteria you are using to determine
that there's a 80% match between record 1 and record 2.

How will Access know if you don't "explain" what constitutes a "match"?
 
Looks as though he's comparing fields, regardless of their position.

1 | BACD | DGEA | YAK | AZ | UAS |
2 | AZ | AKA | DGEA | BACD | YAK | IOAA|
3 | YAK | UAS | BACD | AZ |
4 | JALL | NXSS | ELAK | AZ | UAS
5 | AZ | YAK | BACD | UAS |

Records 1 and 2 both have fields with BACD, DGEA, YAK and AZ, but each have
1 field that doesn't match, therefore 4 out of 5 fields match (80%)

Records 3 and 5 each have fields with YAK, UAS, BACD and AZ, therefore 100%
match.

However, it certainly isn't an easy proposition!

Mark: I'm assuming this is an artificial example. If you can explain the
real case, perhaps we can suggest a better data model (what you're trying to
do suggests a data model that hasn't been properly normalized)
 
As I indicated earlier, there isn't an easy way of doing what you want,
largely because your data isn't normalized.

If you could get your data stored as Report Number, Field Number, Field
Value, like:

1 | 1 | BACD
1 | 2 | DGEA
1 | 3 | YAK
1 | 4 | AZ
1 | 5 | UAS
2 | 1 | AZ
2 | 2 | AKA
2 | 3 | DGEA
2 | 4 | BACD
2 | 5 | YAK
2 | 6 | IOAA

etc

it would be much easier, because you could sort by report number and field
value (ignoring the field number), and then try matching that way.
 
Thanks for the info......I'll give it a try
Mark

Douglas J. Steele said:
As I indicated earlier, there isn't an easy way of doing what you want,
largely because your data isn't normalized.

If you could get your data stored as Report Number, Field Number, Field
Value, like:

1 | 1 | BACD
1 | 2 | DGEA
1 | 3 | YAK
1 | 4 | AZ
1 | 5 | UAS
2 | 1 | AZ
2 | 2 | AKA
2 | 3 | DGEA
2 | 4 | BACD
2 | 5 | YAK
2 | 6 | IOAA

etc

it would be much easier, because you could sort by report number and field
value (ignoring the field number), and then try matching that way.
Click to expand...
 
Back
Top