Updating a field based on two criteria

[pauldt123]

Any help would be greatly appreciated,

I have a three table layout:
Teams (TeamID, TeamName)
Project (ProjectID, ProjectName,signedOff)
Answers (TeamID,ProjectID,Response)


"Teams" (TeamID primarykey) and "Project" (ProjectID primary key) are linked to "Answers" (ProjectID, TeamID composite key) but not to each other.


i have a field in project called "signedOff"
i need "signedoff" to be Yes once all teams have set "Response" to Yes

Hope that makes sense
thanks

 

[SeeThru]

Umm how about an update query..

add the Project table
add a calucaled field....

if dcount("TeamID","answers")=dcount("TeamID","Answers","Response='yes'"

In the calculated field criteria, enter 'True'. This should show all records in Projects where they are all 'Yes'. Then turn it into an update quesry and update the signedOff to 'Yes'


Personally, I wouldnt bother - I would work out the updsate on the floy when I needed it.

 

[pauldt123]

SeeThru,

Thanks for your help, I'm still struggling slightly though. I assume that the if statement is the calculated field, having tried it the syntax is apparently incorrect (operand without operator), although I can't see where the error is. Does anything spring to mind?

Thanks again for your help, it's greatly appreciated.

 

[PHV]

You may try this update query (SQL code):
UPDATE Project SET signedOff = True
WHERE DCount('TeamID','Answers','ProjectID=' & ProjectID) = DCount('TeamID','Teams')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pauldt123]

Thanks for the suggestions, I tried running the SQL query and it returned the following error message: "Unknown". Any advice?

Thanks again

 

[pauldt123]

Thanks for all of your help, I feel I may need to clarify my objectives a little.

Essentially I need an update query based on a Dcount of the number of teams associated to each project, and a Dcount of the number of teams who have ticked "response" for each project. If the two are equal "signed off" should be set to true. In the SQL query above I can't see any reference to "response = yes", am I mis-understanding the query altogether?