Re: Report for table data differences

Sweetetc wrote:
You are awesome this worked great I do have a couple issue I had actually
just posted it to the Query section

I am comparing two large tables (3600 records) using <>. I have two issues.

1. One of the sets of column I can see visually match. ALL records in each
column have "N" yet the results are showing all records as not being equal.
I have checked the format which is text and the field size which is 255. Why
would this be showing false results? How can I get it to stop.

Is that because the mismatch is in a different column?


2. Another set of columns I am comparing is name. Where I pulled the data
from had different field lengths. So one column is John Smith School and the
field I am comparing truncated John Smith Sch How can I get these two columns
to appear to match?


This is an extremely difficult problem with no totally
correct solution. The best you can do is to try to come
close and provide a mechanism that you can use to improve
the matching of "similar" values.

I usually address this by creating a table of abbreviations
and their full text. This will allow you to create a query
that can substitute the full name for the abreviations.
When you come across a mismatched record because of an
abbreviation, add the abbreviation to the table so you don't
have to deal with it again. Unfortunately, humans can make
up more abbreviations than you can possible anticipate,
especially when you consider all possible typos.

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: False results using <>
    ... Please post your query in SQL. ... would this be showing false results? ... Another set of columns I am comparing is name. ... So one column is John Smith School and the ...
    (microsoft.public.access.queries)
  • Re: Report for table data differences
    ... I am comparing two large tables using. ... So one column is John Smith School and the ... inner join the field in the tables. ... My query is Joined at fund ID where including only those that are equal ...
    (microsoft.public.access.reports)
  • Re: Date range overlap
    ... >Comparing to a pair of dates is much easier, ... >and both date controls on the form have values, ... >> table to provide the query with the data then delete ... >> ranges using an unbound form control? ...
    (microsoft.public.access.formscoding)
  • RE: Dissapearing Query
    ... looks like you are comparing the Name Property of the table with the data in ... I don't think that Access is actually deleting the query. ... in case SQL was choosing to be picky about what I named my fields. ... So, after I open my form and those boxes pop up, I will try to play ...
    (microsoft.public.access.queries)
  • Re: Identifying Duplicate Records
    ... Whatever you use, you then can use the results to flag the dupes for review in one way or another, or bring up the dupe records to look at them. ... This is a tricky little query to understand, from a beginner to intermediate point of view. ... To find our duplicate records we build a query inside the IN function that will return only those values that occur more than once in the table. ... then comparing the results but the details of ...
    (microsoft.public.access.formscoding)