In Microsoft Access, if you create a delete query based on two or more related tables, you must open the Property Sheet and set the Unique Records property to "Yes". If you leave it set to "No", you get the error message "Could not delete from the specified tables".
Now, I know how to make the delete query work (by changing the Unique Records property). What I don't understand is why. Why does that property have to be set to "Yes"? What does that change about the delete query? What problem is caused by leaving it as "No"? If you could explain why setting that process is needed, I would greatly appreciate it.
Here is an example with the associated SQL code. Let's say I have a table called
goop with the primary key
id and a query called
blab with the foreign key
goopid. If I create a query that uses
blab with a relationship connecting
blab.goopid and include all fields from
goop, when I change it to a delete query, the SQL code shows up as:
DELETE goop.* FROM goop INNER JOIN blab ON goop.id = blab.goopid;
When I change the Unique Records property to Yes, the SQL code changes to:
DELETE DISTINCTROW goop.* FROM goop INNER JOIN blab ON goop.id = blab.goopid;