from Pierre Leclerc at http://www.excel-vba.com



Database 101

Unless you develop a database with Excel for your small enterprise, store, department or branch get ready to either spend a lot of time learning and trying or to spend a lot of money hiring a database specialist and developing your database. And at the end of the process you will still need Excel to develop your reports and analysis.

Allow me to start with very basic notions about databases.

In a modern database data are stored in a very specific way. In what DB people call TABLES, records (rows of data) are stored one after the other without interruption. Each record comprises many fields (columns) for record number, date, name, amounts, account number, etc. A basic table can look like this:

Rec. #

Date

Name

Acc. #

Amount

1

01/02/2005

Sales

90001

3542.12

2

03/02/2005

Transport

80002

356.45

3

01/06/2005

Sales

90001

56325.42

4

01/22/2005

Expenses

80003

15365.42

5

02/18/2005

Expenses

80003

3652.45

6

01/17/2005

Transport

80002

365.42

7

02/15/2005

Sales

90001

3654.78

There are usually many tables in a database (clients, employees, products, sales, etc...) The data is organized so that the computer can easily find what you are looking for.

The three other main components of the database are the query, the form and the report.

The QUERY is a sub-table, an extract from the table. Using a language called SQL (pronounce sequel) DB people extract data (by date, by product, by account, etc) and send the resulting sub-table to the analyst. In a sales table you just have the client number so if you want to analyze data by city the DB person has to combine data from the sales table and the clients table. He then send the sub-table to the analyst. For DB people developing a query is very easy and it can be automated so that every morning the analyst has his sub-table.

The FORM or GUI (Graphical User Interface) is this screen that allows you to enter data in the database because you usually don't have access to the tables. Some forms can be complex things with drop-down lists that are there not to make your work easy but to make sure that the values that you enter in the database are valid. There are also filters to tell you that only dates can be entered in a field or numbers or zip codes, etc. Because many people will be using the forms these safety components can be quite extensive.

Finally, the REPORTS allow you to organize and analyze the data (from queries) and develop a document with the proper layout to present the results of the analysis. In ALL database programs this is the weak spot. That is why downstream from ALL databases people use Excel to organize and analyze the data (from queries) and develop reports.

A database programs also allows management of the traffic in (thousands of users), the traffic out, the security and integrity of the data. It can become a huge thing and can cost tens or millions of dollars. The database is for the data specialist and the data within it is for the data specialist and rarely can one be both.

Behind all accounting programs or other data monitoring programs there is a database. It is either integrated into the program or the program feeds an existing database. So from all these programs and databases you can extract data and develop reports and analysis with Excel.

A lot of my clients have large centralized databases (EssBase, Oracle, SAP, Sybase, SQL Server...) or large centralized accounting, financial or manufacturing applications (JDE, SAP, Oracle, PeopleSoft, SmartStream...) but the data is analyzed and the reports are developed using Excel. They become really efficient in analyzing and reporting when their employees learn about SUMPRODUCT and INDEX/MATCH formulas. They adopt this approach because Excel is the most user friendly analyzing and reporting application so they don't need to train a few analysts on other reporting applications creating a bottleneck at the report development level. Adopting Excel also allows all the analyst and decision makers to develop significant analysis and reports improving the bottom line of the whole corporation.