Financial Form

[Luke]

Ok I'm gonna give it a try I just have a little bit of editing to do first. Thanks a ton I'll let you know how it goes.

[Luke]

When you say do a crosstab query using:
TRANSFORM Sum(qryFinancialInfo.Amount) AS SumOfAmount
SELECT qryFinancialInfo.Category, Sum(qryFinancialInfo.Amount) AS [TotalOfAmount]
FROM qryFinancialInfo
GROUP BY qryFinancialInfo.Category
PIVOT Format([Date],"yyyy");

Don't you mean a union query???

[Luke]

Also, I changed Gross Margin to GrossMargin in my Table as well as my SQL Union Query. Then I tried to run a union query with the info you posted and it says "reserved error (-5500), there is no message for this error". Should I undo my changes to the Gross Margin? Thanks

[June7]

No, TRANSFORM is a crosstab query. The queries I posted work with the table you provided (populated it with data). Create the UNION and then use it in the Crosstab.

Changing the field name should not be an issue. Just make sure the queries reflect that change. I made the change without issue.

[Luke]

Ok I'm having trouble understanding the TRANSFORM Crosstab query. I have put data in my financial info table. I have the UNION query that contains this:

SELECT "Revenue" As Category, [Date], Revenue As Amount
FROM [Financial Info]
UNION SELECT "GrossMargin", [Date], GrossMargin
FROM [Financial Info]
UNION SELECT "EBITDA", [Date], EBITDA
FROM [Financial Info];

So am I supposed to make another UNION query and do a crosstab between the 2 unions? Or just another crosstab query with the info you gave me? I'm confused on where I should enter this data:

TRANSFORM Sum(qryFinancialInfo.Amount) AS SumOfAmount
SELECT qryFinancialInfo.Category, Sum(qryFinancialInfo.Amount) AS [TotalOfAmount]
FROM qryFinancialInfo
GROUP BY qryFinancialInfo.Category
PIVOT Format([Date],"yyyy");

[June7]

Review post #15 again.

[Luke]

I've reviewed post #15 I'm just having trouble understanding how to make the crosstab query. Do I use the query wizard and query design? I'm confused as to where I'm typing this in: TRANSFORM Sum(qryFinancialInfo.[Amount]) AS SumOfAmount
SELECT qryFinancialInfo.[Entity], qryFinancialInfo.[Category], Sum(qryFinancialInfo.[Amount]) AS [Total Of Amount]
FROM qryFinancialInfo
GROUP BY qryFinancialInfo.[Entity], qryFinancialInfo.[Category]
PIVOT Format([Date],"yyyy");

Do I type this in the criteria of query design view?

[June7]

You could use the query wizard for crosstab or you can open query designer in SQL View and copy/paste the SQL statement from the post into the editor. Then switch to Design View and see how it is structured in the grid

Be sure to use the revised Union query I show in post #15. Think I was editing as you posted subsequent post and that is why I suggested you review it again, if you haven't.

[Luke]

It's starting to come together. My problem now is that when I try to run the query, it says it doesn't recognize "Date" as a valid field name/expression and it also says there's a syntax error in TRANSFORM statement.