Is it possible to generate and save summary reports in Access

[AVT]

Hi all,

I have a made a huge database in excel but since the size has increased too much I am planning to migrate it into Access.
So far I have learnt how to make basic database in Access but I understand calculated fields can't/should not be made and saved in Access.

Lets say I have data for 2 years in separate tables as follows:

Table2011

Company Net Sales PAT
A 1000 100
B 2000 150
C 3000 120

Table2010

Company Net Sales PAT
A 800 90
B 2200 180
C 2500 90



In excel I was able to make a summary tables as follows

Summary Table1

Company Sales growth PAT growth
A 25% 11%
B -10% -15%
C 20% -25%


Summary Table 2 2011 2010
Aggregate Sales (A + B + C) 6000 5500
Aggregate PAT (A + B + C) 370 360
Sales growth 10%
PAT growth 1%

I need these tables for my further VBA programming.
1. Can I make and save such tables in Access?
2. Is there any advantage in moving to Access or should I continue using Excel for the same?

[jzwp11]

Welcome to the forum!

Access is much different from Excel and will require a different way of thinking about and organizing your data.

You would not have data split into two tables by year in Access or any relational database. The rule is that like data should be in the same table. Here are more rules that you should be aware of when it comes to relational databases.

You will need to include a date field or a year field in the table.

tblSales
-pkSaleID primary key, autonumber
-txtCompany
-currNetSales
-PAT
-datefield


With this design you can use a query to separate each year's data. You would base your report on the query.

[mahmudd]

How do i do an aggregate of a calculated column which contains an iif function?

[jzwp11]

Why would you need an IIF() function? You would just create an aggregate query that groups on the year and sums the value. If you want the values for each customer for each year, then you would group by customer within year.

You can aggregate a query field that uses a calculation & functions. You would create a query that uses the query that has the IIF() function and do the grouping in the second query.