Delete fields

[Guest]

Hi! I want to delete from TM_A MAIN where there are no records from the
subforms TM_bi, TM_other, and TM_elisa. The SQL view is:

DELETE DISTINCTROW [TM_A MAIN].bnum, [TM_bi.bnum] & [TM_elisa.bnum] &
[TM_other.bnum] AS Expr1
FROM (([TM_A MAIN] LEFT JOIN TM_bi ON [TM_A MAIN].bnum = TM_bi.bnum) LEFT
JOIN TM_other ON [TM_A MAIN].bnum = TM_other.bnum) LEFT JOIN TM_elisa ON
[TM_A MAIN].bnum = TM_elisa.bnum
WHERE ((([TM_bi.bnum] & [TM_elisa.bnum] & [TM_other.bnum]) Not Like "*"));

It gives me the error "Specifiy the table containing the records you want to
delete"... How do I delete from TM_A MAIN?

Thanks!

 

[Allen Browne]

The DELETE needs to specify the FROM table.
(In query design view, you do this by choosing the * from the table you wish
to delete from.)

Then use subqueries to choose only the records that have no match in any of
the other 3 tables. It will look like this:

DELETE FROM [TM_A MAIN]
WHERE NOT EXISTS
(SELECT bnum FROM TM_bi
WHERE TM_bi.bnum = [TM_A MAIN].bnum)
AND NOT EXISTS
(SELECT ...

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[Guest]

thanks a lot! it worked well... thanks also for the link on subqueries!

The DELETE needs to specify the FROM table.
(In query design view, you do this by choosing the * from the table you wish
to delete from.)

Then use subqueries to choose only the records that have no match in any of
the other 3 tables. It will look like this:

DELETE FROM [TM_A MAIN]
WHERE NOT EXISTS
(SELECT bnum FROM TM_bi
WHERE TM_bi.bnum = [TM_A MAIN].bnum)
AND NOT EXISTS
(SELECT ...

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi! I want to delete from TM_A MAIN where there are no records from the
subforms TM_bi, TM_other, and TM_elisa. The SQL view is:

DELETE DISTINCTROW [TM_A MAIN].bnum, [TM_bi.bnum] & [TM_elisa.bnum] &
[TM_other.bnum] AS Expr1
FROM (([TM_A MAIN] LEFT JOIN TM_bi ON [TM_A MAIN].bnum = TM_bi.bnum) LEFT
JOIN TM_other ON [TM_A MAIN].bnum = TM_other.bnum) LEFT JOIN TM_elisa ON
[TM_A MAIN].bnum = TM_elisa.bnum
WHERE ((([TM_bi.bnum] & [TM_elisa.bnum] & [TM_other.bnum]) Not Like "*"));

It gives me the error "Specifiy the table containing the records you want
to
delete"... How do I delete from TM_A MAIN?

Thanks!