Trying to output the data created by TRANSFORM into a local Access table using VBA?

[kutlesh]

I have this query:

TRANSFORM First (MyData2025.K1) AS FirstOfK1
SELECT
    MyData2025.Id1,
    MyData2025.Id2,
    MyData2025.Id3,
    MyData2025.TableId
FROM
    MyData2025
WHERE
    (((MyData2025.TableId) = "0"))
GROUP BY
    MyData2025.Id1,
    MyData2025.Id2,
    MyData2025.Id3,
    MyData2025.TableId PIVOT MyData2025.RowNum;

Its pivoting the data perfectly, except I want to output the data coming from it into a local table.
But there is a catch, I want the code to be able to run on a button click which means, this needs to be programmed into VBA code.
I have it as a VBA function but it doesn't work.

Public Function PivotirajSSOM(ByVal MyData2025 As String) As String
    Dim sql As String
    
    sql = "select * into PivotedDataTable from pdtc where (pdtc.Id1, pdtc.Id2, pdtc.Id3, pdtc.TableId) in (TRANSFORM First (" &  MyData2025 & ".K1) AS FirstOfK1 "
    
    sql = sql & "SELECT "
    
    sql = sql & "" &  MyData2025 & ".Id1, "
    sql = sql & "" &  MyData2025 & ".Id2, "
    sql = sql & "" &  MyData2025 & ".Id3, "
    sql = sql & "" &  MyData2025 & ".TableId "
    
    sql = sql & "FROM " &  MyData2025 & " "
    
    sql = sql & "WHERE " &  MyData2025 & ".TableId = ""0"" "
    
    sql = sql & "Group BY "
    
    sql = sql & "" &  MyData2025 & ".Id1, "
    sql = sql & "" &  MyData2025 & ".Id2, "
    sql = sql & "" &  MyData2025 & ".Id3, "
    sql = sql & "" &  MyData2025 & ".TableId PIVOT " &  MyData2025 & ".RowNum) as pdtc "
    
    PivotirajSSOM = sql
End Function

' Then it would be called like so
DoCmd.RunSQL PivotirajSSOM(MyData2025 )

I am not sure of the syntax for this as I tried few things and it didn't work.

How can I output date from a Crosstab query into a local table using VBA code?

[Zvoni]

This looks convoluted.
Why are you changing the logic?
It doesn't make sense to alias your actual Select statement, just to check in your SELECT INTO if it exists in your actual SELECT

select * into PivotedDataTable from (
TRANSFORM First (MyData2025.K1) AS FirstOfK1
SELECT
    MyData2025.Id1,
    MyData2025.Id2,
    MyData2025.Id3,
    MyData2025.TableId
FROM
    MyData2025
WHERE
    MyData2025.TableId = "0"
GROUP BY
    MyData2025.Id1,
    MyData2025.Id2,
    MyData2025.Id3,
    MyData2025.TableId PIVOT MyData2025.RowNum)
;

EDIT: And TableID is really a String?

[kutlesh]

Well i tried "select into from" syntax in the internal select statement. It didn't work!

TRANSFORM First (MyData2025.K1) AS FirstOfK1
SELECT
    MyData2025.Id1,
    MyData2025.Id2,
    MyData2025.Id3,
    MyData2025.TableId
into PivotedDataTable 
FROM
    MyData2025
WHERE
    MyData2025.TableId = "0"
GROUP BY
    MyData2025.Id1,
    MyData2025.Id2,
    MyData2025.Id3,
    MyData2025.TableId PIVOT MyData2025.RowNum

[Zvoni]

This is making even less sense.
Have you tried the SELECT INTO directly in Access?

[kutlesh]

It works when using the Crosstab query inside another query but with drag and drop.
Not sure what changes when you recode the query using text and VBA...

[Zvoni]

Just saw something

sql = sql & "" & MyData2025 & ".Id1, "
sql = sql & "" & MyData2025 & ".Id2, "
sql = sql & "" & MyData2025 & ".Id3, "
sql = sql & "" & MyData2025 & ".TAB PIVOT " & MyData2025 & ".RowNum) as pdtc "

PivotirajSSOM = sql

Is this a Typo??

[kutlesh]

Sorry, yes, it was a typo, but it is wrong here in the page, not in my code.
Should be TableId.

[Zvoni]

OK, something else:

' Then it would be called like so
DoCmd.RunSQL PivotirajSSOM(MyData2025 )

Are you sure??

Public Function PivotirajSSOM(ByVal MyData2025 As String) As String

expects a String as a Parameter.
Your example "how it would be called" implies that MyData2025 is a (String-Variable) outside.
What's the Value of MyData2025 before you call your Sub?

and is this really correct, that that Parameter contains the TABLE-Name from which to do the Pivot?

IN a nutshell: Check the Returnvalue of
"PivotirajSSOM(MyData2025 )" BEFORE Executing it

Dim s  As String
' Then it would be called like so
s = PivotirajSSOM(MyData2025)
Debug.Print s
DoCmd.RunSQL s

[kutlesh]

Yea forgot to note that. The function parameter is the name of the local table containing the data.

[Zvoni]

Well, what's the Debug-Output of the query?
I still think you have a syntax error somewhere