delete records

[rainbows]

i am trying to delete records from a table called stocklistapi which had about 6000 records . the other temporary table is called stocklist new

i have 600 records in that

how can i delete the records is stocklist api that does not match the records in stocklist new . using the field called stock number to delete them

thanks

steve

 

[CJ_London]

Left join the api table to the new table on stock number and delete those records in your api table where the new table stocknumber is null

 

[arnelgp]

DELETE [stocklistapi].* FROM [stocklistapi] LEFT JOIN [stocklist new]
ON [stocklistapi].[StockNumber] = [stocklist new].[StockNumber]
WHERE ([stocklist new].[StockNumber] Is Null)

 

[rainbows]

thanks , i am getting this error , but what i dont understand is created the database so i cannot see why this would come up??? as i can delete , tables, records etc

 

[CJ_London]

you need to show the sql you are actually using

 

[Mike Krailo]

This may or may not be your issue.

From Microsoft site:

If you build a delete query by using multiple tables and the query's Unique Records property is set to No, Access displays the error message Could not delete from the specified tables when you run the query.

To fix this problem, set the query's Unique Records property to Yes.

1. Open the delete query in Design view.
2. If the query property sheet is not open, press F4 to open it.
3. Click the query designer to show the query properties (rather than the field properties).
4. In the query property sheet, locate the Unique Records property, and set it to Yes

 

[rainbows]

DELETE [stocklistapic].* FROM [stocklistapic] LEFT JOIN [stocklistnew]
ON [stocklistapic].[StockNumber] = [stocklistnew].[StockNumber]
WHERE ([stocklistnew].[StockNumber] Is Null)

the query is showing the records but not deleting the

steve

 

[rainbows]

i am sorry i posted the last post befpre i read yours

yes it was set to NO i set it to yes and all is great

thanks everyone

steve