Query output using Transform function

[Tom Norton]

Using syntax below I am unable to generate a table with years in columns and selected categories in rows. Currently the query output renders all items, both the selected categories as well as years as columns. Maybe there is another way besides the TRANSFORM function to output the preferred table. Any insight would be appreciated.

Crosstab query for Company “ABCD”



TRANSFORM [Company Review Log_2021_ABCD].[Fiscal Year]

SELECT [Company Review Log_2021_ABCD].Goodwill, [Company Review Log_2021_ABCD].[Intangible Assets], [Company Review Log_2021_ABCD].[Current Assets], [Company Review Log_2021_ABCD].[Current Liabilities]

FROM [Company Review Log_2021_ABCD]

GROUP BY [Company Review Log_2021_ABCD].Goodwill, [Company Review Log_2021_ABCD].[Intangible Assets], [Company Review Log_2021_ABCD].[Current Assets], [Company Review Log_2021_ABCD].[Current Liabilities]

PIVOT Format ([Fiscal Year], "yyyy")

IN ("2017". "2018", "2019");

_____________________________________________________________

Where preferred table output will read this way:

Financial Category	2017	2018	2019
Goodwill
Intangible Assets
Current Assets
Current Liabilities

 

[Micron]

Cannot tell from the info which are fields and which are data so no suggestion there. You must choose a row, field and value but you seem to not be showing what the Value part of the query would be. I think the first part ought to be something like

TRANSFORM Count([tbl1].[Field1]) As CountOfField1 - or Sum, Avg, Min, Max, etc if not Count.
Did you use the query builder to make that? I can't replicate it because it errors with no Value specification.

EDIT - so of course, the left column bottom 4 values are field names as they're referenced as

.[field] in your sql. That leaves not knowing what Financial Category is. I take it then that the years are values, but no idea from what table or field. You might have to post some actual data to get help with this.

 

[Tom Norton]

Using syntax below I am unable to generate a table with years in columns and selected categories in rows. Currently the query output renders all items, both the selected categories as well as years as columns. Maybe there is another way besides the TRANSFORM function to output the preferred table. Any insight would be appreciated.

Crosstab query for Company “ABCD”



TRANSFORM [Company Review Log_2021_ABCD].[Fiscal Year]

SELECT [Company Review Log_2021_ABCD].Goodwill, [Company Review Log_2021_ABCD].[Intangible Assets], [Company Review Log_2021_ABCD].[Current Assets], [Company Review Log_2021_ABCD].[Current Liabilities]

FROM [Company Review Log_2021_ABCD]

GROUP BY [Company Review Log_2021_ABCD].Goodwill, [Company Review Log_2021_ABCD].[Intangible Assets], [Company Review Log_2021_ABCD].[Current Assets], [Company Review Log_2021_ABCD].[Current Liabilities]

PIVOT Format ([Fiscal Year], "yyyy")

IN ("2017". "2018", "2019");

_____________________________________________________________

Where preferred table output will read this way:

Financial Category	2017	2018	2019
Goodwill
Intangible Assets
Current Assets
Current Liabilities

Thank you for following with suggestions.

 

[Tom Norton]

Based on member suggestion here is syntax and output (Access query). TRANSFORM function may not have an aggregate option that applies in this case(?) ISSUE: how to have query output Crosstab with Goodwill, Intangible Assets, etc. formatted as rows, with years as columns.

TRANSFORM COUNT([Company Review Log_2021_ABCD].[Fiscal Year]) AS FinalResults

SELECT [Company Review Log_2021_ABCD].Goodwill, [Company Review Log_2021_ABCD].[Intangible Assets], [Company Review Log_2021_ABCD].[Current Assets], [Company Review Log_2021_ABCD].[Current Liabilities]

FROM [Company Review Log_2021_ABCD]

GROUP BY ([Company Review Log_2021_ABCD].Goodwill), ([Company Review Log_2021_ABCD].[Intangible Assets]), ([Company Review Log_2021_ABCD].[Current Assets]), ([Company Review Log_2021_ABCD].[Current Liabilities])

PIVOT Format([Company Review Log_2021_ABCD].[Fiscal Year], "yyyy")

IN ("2017", "2018", "2019");

Query4​
Goodwill​	Intangible Assets​	Current Assets​	Current Liabilities​	2017​	2018​	2019​
14,700.00​	7,400.00​	37,600.0​	13,488.0​
14,700.00​	8,600.00​	49,476.0​	9,020.0​
14,700.00​	19,400.00​	18,400.0​	12,800.0​

 

[Micron]

I'm guessing that is not actual data that I suggested you post, rather it is the output of your query that isn't doing what you want.

If your table does/will contain a new field for every year, my suggestion would be to abandon that idea now and fix your table structure. It is a bad idea to build db tables like spreadsheets, especially if it means having to add fields of similar data. This would mean you'd need to research db normalization.