Deleted records don't stay deleted

[Evi]

I have a find unmatched query in my access 2000 database. When I delete some
of these records, the 'You Are About to Delete' warning appears. The records
go. The record count goes down.
But when I re-open the query, there they are again. However, if I delete the
records from the underlying Table they stay deleted.

No Halloween jokes please!

Evi

 

[Allen Browne]

Switch the Delete query to SQL View.

It will say something like:
DELETE ... FROM Table1 WHERE ...

Which table are you deleting from?

 

[Evi]

Hi Allen, thanks for replying. It's not a delete query, I need to see the
records and delete them by hand because some of them will be valid to keep.
The Sql is
SELECT TblEnglish.EngID, TblEnglish.English
FROM TblEnglish LEFT JOIN TblTranslation ON TblEnglish.EngID =
TblTranslation.EngID
WHERE (((TblTranslation.EngID) Is Null))
ORDER BY TblEnglish.English DESC;

I want to delete some of these unmatched records from TblEnglish because
they are invalid but others will be translated and re-entered into
TblTranslation at a later date so need to be kept. It's a simple English to
Esperanto dictionary database.

Evi

 

[Allen Browne]

You are not always clear in a multi-table query which table you are deleting
from.

Perhaps you could create a delete query along these lines:
http://allenbrowne.com/subquery-01.html#DeleteUnmatched

 

[Evi]

Nice query, Allen. I'll certainly be able to use it in other databases but
for this one, I can't delete all the records in the Find Unmatched as some
of them will be valid and just need to be added to the Translation table at
a later date. So I need to check them visually and then delete them 1 by 1.
I could delete them from TblEnglish but there are 90,000+ records in there
so that I'd have a fair bit of scrolling to do. There are only 2000 in my
FindUnmatched query so it would be much easier to delete them from there.
Perhaps I could add a Yes/No field to TblEnglish and then mark those records
which need to be deleted in the FindUnmatched query and then adapt your
query to include the Yes/No. Would that work?
Evi

 

[John Spencer]

Try changing the query to use a subquery to identify the records you
want to see. Then you should be able to delete records from the query.

SELECT TblEnglish.EngID, TblEnglish.English
FROM TblEnglish
WHERE EngID IN
(SELECT TblEnglish.EngID
FROM TblEnglish LEFT JOIN TblTranslation
ON TblEnglish.EngID =TblTranslation.EngID
WHERE TblTranslation.EngID Is Null)
ORDER BY TblEnglish.English DESC

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

 

[Allen Browne]

Yes: you could add a yes/no field to your table, and check the boxes in the
records that need to be deleted (assuming you have an updatable query, and
it's the records on the correct side of the join.)

 

[Evi]

Yay, that did it!!! Thank you, John and Allen.
Evi