If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Microsoft Access 2003 Update Query to a null value
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? |
#2
|
|||
|
|||
Microsoft Access 2003 Update Query to a null value
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. "Richard Flores" wrote: 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? |
#3
|
|||
|
|||
Microsoft Access 2003 Update Query to a null value
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 Richard Flores wrote: 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? |
Thread Tools | |
Display Modes | |
|
|