UPDATE query using SQL WHERE, COUNT

[AccessWomble]

Hi everyone
Quite new to Access and SQL so this could be something as simple as brackets..



I have tables called:

• tblSections, and each record in there 'has' x related records in the table
• tblActions,

They are linked via Primary/Foreign keys: SectionsID/SectionsForeignID.


I want to create an UPDATE query to place the word 'Overdue' in the field tblSections.SectionStatus, for any record for which:

• tblSections.DateDue is in the past, AND
• Any one of the related records in tblActions has value either 'Outstanding', or Is Null.

Here's what i have so far, which compiles but when run gives error:
'Data type mismatch in criteria expression'.
The strategy is to count the records in tblActions that are oustanding or null, ensure the count is zero and so on. (The dateDiff part works fine on its own in a simple query, that's not a problem here, although i've been messing around with brackets since then to try and get it to work. )

UPDATE tblSections SET tblSections.SectionStatus = "Overdue" 
WHERE (((DateDiff("d",[DateDue],Now()))>0) AND (((SELECT COUNT([tblActions.ActionStatus]) FROM tblActions 
WHERE [tblActions.SectionsForeignID] = [tblSections.SectionsID] 
AND ([tblActions.ActionStatus] = 'Outstanding' OR [tblActions.ActionStatus] is Null)))=0));

Thanks for looking!

 

[MarkK]

Consider not doing this in the table, but rather in the query you use to retrieve records. Overdue is not so much data as it is an interpretation of data that may change unexpectedly as a result of the passage of time. If you bother to 'hardwire' this status into the data itself, then you are forced also to undo this status based on some other event, either payment or extension of terms or whatever.
And you risk a form of repetion where Overdue can in one part of your system be determined by applying specified logic, and in another part of your system can be determined by reading a value from a table. What happens in the case where these determinations disagree? And if the application of the logic can be said to be authoritative, then the value in the table should always be suspected of being wrong, and if it might always be wrong it is of no use.
I would not store 'Overdue'. I would calculate it every time I need it.

 

[rainman89]

And to add to lagbolt's post, and iif statement would work fine to accomplish this within a query.

http://www.techonthenet.com/access/functions/advanced/iif.php

 

[AccessWomble]

Hi lagbolt
thanks for the reply. Within the context of my question so far I agree with you, I see exactly what you mean, and i feel like something has 'clicked', like I've learned something crucial about databases.
If i give you a bit more insight into what I'm trying to do however, can you tell me if you'd still do it your way or if in fact there's merit in writing data to the table as I suggested?

There's another table called tblMainDetails, and each record in there has x associated records in tblSections. tblMaindetails will be fairly low volume data.
I was going to use 'SectionStatus' of each record in tblSections associated with a given record in tbl MainDetails, to deduce an 'overall' status.
Eventually i want to created a kind of visual monitor, made of coloured boxes on a form to show the SectionStatus of each section for each record in tblMainDetails , with ability to query/filter for specific 'overall' statuses. Would you still stick to what you said before and save these statuses nowhere, instead deducing them with queries whenever required?
Would the queries not become vastly complex and slow?
Thanks