calculate checkbox value by checkboxes in linked table

[svdoerga]

Simplified, my tables are:

Receivement(ReceivementID(PK, autonr), CaseClosed(checkbox))
Item(ItemID(PK, autonr), ReceivementID(FK,nr), Finished(checkbox))

So each receivement has multiple items. When the "Finished" field is checked for all the items a receivement has, the "CaseClosed" checkbox should be set to true in the receivement automatically.

Is there a way to implement this calculation on the level of the tables (in table design)? Or should it be done with a query?

Fields from the Receivement table are shown on a form that has a query as recordsource. So far I figured how you can add calculated fields to a query, but I dont know how to build the expression so that it uses the AND operator on the values of the "Finished" checkbox of all the items belonging to a receivement. I've gotten as far as:

CaseClosed: [Item].[Finished]

Can anyone help?

 

[PHV]

I'd use a query with a SQL code like this:
UPDATE Receivement SET CaseClosed = True
WHERE (SELECT COUNT(*) FROM Item WHERE ReceivementID=Receivement.ReceivementID)
= (SELECT COUNT(*) FROM Item WHERE ReceivementID=Receivement.ReceivementID AND Finished=True)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

how about

update Receivement 
left join(Select ReceivementID from item where Finished = false) as unfinshed
on unfinshed.ReceivementID =Receivement.ReceivementID  
set CaseClosed = True
where unfinshed.ReceivementID  is null

 

[svdoerga]

I'm not sure where to use those queries. I have tried it in the query design of the query that is the recordsource of the form I'm displaying the data on. There I tried changing "field" for the CaseClosed checkbox to both:

CaseClose: update Receivement left join(Select ReceivementID from item where Finished = false) as unfinshed on unfinshed.ReceivementID =Receivement.ReceivementID set CaseClosed = True where unfinshed.ReceivementID is null

and

CaseClosed: UPDATE Receivement SET CaseClosed = True WHERE (SELECT COUNT(*) FROM Item WHERE ReceivementID=Receivement.ReceivementID) = (SELECT COUNT(*) FROM Item WHERE ReceivementID=Receivement.ReceivementID AND Finished=True)

but they both give syntax errors. Am I looking at the wrong place to use it?

 

[svdoerga]

I still haven't been able to figure this out, can anyone help?

 

[PWise]

copy either my or phv sql in a new query window and run it it will the Receivement table

 

[svdoerga]

Ah I got it, thanks PWise. The only seems to be one issue left. This query set CaseClosed to true where needed, but it could also happen that it needs to be set to false when one of the item's actionperformed gets unchecked.

Is there a way to incorporate that into this query or should I make it a 2nd query and always run both queries after one another?