Delete unlinkable records from table

[OTWarrior]

I have a two tables, one being the main table, and the second table linking to the main table by it's unique ID. I have come across a load of records in table 2 that contain this unique ID, but Table one doesn't have this number, since I cannot link them, I need to delete the records from table 2.

I can make a select query to find them, but whenever I try a delete query it just asks me "Please select the table where you want to delete the records from". And for some reason in the sql builder I cannot change "where" to "From"

Any help would be appreciated.

This is what I have so far:

DELETE tbl_DOM_Bookings.anBookingID
FROM tbl_DOM_Bookings RIGHT JOIN tbl_DOM_Diaries ON tbl_DOM_Bookings.anBookingID = tbl_DOM_Diaries.nDomBookingID
WHERE (((tbl_DOM_Bookings.anBookingID) Is Null));

[CreganTur]

Well... you can't delete single fields in a Delete Query- you have to delete the whole record, so the fact that your query is declaring only a single field may be the problem. Try:


DELETE tbl_DOM_Bookings.*
FROM tbl_DOM_Bookings RIGHT JOIN tbl_DOM_Diaries ON tbl_DOM_Bookings.anBookingID = tbl_DOM_Diaries.nDomBookingID
WHERE (((tbl_DOM_Bookings.anBookingID) Is Null));


HTH:thumb

[OTWarrior]

I was trying to delete the records rather than the field, so thank you for clearing that up (I haven't done a delete query like this before).

Unfortunately, I get a message saying "Unable to delete from Specified tables" when I try to run your code.

What could I have done wrong here?

[CreganTur]

Unfortunately, I get a message saying "Unable to delete from Specified tables" when I try to run your code.

What could I have done wrong here?

From doing some research it seems like the only way to delete records from a single table, when you require a joined table to filter the records correctly, you have to use a subquery. See this (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23230429.html).

Now... I've been looking at your code trying to figure out how to refine it into a Delete query with a subquery, and so far I haven't been able to do it- I think it's because I'm not familiar with your table structure. But... would you not get the same results from using:
DELETE tbl_DOM_Bookings.*
FROM tbl_DOM_Bookings
WHERE tbl_DOM_Bookings.anBookingID Is Null;

I ask because at first glance it looks like the join isn't needed. I could be very wrong here... just a thought.

[OTWarrior]

the anBookingID in the tbl_DOM_Bookings table is an autonumber, so wouldn't be blank on this table, only in the second table tbl_DOM_Diaries.

It's a bit of a bugger to understand how it was written (It was created by a colleague of mine who has moved on), but the easiest way to explain it is the bookings table is like a folder in windows, and the diaries are the files within that folder.

Normally the "folder" would be:

\BookingID\

and the files would be

\BookingID\Diary1
\BookingID\Diary2

If the booking ID is not present in the booking table then it would be like this

\BookingID1\

\BookingID3\

#####

\BookingID2\Diary1
\BookingID2\Diary2

Now this would mean there isn't a link between where the Diaries, so you cannot easily find which "folder" they should be in.

By using the select query to find which diaries have booking ID's that are no longer present in the booking table, I can remove the diaries which are making the report figures wrong.

Thanks for your help again Randy, you are very helpful as always :)