Microsoft Excel or Access

Some people who have to work with business data move from Excel to Access thinking that they will be able to do more with  Access. Most of the time it is a costly mistake.

Microsoft Access is a database application and Microsoft Excel is a data analysis and reporting application. Even if you start using Access or any other database program you will still need Excel to develop reports and analyses.

Some people with a limited knowledge of Excel (mainly not knowing about SUMPRODUCT  and the database functions in Excel) decide to move to Access. They get the training on Access, a training that is much longer, much more difficult and much more expensive than the training for Excel. At the beginning learning about Access is very interesting it's like discovering a new video game, like discovering a new power. But after a few months you start realizing the limits of Access. After countless hours and thousands of dollars spent in the ordeal you call a good consultant and he develops in Excel what you have tried to develop in Access in one fifth of the time and for one fifth of the price.

20% of the spreadsheets that I develop are stand-alone the other 80% are applications to analyze data coming from all kinds of databases (Oracle, Db2 on AS/400, SAP, Sybase, SQL Server) or ERP programs (JDE, PeopleSoft, SAP, Oracle). I design applications to organise and analyze data and to generate reports. In these spreadsheets 75% of the automation comes from formulas (SUMPRODUCT) not macros (VBA procedures).

8 years ago I  developed a payroll modeling tool for a major bank (13,000 employees). It includes modeling salaries, taxes and social benefits (18 different packages). They had worked at an Access solution for 2 years and the THING was getting so bulky, so costly and  so user unfriendly that they called me. I have designed an Excel  solution in 3 months and since then the users of the application haven't called me because they can modify all parameters, maintain  the application and they can even modify the application as things change.

They have called me many times for other things. The new knowledge that they acquired about Excel while working with me has allowed them to design analysis and reporting applications that they never thought possible. As they do not have the time to develop  them by themselves they call me. I LISTEN, question and SUGGEST. I develop what they need  and from then on they are on their own for fine tuning and maintenance and they love this aspect of our relation.

One does not switch from Excel to Access, one makes the decision that he needs a database to handle his data and then purchases Access or any other database.

What are the limits of Excel as a database? There are two:

- the main limit of Excel as a database  is that data cannot be entered in Excel by more than one user at the time although an unlimited number of users can open an Excel file simultaneously.

- the capacity of Excel is SOMEWHAT limited as far as data is concerned . Each sheet in an Excel file (workbook)  can hold a maximum of 65,500 records (rows) (1,000,0000 rows in Excel 2007) with a maximum number of fields (columns)  of 256. If you have  more data, each Excel workbook can hold 256 sheets. If you have even more data, you can use as many workbooks as you please and link them with formulas.

Although all database applications are not limited as far as quantity of data in concerned, all of them (Access, Oracle, Sybase, dB on AS/400, SQL Server, Essbase, Cognos  ....) have one MAJOR problem their reporting and analysis capabilities. All accounting, manufacturing, sales, HR  and other specialized applications feeding databases (JDE, Oracle, SAP, Smartstream, Peachtree, UniverSale....) suffer from the same problem. Even if they offer you numerous reporting and analysis templates you always need a template that they do not have. Because your business is unique, your reporting and analysis needs are unique. This is why you will ALWAYS need Excel.

If there is already a database in your organization you do not need Access and I must honestly add  that if you are ready for  a database Access is not the product that you are looking for. Consider the very cheap but powerful SQL Server from Microsoft, Oracle, Sybase, etc. and get ready to hire a DBA (Data Base Administrator). Get ready to open your wallet and do not think that your reporting problems will be solved.

If there is already a database in your organization what you need to work on is accessing the data. The database people can send you the data that you need as TXT, CSV  or even XLS files. Ask your Database Administrator to look at ways to make the data available to the data analysts and report designers.

The worst aspect of developing reports with the database reporting application is that just a few people develop the ability to use it. This creates a reporting bottle neck and deprives the enterprise of the input of the majority of its employees and analysts. The only way to avoid such a situation is by making the data available to data analysts and empowering them with the best reporting application (Excel).

Access, Oracle and other ERP systems are DATABASE specialists environments, Excel is a DATA specialists environment.

With all due respect, you are probably contemplating Access because you do not know enough about reporting in Excel. Discover SUMPRODUCT, discover the very powerful database functionalities in Excel and you will forget about Access or the reporting application of your database.

Learn VBA, get a promotion, get a better job, make more money