logo
Lemming-img
bullet-img   .... Contact us immediately at affordsol@skynet.be

greenball-img  About DATABASES in Excel This is an excerpt from Pierre Leclerc (www.excel-vba.com)



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:


Many tables are usually 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 it 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.
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 companies 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.


left arrow Back Home

Kilroy site by   Affordable Solutions