UtterAccess Discussion Forums > Help Using Different Parts Of Sql String

Full Version: Help Using Different Parts Of Sql String

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

kstoneman

Apr 9 2012, 04:06 PM

If I run this SQL select statement, I get everything I need to populate different Excel cells:

CODE

strSql3 = "SELECT Sum([Calls_WI_SL])/Sum([nco])) AS SL, (Sum([nco])-Sum([nch2]))/Sum([nco]) AS Aband, Sum(SIS.NCO) AS NCO1, Sum(SIS.NCH2) AS NCH, Sum([Work_vol])/Sum([NCH2]) AS AHT, Sum([ASA_sec])/Sum([NCH2]) AS ASA, ,Sum([Work_Vol])/Sum([Staff_Sec]) AS OCC, Sum(SIS.NCO_Fore) AS NCO_Fore1, Sum(SIS.Calls_WI_SL) AS Calls_WI_SL1" & _
            " FROM Curran_Sites_LOBs CSL INNER JOIN SIS_daily_temp SIS ON CSL.CT_ID = SIS.CT_ID" & _
            " WHERE SIS.Date =#" & begindate & "#" & _
            " GROUP BY CSL.BU_ID" & _
            " HAVING CSL.BU_ID = " & ID_BU & _
            " ORDER BY CSL.BU_ID"
Set rs3 = CreateObject("ADODB.Recordset")
rs3.Open strSql3, con, 1

From this string, I would like to use SL, Aband, NCO1, NCH, AHT in one group.
A second string with ASA, OCC
A third string with NCO_Fore1
A fourth string with Calls_WI_SL1
Each string populates a different section in my workbook.

I’m trying to avoid making multiple SQL strings to using the same criteria that the main string has.

I’ve tried using the following without success:

CODE

strsql42 = "select rs3!Calls_WI_SL1" & _
" FROM rs3"
Set rs42 = CreateObject("ADODB.Recordset")
rs42.Open strsql42

If anyone can point me in the right direction, I would appreciate it.

CyberCow

Apr 9 2012, 06:19 PM

Something like.....

Dim strSQL as String, strSQLa as String, strSQLb as String, strSQLc as String

strSQLa = "statement_part_a"strSQLb = "statement_part_b"
strSQLc = "statement_part_c" strSQL = strSQLa & strSQLb & strSQLc

Hope this helps

kstoneman

Apr 9 2012, 07:16 PM

CyberCow,

I'm not sure I follow your coding. I want each value of the string in a separate column, like sh4.Cells(100, 2) = rs3![ASA] ,rs3![OCC] where ASA in column "B" and OCC in column "C".

Your concatenation strSQL would put the results of that string all in one cell

arnelgp

Apr 10 2012, 02:23 AM

You cannot Select a recordset from a recordset.
You can only Select from a table.

But...

You can create a Recordset from a Recordset.

Following your code above.

Dim rs42 As ADODB.RecordSet

set rs42 = CreateRSFromRS( rs3, "Calls_WI_SL1")

' as any recordset you must propertly close rs42 after using
' rs42.Close
' Set rs42 = Nothing

CODE

'****************************************************************************
******
'* Procedure : CreateRSFromRS
'* Author    : Arnel G. Puzon
'* Date      : 10/04/2012
'* Purpose   :
'*
'* Return Value: ADODB.Recordset
'*
'* Parameters:
'*
'*      rs              ADODB.Recordset where you want to extract records
'*      strFieldName    String. Name of the field you want to extract.
'****************************************************************************
******

Function CreateRSFromRS(ByVal rs As ADODB.Recordset, strFieldName As String) As ADODB.Recordset
    Dim rsRet As ADODB.Recordset

   On Error GoTo CreateRSFromRS_Error

    Set rsRet = New ADODB.Recordset
    ' create field
    With rsRet.Fields
        .Append strFieldName, rs.Fields(strFieldName).Type, rs.Fields(strFieldName).DefinedSize
    End With

    ' open recordset
    rsRet.Open

    ' append records
    With rs
        .MoveFirst
        While Not .EOF
            rsRet.AddNew
            rsRet(strFieldName).Value = rs(strFieldName).Value
            rsRet.Update
            .MoveNext
        Wend
    End With

    Set CreateRSFromRS = rsRet

   On Error GoTo 0
   Exit Function

CreateRSFromRS_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateRSFromRS of Module Module6"

End Function

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.