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!

 

[Fazza]

Lita,

This should be doable. I have successfully used TRANSFORM within Excel; and have kept some scribbled notes too.

How about attacking this first up without the TRANSFORM step just to be sure that the query is working OK? Maybe you've done that already? When that is confirmed, sort out the SQL.

My notes show in summary,

TRANSFORM [data field]
SELECT [row field/s]
FROM [data source]
optional WHERE [criteria]
GROUP BY [row field/s]
PIVOT [column field] optional IN [particular column field/s]

One observation, from my notes and an example I've kept is that the row field/s for the SELECT and GROUP BY are identical. This is not the case in your code. Viz, your SELECT has units.undv# & COUNT(...) and the GROUP BY has only the units.undv#. Without being sure, it looks like the COUNT should not be there. It is a data field not a row field?

HTH
Fazza

 

[lmonaghan]

Thank you for your response! I tried your suggestion of bringing in the data without the TRANSFORM and it is imported just find. I then did an aggregate function and grouping it by two different fields and that worked fine. The last step was to combine the two with the TRANSFORM and it doesn't work.

Here is the succession of queries that I wrote:

    SQL = "SELECT units.ununit, units.unctyt, units.undv# " _
    & "FROM i93file.units " _
    & "WHERE units.undel<>'D' AND units.unflet IN ('01') " _
    & "ORDER BY units.ununit "
    
    SQL2 = "SELECT units.undv#, units.unctyt, count(units.ununit) as count " & _
    "FROM i93file.units " & _
    "WHERE units.undel<>'D' AND units.unflet IN ('01') " & _
    "GROUP BY units.undv#, units.unctyt " & _
    "ORDER BY units.undv# "
    
    SQL3 = "TRANSFORM COUNT(units.ununit) as units " & _
    "SELECT units.undv# " & _
    "FROM i93file.units " & _
    "WHERE units.undel<>'D' AND units.unflet IN ('01') " & _
    "GROUP BY units.undv# " & _
    "PIVOT units.unctyt; "

Any other thoughts?

 

[Fazza]

Well, it must be close, Lita.

Try simplifying it a little more and see if you can get ANY cross-tab result.

Just noticed, why is there a ";" at the end of the query string? What if that is deleted? Fix it?

Anyway, back to the earlier thought, simplify as much as possible. Say to,

    SQL3 = "TRANSFORM COUNT(units.ununit) " & _ 
    "SELECT units.undv# " & _ 
    "FROM i93file.units " & _ 
    "GROUP BY units.undv# " & _ 
    "PIVOT units.unctyt"

That is, have taken out the name change for the count - "AS units" and the whole WHERE clause, and removed the final (superfluous?) punctuation.

Does this simpler TRANSFORM work?

Fazza

 

[Fazza]

Lita,

If a query "doesn't work", please explain exactly what happens or doesn't work or error message received.

thanks,
Fazza

 

[tusharm]

Jumping into the conversation late...

For 95% of the SQL statements I use, I use the UI in Access (or MSQuery) to generate the query. Once that happens, I copy+paste+modify the SQL from that program into VB(A).

In your case, since you can create the desired result in Access, do so. Then, in Access, switch to the SQL view and copy the SQL statement into VB(A).

 

[lmonaghan]

My query returns the following error:
Run-Time error '1004':
Application-defined or object-defined error

I tried your latest suggestion and return the same error. I have also tried copying the SQL in Access and pasting it into Excel VBA with the same result.

It seems that I must be close! I do appreciate your help on the subject. I want to avoid bringing in the data and putting it into a pivot table, but if that is the end result, then so be it. I'd still like to understand why this isn't working though.

Lita

 

[tusharm]

When you write "your latest suggestion" it would help if you mentioned who you are referring to!

Have you established a database connection? Initialized the appropriate variables? As the correct data types?

It's hard to believe a SQL query runs in Access but fails in Excel. After all, SQL is SQL and irrespective of the supporting app, the SQL statement itself cannot yield different results. What you do before it may and what you do after it may but not not the execution of the SQL statement itself (unless, like I asked above, your database connection is different/incorrect).

My query returns the following error:
Run-Time error '1004':
Application-defined or object-defined error

I tried your latest suggestion and return the same error. I have also tried copying the SQL in Access and pasting it into Excel VBA with the same result.

It seems that I must be close! I do appreciate your help on the subject. I want to avoid bringing in the data and putting it into a pivot table, but if that is the end result, then so be it. I'd still like to understand why this isn't working though.

Lita

 

[Fazza]

Lita,

I am concerned there is an overlap of problems here. It seemed like the simpler queries worked and it was only the SQL to fix. I am now unclear if the simpler query still works.

1. Can you please confirm that your code works with your simpler query - not the cross tab.

2. After step 1 works, please test with the simplified cross-tab query & post the results including a print out of the SQL string. Maybe via a debug.print in VBA.

BTW, I am unclear if you tried deleting the final ";" in the earlier posted SQL.

regards,
Fazza

 

[Fazza]

Hi Tushar,

I write SQL either directly into MS Query or create it via code in Excel VBA or sometimes in the VBA immediate window. I had understood that there were subtle differences in syntax between SQL for MS Access and MS Excel so I have not used that route. Can you please comment on any details that need to change for SQL from MS Access to work in Excel?

regards,
Fazza

 

[Fazza]

Hi Lita,

Wondering about the status of this. Would appreciate your advice.

regards,
Fazza