Filtering results by condition in query? (Access 2010)

[stevekos07]

I hope this isn't too confusing for you to be able to assist me.

I have a requirement to produce a yes/no result in a form field based on a range of conditions in a query. We are a service which delivers support for frail aged clients daily based on their individual requirements. For any particular client we need to know whether they require a service call today or any selected day, based on the following conditions:

Certain days the client has selected not to be called (e.g. no call Mon, Wed, Fri).
Days selected at random based on the client's variable requirements (e.g. no call from 1/1/16 to 3/1/16 etc.)

I can set up my tables and query to produce a yes/no result for each option (by yes/no on each day of the week and a table of From-to dates for the casual selections, and an IIF expression to check each variable to produce a Yes/No result for each variable).

When I run the query I may get multiple results for any client based on each condition, but if I get a "No" result for at least one result in the query for a particular date based on the above then the result is "No" for that day.

e.g.

John - No Call Mon, Wed (Today is Sat) = Yes
John - No Call from 10/8/15 to 18/8/15 (Today is 15/8/15) = No
Etc.

(I hope you are still with me!)

How do I show a "No" or "yes" in a text field based on a Date/Time filter on a form or report which shows a "Yes" by default and a "No" if ANY result in the query result list for that client is a "No"?

[Rabbit]

Basically you have a queries that return a bunch of yes's and no's. You just want to return a no if there are any no's. Is that correct?

Take that query, and return the Min() of the yes/no field. If there are any no's it will return no because n is "smaller" than y.

[stevekos07]

That didn't work. When I ran the query with the Min criteria it produced no results. I actually don't want to filter out values per client, I just want the most valid result for all of the conditions. For instance a client may have one 'set' of days of the week that they select for no calls, but they may have more than one 'From-To' set of dates in addition. e.g. Normally call on Mondays, but for the next two mondays no call. This would not be a permanent change.

This is my expression in the query to give you an idea:

CallToday: IIf([Date]>=[tblClientNoCall Days].[NoCallFrom] And [Date]<=[tblClientNoCall Days].[NoCallTo]
Or Weekday([Date],1)=1 And [Sun]=True
Or Weekday([Date],1)=2 And [Mon]=True
Or Weekday([Date],1)=3 And [Tue]=True
Or Weekday([Date],1)=4 And [Wed]=True
Or Weekday([Date],1)=5 And [Thu]=True
Or Weekday([Date],1)=6 And [Fri]=True
Or Weekday([Date],1)=7 And [Sat]=True,"No","Yes ")

[stevekos07]

Here is a screenshot of the results. You might comment that my multiple columns of days breaks normalisation, which technically it does, but it works, and as days of the week are hardly going to increase or decrease any time soon, the format is as stable as any other way of presenting this.

[Rabbit]

You don't put the min in the criteria, it goes in the select clause.

If you want to keep all the rows, then you still need the min, you just then have to join that result back to the full data set

[stevekos07]

Thanks for that Rabbit. That worked. I have a couple of other challenges with this to deal with though before it is done. There can be exceptions to the regular no-call days as well, so I am adding an "Exceptions " field as a final logical test. As a relative newcomer to Access this has been a real test for me too, but I'm getting there :).