Delete Queries Question

[News]

I am trying to run a delete query that deletes records in a table called
PhysInv which have the same ItemNo as records in a table called
ItemToDelete. Access says it cannot delete records from the specified
tables but I can see the records I want to delete in datasheet view. What's
the problem?

DELETE PhysInv.*, ItemToDelete.PhysInvItemNo
FROM PhysInv INNER JOIN ItemToDelete ON PhysInv.ItemNo =
ItemToDelete.PhysInvItemNo
WHERE (((ItemToDelete.PhysInvItemNo)=[PhysInv]![ItemNo]));

 

[Jason Lepack]

The join already performs the criteria that was in the WHERE clause.

DELETE PhysInv.*
FROM PhysInv INNER JOIN ItemToDelete
ON PhysInv.ItemNo = ItemToDelete.PhysInvItemNo

Cheers,
Jason Lepack

 

[DJJ]

Yes, I noticed that and I removed the where clause but I still get the same
error message.

DJJ

The join already performs the criteria that was in the WHERE clause.

DELETE PhysInv.*
FROM PhysInv INNER JOIN ItemToDelete
ON PhysInv.ItemNo = ItemToDelete.PhysInvItemNo

Cheers,
Jason Lepack

I am trying to run a delete query that deletes records in a table called
PhysInv which have the same ItemNo as records in a table called
ItemToDelete. Access says it cannot delete records from the specified
tables but I can see the records I want to delete in datasheet view.
What's
the problem?

DELETE PhysInv.*, ItemToDelete.PhysInvItemNo
FROM PhysInv INNER JOIN ItemToDelete ON PhysInv.ItemNo =
ItemToDelete.PhysInvItemNo
WHERE (((ItemToDelete.PhysInvItemNo)=[PhysInv]![ItemNo]));

 

[Jason Lepack]

You're trying to delete from two tables at once and that's not
possible. Use the query that I posted back to you to delete the
records.

Yes, I noticed that and I removed the where clause but I still get the same
error message.

DJJ

The join already performs the criteria that was in the WHERE clause.

DELETE PhysInv.*
FROM PhysInv INNER JOIN ItemToDelete
ON PhysInv.ItemNo = ItemToDelete.PhysInvItemNo

Cheers,
Jason Lepack

I am trying to run a delete query that deletes records in a table called
PhysInv which have the same ItemNo as records in a table called
ItemToDelete. Access says it cannot delete records from the specified
tables but I can see the records I want to delete in datasheet view.
What's
the problem?
DELETE PhysInv.*, ItemToDelete.PhysInvItemNo
FROM PhysInv INNER JOIN ItemToDelete ON PhysInv.ItemNo =
ItemToDelete.PhysInvItemNo
WHERE (((ItemToDelete.PhysInvItemNo)=[PhysInv]![ItemNo]));- Hide quoted text -- Show quoted text -

 

[John Spencer]

Try
DELETE PhysInv.*
FROM PhysInv INNER JOIN ItemToDelete
ON PhysInv.ItemNo = ItemToDelete.PhysInvItemNo

If that doesn't work try
DELETE PhysInv.*
FROM PhysInv
WHERE PhysInv.ItemNo IN
(SELECT ItemToDelete.PhysInvItemNo
FROM ItemToDelete)

Note that in both cases the DELETE clause only refers to one table. You can
only DELETE FROM one table in any query.

Classically, you don't need anything beyond
DELETE
FROM TableName

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[DJJ]

The sub query worked... Thanks

Microsoft doesn't have any info about subqueries working with delete
queries. I don't understand that...

DJJ

Try
DELETE PhysInv.*
FROM PhysInv INNER JOIN ItemToDelete
ON PhysInv.ItemNo = ItemToDelete.PhysInvItemNo

If that doesn't work try
DELETE PhysInv.*
FROM PhysInv
WHERE PhysInv.ItemNo IN
(SELECT ItemToDelete.PhysInvItemNo
FROM ItemToDelete)

Note that in both cases the DELETE clause only refers to one table. You
can only DELETE FROM one table in any query.

Classically, you don't need anything beyond
DELETE
FROM TableName

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I am trying to run a delete query that deletes records in a table called
PhysInv which have the same ItemNo as records in a table called
ItemToDelete. Access says it cannot delete records from the specified
tables but I can see the records I want to delete in datasheet view.
What's the problem?

DELETE PhysInv.*, ItemToDelete.PhysInvItemNo
FROM PhysInv INNER JOIN ItemToDelete ON PhysInv.ItemNo =
ItemToDelete.PhysInvItemNo
WHERE (((ItemToDelete.PhysInvItemNo)=[PhysInv]![ItemNo]));