To delete all drivers who are not associated with any vehicles, execute
this
query:
DELETE FROM Drivers
WHERE Not Exists
(SELECT VRM FROM VehiclesDrivers
WHERE VehiclesDrivers.DriverID = Drivers.DriverID);
Try it in the form's AfterDelConfirm event (assuming Access tables in an
MDB, not an ADP.)
The WHERE clause contains a subquery.
If you want to know more about those, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
Thanks Allen for your reply.
There may be multiple drivers associated with each vehicle - so is it
possible to look up each DriverID in turn in the VehiclesDrivers table?
You
wrote in your post, "If none are found...." Could I use a DCount to
just
count any records in the VehiclesDrivers for this DriverID, and if it's
0
(or
would it need to be "1", since this VRM entry in the VehiclesDrivers
table
hasn't been deleted yet [has it?]) then delete the driver from Drivers
table?
I'm thinking this through step by step....
The drivers who are associated with the vehicle will all be listed in
the
VehiclesDrivers table and will have the vehicle table key (VRM) value
in
common. This VRM is the same as the VRM for the vehicle which is about
to
be
deleted.
For each of these drivers (ie records in the VehiclesDrivers table
where
VRM
= the same as the vehicle about to be deleted), I need to lookup the
VehiclesDrivers table to see whether the DriverID in this record has
any
other entries in the table (ie is associated with any other vehicles.)
(What is the code for this?)
If yes, don't delete from Drivers table. If no, execute DELETE query
to
remove the record (DriverID) from the Drivers table.
I've never written a DELETE query before.. what is the code for this?
Is this thinking correct? How do I put all of this into code/ SQL?
Can you advise Allen?
thanks
rich
:
Access cannot do this at the engine-level, as it has no triggers.
If the deletions are performed through a form, you could try using the
AfterDelConfirm event of the subform to DLookup() the DriverID from
the
parent form in the VehiclesDrivers table. If none are found, execute a
DELETE query statement to delete the record from that table (or from
the
parent form if you prefer.) I don't know if that will work, or if you
could
hit timing issues with the events.
Hi
I have 3 tables:
Vehicles (PK VRM)
Drivers (PK Driver ID)
VehiclesDrivers (PK VRM and DriverID)
(As you can see, the vehicles - driver relationship is a many to
many.)
I have a main form, Vehicles with a continuous subform, showing
drivers
relating to the driver. On this main form, I also have a delete
button to
remove the vehicle from the database. How can I set things up so
that
any
drivers who are not associated with any other vehicles in the
database
are
also deleted from the database? To clarify, when I delete the
vehicle
record, I need to check the VehiclesDrivers table. Any drivers
which
appear
in the Drivers table but not the DriversVehicles table should be
deleted from the Drivers table.