Ms-access – Unable to add records to query in Access 2007

join;ms accessquery

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!

Best Answer

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