Update not working in access

[johnmerlino]

Hey all,

I'm using Access 2010. I have a query created in Query Design. It looks like this:

Field: defense
Table: contacts
Update To: temp_table.defense
Criteria: temp_table.last_name AND temp_table.first_name AND temp_table.middle_initial

The idea is to query the temp_table and update the defense field of the contacts table to the value in the defense field of the temp_table where the contacts.last_name = temp_table.last_name and the contacts_first_name = temp_table.first_name and contacts.middle_initial = to temp_table.middle_initial. Although I do have fields in both table that meet these three criteria, when I run the query, the contact's defense field does not get updated to the value of the temp_table defense field. Rather, I get a message that says 0 changed, meaning that nothing was updated. So I'm not sure what I'm doing wrong.

Thanks for response.

[kimmer]

It sounds like it is updating rows where Defense = "temp_table.last_name" AND "temp_table.first_name" AND "temp_table.middle_initial"

Since the Defense field doesn't contain that text for any records, no records are being updated.

Make copies of both tables and set the copies aside as a safety measure!

1. Create a new select query and add both of your tables.
2. Draw a join line between Lastname and Lastname.
3. Draw a join line between Firstname and Firstname.
4. Draw a join line between MiddleInitial and MiddleInitial.
5. Add the field Defense from the Contacts table to the grid.
6. View the query to see that is looks like it is displaying the correct number of records.
7. Return to Design View and change the query type to Update.
8. In the Update To row for the Defense field, enter: temp_table.defense
9. Run