TRANSFORM in SQL Statement

lmonaghan

lmonaghan

Board Regular
Joined
Nov 27, 2004
Messages
84
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:

Code: 
    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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
I get a Run-Time error '1004'. I was trying to modify the data pulled from the following code to create a crosstab query:

Code: 
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.
 
Upvote 0
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?
 
Upvote 0
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.

Code: 
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.



Code: 
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.
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
Why not just use ODBC to link to the AS400 data as the data source for a pivot table?
 
Upvote 0
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.
 
Upvote 0
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:

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Import multiple tables at once via SQL statement ... ?
Replies
7
Views
6K
ralliartur
R
lmonaghan
  • Question Question
Assigning Dynamic Name to Fields in SQL Statement & More
Replies
0
Views
508
lmonaghan
lmonaghan
T
  • Question Question
MS Query/SQL statement for distinct values
Replies
0
Views
2K
Texas Longhorn
T

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top