1

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 goop and blab with a relationship connecting goop.id to 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;
  • 1
    Can you view the query in SQL and see the difference between the two selections. Maybe that will explain more. – nsb53 Feb 24 at 2:43
  • Thanks for the suggestion, nsb53. I updated the question. – Paul de Barros Mar 7 at 19:01
  • 1
    I think this link should answer your question. fmsinc.com/microsoftaccess/query/snytax/delete-query.html – nsb53 Mar 7 at 20:35
  • Scroll down to the section labeled “Use DISTINCTROW with Delete Queries for Non-Key Field Joins” – nsb53 Mar 7 at 20:43
  • Thanks for the link, nsb53. I think it gets me closer to the answer, but not all the way. My question was "Why does the Unique Records property have to be set to Yes is a delete query?" The link just seems to change the question to "Why does Access require the DISTINCTROW syntax in a delete query?" Or do you think that's a separate question, one about SQL rather than Access? – Paul de Barros Mar 8 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.