Update query question

[AnnPhil]

I can’t figure out a way to make an update query to do the following:

I have 3 tables, Employee, Personnel Actions and Action Details; I have a form to enter the Personnel Action for an employee but need an update query after the data is entered into the Action Details table to update just the information that has changed into the Employee table.

My Action Details table has two fields, "FieldName" and "NewFieldValue". So for each Personnel Action there could be one or many fields that change. How do i create a update query that will look at each "FieldName" and its "NewFieldValue" and then find that field in the Employee table and update it with the new value?

I am attaching a word doc with the form and table structure if this helps.

Thanks in advance for any help

 

[RainLover]

I would like to suggest that this really isn't the proper method of doing things. In a Database one does not store the same information twice. It can and usually does lead to errors.

Perhaps a better way of doing things is to relook at your table design with the view of avoiding redundant data. (That is the same data twice)

If you wanted to post your table structure then I could have a look and give you some assistance in this matter.

 

[Rabbie]

Unless your data is normalised you will have problems in the future. See this link for help in Normalizing your data. If you have the same info stored more than once in a DB then you WILL have problems keeping the data consistent.

 

[AnnPhil]

Maybe I am not making myself clear. Did you look at my attached file? Here is my table structure, as far as I know it is normalized. I want to track employee personnel actions and keep the current information in the employee table. No repeated data here, that is why the Action details table only tracks the specific fields that are effected by the personnel action. So i am a bit confused my your emails


Employee table:
SSN
Status
Title
Position
Grade
Salary


Personnel Actions:
ActionID
SSN
EffectiveDate
Nature of Action


Action Details:
DetailsID
ActionID
FieldName
NewFieldValue

 

[Rabbie]

If your form is bound to your table then it should update automatically. If you are entering the same info twice then your data is not normalised.

 

[AnnPhil]

Right now i am entering the data into the Personnel Actions and Action Details but then would like to build an Update query to the employee table for just those fields that change. Over time the Action Details table will have a history of changes and the Employee table will only have one record per employee with just the current data. I need help building that action query.

Tried another method by building a temp table so the details were in that and then two append queries, one to update employee table and one to append to details table but couldn't get that work for both. Not sure what to do

 

[RainLover]

Right now I am entering the data into the Personnel Actions and Action Details but then would like to build an Update query to the employee table for just those fields that change.

The details held in the PersonalActions Table and the ActionDetails Table don't seem to have anything to do with the Employee Table.

So what is it that you want to update?? Sorry Totally Confused.

Suggestion: No spaces in any names. Also start all table names with tbl

So Employee Table would be named tblEmployee