469,935 Members | 1,585 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,935 developers. It's quick & easy.

Delete Query-Identifying the table with records for deletion

HI again:

I have a query which selects records on tblPOmods on the basis of information on two other
tables. I wish to delete the selected transactions, and am having a problem doing it. It
tells me that if "could not delete from the specified tables". I have tried to set the
"where" to "From" for the fields I selected, but the "From" wont take and bounces back to
"where". I have tried other strategies, and got a comment "Identity table to delete", but
the "where" still won't change to a "From".

Does anyone have a clue what I could be doing wrong? Again the manual is pretty much
silent on his kind of problem.

Regards

John Baker
Nov 12 '05 #1
4 1806
Drag the * from the main table into the query design grid. You can then
choose FROM under this column.

It can be hairy deleting from a query based on multiple tables. Have you
considered the impact of the join types, referential integrity of the
relation, cascading deletes, etc?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Baker" <Ba******@Verizon.net> wrote in message
news:q8********************************@4ax.com...

I have a query which selects records on tblPOmods on the basis of information on two other tables. I wish to delete the selected transactions, and am having a problem doing it. It tells me that if "could not delete from the specified tables". I have tried to set the "where" to "From" for the fields I selected, but the "From" wont take and bounces back to "where". I have tried other strategies, and got a comment "Identity table to delete", but the "where" still won't change to a "From".

Does anyone have a clue what I could be doing wrong? Again the manual is pretty much silent on his kind of problem.

Regards

John Baker

Nov 12 '05 #2
Thank You.

That does work, but I have changed the strategy based on your good point. There appear to
be some significant risks associated with this kind of delete strategy. I shall use it
sparingly, but in any case your thought was much appreciated. I just wonder why they
designed Approach with so many pitfalls for the unwarey or uninitiated.

Regards

John Baker

"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
Drag the * from the main table into the query design grid. You can then
choose FROM under this column.

It can be hairy deleting from a query based on multiple tables. Have you
considered the impact of the join types, referential integrity of the
relation, cascading deletes, etc?


Nov 12 '05 #3
Allen:

I had occasion to try that method, and had some trouble. I dragged the * into the query as
you suggested, and it still wont delete from the files. How many criteria do you need in
the query itself? All I have is two linked tables, and where there is a match I want to
delete the record from one of them from one of them. THis is a one to many link, and the
"many" side is the one I wish to delete.

This is one time I want to use a query!

Regards

John Baker
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
Drag the * from the main table into the query design grid. You can then
choose FROM under this column.

It can be hairy deleting from a query based on multiple tables. Have you
considered the impact of the join types, referential integrity of the
relation, cascading deletes, etc?


Nov 12 '05 #4
Deleting from the "many" side should be okay.
This example works with the Northwind sample database:

DELETE [Order Details].*, Orders.OrderID, [Order Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE (((Orders.OrderID)=10619) AND (([Order Details].ProductID)=22));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Baker" <Ba******@Verizon.net> wrote in message
news:cs********************************@4ax.com...
Allen:

I had occasion to try that method, and had some trouble. I dragged the * into the query as you suggested, and it still wont delete from the files. How many criteria do you need in the query itself? All I have is two linked tables, and where there is a match I want to delete the record from one of them from one of them. THis is a one to many link, and the "many" side is the one I wish to delete.

This is one time I want to use a query!

Regards

John Baker
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:
Drag the * from the main table into the query design grid. You can then
choose FROM under this column.

It can be hairy deleting from a query based on multiple tables. Have you
considered the impact of the join types, referential integrity of the
relation, cascading deletes, etc?

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Gustaf Liljegren | last post: by
11 posts views Thread by Sheldon Glickler | last post: by
4 posts views Thread by Ignoramus6539 | last post: by
3 posts views Thread by webandwe | last post: by
1 post views Thread by jmarcrum | last post: by
13 posts views Thread by mac | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.