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.​

 

[James Dudden]

What is or isn't happening?

 

[jdraw]

You've shown a Select query, not an Update query. What is the sql for the UPDATE query?

 

[haydenbl]

It tells me that the query is not updatable.

 

[haydenbl]

I want to be able to use this query with a form. 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.

 

[James Dudden]

Are you linking another query in to this query that has grouping switched on?

 

[jdraw]

We only know what you tell us about your situation/problem/opportunity. So please be as thorough as possible.

Show us the query that is giving the error message.

 

[haydenbl]

The query is at the very top. It is not linking to another query, just to the two tables.

 

[haydenbl]

Attached is a simple version of my database.

 

[Pat Hartman]

In order for the query to be updateable one side of the join must be to a primary key. You are joining data field to data field.

 

[haydenbl]

I have tried making one or both a primary key and that does not work either. I know this should not be that difficult. Thanks for all the ideas. Any more would be greatly appriecated.

 

[James Dudden]

From the original database you uploaded you need to add a Member_ID field to the Test table and link them up with a relationship. Then in the new field add the missing values (i.e. the ID values from the Quest Second table. Then in the query link on the new Member_ID relationship. This should work.

 

[Pat Hartman]

I did exactly what I told you to do. I don't know if it was correct. Only you know what your primary keys should be. You can't just add random autonumber fields and not use them properly. If you have an autonumber it should be the PK so I removed it from the table I modified.

If you want the autonumber to be the PK, then it must be added as a FK to the other table. You don't add a data field. A foreign key in one table is ALWAYS the primary key (or in some very special cases a unique index) in the table it points to.