Trouble with Delete Query

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

Hi,

I have two tables. One is called Attendees and the other
is called MailingList. They both have a common field
called ID. There is a check box on a form called
inactive that corresponds to the Attendees table. What I
want to do is to have someone be able to check the
inactive check box on a form to inactivate someone in the
attendees tables. I would then like to run a delete
query that would remove them from my mailing list table.
So basically if Inactive in Attendees is Yes delete this
record from the Mailing list table based on ID. Can
anyone help?

Thanks,

Chuck
 
To do a deletion, you have to use a subquery instead of joined tables. Try
this (note: test on a copy of your data!!!!):

DELETE [MailingList].*
FROM [MailingList]
WHERE [MailingList].[ID] IN
(SELECT Attendees .[ID]
FROM Attendees
WHERE Attendees.Inactive <> False);
 
I see I have an inadvertent space in the posted query. Try this:

DELETE [MailingList].*
FROM [MailingList]
WHERE [MailingList].[ID] IN
(SELECT Attendees.[ID]
FROM Attendees
WHERE Attendees.Inactive <> False);

--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
To do a deletion, you have to use a subquery instead of joined tables. Try
this (note: test on a copy of your data!!!!):

DELETE [MailingList].*
FROM [MailingList]
WHERE [MailingList].[ID] IN
(SELECT Attendees .[ID]
FROM Attendees
WHERE Attendees.Inactive <> False);

--

Ken Snell
<MS ACCESS MVP>

ChuckW said:
Hi,

I have two tables. One is called Attendees and the other
is called MailingList. They both have a common field
called ID. There is a check box on a form called
inactive that corresponds to the Attendees table. What I
want to do is to have someone be able to check the
inactive check box on a form to inactivate someone in the
attendees tables. I would then like to run a delete
query that would remove them from my mailing list table.
So basically if Inactive in Attendees is Yes delete this
record from the Mailing list table based on ID. Can
anyone help?

Thanks,

Chuck
Click to expand...
Click to expand...
 
Back
Top