delete query using more than one table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nicknameClair - 08 May 2006 16:47 GMT

Hello, I'm trying to create a delete query using two tables. I want to
delete records from the first table and only use the second table for my
criteria. The following is example of the SQL that is created. It does not
work for some reason. Could someone please help? Thank you

DELETE FIRST_TABLE.*, FIRST__TABLE.Note
FROM FIRST_TABLE, [SECOND_TABLE]
WHERE (((FIRST_TABLE.Note)="Matched RRR,
RPP"+Format(Month!First_Date,"mmm")+" "+Format(Month!First_Date,"yyyy")));

Reply to this Message

John Spencer - 08 May 2006 17:05 GMT

Is there some kind of relationship between the two tables? Also, what is
Month!FirstDate?

Perhaps the following would work, but I would carefully test this on a copy
of the data. This was obviously a non-working query as it contained typing
errors. For example "First__Table" instead of "First_Table". Note the two
underscores.

DELETE DistinctRow FIRST_TABLE.Note
FROM FIRST_TABLE
WHERE FIRST_TABLE.Note IN
(SELECT "Matched RRR, RPP" & Format(First_Date,"mmm yyyy")
FROM [SECOND_TABLE])

> Hello, I'm trying to create a delete query using two tables. I want to
> delete records from the first table and only use the second table for my
[quoted text clipped - 6 lines]
> WHERE (((FIRST_TABLE.Note)="Matched RRR,
> RPP"+Format(Month!First_Date,"mmm")+" "+Format(Month!First_Date,"yyyy")));

Reply to this Message

nicknameClair - 09 May 2006 16:44 GMT

Thank you John, I got it to work.

> Is there some kind of relationship between the two tables? Also, what is
> Month!FirstDate?
[quoted text clipped - 20 lines]
> > WHERE (((FIRST_TABLE.Note)="Matched RRR,
> > RPP"+Format(Month!First_Date,"mmm")+" "+Format(Month!First_Date,"yyyy")));

Reply to this Message