AT A LOSS said:
I have 2 tables - They only have 1 field in common, customer number.
Other than that everything is different about these. One file is a
master file(180K records) one is much smaller only about 3K records.
The goal is to return a query that shows the customer numbers that
exist in both files, and to update either manually or automatically
another field in the master file based on the 'duplicate' customer
numbers.
I'm not that experienced with access....so ANY help would be
fabulous...
Create a new query in design view. Add both the master table and the
other table to it. If the tables aren't automatically joined on the
customer number field, join the tables by dragging the customer number
field from one table and dropping it on the matching field in the other
table. That should be the only join between the tables, and it should
be an inner join -- neither end of the join line should have an
arrowhead on the end.
Drag all the fields from the master table to the field grid. If you
switch the query to datasheet view, you'll see all the records in master
that have matches in the other table. However, if a record is matched
more than once, you'll see multiple copies of it in the results. If you
wanted to print these records, you'd probably just set the query to
return only unique values, but if you want to update the master table
from this query you can't do that because it will become nonupdatable.
You don't say what kind of update you want to do. Suppose you wanted to
set a yes/no field in the master table to True if the record was
matched. Assuming that the field is currently False for all records,
you could take the query you built, switch it back to Design view,
change the Query Type to Update Query, enter True in the "Update To:"
line under the field you want to update, and then run the query. That
would do it. If you have a more elaborate update in mind, you'll have
to give a more detailed expanation.
