Delete Query based on Two Tables

[Dave C]

I'm trying to write a query that will delete customer records from the
Customer table (tblCustomers) if they have no related records in the Bookings
table (tblBookings). When I run the query the message "Specify the table
containing the records you want to delete" is displayed. I set the query with
two fields - intCustomerID (tbl Customers) and intBookingID (tblBookings)
with the intBookingID criteria being "Is Null".

The SQL query is as follows:

DELETE tblCustomers.intCustomerID, tblBookings.intBookingID
FROM tblCustomers LEFT JOIN tblBookings ON tblCustomers.intCustomerID =
tblBookings.intCustomerID
WHERE (((tblBookings.intBookingID) Is Null));

I would appreciate any suggestions as to what I need to do to resolve this
problem.

Thanks in anticipation ..... Dave

 

[Jeff Boyce]

Dave

If you've gone to the effort to collect customer information, is there a
business reason for deleting that info? Are you "tidying up"? What do you
hope to accomplish by removing those records? What will you do if one of
the deleted customers comes back?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Whittle]

Run this:

Select tblCustomers.*
From tblCustomers
Where tblCustomers.intCustomerID NOT IN
(SELECT tblBookings.intCustomerID
FROM tblBookings) ;

If the above returns the proper records for deletion:

DELETE tblCustomers.*
From tblCustomers
Where tblCustomers.intCustomerID NOT IN
(SELECT tblBookings.intCustomerID
FROM tblBookings) ;

 

[Dave C]

Jeff,
Thanks for your interest and response. This is for college project where the
brief was for a hotel that has a high "one night stay" history where customer
records on the database 'need to be controlled' by the user. I've made the
number of years of booking inactivity controllable by the user, so they can
input the number of years in the system set-up before a record is finally
deleted.

Once again, thanks for your input.

Dave

 

[Dave C]

Jeff,
Thanks for your response. This worked a treat!

Dave