Re: Delete Query - Delete matching records from 2 tables - Access
- From: Chris Stammers <ChrisStammers@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 22 Jan 2009 06:44:10 -0800
Many thanks for your help. Your last option worked fine.
Regards,
Chris
"John Spencer (MVP)" wrote:
YOu can only delete records from ONE table at a time..
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.
Without a backup you cannot restore the data if this does not work the way you
expect.
To delete records from Daily Check the query might look like
DELETE
FROM [Daily Check]
WHERE policy in (SELECT Policy From Retention)
To delete records from Retention the query would look like
DELETE
FROM Retention
WHERE policy in (SELECT Policy From [Daily Check])
You *MIGHT* be able to get this to work. Note that only one table is
mentioned in the DELETE clause.
DELETE DISTINCTROW Retention.*
FROM [Daily Check] INNER JOIN Retention
ON [Daily Check].policy = Retention.POLICY;
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Chris Stammers wrote:
Hello,
I have a query to delete records that match from the 2 tables contained.
When I view the result of the query before running, it looks like it is going
to work however when I actually come to run it, I get the error 'Could Not
Delete From Specified Tables'. After reading the Help pages, I unchecked the
box 'Open Databases using record-level locking' from the Advanced Options tab
and this hasn't helped. Is it the case that I will need to close Access for
the tables to update before the query will run properly? Here is the SQL:
DELETE Retention.*, [Daily Check].policy, [Daily Check].[extracted on] AS
Expr1
FROM [Daily Check] INNER JOIN Retention ON [Daily Check].policy =
Retention.POLICY;
Many thanks.
Chris
- References:
- Delete Query - Delete matching records from 2 tables - Access 2000
- From: Chris Stammers
- Re: Delete Query - Delete matching records from 2 tables - Access 2000
- From: John Spencer (MVP)
- Delete Query - Delete matching records from 2 tables - Access 2000
- Prev by Date: getting data from a linked sql
- Next by Date: Re: Delete Query - Delete matching records from 2 tables - Access
- Previous by thread: Re: Delete Query - Delete matching records from 2 tables - Access 2000
- Next by thread: Only numbers
- Index(es):
Relevant Pages
|
