Insert the output of TRANSFORM into another table

[ria.arora]

Hi

I want insert the result of below query in one table, any idea how to achieve this?

TRANSFORM Last(Nz(RevenueInUSD,0)) AS WKRevenueInUSD
SELECT [Banker]
FROM tbl_TB INNER JOIN tbl_Weekly_Calendar ON tbl_TB.ValBookDate=tbl_Weekly_Calendar.ReportingDate
WHERE [BU]='Asia' And Year([ValBookDate])='2012' And ([Week Number]>20 And [Week Number]<26)
GROUP BY [Banker]
PIVOT [Week Number]

Insert into does not work

INSERT INTO tmp_Weekly_Dashboard_Data ([Private Banker], [Week1], [Week2], [Week3], [Week4], [Week5])
TRANSFORM Last(Nz(RevenueInUSD,0)) AS WKRevenueInUSD
SELECT [Banker]
FROM tbl_TB INNER JOIN tbl_Weekly_Calendar ON tbl_TB.ValBookDate=tbl_Weekly_Calendar.ReportingDate
WHERE [BU]='Asia' And Year([ValBookDate])='2012' And ([Week Number]>20 And [Week Number]<26)
GROUP BY [Banker]
PIVOT [Week Number]

 

[Isskint]

Use a VBA routine to create a recordset copy and step through each record in the query and write it to your new table

 

[ria.arora]

Thanks Isskint. I'm new to Access VBA Can you pleae give me a sample code for this.

Appreciate your help

 

[ria.arora]

I have managed to do that using following code

Private Sub ProcessWeeklyData()
    On Error GoTo ErrHandler
    
    Dim rsTmp As DAO.Recordset
    Dim strInsertSQL As String
    Dim strSelectSQL As String
    Dim fName As Field
    Dim qryWklyDashboardData As String
    Dim strField1, strField2, strField3, strField4, strField5, strField6 As String
    
    qryWklyDashboardData = "qryWeeklyDashboardData"
    Set rsTmp = gsPnPDatabase.OpenRecordset(qryWklyDashboardData)

    For Each fName In rsTmp.Fields
        If fName.OrdinalPosition = 0 Then
            strField1 = fName.Name
        ElseIf fName.OrdinalPosition = 1 Then
            strField2 = fName.Name
        ElseIf fName.OrdinalPosition = 2 Then
            strField3 = fName.Name
        ElseIf fName.OrdinalPosition = 3 Then
            strField4 = fName.Name
        ElseIf fName.OrdinalPosition = 4 Then
            strField5 = fName.Name
        ElseIf fName.OrdinalPosition = 5 Then
            strField6 = fName.Name
        End If
    Next
    rsTmp.Close
   
    strSelectSQL = "SELECT [" & strField1 & "], NZ([" & strField2 & "],0), NZ([" & strField3 & "],0), NZ([" & strField4 & "],0), NZ([" & strField5 & "],0), NZ([" & strField6 & "],0)" & _
                    " FROM qryWeeklyDashboardData "
    'Insert data into tbl_Users TABLE in MS Access Database
    Set rsTmp = gsPnPDatabase.OpenRecordset(strSelectSQL)
    
    Do While Not rsTmp.EOF
        'Insert into tbl_Users in differnt database's table
        strInsertSQL = "INSERT INTO tmp_Weekly_Dashboard_Data" & _
                        " ([Private Banker], [Week1], [Week2], [Week3], [Week4], [Week5], [YearMonthWeek], [Comment]) " & _
                        "VALUES" & _
                        " ('" & rsTmp.Fields(0) & "', " & rsTmp.Fields(1) & ", " & rsTmp.Fields(2) & ", " & _
                        " " & rsTmp.Fields(3) & ", " & rsTmp.Fields(4) & ", " & rsTmp.Fields(5) & ", '" & strYearMthWk & "', ""Weekly"" )"
        gsPnPDatabase.Execute (strInsertSQL)
        rsTmp.MoveNext
    Loop
    
ExitHandler:
    DoCmd.Hourglass (False)
    Exit Sub
ErrHandler:
    MsgBox "Error detected, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
    lblStatus.Caption = Err.Description
    DoCmd.Hourglass (False)
    varStatus = SysCmd(acSysCmdClearStatus)
    Resume ExitHandler
End Sub

I got another issue over here. Data is coming from qryWeeklyDashboardData (which is predefined query in MS Access Database). Is there anyway that I can pass 3 parameters (which are hardcoded in query currently) to the query? Please help...

TRANSFORM Last(Nz(RevenueInUSD,0)) AS WKRevenueInUSD
SELECT [Private Banker]
FROM tbl_TBook INNER JOIN tbl_Weekly_Calendar ON tbl_TBook.ValBookDate=tbl_Weekly_Calendar.ReportingDate
WHERE [BU]='Asia' And Year([ValBookDate])='2012' And ([Week Number]>20 And [Week Number]<26)
GROUP BY [Private Banker]
PIVOT [Week Number];

 

[Isskint]

first, good bit of code you figured out, nice

There are a couple of ways to supply parameters to a query without actually typing them in. Option 1 is to use a form to supply the parameters. Just substitute the current parameter values in the query with form!ControlName. Option 2 is to rewrite the query on the fly through VBA. Much as you did for inserting the TRANSFORM output. Something like;

Public Sub ChangeQrySQL()

On Error GoTo ChangeQrySQLErr

Dim qry As QueryDef
Dim strSQL As String

Set qry = CurrentDb.QueryDefs("Transactions Query")
strSQL = "your sql here"

DoCmd.SetWarnings False
qry.SQL = strSQL

ChangeQrySQLExit:
DoCmd.SetWarnings True
Exit Sub

ChangeQrySQLErr:
MsgBox Err.Number & vbLf & Err.Description, vbInformation, "An error has occured"
GoTo ChangeQrySQLExit
End Sub