Elegant way of getting multiple results

[PhilOfWalton]

I have a table with 5 Yes/No fields Mon, Tue, Wed, Thu, Fri.
I want the following results from a query.

If all 5 fields are True "Any day"
If all 5 fields ate False "" - Blank
If say Mon, Wed & Thu are True "Monday or Wednesday or Thursday"

I can obviously do it with a bit of code, but is there a way to do it within a Query?

Thanks

Phil

[zmbd]

SELECT
   Switch(Condition1,Result1,Condition2,Result2)
      AS DaysSelected
FROM Table;

The result# can be a calculated value...

Really not sure why you would build a table like this as it may break the rules of normality.

Also as it the IIF() the Switch() (Syntax) evaluatates all of the conditions and the results clauses; thus, if there's an error in any of the conditionals or results the whole thing will fail and it can be slow on large datasets.

[PhilOfWalton]

Thanks, Z, but switch only returns one result (the first true condition that is found)

I am looking for if Mon, Tue & Thu are all true, to return 3 values

I have gone it, but it's not pretty.

SELECT TblSpecialService.SPecialSeviceID, TblSpecialService.DMon, TblSpecialService.DTue, TblSpecialService.DWed, TblSpecialService.DThurs, TblSpecialService.DFri, 
IIf([DMon]=False Or [DTue]=False Or [DWEd]=False Or [DThurs]=False Or [DFri]=False,IIf([DMon]=True,"Monday or ")) AS Expr2, IIf([DMon]=False Or [DTue]=False Or [DWEd]=False Or [DThurs]=False Or [DFri]=False,IIf([DTue]=True,"Tuesday or ")) AS Expr3, IIf([DMon]=False Or [DTue]=False Or [DWEd]=False Or [DThurs]=False Or [DFri]=False,IIf([DWed]=True,"Wednesday or ")) AS Expr4, IIf([DMon]=False Or [DTue]=False Or [DWEd]=False Or [DThurs]=False Or [DFri]=False,IIf([DThurs]=True,"Thursday or ")) AS Expr5, IIf([DMon]=False Or [DTue]=False Or [DWEd]=False Or [DThurs]=False Or [DFri]=False,IIf([DFri]=True,"Friday or ")) AS Expr6, [expr2] & [expr3] & [expr4] & [expr5] & [expr6] AS Expr7, IIf([DMon]=False Or [DTue]=False Or [DWEd]=False Or [DThurs]=False Or [DFri]=False,IIf(Not IsNull([Expr7]),Left([Expr7],Len([Expr7])-3)),"Any Day") AS Expr8
FROM TblSpecialService;
 

Phil

[zmbd]

Post#2 ... The result# can be a calculated value...

Then you set something like (using field Monday = [M] etc..):

Case1:
(([M]=true) AND ([T]=True) AND ([W]=True) 
   AND ([R]=True) AND ([F]=True))
Result1:
"AnyDay"
 
Case2:
(([M]=False) AND ([T]=False) AND ([W]=False) 
   AND ([R]=False) AND ([F]=False))
Result2:
""
 
Case3
(([M]=true) OR ([T]=True) OR ([W]=True) 
   OR ([R]=True) OR ([F]=True))
Result3:
(iif([M]=true,":M:","" )+iif([T]=true,":T:","")
   +iif([W]=true,":W:","")+iif([R]=true,":R:","")
   +iif([F]=true,":F:",""))
 

Of course you can insert something else instead of colons and with propagation of nulls most of the separators can be vanished.

Works quite nicely in my test database

; however, it still begs the question about normalization.

[PhilOfWalton]

Thanks, that looks great, Much more elegant than my solution

Phil

[ADezii]

