A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Microsoft Access 2003 Update Query to a null value



 
 
Thread Tools Display Modes
  #1  
Old July 10th, 2008, 04:17 AM posted to microsoft.public.access.queries
Richard Flores
external usenet poster
 
Posts: 1
Default 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  
Old July 10th, 2008, 10:06 AM posted to microsoft.public.access.queries
Dennis
external usenet poster
 
Posts: 1,222
Default 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  
Old July 10th, 2008, 12:51 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:41 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.