Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've found this excellent resource that covers why a query might be read-only. I have a query that is not read only, yet I'm unable to add new records to it.

There are three tables. Let's call them A, B, and C. Each has a unique primary key, but B and C also contain a foreign key that indexes into A. If building a query that does a inner join between A and B, the query is both editable and I can add new records to it. Similarly, a query that inner joins A and C is editable and supports adding new records. However, when doing inner join on A and B, then inner joining result with C, I can edit records, but not add new records. There is presumably something amiss with the "one-to-manyness" of this query, but I'm having a hard time seeing it, and also thinking through possible remedies.

With typical usage, tables A, B, and C should all have 1:1 relationships (though this is not enforced). Any ideas?

Here's the SQL syntax for the join

SELECT [Contact Information].[Record #],
        [Contact Information].[Last Name],
        [Contact Information].[First Name], 
        [Study Information].[Study Group],
        [Study Information].[Study Status], 
        [Clinical Information].[Contact Name], 
        [Study Information].[Contact Name2]
FROM ([Contact Information] 
        INNER JOIN [Study Information] 
            ON [Contact Information].[Record #] = [Study Information].[Contact Name2]) 
        INNER JOIN [Clinical Information] 
            ON [Contact Information].[Record #] = [Clinical Information].[Contact Name];

and here's a link to image that shows how Access is visually interpreting the query

Any ideas? Thanks!

share|improve this question
1  
Your query is the wrong way around to add records. If you delete one of the 'many' tables, [Clinical Information], for example, you will be able to add records. BTW spaces and special characters in field names will eventually drive you insane. –  Remou Jul 3 '12 at 19:14
add comment

1 Answer 1

Microsoft Access has a setting in the Query Properties window called Recordset Type. You need to set this to Dynaset (Inconsistent Updates) to allow your query to be updatable.

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.