Simple delete query

[CoffeeGuru]

My database now has a table of acceptable products
My Master table tblMaster has a load of records that need to be cleared out, as they are not in tblProducts. My new upload method should take care of this in the future.

I have run the "Find Unmatched Query Wizard" to get a list of all the rubbish product codes [UID] in [tblMaster]

So now I want to DELETE all records FROM [tblMaster] WHERE [tblMaster Without Matching tblProducts]![UID] = [tblMaster].[UID]

DELETE [tblMaster] *
FROM [tblMaster] 
INNER JOIN [001 tblMaster Without Matching tblProducts] 
ON [tblMaster].UID = [001 tblMaster Without Matching tblProducts].UID;

I am expecting 7367 records to be deleted
a. how do I write this as my attempt complains that I need to "Specify the table containing the records you want to delete."

b. will Access tell me how many records it is about to delete before doing it.....just a bit worried.

or would it be best to move the records to a temp delete table then just delete the table ... belt and braces affair

 

[pr2-eugin]

You missed the . after tblMaster and the asterisk It should have read..

DELETE tblMaster[COLOR=Red][B].[/B][/COLOR]*

This feels like a Dejavu moment for me.. I feel like I said the same thing yesterday.. Anyway.. That should fix your Error (a).

In regards to (b), Access will inform you how many records it is deleting, unless you are doing it from VBA and you are turning off your warnings or using CurrentDB.Execute method..

I always before deleting records, play with the backup.. So nothing wrong with that.. Specially if that is just going to be a one off thing..

 

[CoffeeGuru]

That should fix your Error

pr2-eugin - unfortunatly not but thanks for the heads up.

I now get the message
Could not delete from specified tables.

[SIZE=3][FONT=Arial]DELETE tblMaster.*[/FONT][/SIZE]
[SIZE=3][FONT=Arial]FROM [tblMaster] [/FONT][/SIZE]
[SIZE=3][FONT=Arial]INNER JOIN [001 tblMaster Without Matching tblProducts] [/FONT][/SIZE]
[SIZE=3][FONT=Arial]ON [tblMaster].UID = [001 tblMaster Without Matching tblProducts].UID;[/FONT][/SIZE]

 

[pr2-eugin]

So a Simple SELECT instead of DELETE returns the records you wish to delete?

 

[CoffeeGuru]

yes a select works perfectly

SELECT [tblmaster].*
FROM [tblMaster] 
INNER JOIN [001 tblMaster Without Matching tblProducts] 
ON [tblMaster].UID = [001 tblMaster Without Matching tblProducts].UID;

does it matter that 001 tblMaster Without Matching tblProducts is a query?

 

[pr2-eugin]

How about..

DELETE * FROM tblMaster 
WHERE tblMaster.UID IN 
(SELECT [001 tblMaster Without Matching tblProducts].UID FROM [001 tblMaster Without Matching tblProducts]);

 

[CoffeeGuru]

OK
That worked, but it just deleted without any warnings, The numbers tally so hopefully its alright.

 

[pr2-eugin]

Glad to help ! Good Luck !