CODEWITHSUNDEEP
sqlvbafunctionms-access
dizzythejazzman
dizzythejazzman

Reputation: 21

MS Access SQL, "function calls"

I'm dabbing in SQL, and using Access for now, because I got sick of attempting to code conditional queries in excel, and I've got a SQL query that has repetition except for the column name, so much like functions, I'd like to clean up the fluff by doing something like CountSubCategories(Category) to reduce my redundant code, as you see below...

SELECT
COUNT(*) AS “Support_Interactions”,
SUM(IIF([Type of Inquiry] = "Confidence Check",1, 0)) AS ConfidenceCheck,
   SUM(IIF(([Type of Inquiry] = "Confidence Check") AND 
   ([Billing]<>NULL),1, 0)) AS CC_Sub_Billing,
   SUM(IIF(([Type of Inquiry] = "Confidence Check") AND 
   ([General & CBS]<>NULL),1, 0)) AS CC_Sub_General&CBS,
   SUM(IIF(([Type of Inquiry] = "Confidence Check") AND 
   ([Phone]<>NULL),1, 0)) AS CC_Sub_Phone,
   SUM(IIF(([Type of Inquiry] = "Confidence Check") AND 
   ([Process]<>NULL),1, 0)) AS CC_Sub_Process,
   SUM(IIF(([Type of Inquiry] = "Confidence Check") AND 
   ([Tools]<>NULL),1, 0)) AS CC_Sub_Tools,
   SUM(IIF(([Type of Inquiry] = "Confidence Check") AND 
   ([Technical Support]<>NULL),1, 0)) AS CC_Sub_TechnicalSupport,
   SUM(IIF(([Type of Inquiry] = "Confidence Check") AND 
   ([WO/SC]<>NULL),1, 0)) AS CC_Sub_WO&SC,
SUM(IIF([Type of Inquiry] = "Product Knowledge",1, 0)) AS ProductKnowledge,
   SUM(IIF(([Type of Inquiry] = " Product Knowledge ") AND 
   ([Billing]<>NULL),1, 0)) AS CC_Sub_Billing,
   SUM(IIF(([Type of Inquiry] = " Product Knowledge ") AND 
   ([General & CBS]<>NULL),1, 0)) AS CC_Sub_General&CBS,
   SUM(IIF(([Type of Inquiry] = " Product Knowledge ") AND 
   ([Phone]<>NULL),1, 0)) AS CC_Sub_Phone,
   SUM(IIF(([Type of Inquiry] = " Product Knowledge ") AND 
   ([Process]<>NULL),1, 0)) AS CC_Sub_Process,
   SUM(IIF(([Type of Inquiry] = " Product Knowledge ") AND 
   ([Tools]<>NULL),1, 0)) AS CC_Sub_Tools,
   SUM(IIF(([Type of Inquiry] = " Product Knowledge ") AND 
   ([Technical Support]<>NULL),1, 0)) AS CC_Sub_TechnicalSupport,
   SUM(IIF(([Type of Inquiry] = " Product Knowledge ") AND 
   ([WO/SC]<>NULL),1, 0)) AS CC_Sub_WO&SC,

As you can see, the same SubCategories exist for the parent Category, so how might I be able to pre-define a function and call it with Category as the variable?

Thanks so much!

Upvotes: 2

Views: 122

Answers (1)

Thomas G
Thomas G

Reputation: 10206

MS access SQL doesnt allow this but VBA is there .

You should basically use 2 nested loops on your categories and subcategories to build your SQL string

I used collections to handle the cats and subcats:

Sub StackOverflow_Solution()

    Dim colCat As New Collection
    Dim colSub  As New Collection
    Dim varCat As Variant
    Dim varSub As Variant
    Dim strSQL As String
    Dim RST As Recordset

    ' fill in categories
    colCat.Add "Confidence Check"
    colCat.Add "Product Knowledge"

    ' fill in sub categories
    colSub.Add "Billing"
    colSub.Add "General "
    colSub.Add "Phone"
    colSub.Add "Process"
    colSub.Add "Tools"


    ' initiate SQL query
    strSQL = "SELECT " & vbCrLf & _
             "COUNT(*) AS 'Support_Interactions', " & vbCrLf

    ' Looping on all categories
    For Each varCat In colCat

        ' Adding category count
        strSQL = strSQL & "SUM(IIF([Type of Inquiry] = '" & varCat & "',1, 0)) AS " & Replace(varCat, " ", "") & ", " & vbCrLf

        ' Looping on all sub categories
        For Each varSub In colSub

            strSQL = strSQL & "    SUM(IIF(([Type of Inquiry] = '" & varCat & "') AND " & vbCrLf & _
                              "    ([" & varSub & "]<>NULL),1, 0)) AS CC_Sub_" & Replace(varSub, " ", "") & ", " & vbCrLf
        Next

    Next

    Debug.Print strSQL


    ' Trigger query
    Set RST = CurrentDb.OpenRecordset(strSQL)

    If Not RST.BOF Then

        'reading resulting recordset

        While Not RST.EOF

            'dostuff with your data

            RST.MoveNext

        Wend

    End If

End Sub

With this sub your strSQL will contain this :

SELECT 
COUNT(*) AS 'Support_Interactions', 
SUM(IIF([Type of Inquiry] = 'Confidence Check',1, 0)) AS ConfidenceCheck, 
    SUM(IIF(([Type of Inquiry] = 'Confidence Check') AND 
    ([Billing]<>NULL),1, 0)) AS CC_Sub_Billing, 
    SUM(IIF(([Type of Inquiry] = 'Confidence Check') AND 
    ([General ]<>NULL),1, 0)) AS CC_Sub_General, 
    SUM(IIF(([Type of Inquiry] = 'Confidence Check') AND 
    ([Phone]<>NULL),1, 0)) AS CC_Sub_Phone, 
    SUM(IIF(([Type of Inquiry] = 'Confidence Check') AND 
    ([Process]<>NULL),1, 0)) AS CC_Sub_Process, 
    SUM(IIF(([Type of Inquiry] = 'Confidence Check') AND 
    ([Tools]<>NULL),1, 0)) AS CC_Sub_Tools, 
SUM(IIF([Type of Inquiry] = 'Product Knowledge',1, 0)) AS ProductKnowledge, 
    SUM(IIF(([Type of Inquiry] = 'Product Knowledge') AND 
    ([Billing]<>NULL),1, 0)) AS CC_Sub_Billing, 
    SUM(IIF(([Type of Inquiry] = 'Product Knowledge') AND 
    ([General ]<>NULL),1, 0)) AS CC_Sub_General, 
    SUM(IIF(([Type of Inquiry] = 'Product Knowledge') AND 
    ([Phone]<>NULL),1, 0)) AS CC_Sub_Phone, 
    SUM(IIF(([Type of Inquiry] = 'Product Knowledge') AND 
    ([Process]<>NULL),1, 0)) AS CC_Sub_Process, 
    SUM(IIF(([Type of Inquiry] = 'Product Knowledge') AND 
    ([Tools]<>NULL),1, 0)) AS CC_Sub_Tools,

You should do a few adaptations as for your column WO/SC, in the column's alias you should replace the "/" with "&" instead of space with nothing.

I made 90% of the work you can do the rest now

Upvotes: 2

Related Questions