Access - Query - Compare Tables

Question

I have a report I run each month on the same data, from month to month I can have changes in any or all of the fifteen fields but the structure of the report will never change. I want to use Access to find all differences between the previous month and the current month report. Changes can also include new entries or the removal of entries. 

Some of my fields include: Organization ID (UID that will not change), President's Name, Home Phone Number, Mailing Address, etc. 

The field values that will not change from month to month are the Organization IDs but if that organization does not have a current president, it may not appear as a record on the current month's report so I also need the Query to show me if an organization has fallen out of the table. Similarly, I can have an Organization on the current month's report that wasn't present last month. 

I would prefer to show all changes with one or two Queries.

Answer 1

In the first part of the UNION ALL query the tables need to be LEFT OUTER JOINed not only on the organization ID column but on the other columns whose values might have changed.  Can you post the SQL of what you've done so far, along with a list of the columns whose values you need to test for having changed.

Answer 2

Karl,

The Organization IDs are unique but are not currently set as the Primary Key for either table. 

Each Table is named with the month it came from (2016-02-10 - President Information by Local). 

At this point I would settle for just seeing what is different but if the changed fields could be identified that would be even better. 

Delilah

Answer 3

Ken,

That sounds wonderful and helpful, but I am struggling as I have limited training with this terminology and this is the most complicated query I have had to build. 

When I build a left outer join on my organization ID I am able to show a record that was present in February but disappeared from March, but changes in the President's Name field are not showing. 

When I add additional outer joins on the fields with values that can change (such as President's Name) my Query now shows me all records rather than just the changes. 

I am certain the process you are describing will work, but I believe I will need more detail in how to implement it. 

Delilah

Answer 4

You will need to use a UNION ALL operation.  The two sets on which the operation acts will be:

1.  All rows for the current month which do not have a match in the set of all rows for the previous month.  This is achieved by joining two instances of the table on the OrganisationID column and on those columns whose values can change.  The join should be a LEFT OUTER JOIN and return rows where a NULL is returned in the OrganisationID column on the right side of the join.

2.  The second set is a limited converse of the first: all rows for the previous month which do not have a match in the set on the key OrganisationID column in the set of rows for the current month.  This is achieved by joining two instances of the table on the OrganisationID column.  The join should be a LEFT OUTER JOIN and return rows where a NULL is returned in OrganisationID column on the right side of the join.

The first set will return rows where any of the changeable columns' values have changed from the previous month, or where the organisation was not represented in the previous month.  The second set will return rows where an organisation represented in the previous month is not represented in the current month.  Both sets are then returned as a single result table by means of the UNION ALL operation

Answer 5

Do you have a primary key?       How do you distinguish last month's data from this month's data?

Do you want only to see which records are different OR to include identifying which fields are not the same?