Could you please help me understand below Query

[BizBoy]

Hi,

I have below query in my code. Am new to MS Access hence would request you to pardon my ignorance.
Could you please help me understand below query.

A short background of issue am facing.
This code is written in old version of MS Access.


Am required to modify the code according to Office 365.

I believe Pivot table is not supported in Office 365 hence I need to change code related to Pivot.
However am not able to understand below query.

Only thing I could understand was, this is a inner join query and some sorting is applied.
Can anyone please help me in this.

Dim MyString As String
MyString = "TRANSFORM First(Table_1.Field_1) AS MyNAME" & _
" SELECT Table_1.Field_2, Table_1.Field_3, Table_1.Field_4, Table_1.Field_5, Table_1.Field_6, Table_1.Field_7" & _
" FROM Table_1 INNER JOIN MyExternalTable ON Table_1.Field_1 = MyExternalTable.ABC_DD_ABC" & _
" GROUP BY Table_1.Field_2, Table_1.Field_3, Table_1.Field_4, Table_1.Field_5, Table_1.Field_6, Table_1.Field_7" & _
" ORDER BY Table_1.Field_2, Table_1.Field_3, Table_1.Field_4, Table_1.Field_5, Table_1.Field_6, Table_1.Field_7" & _
" PIVOT Table_1.Field_8 In (0,1,2,3,4,5,6,7,8,9,10);"

[ranman256]

It is a crosstab query (similar to an excel pivot).
The old crosstabs still work in the new versions of Access. (depending on your table design)
It shows a matrix with MyName as the center values.

It may be easier to just use the query wizard to reconstruct it.

[BizBoy]

Hi ranman sir, thanks a lot for the help.
Would not trouble you for exact solution.

If I understand correctly, though the original macro is in 2010 version, this code should work in Office 365.
Or we need to use query wizard to reconstruct. But macro would not get stuck despite of version change.

If you get time, could you please tell me how to check table design or types of design.
Links will also help.

Have a nice day ahead.

[June7]

Why do you say it is not supported? Does the code error?

[BizBoy]

Hi June sir,
Thanks for the help.
I did not get chance to run the code personally, there are some user access related restrictions.
Am informed that Pivot table won’t work in Office365 hence I need to work on codes related to pivot table.
This is the reason I posted query.
Have a nice day ahead.

[June7]

I can't find anything anywhere that says crosstab will not work in Office365 Access.

[BizBoy]

Thanks for the help June sir.
Since the 'Pivot' was mentioned in the query, I was worried.

To be honest, I was not aware that this type of queries are crosstab queries and pivot is used in these.

Have a nice day ahead.

[June7]

Ability to set tables and forms in Pivot View was removed with Access 2013. But crosstab query should still be possible.