Delete Query-Identifying the table with records for deletion

[John Baker]

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

[Allen Browne]

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

[John Baker]

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?

[John Baker]

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?

[Allen Browne]

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?