Update Query - Specific Record

[NickFazer]

I have two tables called LabSamples and Results that have a 1 to Many relationship using a LabSampleID field. Each LabSample can have many Results what I want to achieve is for the Status field in the LabSamples table to be updated from "On Test" to "Completed" once all the Results for that LabSampleID have been entered. I have made an update query but it is checking all the records in the Results table each time, how can I get it to check only the Results for that LabSampleID only.

tblLabSample
LabSampleID - Autonumber
SampleNo - Text
Status - Text
CompleteDate - Date/Time

tblResults
ResultID - Autonumber
LabSampleID - Number
TestID - Number
Count - Number
Complete - Yes/No
CompleteDate - Date/Time


Thanks

 

[VilaRestal]

I'm not certain how you would know if all results have been completed. If all necessary entries for each sample are guaranteed to be in tblResults then this would do it:

UPDATE tblLabSample
SET Status = 'Completed'
WHERE NOT EXISTS (SELECT * FROM tblResults WHERE tblResults.LabSampleID = tblLabSample.LabSampleID AND Complete = 0)

Otherwise, perhaps you need to make sure the results for that sample have all the necessary entries and not just whether there are none incomplete.

 

[Alansidman]

Here is what your SQL statement needs to look like. You will need to have a Where clause in it.

http://www.w3schools.com/sql/sql_update.asp

 

[RainLover]

I would use the Form,s Before Update Event.

Each time you make an entry the Code will check if all the Text Boxes have a value.

If they do then update the Status

If not then do Nothing.

A rought example is.

Sub FormBeforeUpdate

If Len(Me.txtYourFirstBox) Then

If Len(Me.txtYourSeconeBox) Then

If Len(Me.txtYourThirdBox) Then

Me.Status = "Completed"

End IF

End If

End If

Please TEST.

 

[NickFazer]

Thank you all very much for your help. Settled in the end to run an update query to complete the samples and then another to reverse the process if any of the criteria wasn't met. Probably not the most elegant solution but it seems to work.


Thanks Again


Nick

 

[RainLover]

If you are happy with the solution then that is good.

It can always be changed/updated at another time if it turns out not to be exactly what you need.