Delete query problem

[Tom]

It's been a while since I've played with delete queries, so this is
*probably* something simple that I'm just not seeing...


I have two tables and queries. One table has a lot of rows
(tblElecRateReviewDetail). Some of these rows I need to delete. One
of my two queries works just fine, as I only neeed to match the
contents of one field in the row, to select the rows I want to delete.

Here is the query...

DELETE
tblElecRateReviewDetail.[RD Type]

FROM
tblElecRateReviewDetail

WHERE
(((tblElecRateReviewDetail.[RD Type])="F" Or
(tblElecRateReviewDetail.[RD Type])="I" Or
(tblElecRateReviewDetail.[RD Type])="O" Or
(tblElecRateReviewDetail.[RD Type])="S"));


My second table (tblElecRateReview_Finals) contains the data I need to
select the remaining rows that I need to delete from table 1. This is
what I cannot get to work. I get an error:

"Specify the table containing the records you want to delete."

Here is the query...

DELETE
tblElecRateReviewDetail.[RD Type]

FROM
tblElecRateReviewDetail RIGHT JOIN tblElecRateReview_Finals ON
(tblElecRateReviewDetail.[Loc ID] = tblElecRateReview_Finals.[Loc
ID]) AND
(tblElecRateReviewDetail.[Read YY] = tblElecRateReview_Finals.[Read
YY]) AND
(tblElecRateReviewDetail.[Read MM] = tblElecRateReview_Finals.[Read
MM])

WHERE
(((tblElecRateReviewDetail.[RD Type])="R"));


I'm using Access 2000. I need to match the "Loc ID", "Read YY" and
"Read MM" fields for all the rows in table 1 where "RD Type"="R". (At
this point *all* the rows in table one contain "R" in "RD Type". So I
have to select the rows using the other three fields.)

If someone would be so kind as to give me a push in the right
direction, I would appreciate it.

Thank you in advance,

Tom

 

[Ken Snell [MVP]]

Let's start with a simple change:

Change this
DELETE
tblElecRateReviewDetail.[RD Type]

to this
DELETE
tblElecRateReviewDetail.*

In order to delete from a table, ACCESS Jet needs to have information about
the primary key for the table.

If this doesn't work for you, then we'll have to look at an interim query to
get a list of the records that are to be deleted, and then use that query
(perhaps in a table) to do the work.

 

[Tom]

Thank you sir. I *would* like to find out how to do it. After I sent
my request for help, I thought of a work around. Before I use my
delete query that works, I update the table changing those rows with
"R" and the matching year and month, to either "F" or "I", the values
I look for in the delete query that works. After I update the rows,
*then* I run the delete query. It may not be elegant, but it works.

BTW - I'm pretty sure that I had tried your suggestion, and it didn't
work. Maybe over the weekend I can find the time to try it and see
what happens.