Updatable query in Access 2010

[haydenbl]

I have a very simple query I would like to update. Both tables have primary keys, I am able to update both tables. I used to be able to do this in previous version. This is just a very simple query. I get results, but cannot update.



SELECT Test.Med_no, Test.Quest1, Test.Quest2, Test.empDate, [Quest Second].MEME_BIRTH_DT
FROM Test LEFT JOIN [Quest Second] ON Test.Med_no = [Quest Second].MEDCD_NO;

Any help you can give me would be greatly appreciated.

[JoeM]

Have a look here to see if any of these conditions exist: http://allenbrowne.com/ser-61.html

[haydenbl]

I have been through that list and nothing applies. That is why I can't undertand why this very simply query will not let me update. Any other suggestions?

[JoeM]

Which field is it that you are trying to update?
Are you trying to update a field that might not exist for a particular record because of the LEFT JOIN?

[haydenbl]

I am trying to update Quest1 and Quest2 fields. Basically I have two tables, 1 tables has all the member information in it and the other table has the questionnaire information in it. I want to use the query for a form and have the med_no the same between the two tables and have them see the name and update the questionnaire information.

[JoeM]

If you are just linking to the second table to get a name to display, note that you could alternatively use the DLOOKUP function instead and not have to use the second table at all in the query.
If you would still like someone to find out why your query isn't working, you might need to upload a copy of your database for analysis.

[haydenbl]

QuestBack - Copy.accdb

Attached is a simple version of the database.

[JoeM]

I will take a look at it tonight when I am at a location where I can download it.

[haydenbl]

Thank you for taking a look at it.

[Dal Jeanis]

Include this field in the SELECT and see if that fixes it.

[Quest Second].MEDCD_NO

[haydenbl]

No, that does not help it.

[Dal Jeanis]

Generally, I find that this happens when Access "loses addressability" to one or both of the records.

What's the structure of each of the tables?

Is there a unique key for each record on Test? Make sure the key is included in the query.

Is there a unique key for each record on [Quest Second]? Make sure the key is included in the query.

[JoeM]

Are Test.Med_no and [Quest Second].MEDCD_NO unique fields within their respective tables?
If so, if you set each field to be Indexed with No Duplicates, you will then be able to update the fields.