Financial analysis reports 1

[nifty001]

I have a table that houses financial statements for a given company by year and I need to display the information and add analysis information in a report format comparing year to year to year. For example:

1998 1999 2000
Income $10 $15 $22
Exspenses $8 $9 $10
Net Income $2 $6 $12

Any suggestions?

 

[rac2]

What does the financial statements table look like?

Assume

Category Year Amount
Income 1998 10
Income 1999 15
Income 2000 22
Expenses 1998 8
Expenses 1999 9
Expenses 2000 10
Net Income 1998 2
Net Income 1999 6
Net Income 2000 12

Then -

SELECT Category, a.Amount AS "1998", b.Amount AS "1999",  c.Amount AS "2000"
FROM Financials a
JOIN Financials b ON a.Category = b.Category
JOIN Financials c ON a.Category = c.Category
WHERE a.Year = '1998'
  AND b.Year = '1999'
  AND c.Year = '2000'

 

[zerosandones]

Using the following tblFinancials table:

Category Year Amount
------------ ----- ------
Income 1998 10.00
Income 1999 15.00
Income 2000 22.00
Expenses 1998 8.00
Expenses 1999 9.00
Expenses 2000 10.00
Net Income 1998 2.00
Net Income 1999 6.00
Net Income 2000 12.00

You can achieve your goal by using a Cross-tab query as follows:

TRANSFORM Sum(tblFinancials.Amount) AS SumOfAmount
SELECT tblFinancials.Category
FROM tblFinancials
GROUP BY Switch([Category]="Income",1,[Category]="Expenses",2,True,3), tblFinancials.Category
ORDER BY Switch([Category]="Income",1,[Category]="Expenses",2,True,3), tblFinancials.Year
PIVOT tblFinancials.Year;

Special Note: You'll notice the use of the Switch statement which I have used here to control the row order of the returned results.

Hope this helps.

 

[nifty001]

Thanks