Generating a new table via crosstab query - can someone see my mistake? (1 Viewer)

[REvans]

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

 

[plog]

I know there are some replies along the lines of "why are you doing this when it changes each time" and similar.

Here's my similar: why are you doing this when it goes against normalization? Storing data and reporting on data are completely seperate operations. You should always store data appropriately. Table fields shouldn't be named after data.

As for your issue, divide and conquer, you're trying to do everything all at once. Make a cross-tab query first. The designer will help you. Get it looking like you want. Then, save it. Then make another query based upon that query and do the MAKE TABLE.

 

[plog]

Sorry, initially missed the part where you did make a working pivot. I would do a MAKE TABLE not a SELECT INTO

 

[REvans]

Hello Plog, thanks for replying so quickly.
The thing is, the code is run from VBA inside Excel. I know how to run single SQL code executions from inside that environment, but not saving a query and incorporating that in a subsequent step. But maybe something to investigate. Thanks again. R.

 

[REvans]

Sorry, crossed reply. I didn't think of MAKE TABLE.

 

[REvans]

I may have missed something- I though Select Into was the way to make a table.

 

[arnelgp]

you need to Save your Crosstab query first,
then Create a New query (Create table) that
uses the saved crosstab query.

Select * Into newTable From qryCrosstab;

 

[isladogs]

Agree with @arnelgp.
Indeed that point was stressed in the original thread from 2017

 

[REvans]

I've followed that up using ADOX inside Excel vba to create the qryCrosstab. So the code makes and saves the query, then runs the "Select * Into newTable From qryCrosstab;" type command.
It works too! Thank you arnelgp and isladogs for the part I had missed, and plog for the immediate reply.
I knew this was the correct forum to ask.