1. let's assume you have the following Table named tblTest:
   

   PK    Mon    Tue    Wed    Thu    Fri
   1    Yes    Yes    Yes    Yes    Yes
   2    No    No    No    No    No
   3    Yes    Yes    No    No    No
   4    Yes    Yes    Yes    No    No
   5    Yes    No    Yes    No    Yes
   6    Yes    Yes    No    Yes    No
   7    No    Yes    Yes    No    No
   8    No    No    No    Yes    Yes
   9    Yes    Yes    Yes    Yes    No
   10    No    Yes    Yes    Yes    Yes
   11    No    No    Yes    Yes    No
   12    Yes    No    No    No    Yes
   13    No    Yes    No    Yes    Yes
   14    No    Yes    No    No    No
   15    No    No    Yes    No    No
   16    No    No    No    Yes    No
   17    Yes    Yes    No    No    Yes

2. Create the following Query that uses a Calculated Field to generate the appropriate Results:
   

   SELECT tblTest.PK, tblTest.Mon, tblTest.Tue, tblTest.Wed, tblTest.Thu, tblTest.Fri, fCheckAvailability([Mon],[Tue],[Wed],[Thu],[Fri]) AS Availability
   FROM tblTest;

3. Function Definition:
   

   Public Function fCheckAvailability(blnMon As Boolean, blnTue As Boolean, blnWed As Boolean, _
                                      blnThu As Boolean, blnFri As Boolean) As String
   Dim strBuild As String
    
   'Eliminate the extremes first
   If blnMon And blnTue And blnWed And blnThu And blnFri Then
     fCheckAvailability = "Any Day"
       Exit Function
   'ElseIf Not (blnMon And blnTue And blnWed And blnThu And blnFri) Then
   ElseIf Not blnMon And Not blnTue And Not blnWed And Not blnThu And Not blnFri Then
     fCheckAvailability = "No Days"
       Exit Function
   End If
    
   If blnMon Then strBuild = strBuild & "Monday Or "
   If blnTue Then strBuild = strBuild & "Tuesday Or "
   If blnWed Then strBuild = strBuild & "Wednesday Or "
   If blnThu Then strBuild = strBuild & "Thursday Or "
   If blnFri Then strBuild = strBuild & "Friday Or "
    
   fCheckAvailability = Left$(strBuild, Len(strBuild) - 4)
   End Function

4. Query after execution:
   

   PK    Mon    Tue    Wed    Thu    Fri    Availability
   1     Yes    Yes    Yes    Yes    Yes    Any Day
   2     No    No    No    No    No        No Days
   3     Yes    Yes    No    No    No         Monday Or Tuesday
   4     Yes    Yes    Yes    No    No       Monday Or Tuesday Or Wednesday
   5     Yes    No    Yes    No    Yes    Monday Or Wednesday Or Friday
   6     Yes    Yes    No    Yes    No     Monday Or Tuesday Or Thursday
   7     No    Yes    Yes    No    No    Tuesday Or Wednesday
   8     No    No    No    Yes    Yes    Thursday Or Friday
   9     Yes    Yes    Yes    Yes    No     Monday Or Tuesday Or Wednesday Or Thursday
   10    No    Yes    Yes    Yes    Yes    Tuesday Or Wednesday Or Thursday Or Friday
   11    No    No    Yes    Yes    No    Wednesday Or Thursday
   12    Yes    No    No    No    Yes     Monday Or Friday
   13    No    Yes    No    Yes    Yes    Tuesday Or Thursday Or Friday
   14    No    Yes    No    No    No      Tuesday
   15    No    No    Yes    No    No      Wednesday
   16    No    No    No    Yes    No     Thursday
   17    Yes    Yes    No    No    Yes    Monday Or Tuesday Or Friday

P.S. - I do apologize for the poor formatting - running out the door and had no time to do so properly. You can also download the Demo if you like.

[PhilOfWalton]

Again that is sweet, but I have always found calling functions from queries slows things down, hence my earlier remark about avoiding VBA.

It's probably a new topic, but what is your experience of the speed using built in functions against "bespoke" functions?

Phil

[ADezii]

what is your experience of the speed using built in functions against "bespoke" functions?

1. To be perfectly honest, I have never dealt with such a large number of Records whereas the differences between the two approaches would be that significant.
2. Personally, I find the use of multiple IIf() Constructs, which appears to be warranted here, to be very confusing and not very intuitive at all. I would easily sacrifice some processing time rather than deal with them. Again, only my personal opinion.

