Microsoft Access 2003 Update Query to a null value

[Richard Flores]

I have a database that tracks items and who they belong to for work. The
person and everything they have been given are entered into a form which
updates the appropriate tables. Every item has it's own table with the serial
numbers already listed and blank fields for a person's name and locker
number.The update query works just fine to fill this information in. Now, the
problem I run in to is when someone LEAVES. Their record is deleted from the
main table manually. However, I need to find a way to remove them from the
other tables automatically once the main record is gone. Any help?

 

[Dennis]

If you have relationships defined between the tables, then you can edit the
join type in the relationships window and tick the checkbox to enforce
referential integrity. You will then have the option to Cascade Updates and
Cascade deletes which means when you delete a record from a table, then the
records in other tables where they are joined/related will also be deleted.

 

[John Spencer]

You will need update queries that work to set the fields in the table to
blank. Or you could run occasional update queries to set the fields in
subordinate tables to blank where ther is no match in the main table.

For example you would need an update query that might look like the following
which would set fields in the MultiMeterTable (made up name) to blank (null)
when there was no matching record in the main table based on employee name and
locker number.

UPDATE MultiMeterTable LEFT JOIN MainTable
ON MultiMeterTable.EmployeeName = MainTable.EmployeeName
AND MultiMeterTable.LockerNumber = MainTable.LockerNumber
SET EmployeeName = Null
, LockerNumber = Null
WHERE MainTable.EmployeeName is Null

It sounds to me as if you could use a redesign of your table structure, but it
is difficult to say for sure based on the limited information in your posting.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County