1

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!

1
  • 2
    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, 2012 at 19:14

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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