Delete Query Question

[Mike Wiseley]

I would like to create a delete query that consists of two tables linked
together. One table will be the target for the deletions while the other
table's equi-joins will tell the delete query which records in the target
table to delete. When you try to do this, an error message appears asking
you which table you want to delete records from (of the two tables shown in
the query). How do you tell Access what to do in this case???

[Allen Browne]

Try a subquery in the WHERE clause, e.g.:
DELETE FROM tblInvoice
WHERE tblInvoice.Invo iceID =
(SELECT InvoiceID FROM tblInvoiceDetai l WHERE
tblInvoiceDetai l.InvoiceDetail ID = 9);

The example assumes cascading deletes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Wiseley" <mi**********@g te.net> wrote in message
news:QV******** *********@nwrdd c02.gnilink.net ...

I would like to create a delete query that consists of two tables linked
together. One table will be the target for the deletions while the other
table's equi-joins will tell the delete query which records in the target
table to delete. When you try to do this, an error message appears asking
you which table you want to delete records from (of the two tables shown in the query). How do you tell Access what to do in this case???

[tom]

I believe Access also lets you specify which table in your FROM clause
to delete from, like so (The "I.*" part):

DELETE I.*
FROM tblInvoice as I
INNER JOIN tblInvoiceDetai l as D
On I.InvoiceID = D.InvoiceID
WHERE D.InvoiceDetail ID = 9;

-td

Try a subquery in the WHERE clause, e.g.:
DELETE FROM tblInvoice
WHERE tblInvoice.Invo iceID =
(SELECT InvoiceID FROM tblInvoiceDetai l WHERE
tblInvoiceDetai l.InvoiceDetail ID = 9);

[Mike Wiseley]

I tried the following variation of your theme:

Delete * From Table1 Where Table1.[Departure Point] like
(SELECT Distinct cTable1.[Departure Point] FROM cTable1 inner join Table1 on
cTable1.[Departure Point] = Table1.[Departure point]);

Which was an attempt to delete multiple records from Table1 based on a match
against cTable1.
Access says "at most, one record can be returned from this subquery".
"tom" <to*@nuws.com > wrote in message
news:c1******** *************** ***@posting.goo gle.com...

I believe Access also lets you specify which table in your FROM clause
to delete from, like so (The "I.*" part):

DELETE I.*
FROM tblInvoice as I
INNER JOIN tblInvoiceDetai l as D
On I.InvoiceID = D.InvoiceID
WHERE D.InvoiceDetail ID = 9;

-td

Try a subquery in the WHERE clause, e.g.:
DELETE FROM tblInvoice
WHERE tblInvoice.Invo iceID =
(SELECT InvoiceID FROM tblInvoiceDetai l WHERE
tblInvoiceDetai l.InvoiceDetail ID = 9);

[Allen Browne]

Mike, if you are still stuck on this, would it be possible to use a Yes/No
field to flag the records for deletion? That's easy to do with an Update
query, and then the delete query is just:
DELETE FROM MyTable WHERE (IsPicked = True);

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Wiseley" <mi**********@g te.net> wrote in message
news:Z4******** ********@nwrddc 02.gnilink.net. ..

I tried the following variation of your theme:

Delete * From Table1 Where Table1.[Departure Point] like
(SELECT Distinct cTable1.[Departure Point] FROM cTable1 inner join Table1 on cTable1.[Departure Point] = Table1.[Departure point]);

Which was an attempt to delete multiple records from Table1 based on a match against cTable1.
Access says "at most, one record can be returned from this subquery".
"tom" <to*@nuws.com > wrote in message
news:c1******** *************** ***@posting.goo gle.com...

I believe Access also lets you specify which table in your FROM clause
to delete from, like so (The "I.*" part):

DELETE I.*
FROM tblInvoice as I
INNER JOIN tblInvoiceDetai l as D
On I.InvoiceID = D.InvoiceID
WHERE D.InvoiceDetail ID = 9;

-td

Try a subquery in the WHERE clause, e.g.:
DELETE FROM tblInvoice
WHERE tblInvoice.Invo iceID =
(SELECT InvoiceID FROM tblInvoiceDetai l WHERE
tblInvoiceDetai l.InvoiceDetail ID = 9);

[Mike Wiseley]

That is in fact how I handle the situation at present. I was looking for an
alternate way of handling at the start of this thread.

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au...

Mike, if you are still stuck on this, would it be possible to use a Yes/No
field to flag the records for deletion? That's easy to do with an Update
query, and then the delete query is just:
DELETE FROM MyTable WHERE (IsPicked = True);

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Wiseley" <mi**********@g te.net> wrote in message
news:Z4******** ********@nwrddc 02.gnilink.net. ..

I tried the following variation of your theme:

Delete * From Table1 Where Table1.[Departure Point] like
(SELECT Distinct cTable1.[Departure Point] FROM cTable1 inner join

Table1 on

cTable1.[Departure Point] = Table1.[Departure point]);

Which was an attempt to delete multiple records from Table1 based on a

match

against cTable1.
Access says "at most, one record can be returned from this subquery".
"tom" <to*@nuws.com > wrote in message
news:c1******** *************** ***@posting.goo gle.com...

I believe Access also lets you specify which table in your FROM clause
to delete from, like so (The "I.*" part):

DELETE I.*
FROM tblInvoice as I
INNER JOIN tblInvoiceDetai l as D
On I.InvoiceID = D.InvoiceID
WHERE D.InvoiceDetail ID = 9;

-td

> Try a subquery in the WHERE clause, e.g.:
>
>
> DELETE FROM tblInvoice
> WHERE tblInvoice.Invo iceID =
> (SELECT InvoiceID FROM tblInvoiceDetai l WHERE
> tblInvoiceDetai l.InvoiceDetail ID = 9);
>