Compare Differences of 2 Tables

[AFSSkier]

I have two tables [OLD] & [NEW]. I would like to query any changes from the
old to the new. In the example below, SKU 12346 changed to 60, 12347 is not
in the [NEW] table, 12348 is added in the [NEW] table.

SKU OLD VALUE
12345 85
12346 70
12347 85

SKU NEW VALUE
12345 85
12346 60
12348 90

SKU OLD NEW CHANGED
12345 85 85
12346 70 60 Y
12347 85 Y
12348 90 Y

 

[Jeff C]

You want to use a one-sided "join" between your two tables in a query. Bring
in both tables and join them on the SKU or the "common" field. in the query
bring in the field with the changed value from each table and preface with
"OLD:" and "NEW:" to differentiate them. Right click on the "join" and
select "join properties". In that widow select the table you want to see
compared to. trry it one way and then the other and you will see the
differences.

You can add fields to your query to filter your results the way you like, or
to run update queries based on this one to update or highlight changes in
your data.

 

[AFSSkier]

With this I would have to create two separate queries & two reports, I want
only one report.

You want to use a one-sided "join" between your two tables in a query. Bring
in both tables and join them on the SKU or the "common" field. in the query
bring in the field with the changed value from each table and preface with
"OLD:" and "NEW:" to differentiate them. Right click on the "join" and
select "join properties". In that widow select the table you want to see
compared to. trry it one way and then the other and you will see the
differences.

You can add fields to your query to filter your results the way you like, or
to run update queries based on this one to update or highlight changes in
your data.
--
Jeff C
Live Well .. Be Happy In All You Do

I have two tables [OLD] & [NEW]. I would like to query any changes from the
old to the new. In the example below, SKU 12346 changed to 60, 12347 is not
in the [NEW] table, 12348 is added in the [NEW] table.

SKU OLD VALUE
12345 85
12346 70
12347 85

SKU NEW VALUE
12345 85
12346 60
12348 90

SKU OLD NEW CHANGED
12345 85 85
12346 70 60 Y
12347 85 Y
12348 90 Y

 

[Jeff C]

No you won't, one query with a report based on it.
--
Jeff C
Live Well .. Be Happy In All You Do

With this I would have to create two separate queries & two reports, I want
only one report.

You want to use a one-sided "join" between your two tables in a query. Bring
in both tables and join them on the SKU or the "common" field. in the query
bring in the field with the changed value from each table and preface with
"OLD:" and "NEW:" to differentiate them. Right click on the "join" and
select "join properties". In that widow select the table you want to see
compared to. trry it one way and then the other and you will see the
differences.

You can add fields to your query to filter your results the way you like, or
to run update queries based on this one to update or highlight changes in
your data.
--
Jeff C
Live Well .. Be Happy In All You Do

I have two tables [OLD] & [NEW]. I would like to query any changes from the
old to the new. In the example below, SKU 12346 changed to 60, 12347 is not
in the [NEW] table, 12348 is added in the [NEW] table.

SKU OLD VALUE
12345 85
12346 70
12347 85

SKU NEW VALUE
12345 85
12346 60
12348 90

SKU OLD NEW CHANGED
12345 85 85
12346 70 60 Y
12347 85 Y
12348 90 Y

 

[AFSSkier]

If I understand your way correctly, I would have 3 queries. Create the 1st
query [OLD QUERY], joining the [OLD] ---> [NEW]. I get the difference of the
items that are only in the [OLD] table matching the [NEW] table.
Then create the 2nd query [NEW QUERY], joining the [NEW] ---> [OLD]
(creating 2nd query). The items added in the [NEW] are captured, but leaving
out the items that were in the [OLD] table & not in the [NEW].
Then create the 3rd query [NEW&OLD] for the report.

The only way I currently know how to do this (with less steps) is to APPEND
the [NEW] into [OLD]. Then do the [NEW&OLD] query joining the APPENDED [OLD]
table (now having the added new items) ---> [NEW] table, with a NEW, an OLD &
CHG field for the report.

No you won't, one query with a report based on it.
--
Jeff C
Live Well .. Be Happy In All You Do

With this I would have to create two separate queries & two reports, I want
only one report.

You want to use a one-sided "join" between your two tables in a query. Bring
in both tables and join them on the SKU or the "common" field. in the query
bring in the field with the changed value from each table and preface with
"OLD:" and "NEW:" to differentiate them. Right click on the "join" and
select "join properties". In that widow select the table you want to see
compared to. trry it one way and then the other and you will see the
differences.

You can add fields to your query to filter your results the way you like, or
to run update queries based on this one to update or highlight changes in
your data.
--
Jeff C
Live Well .. Be Happy In All You Do


:

I have two tables [OLD] & [NEW]. I would like to query any changes from the
old to the new. In the example below, SKU 12346 changed to 60, 12347 is not
in the [NEW] table, 12348 is added in the [NEW] table.

SKU OLD VALUE
12345 85
12346 70
12347 85

SKU NEW VALUE
12345 85
12346 60
12348 90

SKU OLD NEW CHANGED
12345 85 85
12346 70 60 Y
12347 85 Y
12348 90 Y