Delete Query

N

neeraj

I have two queries which I have joined through a field. I am trying to delete
records from one query based on a criteria. I had accomplished this using
joined tables but due to permission issues, I am having to change tables to
queries. I dont know if one can delete records from a query. Can someone help?
 
S

Steve Schapel

Neeraj,

Yes, you can delete records via a query... as long as it is an
updateable query. If the nature of the delete query's component
queries, or the nature of the join between them, will mean the query is
non-updateable, you will need to take another approach. Please post
back with more datails if you need more explicit help.
 
N

neeraj

Here is the SQL statement:
DELETE DISTINCTROW [SQ1].*, [SQ1].Field2
FROM [SQ1] INNER JOIN [SQ2] ON [SQ1].Field1 = [SQ2].Field1
WHERE ((([SQ1].Field2)="Val1"));
There are 2 select quesries that I have joined: SQ1 and SQ2 on a field say
Field1. I am trying to delete records from SQ1 based on criteria of
Field2=val1
I have tried with the 'Unique Records' property set to both 'yes' and 'no'
and it doesn't work.
 
S

Steve Schapel

Neeraj,

Well, I'd need to see the details of SQ1 and SQ2 to really know what's
going on. But here's the acid text... make a normal Select query, like
this:
SELECT [SQ1].*
FROM [SQ1] INNER JOIN [SQ2] ON [SQ1].Field1 = [SQ2].Field1
WHERE ((([SQ1].Field2)="Val1"))
.... and see if you can add a new record or edit the data in the query
datasheet. If not, you are dealing with a non-updateable query. If
this is the case, you might try something like this...
DELETE [SQ1].*
FROM [SQ1]
WHERE (([SQ1].Field2="Val1") AND ([SQ1].Field1 IN(SELECT Field1 FROM
SQ2)))

--
Steve Schapel, Microsoft Access MVP

Here is the SQL statement:
DELETE DISTINCTROW [SQ1].*, [SQ1].Field2
FROM [SQ1] INNER JOIN [SQ2] ON [SQ1].Field1 = [SQ2].Field1
WHERE ((([SQ1].Field2)="Val1"));
There are 2 select quesries that I have joined: SQ1 and SQ2 on a field say
Field1. I am trying to delete records from SQ1 based on criteria of
Field2=val1
I have tried with the 'Unique Records' property set to both 'yes' and 'no'
and it doesn't work.




:

Neeraj,

Yes, you can delete records via a query... as long as it is an
updateable query. If the nature of the delete query's component
queries, or the nature of the join between them, will mean the query is
non-updateable, you will need to take another approach. Please post
back with more datails if you need more explicit help.
Click to expand...
Click to expand...
 
Top