Update a single record

[Dan]

I know the easiest way to update 1 record is to making a change directly in
the Form field, however I need to show this change in a report. I have an
employee tracking database which show name,address, wage, job, department
etc...When I change their wage, dept or job I need to produce a report which
will show current wage, job, or dept and also show the new wage, job or dept.
If I just update the form field, I cannot show all the information on the
report.

Should I create a temporary table to store the new wage, job or dept, print
the report, then use an append/update query to move the new data to the main
table. or is there a better way to achieve the results I need. I need the
report to submit to other departments to have the information updated in
other software programs.

thanks for your help.

 

[KARL DEWEY]

I would create a new wage record so as to retain the old wage rate
information and show when the new rate was effective. Of course you will
need one-to-many relationship between the employee and the wage table.

 

[Dan]

I am not sure I understand how that would work. Is the old wage being
archived some how in this new table?

 

[KARL DEWEY]

You would keep a running history of wage rates. Have EmpID, Rate, EffDate,
Archive (or name it OldRate or some such and allways use criteria to pull
current rate).

 

[Dan]

Thanks Karl

excuse my ignorance as I am still new to Access. Are you suggesting a
junction table? I currently have 3 tables:
tblemployee
employeeID
Name
wage
jobID
DeptID
etc...

tbljob
jobID
job
jobcode

tbldepartment
departmentID
departmentname
departbudgetcode

you are suggesting another table

tblwages
wageID
employeeID
newwage
newwageeffectdate
oldwage

and than run a query to archive the current wage than update the new wage?

 

[Dan]

So I have added 2 fields to my tbl_employee, newwage and newwageeffectdate.
My report is now able to do what I want, partially. Now I need to archive
the changes to another table with the information: employee, wage, new wage,
and new wage date. ONce this info is archived I need to update the wage with
the newwage, then clear the new wage field ( for the next time a wage
increase is done)

I am having issues understanding queries, how do I append just the current
record to the archive table, and is it possible to update field-1 with
field-2 from the same table? then delete the value from field-2(again with
just the current record)