Update a single record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
I am not sure I understand how that would work. Is the old wage being
archived some how in this new table?
 
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).
 
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?
 
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top