We have a database that is queried through Access 97 front end. There is a table that stores Taskbook Assessment data. The table has 21 fields. I would like to be able to update 6 of then with an Update query based on the data that is in them.
From time to time we have to enter data on behalf of other people. Normally this is done through the normal Web access page and complete the assessment as if we were the assessor and then we will need to go through the admin menu and edit each record line by line. Time consuming.
The fields involved in the query are Mentor 1 ID, Mentor 1 Date, Mentor 2 ID, Mentor 2 Date, Assessor ID, Assessor Date, Worker ID.
I can easily do a query that will do Mentor 1 ID and Mentor 1 Date then a separate query for Mentor 2 and a 3rd query for Assessor.
I can’t work out how to do an update query which will do the required data for all 3 at once.
The problem is that there are 6 combinations which could happen and I only want the data updated for that specific combination.
The combinations would be:
Mentor 1
Mentor 2
Assessor
Mentor 1 & Mentor 2
Mentor 2 & Assessor
Mentor 1, Mentor 2 & Assessor
If someone was to “Sign” as Mentor 1 and Mentor 2 then that is the only related data I want changed