editing lookup table, automatically update mother table?

[Yogibear]

My tblEmp has a lookup to tblCareerType. The tbleCareerType has to be edited. For instance, a record in tblCareerType is "faculty research" must be changed to "faculty - research." (I have no comment on that )

I thought once the lookup table was edited, the main table would refresh. It doesn't......what am I missing aside from half my brain?

In true panic rush mode I did a find and replace in tblEmp for the changes that needed to be made. I then made the edits on the tblCareerType ensuring they match exactly.



This is not how I want to operate -- but is that just how it is?

Please. Help. THANK YOU!!

[ranman256]

It could be the EMP tbl is storing the text from the lookup table and NOT referencing the table.
if this is so, once your changes are made to the lookup tbl, you must run an update to EMP to the new values.

if the EMP table had linked to the lookup tbl, the changes would be immediate.

[CJ_London]

There are two solutions:

1. Add an autonumber primary key to your tblCareerType and modify the tables that reference that table to use a long family key and update with the newly generated autonumbers - that would the 'normal' structure.

2. Having said that your design is OK for limited number of career types, but will cause database bloat because you are storing multiple text values of perhaps 20 or 30 bytes against the 4 bytes of a long. But to fix it, you need to go into tblCareerType and set the careertype name as a primary key if not already done. Then go into relationships and set a one to many relationship between tblCareerTypes and tblEmp, plus any other tables that use the career type. Double clicking on the relationship line opens up a small form - tick 'enforce referential integrity' and 'Cascade Update Related Fields'. I do not recommend you tick the 'cascade delete' option - if you delete a type, all records in tblEmp which use that type will also be deleted, and I'm sure you wouldn't want that!