Access 97 Multiple update query

[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.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
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.
<o> </o>
The fields are Mentor 1 ID, Mentor 1 Date, Mentor 2 ID, Mentor 2 Date, Assessor ID, Assessor Date.
<o> </o>
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 3<SUP>rd</SUP> query for Assessor.
<o> </o>
I can’t work out how to do an update query which will do the required data for all 3 at once.
<o> </o>
The problem is that there are 6 combinations which could happen and I only want the data updated for that specific combination.
<o> </o>
The combinations would be:
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Mentor</st1lace></st1:City> 1
<st1:City w:st="on"><st1lace w:st="on">Mentor</st1lace></st1:City> 2
Assessor
<st1:City w:st="on">Mentor</st1:City> 1 & <st1:City w:st="on"><st1lace w:st="on">Mentor</st1lace></st1:City> 2
<st1lace w:st="on"><st1:City w:st="on">Mentor</st1:City></st1lace> 2 & Assessor
<st1:City w:st="on">Mentor</st1:City> 1, <st1:City w:st="on"><st1lace w:st="on">Mentor</st1lace></st1:City> 2 & Assessor
<o> </o>
If someone was to “Sign” as <st1:City w:st="on">Mentor</st1:City> 1 and <st1:City w:st="on"><st1lace w:st="on">Mentor</st1lace></st1:City> 2 then that is the only related data I want changed
I have tried to join 3 separate Update queries but obviously that does not work

 

[Phildaburn]

There's not a valid combination of Mentor 1 & Assessor?

What other fields of data allow you to know when you're looking at the data who was involved?

Phil...

 

[grahamiwa]

Hi Phil

Yes Mentor 1 & Assessor could be a valid combination but very unlikely as someone would sign as mentor 1 first.

The full table is below
<TABLE style="WIDTH: 143pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=190 x:str><COLGROUP><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><COL style="WIDTH: 123pt; mso-width-source: userset; mso-width-alt: 5997" width=164><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 143pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 height=20 width=190 colSpan=2>TBTranscript</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 vAlign=top align=left><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="Z-INDEX: 8; POSITION: absolute; MARGIN-TOP: 1.5pt; WIDTH: 15pt; HEIGHT: 12.75pt; MARGIN-LEFT: 3pt" id=_x0000_s1039 type="#_x0000_t75"><v:imagedata o:title="" src="file:///C:\DOCUME~1\gcarncr\LOCALS~1\Temp\msohtml1\02\clip_image001.png"></v:imagedata><?xml:namespace prefix = x ns = "urn:schemas-microsoft-comffice:excel" /><x:ClientData ObjectType="Pict"><x:SizeWithCells></x:SizeWithCells><x:CF>Bitmap</x:CF> <x:AutoPict></x:AutoPict></x:ClientData></v:shape><TABLE cellSpacing=0 cellPadding=0><TBODY><TR><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 20pt; HEIGHT: 15pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=20 width=26> </TD></TR></TBODY></TABLE></TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27>TBTranscriptID</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=20> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27>TBTaskskillID</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27>TBRoadmapID</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Mentor1SignOff</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Mentor1ID</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Mentor1Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Mentor2SignOff</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Mentor2ID</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Mentor2Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>AssessorSignOff</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>AssessorID</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>AssessorDate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>SupervisorSignOff</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>SupervisorID</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>SupervisorDate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>WorkerAcknowledgement</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>WorkerID</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>WorkerDate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #d4d0c8" class=xl26 height=17> </TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>Comment</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24> </TD></TR></TBODY></TABLE>

<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <o:lock aspectratio="t" v:ext="edit">The query needs to check that the ID's for Mentor 1, Mentor 2, or Assessor match my ID "278", that the date as entered in the table matches a date input in a message box and the WorkerID matches the Worker ID again in a message box.</o:lock></v:shapetype>
<v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><o:lock aspectratio="t" v:ext="edit">I am new on here so I dont know how to put a document file in here so that I can illustrate it a little better.</o:lock>
<o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>