Comparing data between 2 similar tables.

[PaulClaudia]

Hi All,

I have a Access 2002 DB with 2 tables containing 16 columns each. The second
table is a newer version of the first with changes to some of the data. Would
anyone know how to create a report that tells which records have had data
changed and what the new data is?

any help would be very much appreciated

 

[John W. Vinson]

Hi All,

I have a Access 2002 DB with 2 tables containing 16 columns each. The second
table is a newer version of the first with changes to some of the data. Would
anyone know how to create a report that tells which records have had data
changed and what the new data is?

any help would be very much appreciated

Create a Query joining the two tables on their Primary Key (they *do* have a
primary key... I hope!?)

Put a criterion on each other field of

<> [othertable].[samefield]

using your table and fieldnames. Put the criteria on DIFFERENT lines of the
query grid so it will use OR logic, and show you the difference in any of the
fields.

 

[PaulClaudia]

Hi John, Firstly many thanks for replying,

sorry to appear a bit thick but I'm new to Access.

I've created a query that has joined the tables into one table by primary
key. The query result now has 32 columns. I went into design view and added
the line in your reply replacing the [othertable] and [samefield] with the
actual data

e.g.

Field Dispatch Date
Table NEW TABLE
Sort
Show [Y]
Criteria <>[OLD TABLE].[Dispatch Date]
Or


I have gone along all 32 columns populating the Criteria cells and rerun the
query. this gives no results ( and I know some of the data is different) any
idea what I've done wrong?

thnaks again
PC


e.g

--
PC

Hi All,

I have a Access 2002 DB with 2 tables containing 16 columns each. The second
table is a newer version of the first with changes to some of the data. Would
anyone know how to create a report that tells which records have had data
changed and what the new data is?

any help would be very much appreciated

Create a Query joining the two tables on their Primary Key (they *do* have a
primary key... I hope!?)

Put a criterion on each other field of

<> [othertable].[samefield]

using your table and fieldnames. Put the criteria on DIFFERENT lines of the
query grid so it will use OR logic, and show you the difference in any of the
fields.

 

[Douglas J. Steele]

Did you remember to put each criteria on a different line of the grid?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi John, Firstly many thanks for replying,

sorry to appear a bit thick but I'm new to Access.

I've created a query that has joined the tables into one table by primary
key. The query result now has 32 columns. I went into design view and
added
the line in your reply replacing the [othertable] and [samefield] with the
actual data

e.g.

Field Dispatch Date
Table NEW TABLE
Sort
Show [Y]
Criteria <>[OLD TABLE].[Dispatch Date]
Or


I have gone along all 32 columns populating the Criteria cells and rerun
the
query. this gives no results ( and I know some of the data is different)
any
idea what I've done wrong?

thnaks again
PC


e.g

--
PC

Hi All,

I have a Access 2002 DB with 2 tables containing 16 columns each. The
second
table is a newer version of the first with changes to some of the data.
Would
anyone know how to create a report that tells which records have had
data
changed and what the new data is?

any help would be very much appreciated

Create a Query joining the two tables on their Primary Key (they *do*
have a
primary key... I hope!?)

Put a criterion on each other field of

<> [othertable].[samefield]

using your table and fieldnames. Put the criteria on DIFFERENT lines of
the
query grid so it will use OR logic, and show you the difference in any of
the
fields.

 

[PaulClaudia]

Hi Doug,

I've just tried that and I have 9 criteria lines that I can populate and the
query would need to be 16 lines. I have tried just selecting a few of the
columns to test it but I need it to compare all 16 cells in the record.


Also if a cell in the old table is blank and then in the new table it has
data it doen't pick any change up.

sorry to be a pain ( but I very much appreciate you help)

--
PC

Did you remember to put each criteria on a different line of the grid?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi John, Firstly many thanks for replying,

sorry to appear a bit thick but I'm new to Access.

I've created a query that has joined the tables into one table by primary
key. The query result now has 32 columns. I went into design view and
added
the line in your reply replacing the [othertable] and [samefield] with the
actual data

e.g.

Field Dispatch Date
Table NEW TABLE
Sort
Show [Y]
Criteria <>[OLD TABLE].[Dispatch Date]
Or


I have gone along all 32 columns populating the Criteria cells and rerun
the
query. this gives no results ( and I know some of the data is different)
any
idea what I've done wrong?

thnaks again
PC


e.g

--
PC

On Tue, 12 Feb 2008 09:14:01 -0800, PaulClaudia

Hi All,

I have a Access 2002 DB with 2 tables containing 16 columns each. The
second
table is a newer version of the first with changes to some of the data.
Would
anyone know how to create a report that tells which records have had
data
changed and what the new data is?

any help would be very much appreciated

Create a Query joining the two tables on their Primary Key (they *do*
have a
primary key... I hope!?)

Put a criterion on each other field of

<> [othertable].[samefield]

using your table and fieldnames. Put the criteria on DIFFERENT lines of
the
query grid so it will use OR logic, and show you the difference in any of
the
fields.

 

[Douglas J. Steele]

Hi Doug,

I've just tried that and I have 9 criteria lines that I can populate and
the
query would need to be 16 lines. I have tried just selecting a few of the
columns to test it but I need it to compare all 16 cells in the record.

You can add additional criteria lines (use the Insert menu)

Also if a cell in the old table is blank and then in the new table it has
data it doen't pick any change up.

I was wondering about that. That's a bit more of a pain to correct.

Since they're causing a problem, I'm assuming they're actually Null, not
blank.

Pick a value that will never occur naturally in the field (something like
XXYYXX, or anything you like). For each entry in the Field row of the grid,
change it from

Field Dispatch Date

to

Field Nz([Dispatch Date], "XXYYXX")

Also, change the criteria from

Criteria <>[OLD TABLE].[Dispatch Date]

to

Criteria <>Nz([OLD TABLE].[Dispatch Date], "XXYYXX")

Now, unfortunately, this is going to have the effect of making the field
names in the result Expr0001, Expr0002 and so on. If that's a problem, you
can change it from

Field Nz([Dispatch Date], "XXYYXX")

to

Field DispDate: Nz([Dispatch Date], "XXYYXX")

(or whatever makes sense to you to identify the fields. Unfortunately, you
cannot use the same name as an alias: you cannot put

Field [Dispatch Date]: Nz([Dispatch Date], "XXYYXX")