[zmbd]

ADezii, agreeded, nested IIF() can be a nightmare to handle and it is the main reason I suggested the Switch() to avoid the nested IIF(); thus requiring on the simpler string of IIF() for the final condition result.

PhilOfWalton as with ADezii, I have smaller databases in Access; thus, I've not seen very much difference between custom user functions and the built in functions. With that said, I still try to stay with those that are native to Access or to the SQL when possible.

[PhilOfWalton]

Am going along with ADezii's solution, but want to modify it, as I use the same routine in a couple of places.

So I have modified the code to

Public Function fCheckAvailability(Typ As Byte, Flds As Variant) As String
 
    Dim strBuild As String
    Dim DaysArray(1, 7) As String
    Dim i As Integer, j As Integer
    Dim ChrsToCut As Integer
 
    DaysArray(0, 0) = "No Day"
    DaysArray(0, 1) = "Monday Or "
    DaysArray(0, 2) = "Tuesday Or "
    DaysArray(0, 3) = "Wednesday Or "
    DaysArray(0, 4) = "Thursday Or "
    DaysArray(0, 5) = "Friday Or "
    DaysArray(0, 6) = "Any Day "
    DaysArray(0, 7) = "4"
 
    DaysArray(1, 0) = "No Time"
    DaysArray(1, 1) = "08:00 to 10:00  Or  "
    DaysArray(1, 2) = "10:00 to 12:00  Or  "
    DaysArray(1, 3) = "12:00 to 2:00  Or  "
    DaysArray(1, 4) = "2:00 to 4:00  Or  "
    DaysArray(1, 5) = "4:00 to 6:00  Or  "
    DaysArray(1, 6) = "Any Time "
    DaysArray(1, 7) = "6"
 
    ' Eliminate the extremes first
    For i = 0 To 4
        If Flds(i) = True Then
            j = j + 1
        End If
    Next i
 
    If j = 0 Then                           ' No true found
        fCheckAvailability = DaysArray(Typ, 0)
        Exit Function
    End If
 
    If j = 5 Then                           ' All true found
        fCheckAvailability = DaysArray(Typ, 6)
        Exit Function
    End If
 
    For i = 0 To 4
        If Flds(i) = True Then
            strBuild = strBuild & DaysArray(Typ, i + 1)
        End If
    Next i
 
    ChrsToCut = CInt(DaysArray(Typ, 7))
 
    fCheckAvailability = Left$(strBuild, Len(strBuild) - ChrsToCut)
 
End Function
 

This bit of code runs the routine and produced spot on results

Sub Test()
 
    Dim Arr() As Variant
    Dim Typ As Byte             ' 0 for days, 1 for times
 
    Typ = 1
    ReDim Arr(5)
    Arr = Array(0, -1, -1, 0, -1)
    Debug.Print fCheckAvailability(Typ, (Arr))
 

So my problem is running the fCheckAvailability from a query.

The query is

SELECT SPecialSeviceID,DMon, DTue,DWed,DThurs, DFri,
 fCheckAvailability(0,Array([DMon],[DTue],[DWed],[DThurs],[DFri])) AS Availability
FROM TblSpecialService;
 

All I get in the Availablility field is #Error, and the fCheckAvailability procedure never gets run (Breakpoint on first line never gets highlighted)

Do what is the correct version of
fCheckAvailability(0,Array([DMon],[DTue],[DWed],[DThurs],[DFri]))

Thanks

Phil

[ADezii]

