I have a delete query that works as select query, but will errors when
changed to a delete query. As part of the query I have the table I am
deleting the records from as well as a query limits to records to be deleted.
The main table contains invoice attributes with a multi-field primary key
(invoice number & revenue category). The query may list multiple invoice
numbers, but always unique. So the relationship is always many to one. I
ran into a similar situation with update queries, but once I converted the
limiting query into a Make Table Query and used the results of the new table
in the Update Query it worked. I tried converting my limit query to a Make
Table Query and use the results, but I received the same error. Below is the
SQL code for the query in case it helps, but I have to warn I have no
training in SQL, and I usually only use when given advise.

DELETE [tblRA - Invoices by Revenue Category].*
FROM [tblRA - Invoices by Revenue Category] RIGHT JOIN [tblRR - Arrangement
Adjustment Clear TEMP] ON [tblRA - Invoices by Revenue
Category].InvoiceNumber = [tblRR - Arrangement Adjustment Clear
TEMP].InvoiceNumber;

On Thu, 3 Jan 2008 17:21:01 -0800, mikeycan
<(E-Mail Removed)> wrote:

>I have a delete query that works as select query, but will errors when
>changed to a delete query.

Why the right join? That will - if the query works at all - delete all the
records in your table!

If you want to delete only those records in
[tblRA - Invoices by Revenue Category] which exist in
[tblRR - Arrangement Adjustment Clear TEMP] try

DELETE [tblRA - Invoices by Revenue Category].*
FROM [tblRA - Invoices by Revenue Category] INNER JOIN [tblRR - Arrangement
Adjustment Clear TEMP] ON [tblRA - Invoices by Revenue
Category].InvoiceNumber = [tblRR - Arrangement Adjustment Clear
TEMP].InvoiceNumber;

This will work only if there is a unique index on InvoiceNumber. An
alternative is to use an IN clause:

DELETE [tblRA - Invoices by Revenue Category].*
FROM [tblRA - Invoices by Revenue Category]
WHERE InvoiceNumber IN
(SELECT InvoiceNumber FROM
[tblRR - Arrangement Adjustment Clear TEMP] );


Back everything up first of course!!!

John W. Vinson [MVP]