Hello,
There are a few posts about this, both on this and other forums. As the ones on here were the best ones I could find, I thought I would ask on here.
The answer seems to be to use a make-table query (select ...into..from) with the crosstab query (transform ...) and it all works fine.
I've not found many examples of the actual code except an excellent one on here. So I copied that, and tried lots of things, and no result.
Here's one of the threads I looked at on here- https://www.access-programmers.co.u...rating-a-new-table-via-crosstab-query.297099/. It gives a complete example which I thought I was copying.
Below is the source table. the pivot makes all the distinct items in Des Data Source become columns.
I tried it originally in vba in Excel running Access via ADODB. I've now tried in Access itself, using the SQL view in the query window. Results are the same.
This is the crosstab query itself below. This works just right.
TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value]) AS Bungo SELECT [B--GraphDSRowinPeriod].[Des DateTime] FROM [B--GraphDSRowinPeriod] GROUP BY [B--GraphDSRowinPeriod].[Des DateTime] PIVOT [B--GraphDSRowinPeriod].[Des Data Source]
This works too. I found something like this on this forum. (This has no crosstab, just here to illustrate syntax of first part.)
SELECT * INTO [C--GraphDSRowinPeriod-Conv] FROM [B--GraphDSRowinPeriod];
So I have combined these like this, and with countless variations in number of brackets, semicolons at the end, and so on.
SELECT TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value]) AS Bungo SELECT [B--GraphDSRowinPeriod].[Des DateTime] FROM [B--GraphDSRowinPeriod] GROUP BY [B--GraphDSRowinPeriod].[Des DateTime] PIVOT [B--GraphDSRowinPeriod].[Des Data Source].* INTO [C--GraphDSRowinPeriod-Conv] FROM TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value]) AS Bungo SELECT [B--GraphDSRowinPeriod].[Des DateTime] FROM [B--GraphDSRowinPeriod] GROUP BY [B--GraphDSRowinPeriod].[Des DateTime] PIVOT [B--GraphDSRowinPeriod].[Des Data Source];
With the above in Access I get-
Syntax error (missing operator) in query expression "TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value])".
If I try this-
SELECT * INTO [C--GraphDSRowinPeriod-Conv] FROM TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value]) AS Bungo SELECT [B--GraphDSRowinPeriod].[Des DateTime] FROM [B--GraphDSRowinPeriod] GROUP BY [B--GraphDSRowinPeriod].[Des DateTime] PIVOT [B--GraphDSRowinPeriod].[Des Data Source];
I get-
Syntax error in FROM clause
I know there are some replies along the lines of "why are you doing this when it changes each time" and similar. In this case this table is an intermediary in a Excel graph plotting from data loaded into Excel by previous parts of the vba code. It needs to stay in Access as a table, then get more operations based on the columns (hence the pivot/transform of the table) and then gets output to Excel after that. I could give up and load into Excel and then out again (the remaining operations are not simple in Excel) but if some people on here have managed to do this there must be a way.
Any ideas?
I understand that you are busy- if you have some working code similar enough that may give me another avenue to try that would be welcome. My Access is 2010.
All the best and thanks for reading this post,
Rog
There are a few posts about this, both on this and other forums. As the ones on here were the best ones I could find, I thought I would ask on here.
The answer seems to be to use a make-table query (select ...into..from) with the crosstab query (transform ...) and it all works fine.
I've not found many examples of the actual code except an excellent one on here. So I copied that, and tried lots of things, and no result.
Here's one of the threads I looked at on here- https://www.access-programmers.co.u...rating-a-new-table-via-crosstab-query.297099/. It gives a complete example which I thought I was copying.
Below is the source table. the pivot makes all the distinct items in Des Data Source become columns.
I tried it originally in vba in Excel running Access via ADODB. I've now tried in Access itself, using the SQL view in the query window. Results are the same.
This is the crosstab query itself below. This works just right.
TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value]) AS Bungo SELECT [B--GraphDSRowinPeriod].[Des DateTime] FROM [B--GraphDSRowinPeriod] GROUP BY [B--GraphDSRowinPeriod].[Des DateTime] PIVOT [B--GraphDSRowinPeriod].[Des Data Source]
This works too. I found something like this on this forum. (This has no crosstab, just here to illustrate syntax of first part.)
SELECT * INTO [C--GraphDSRowinPeriod-Conv] FROM [B--GraphDSRowinPeriod];
So I have combined these like this, and with countless variations in number of brackets, semicolons at the end, and so on.
SELECT TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value]) AS Bungo SELECT [B--GraphDSRowinPeriod].[Des DateTime] FROM [B--GraphDSRowinPeriod] GROUP BY [B--GraphDSRowinPeriod].[Des DateTime] PIVOT [B--GraphDSRowinPeriod].[Des Data Source].* INTO [C--GraphDSRowinPeriod-Conv] FROM TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value]) AS Bungo SELECT [B--GraphDSRowinPeriod].[Des DateTime] FROM [B--GraphDSRowinPeriod] GROUP BY [B--GraphDSRowinPeriod].[Des DateTime] PIVOT [B--GraphDSRowinPeriod].[Des Data Source];
With the above in Access I get-
Syntax error (missing operator) in query expression "TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value])".
If I try this-
SELECT * INTO [C--GraphDSRowinPeriod-Conv] FROM TRANSFORM SUM([B--GraphDSRowinPeriod].[Des Value]) AS Bungo SELECT [B--GraphDSRowinPeriod].[Des DateTime] FROM [B--GraphDSRowinPeriod] GROUP BY [B--GraphDSRowinPeriod].[Des DateTime] PIVOT [B--GraphDSRowinPeriod].[Des Data Source];
I get-
Syntax error in FROM clause
I know there are some replies along the lines of "why are you doing this when it changes each time" and similar. In this case this table is an intermediary in a Excel graph plotting from data loaded into Excel by previous parts of the vba code. It needs to stay in Access as a table, then get more operations based on the columns (hence the pivot/transform of the table) and then gets output to Excel after that. I could give up and load into Excel and then out again (the remaining operations are not simple in Excel) but if some people on here have managed to do this there must be a way.
Any ideas?
I understand that you are busy- if you have some working code similar enough that may give me another avenue to try that would be welcome. My Access is 2010.
All the best and thanks for reading this post,
Rog