Once and for all...Can this be done

[rfuscjr via AccessMonster.com]

I have two tables linked by INV_NUM. Where there is an invoice in each table,
I want to delete that entry from table1...sounds simple enought. I drag in
each table in Design view and it will not work...could not from specified
tables.


DELETE ztemp1.*, *
FROM ztemp1 INNER JOIN ztemp2 ON ztemp1.INV_NUM = ztemp2.INV_NUM;

 

[Jerry Whittle]

DELETE *
FROM ztemp1
WHERE INV_NUM In
(select INV_NUM from ztemp2);

Make a backup of the database first!

 

[John Spencer]

Yes it can be done, but not in the way you have defined the query.

DELETE ztemp1.*
FROM ztemp1
WHERE Inv_Num IN (SELECT Inv_Num FROM ztemp2)

You MIGHT be able to use the DISTINCTROW with the join to get the delete to
work. NOTE that you cannot refer to any field in zTemp2 in the DELETE clause
- so that means you need to remove the ", *" since that would be ALL FIELDS in
ALL tables. Using DISTINCTROW is problematic.

DELETE DISTINCTROW ztemp1.*
FROM ztemp1 INNER JOIN ztemp2 ON ztemp1.INV_NUM = ztemp2.INV_NUM;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[rfuscjr via AccessMonster.com]

works but I really do not understand why 'our way' does not...thanks though!

Yes it can be done, but not in the way you have defined the query.

DELETE ztemp1.*
FROM ztemp1
WHERE Inv_Num IN (SELECT Inv_Num FROM ztemp2)

You MIGHT be able to use the DISTINCTROW with the join to get the delete to
work. NOTE that you cannot refer to any field in zTemp2 in the DELETE clause
- so that means you need to remove the ", *" since that would be ALL FIELDS in
ALL tables. Using DISTINCTROW is problematic.

DELETE DISTINCTROW ztemp1.*
FROM ztemp1 INNER JOIN ztemp2 ON ztemp1.INV_NUM = ztemp2.INV_NUM;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have two tables linked by INV_NUM. Where there is an invoice in each table,
I want to delete that entry from table1...sounds simple enought. I drag in

[quoted text clipped - 3 lines]

DELETE ztemp1.*, *
FROM ztemp1 INNER JOIN ztemp2 ON ztemp1.INV_NUM = ztemp2.INV_NUM;