Comparing tables

[RA]

I collect reports weekly from an automated system. I save the reports as
individual tables in Access. All of the fields are identically names. I
need a query that will look through the newest table and comparer it to the
prior table (I know that part may be manual) and show only the items that
changed, old value and new value (and the additions or deletions in a single
query would be nice)

 

[RA]

I found it:

SELECT [Cisco 7-29].EnterpriseName, [Cisco Very Old].EnterpriseName
FROM [Cisco 7-29] INNER JOIN [Cisco Very Old] ON [Cisco 7-29].RouteID =
[Cisco Very Old].RouteID
WHERE [Cisco 7-29].EnterpriseName<> [Cisco Very Old].EnterpriseName;

 

[KARL DEWEY]

The INNER join will only show those that match identically. Any additions
and deletions will be omitted. Use a LEFT join to show additions.

I think it would be better to use only one table and append each week, have
a field to identify the date. Then in your query have two instances of the
same table, the second will have a sifix '_1' added by Access. Then set
criteria on the first one for latest date and on second one for earlier
date(s).

--
KARL DEWEY
Build a little - Test a little

I found it:

SELECT [Cisco 7-29].EnterpriseName, [Cisco Very Old].EnterpriseName
FROM [Cisco 7-29] INNER JOIN [Cisco Very Old] ON [Cisco 7-29].RouteID =
[Cisco Very Old].RouteID
WHERE [Cisco 7-29].EnterpriseName<> [Cisco Very Old].EnterpriseName;

I collect reports weekly from an automated system. I save the reports as
individual tables in Access. All of the fields are identically names. I
need a query that will look through the newest table and comparer it to the
prior table (I know that part may be manual) and show only the items that
changed, old value and new value (and the additions or deletions in a single
query would be nice)