Combining 3 update queries for a single table

[Grahamiwa]

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

[June7]

Will have to build decision code such as If Then ElseIf to select the appropriate criteria string for the UPDATE statement. Start the If with the sixth combination you show and work up the list. Whichever condition is met will return the needed string.