How to compare large tables for differences

[Cory]

I have two flat tables of contacts that are identical in design that, among
other things has a fax number field. How can I compare the two to see which
fax numbers have changed?

I've been learning Access by reading a very nice Step by Step book but
nothing I've learned so far has prepared me for directly solves this
problem. I'm guessing that it would be some sort of query but every idea
I've had so far has shortcomings.

Details: My problem is that I have an Exchange 2000 public folder of clients
that has been loosing fax numbers. No user so far has been able to put there
finger on an example as of yet since usually they fix them when they see
them so I decided I would monitor the whole folder and see for myself what
was changing. My strategy was to export the whole thing to database table
today and then later at some time export it again and run a query or
something to determine which had changed. My first problem is that I don't
have anything to use as a unique ID for each company. Most have the company
name but some do not. So I figured I'd make a composite field of names and
company name to create a unique ID. If I were in Excel I would use a lookup
function but I don't know how to do that in Access. And even if there is a
way it's possible one of you have a better idea.

Thanks!

 

[Wayne Morgan]

In the Queries window, click the New button and choose the Unmatched Wizard. You can't get
to this wizard by choosing "New query using wizard". The results will show you which fax
numbers in one table don't have a match in the other table.

 

[Cory]

Terrific! I knew there must be a simple way. Thanks!

 

[Cory]

I just tried your suggestion and I don't think this is the right solution. I
tried it on a control set and it returned no records. I read more closely
and now I believe that the wizard you suggest is designed to show me records
that are missing or 'not in the set of records' in another table. In my case
I'm looking for something that will show me a field that is populated in one
table and blank in the other. Or better yet a change in the field. But
generaly the records will be the same from table to table. As far as I can
tell this wizard will not do this for me unless I'm not implementing it
correctly. If this is the case maybe you could tell me what I'm getting
wrong.

Last night I got the idea to create a 1 to 1 relationship so that I could
compare them side by side in the same table and display only those with a
differnece. Unfortunately I don't have a field to link them togther since
some have names w/o company name and visa versa. So I used an action query
to create a composite text filed and that worked pretty well. However I
couldn't come up with a very elegant expression to compare them, maybe one
of you could make a suggestion.

Thanks

 

[Wayne Morgan]

I just tried it again and it appears to work. I used the table with the bad data in the
"table that contains records that I wanted in the query result" and I used the correct
table for the "table that contains related records". If the primary key is the same in
both tables, it should automatically be used to "link" the tables. Choose the field you
want to find the differences in for the "what piece of information is in both tables"
option, in this case it would be the fax number. Place the primary key field and the fax
field as the fields that you want to see in the query results. You will get the Primary
Key and the bad Fax numbers in the output.

To create the composite field name as a primary key, you can select both fields using
Shift+Click in the table design mode then click the key icon on the toolbar. You can also
create a query that will have a calculated field the combines both names into the one
field. Make a query for the 2nd table also, then use these queries as the source for the
unmatched query instead of the tables.

In the design grid, the consolidated name field would look something like:

Expr1:[PersonName] & [CompanyName]

Expr1 would be the name of this field in the query's output.

 

[Cory]

OK, I'll try it again. I must have missed an instruction in there someplace.

The second way you describe making a composite fiels is exactly what I did
but I'll try the other trick too.

Thanks!