Report for table data differences

[R Holtsbery]

Hi all,

I am a new Access user looking for a way to create a report which will
only list data differences between two tables.

Tables are before and after with several thousands lines of data. I am
trying to validate that all the data between the two tables are exactly
the same and I am only looking for a report to show the differences. A
quick example is:

Before Table After Table

Name Symbol Update Name Symbol Update

John 123 N John 123 N
Liz 567 N Liz 567 Y
Mark 089 Y Mark 089 Y

I would want to see a report that said something like:

Liz Before Update N After update Y

This was the only difference in the 2 tables.

 

[Marshall Barton]

I am a new Access user looking for a way to create a report which will
only list data differences between two tables.

Tables are before and after with several thousands lines of data. I am
trying to validate that all the data between the two tables are exactly
the same and I am only looking for a report to show the differences. A
quick example is:

Before Table After Table

Name Symbol Update Name Symbol Update

John 123 N John 123 N
Liz 567 N Liz 567 Y
Mark 089 Y Mark 089 Y

I would want to see a report that said something like:

Liz Before Update N After update Y

This was the only difference in the 2 tables.

The first step in creating any report is to first create a
query that pill the data together. The first step in
creating a query with multiple tables is to determine how
the records are related. Normally two tables a related by a
foreign key in one table that "links" to the primary key in
the other table.

The names of people are not normally useful as a primary key
(because of their lack of uniqueness), but since you did not
provide any other infornation, I will use the name in an
example:

SELECT B.namefield, B.symbol, B.updatefield,
A.symbol, A.updatefield
FROM beforetable As B INNER JOIN aftertable As A
ON B.namefield = A.namefield
WHERE B.symbol <> A.symbol
OR B.updatefield <> A.updatefield

Note that you need to avoid using reserved words for things
you create. In your example the field names are all likely
to be reserved words.

Now, you can use the query as the report's Record Source and
not have to worry about records that are the same in both
tables.

 

[Sweetetc]

Marsh This is what I am looking for i think. I just need to know how to
inner join the field in the tables. I do have a matching field that links it
is the id

So based upon the following
BEFORE AFTER
ID / Name / Symbol / Update ID / Name / Symbol /Update

1 / John /123 /N - 1 /John / 123 / N
2 / Liz /567 /N - 2 /Liz / 567 / Y
3 /Mark /089 /Y - 3 / Mark / 089 /Y

Slashes deonte column fields
My query is Joined at fund ID where including only those that are equal

ID / ID / NAME / NAME / Symbol /symbol /update /Update
Before / after/ before /after / before /after /before /after

How do I iget me Name before and name after linked to add the criteria <>

 

[Marshall Barton]

Marsh This is what I am looking for i think. I just need to know how to
inner join the field in the tables. I do have a matching field that links it
is the id

So based upon the following
BEFORE AFTER
ID / Name / Symbol / Update ID / Name / Symbol /Update

1 / John /123 /N - 1 /John / 123 / N
2 / Liz /567 /N - 2 /Liz / 567 / Y
3 /Mark /089 /Y - 3 / Mark / 089 /Y

Slashes deonte column fields
My query is Joined at fund ID where including only those that are equal

ID / ID / NAME / NAME / Symbol /symbol /update /Update
Before / after/ before /after / before /after /before /after

How do I iget me Name before and name after linked to add the criteria <>

You need to use a query like I posted before:

SELECT B.ID, B.Name. A.Name, B.symbol, A.symbol,
B.update, A.update
FROM beforetable As B INNER JOIN aftertable As A
ON B.ID = A.ID
WHERE B.symbol <> A.symbol
OR B.update <> A.update

The query will guarantee that at least one field is
different, but it is up to you to use code in the report to
deternine which of the fields are different.

 

[Sweetetc]

Marsh

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.

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?

 

[Marshall Barton]

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.