Microsoft Access - Review/Approval workflow Access database

I am relatively new to database so having some challenges with this design. However, I know this must be doable - I am trying to create an Access database to maintain assessment records and to have users review and approve the records. 

The general process for using has to follow the steps mentioned below:

1. Once a record with all the relevant details are Inserted in a single table through form.
2. Press a button to generate a review request or your new change.
3. Once the reviewers get the request prompt view email - they go into database to review it/Change with status reading "Draft".
4. Wait for feedback from the reviewers.
5. If they’re ready for it to go in:
   1. Submit your change to the repository.
   2. Click Close ‣ Submitted on the review request action bar.
6. If they’ve requested changes:
   1. Update the required fields and generate a new record saving the old one.
   2. Email the request to second reviewer with status "Draft".
   3. Jump back to step 4.

This workflow assumes a pre-commit review model and post-commit review.
Finally when it has been reviewed by all users - the final assessment record is sent for managers for Approval. The approval process also simulates the review process model.  

 

Depending on how sophisticated the review process must be "prior" to requested changes actually being committed to the master table, I would suggest using two tables.  One as the master and one that keeps the current state of the record along with all previous "states" of the record as some sort of "audit".

Using Employee and EmployeeAudit as your two tables, Their schemas would be the same.  However, the EmployeeAudit table would have additional columns for ModifiedByUserID, ModifiedDateTime, StatusID, and NextLevelStatusID indicating what level up the management tree is needed for approval.  Perhaps even a NextUserID oriented column instead indicating who needs to approve it next.  Of course, you'd always grab the most recent state of the record by getting the last entry for that Employee key in the EmployeeAudit table.

At some point in the process, you reach the point of final approval and the applicable columns are copied from EmployeeAudit back to Employee.

All of that said, I've seen guys use a gazillion different methodologies for comparing old records to new one's.  Some use XmlDocuments instead of columns in a database.  Others use an Audit table with a row that includes FieldName and FieldValue instead of creating an identical base schema as the master table.

The basic jist is that you want to store the current "state" of the record outside the master table and only overwrite the master table once you've reached the appropriate approval state.

thanks for the prompt reply Robbe. I will try to create the structure but it appears that there would be some front-end coding required to execute the workflow and overwrite the master table once the approval is finalized.

Really appreciate for providing the concept for design.

Actually, a ton of decent UI/front end coding to create a good user experience.

Thanks Robbe - do you know of any site that provides front-end codes and snippets to develop access front end. 
Thanks again for your wonderful suggestions.