CODEWITHSUNDEEP
sqlms-access
Smandoli
Smandoli

Reputation: 7019

Make a query updateable

Here is my non-updateable query in MS Access 2007:

SELECT [OnlineDirectory].BC_SPEC2 
FROM [OnlineDirectory] INNER JOIN Certifs_ABMS 
    ON [OnlineDirectory].ThisID = Certifs_ABMS.ThisID;

Both OnlineDirectory and Certifs_ABMS:

The query

These are both updateable:

SELECT * FROM [OnlineDirectory] 

SELECT * FROM [Certifs_ABMS]

I have reviewed Allen Browne's famous list of hazards and none of them apply. UPDATE: Not true. See accepted answer.

I got excited about adding DISTINCTROW as suggested here, but no success.

How can I make this query updateable?

Upvotes: 2

Views: 561

Answers (1)

Andre
Andre

Reputation: 27634

Both OnlineDirectory and Certifs_ABMS have indexes ("duplicates okay") on the field ThisID.

This is the problem. At least one side of the INNER JOIN needs a unique index (usually a primary key), or the relation is ambiguous.

(Allen Browne: - The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.)

E.g. if there are in both tables two records each with ThisID = 77. How should the records be matched?

If this is a n:m relation, you need a junction table between them.

Upvotes: 1

Related Questions