TRANSFORM in SQL Statement

[lmonaghan]

I searched the forums and I tried the advice from one string which did not work like a charm. Please help in assigning the problem with the following statement:

    SQL = "TRANSFORM SUM(LOAD.DITMIL) AS SUMOFDITMIL " _
        & "SELECT LOAD.DIUNIT, LOADEXT.DIDV# " _
        & "FROM I93FILE.LOAD, I93FILE.LOADEXT " _
        & "WHERE LOAD.DIODR# = LOADEXT.DEODR# AND LOAD.DIDISP = LOADEXT.DEDISP " _
        & " AND LOAD.DIDATE BETWEEN 85261 AND 85267 " _
        & " AND LOAD.DIMULT<>'S' " _
        & " AND LOAD.DICONT IN ('0','1') " _
        & " AND LOADEXT.DIDV#<>'LOG' " _
        & "GROUP BY LOAD.DIUNIT, LOADEXT.DIDV# " _
        & "ORDER BY LOAD.DIUNIT " _
        & "PIVOT LOAD.DIDATE; "

Thanks in advance for your help!

 

[Norie]

What's the actual problem?

 

[lmonaghan]

I get a Run-Time error '1004'. I was trying to modify the data pulled from the following code to create a crosstab query:

Sub UNITS()

    Dim SQL As String

    SQL = "SELECT LOAD.DIUNIT, LOADEXT.DIDV#, SUM(LOAD.DITMIL) AS TOTAL " _
        & "FROM I93FILE.LOAD, I93FILE.LOADEXT " _
        & "WHERE LOAD.DIODR# = LOADEXT.DEODR# AND LOAD.DIDISP = LOADEXT.DEDISP " _
        & " AND LOAD.DIDATE BETWEEN 85261 AND 85267 " _
        & " AND LOAD.DIMULT<>'S' " _
        & " AND LOAD.DICONT IN ('0','1') " _
        & " AND LOADEXT.DIDV#<>'LOG' " _
        & "GROUP BY LOAD.DIUNIT, LOADEXT.DIDV# " _
        & "ORDER BY LOAD.DIUNIT "
        
    Debug.Print SQL
    
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=I93;" _
        , Destination:=Range("A1"))
        .CommandText = SQL
        .Name = "query1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

I have never tried the TRANSFORM statement before and am wondering what I'm doing wrong. I can get it to work just fine in Access, but can't use that application because no one else in the company uses it.

 

[Norie]

Which line causes the error?

Does the SQL statement work in Access?

Why not just use a pivot table with Access as the external data source?

 

[lmonaghan]

When I F8 through all of the lines, it begins to run after the .Refresh BackgroundQuery:=False and after a few seconds, returns the error message.

Sub UNITS()

    Dim SQL As String

    SQL = "TRANSFORM SUM(LOAD.DITMIL) AS SUMOFDITMIL " _
        & "SELECT LOAD.DIUNIT, LOADEXT.DIDV# " _
        & "FROM I93FILE.LOAD, I93FILE.LOADEXT " _
        & "WHERE LOAD.DIODR# = LOADEXT.DEODR# AND LOAD.DIDISP = LOADEXT.DEDISP " _
        & " AND LOAD.DIDATE BETWEEN 85261 AND 85267 " _
        & " AND LOAD.DIMULT<>'S' " _
        & " AND LOAD.DICONT IN ('0','1') " _
        & " AND LOADEXT.DIDV#<>'LOG' " _
        & "GROUP BY LOAD.DIUNIT, LOADEXT.DIDV# " _
        & "ORDER BY LOAD.DIUNIT " _
        & "PIVOT LOAD.DIDATE; "
        
    Debug.Print SQL
    
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=I93;" _
        , Destination:=Range("A1"))
        .CommandText = SQL
        .Name = "query1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

In Access I just built a crosstab query and tried to modify as shown before to work in VBA.

TRANSFORM Sum(I93FILE_LOAD.DITMIL) AS SumOfDITMIL
SELECT I93FILE_LOAD.DIUNIT, I93FILE_LOADEXT.[DIDV#]
FROM I93FILE_LOAD INNER JOIN I93FILE_LOADEXT ON (I93FILE_LOAD.DIDISP = I93FILE_LOADEXT.DEDISP) AND (I93FILE_LOAD.[DIODR#] = I93FILE_LOADEXT.[DEODR#])
WHERE (((I93FILE_LOADEXT.[DIDV#])<>"LOG") AND ((I93FILE_LOAD.DIDATE) Between 85261 And 85267) AND ((I93FILE_LOAD.DIMULT)<>"S") AND ((I93FILE_LOAD.DICONT)="1" Or (I93FILE_LOAD.DICONT)="0"))
GROUP BY I93FILE_LOAD.DIUNIT, I93FILE_LOADEXT.[DIDV#]
PIVOT I93FILE_LOAD.DIDATE;

Access isn't the data source and I have to figure out how to do this without using Access at all.

 

[Norie]

So what is the data source?

Like I said can't you use a pivot table in Excel with whatever the data source is?

A crosstab query in Access is the equivalent of a pivot table in Excel.

In fact a pivot table in Excel has more functionality than a crosstab query in Access.

 

[lmonaghan]

I don't know all of the official database lingo and such, but I access files in the AS/400. I use an ODBC to access the files.

I don't need any drill down functionality of an Excel pivot table. I would like the data to be summarized when it is imported because I don't want to store 10K+ records and then have to build a pivot table on top of that.

Most people in the company don't even know what a pivot table is, let alone know how to use one. If I can send the data to them in a crosstab format, it will be much easier for everyone involved. I can't do this in Access and export it because if this file has to be transferred to someone else in the company, they will not have that application. Everyone has Excel however,

 

[Norie]

Why not just use ODBC to link to the AS400 data as the data source for a pivot table?

 

[lmonaghan]

I do link to the AS400 as the data source through an ODBC. Perhaps I don't follow your question? Are you saying to dump the data into Excel and refresh a pivot table? I can do that, but I would like to figure out why my code isn't working to create the pivot table when I import the data initially.

 

[lmonaghan]

Similar Issue, Different Query

I never did get resolution/answer on this topic as to how to get TRANSFORM to work properly in VBA within an SQL statement. I still need to figure out why this isn't working and am attempting to do it with a much simpler query. Wasn't sure if I should post a new string or continue this one...

I am using Excel 2003 and Microsoft Visual Basic 6.3 to write a query. I connect to a datasource through an ODBC (AS/400). I can write a simple SELECT statement to bring in records, but can't seem to figure out how to structure the data in crosstab/pivot form for summary information only.

I have tried the suggestion found in the following post to no avail and without the Chr(10) in the string:

http://www.mrexcel.com/board2/viewtopic.php?t=70152

Here is the query I am trying to write:

Sub WPR_TRKS()

Dim SQL As String
         
    SQL = "TRANSFORM COUNT(units.ununit) as units" & Chr(10) & _
    "SELECT units.undv#, COUNT(units.ununit) as TOT_UNITS" & Chr(10) & _
    "FROM i93file.units" & Chr(10) & _
    "WHERE units.undel<>'D' AND units.unflet IN ('01')" & Chr(10) & _
    "GROUP BY units.undv#" & Chr(10) & _
    "PIVOT units.unctyt;"
         
    Debug.Print SQL
    
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=I93;" _
        , Destination:=Range("A1"))
        .CommandText = SQL
        .Name = "WPR_TRKS"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Any suggestions???

TIA,
Lita