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

re: Delete Query-Identifying the table with records for deletion

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" <Baker.JH@Verizon.net> wrote in message
news:q82hvvg2hcph2flh4tmic7kba5ft0pqghk@4ax.com...[color=blue]
>
> I have a query which selects records on tblPOmods on the basis of[/color]
information on two other[color=blue]
> tables. I wish to delete the selected transactions, and am having a[/color]
problem doing it. It[color=blue]
> tells me that if "could not delete from the specified tables". I have[/color]
tried to set the[color=blue]
> "where" to "From" for the fields I selected, but the "From" wont take and[/color]
bounces back to[color=blue]
> "where". I have tried other strategies, and got a comment "Identity table[/color]
to delete", but[color=blue]
> the "where" still won't change to a "From".
>
> Does anyone have a clue what I could be doing wrong? Again the manual is[/color]
pretty much[color=blue]
> silent on his kind of problem.
>
> Regards
>
> John Baker[/color]

re: Delete Query-Identifying the table with records for deletion

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" <AllenBrowne@SeeSig.Invalid> wrote:
[color=blue]
>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?[/color]

re: Delete Query-Identifying the table with records for deletion

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" <AllenBrowne@SeeSig.Invalid> wrote:
[color=blue]
>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?[/color]

re: Delete Query-Identifying the table with records for deletion

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" <Baker.JH@Verizon.net> wrote in message
news:csiqvvobdtlprvmqc50mkgfbhqvd3cjevg@4ax.com...[color=blue]
> Allen:
>
> I had occasion to try that method, and had some trouble. I dragged the *[/color]
into the query as[color=blue]
> you suggested, and it still wont delete from the files. How many criteria[/color]
do you need in[color=blue]
> the query itself? All I have is two linked tables, and where there is a[/color]
match I want to[color=blue]
> delete the record from one of them from one of them. THis is a one to many[/color]
link, and the[color=blue]
> "many" side is the one I wish to delete.
>
> This is one time I want to use a query!
>
> Regards
>
> John Baker
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote:
>[color=green]
> >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?[/color]
>[/color]