delete query problem

[pete johnson]

i have two tables, faxprotol and tblbadfaxes. i want to delete all records
in faxprotocol which have a record in tblbadfaxes. of course each table
has a field named faxnumber and i have set a relationship between them.
faxprotocol has many records and tblbadfaxes has unique records (by
faxnumber).

i dragged the asterisk from the "many" table to the grid and in the delete
row i typed [faxprotocol], so it reads "from [faxprotocol]". without the
quotes, of course.

i dragged the field faxnumber from the tblbadfaxes to the grid and typed
[tblbadfaxes]![faxnumber]=[faxprotocol]![faxumber] as the criteria.

i get an error of "the text you entered isn't an item in the list.
where did i go wrong?
pete


[tblbadfaxes]![faxnumber]=[faxprotocol]![faxumber]

 

[Tom van Stiphout]

On Fri, 5 Dec 2008 23:15:35 -0600, "pete johnson"

delete from table1
where table1!FaxNumber in (select FaxNumber from table2)

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

i have two tables, faxprotol and tblbadfaxes. i want to delete all records
in faxprotocol which have a record in tblbadfaxes. of course each table
has a field named faxnumber and i have set a relationship between them.
faxprotocol has many records and tblbadfaxes has unique records (by
faxnumber).

i dragged the asterisk from the "many" table to the grid and in the delete
row i typed [faxprotocol], so it reads "from [faxprotocol]". without the
quotes, of course.

i dragged the field faxnumber from the tblbadfaxes to the grid and typed
[tblbadfaxes]![faxnumber]=[faxprotocol]![faxumber] as the criteria.

i get an error of "the text you entered isn't an item in the list.
where did i go wrong?
pete


[tblbadfaxes]![faxnumber]=[faxprotocol]![faxumber]

You wouldn't use a criterion for this, but rather a Join:

DELETE faxprotocol.*
FROM faxprotocol INNER JOIN tblbadfaxes
ON faxprotocol.faxnumber = tblbadfaxes.faxnumber;

This is the SQL view; to do it in the grid, add both tables to the design
grid, join them by faxnumber, select *only* the * pseudofield from
faxprotocol, and change the query to a Delete query.

Back up your database first of course!!!!

 

[pete johnson]

many thanks to Tom.
also, i see that i mssspelled number as umber in one place.

pete

 

[pete johnson]

On Fri, 5 Dec 2008 23:15:35 -0600, "pete johnson"
snip>>

[tblbadfaxes]![faxnumber]=[faxprotocol]![faxumber]

You wouldn't use a criterion for this, but rather a Join:

DELETE faxprotocol.*
FROM faxprotocol INNER JOIN tblbadfaxes
ON faxprotocol.faxnumber = tblbadfaxes.faxnumber;

This is the SQL view; to do it in the grid, add both tables to the design
grid, join them by faxnumber, select *only* the * pseudofield from
faxprotocol, and change the query to a Delete query.

Back up your database first of course!!!!

Thank You very much.

here is the sql statement from the query:
DELETE FaxProtocol.*
FROM FaxProtocol INNER JOIN tblBadFaxes ON FaxProtocol.FaxNumber =
tblBadFaxes.FaxNumber;


i almost have it. i now have the exact sql statement as above. when I run
the query it says "you are about to change the data in your table, etc"
when I click yes, I really want to do this, it comes back with "Could not
delete from the specified tables".
pete

 

[John W. Vinson]

here is the sql statement from the query:
DELETE FaxProtocol.*
FROM FaxProtocol INNER JOIN tblBadFaxes ON FaxProtocol.FaxNumber =
tblBadFaxes.FaxNumber;


i almost have it. i now have the exact sql statement as above. when I run
the query it says "you are about to change the data in your table, etc"
when I click yes, I really want to do this, it comes back with "Could not
delete from the specified tables".
pete

Make sure that FaxNumber is the Primary Key of tblBadFaxes, or at least has a
unique Index specified.

Or, use the In() syntax suggested elsethread.

 

[pete johnson]

it worked ok when i made faxnumber the primary key of tblbadfaxes, and
followed your previous John's instructions i guess that i never set up
such a query previously.

thank you all very much.

Pete.