1. Please bear with me since I am at work where Access is not available so I am duplicating the functionality as best as I can in Excel
2. To be honest, I do not think that your approach will work, namely passing the Field Values within an Array to the fCheckAvailability() Function.
3. What I "think" will work is to pass the actual Field Values ([DMon],[DTue],[DWed],[DThurs],[DFri]) to the fCheckAvailability_2() Function along with the Typ Argument.
4. Typ will be passed by itself while the remaining Arguments representing the Days of the week will be passed to the Function where they will be integrated into a ParamArray and processed accordingly.
5. Revised Function definition:
   

   Public Function fCheckAvailability_2(Typ As Byte, ParamArray Flds() As Variant) As String
   Dim strBuild As String
   Dim DaysArray(1, 7) As String
   Dim i As Integer, j As Integer
   Dim ChrsToCut As Integer
    
   DaysArray(0, 0) = "No Day"
   DaysArray(0, 1) = "Monday Or "
   DaysArray(0, 2) = "Tuesday Or "
   DaysArray(0, 3) = "Wednesday Or "
   DaysArray(0, 4) = "Thursday Or "
   DaysArray(0, 5) = "Friday Or "
   DaysArray(0, 6) = "Any Day "
   DaysArray(0, 7) = "4"
    
   DaysArray(1, 0) = "No Time"
   DaysArray(1, 1) = "08:00 to 10:00  Or  "
   DaysArray(1, 2) = "10:00 to 12:00  Or  "
   DaysArray(1, 3) = "12:00 to 2:00  Or  "
   DaysArray(1, 4) = "2:00 to 4:00  Or  "
   DaysArray(1, 5) = "4:00 to 6:00  Or  "
   DaysArray(1, 6) = "Any Time "
   DaysArray(1, 7) = "6"
    
   ' Eliminate the extremes first
   For i = 0 To 4
     If Flds(i) = True Then
       j = j + 1
     End If
   Next i
    
   If j = 0 Then                           ' No true found
     fCheckAvailability_2 = DaysArray(Typ, 0)
       Exit Function
   End If
    
   If j = 5 Then                           ' All true found
     fCheckAvailability_2 = DaysArray(Typ, 6)
       Exit Function
   End If
    
   For i = 0 To 4
     If Flds(i) = True Then
       strBuild = strBuild & DaysArray(Typ, i + 1)
     End If
   Next i
    
   ChrsToCut = CInt(DaysArray(Typ, 7))
    
   fCheckAvailability_2 = Left$(strBuild, Len(strBuild) - ChrsToCut)
   End Function
    

6. Sample Test Call (works as intended):
   

   Dim Typ As Byte             ' 0 for days, 1 for times
    
   Typ = 1
    
   MsgBox fCheckAvailability_2(Typ, 0, -1, -1, 0, -1)
    

7. Revised SQL Statement (Field Values passed individually), which infortunately, I cannot test! (LOL):
   

   SELECT SPecialSeviceID,DMon, DTue,DWed,DThurs, DFri,fCheckAvailability(0, [DMon], [DTue], [DWed], [DThurs], [DFri]) As Availability FROM TblSpecialService;

8. Let me know how this works out for you.

[ADezii]

Just noticed an Error in the SQL Syntax above in Item# 7, it should be:

SELECT SPecialSeviceID,DMon, DTue,DWed,DThurs, DFri,fCheckAvailability_2(0, [DMon], [DTue], [DWed], [DThurs], [DFri]) As Availability FROM TblSpecialService;

[PhilOfWalton]

Thanks, cracked it !!!

the relevant bits are for the SQL are

fCheckAvailability(1,[DMon],[DTue],[DWed],[DThurs],[DFri]) AS Availability
 

and

Public Function fCheckAvailability(Typ As Byte, ParamArray Flds() As Variant) As String
 

Your help very much appreciated.

Phil



Phil

[ADezii]

You are very welcome, Phil. Good luck with your Project.

[zmbd]

Phil, please keep in mind:

Elegant way of getting multiple results ...
I can obviously do it with a bit of code, but is there a way to do it within a Query?

You asked for an "Elegant" and basically SQL solution and that is what I had provided, if you had wanted a VBA solution I could have had that in the first reply.
Adezii's code is brilliant, but not simple; however, appears to be better suited for your long-term goal... which I don't think could be easily accomplished with Access's-SQL (not without a few more tables :) if at all )

[PhilOfWalton]

Apologies for that.

I changed my mind after reading various posts indicating that people did not think there was a measurable difference in the time to run a stored procedure against a built in function.
The VBA approach gives more flexibility and can be called using different arguments.

Once again, thanks & apologies

Phil