Home: Services / Database Programming

Database Programming | Reporting Automation | Data Entry

Talk to Your Databases!

A very powerful feature of Excel VBA is the ability to interact with different databases. This example demonstrates the data extraction process from a MS-Access database and presentation in a spreadsheet report.

Database Programming

This database programming example demostrates how our Excel consultants using Excel programming to extract data from a MS-Access database and present them in an Excel spreadsheet. The source data which stored in the tables (see Figure 1) of a Microsoft Access relational database is retrieved and populated into a report in an Excel worksheet (see Figure 2) through OLEDB (Object Linking and Embedding Database) and ADO (ActiveX Data Objects).



Fig 1. Screen shot of a table relationships chart
 Fig 2. Screen shot of the report generated from the database


Automate Your Reporting Needs!

Beside the basic Excel data management and reporting tools (which are useful, but sometimes limited), Excel offers a more flexible reporting function through Excel VBA programming.


Three Report automation examples, are demonstrated. The first reporting example shows how our Excel consultants extract the raw data on a worksheet and populate the information into a report. The second reporting example shows how to incorporate parameters in the report automation process and place the calculated values into the report. Then a report is generated for each of the selected records. The third one is a report consolidation example.

Excel Reporting Automation Example - Parameter Driven Report (1)

This project extracts data from an Excel worksheet (see Figure 3) and populates the information based on the criteria filter (see Figure 4) into a report (see Figure 5). The reporting process is slightly more complex due to the multiple demension of the data (region, country, service, quarter, and year). In additional, the report does not use a pre-styled template since the number of rows or column on the report is unknown before the users make their selection using the filter. Array technique is used in this project to accommandate for the multi-demension of the date and the report output variation.



Fig 3. Screen shot of raw date in a worksheet
Fig 4. Screen shot of the record filter user interface
Fig 5. Screen shot of a final report generated from the raw data


Excel Reporting Automation Example - Parameter Drivn Report (2)

This project uses different Excel files as data sources. One of the major features of this program is that it allows users to select the company file (see Figure 6), define the computational parameters (see Figure 7), select the names of the records (see Figure 8), and build one separate report for each of the records selected (see Figure 9).



Fig 6. Screen shot of the Select Company interface
Fig 7. Screen shot of the Update Parameters interface
Fig 8. Screen shot of Print Option interface
Fig 9. Screen shot of the diferent reports generated


Excel Reporting Automation Example - Report Consolidation

This example demonstrates how individual business reports can be summarized into a consolidated master report despite unique formats and styles. Through Excel report automation, all business reports can come together a smaller chance of human error.



Fig 10. Report consolidation screen shot.


Input Data Into Your Database With Ease!

MS-Access is a relational database management system. It is a wonderful tool for building data entry forms for databases with many-to-many relationship tables. This section provides such an example.


Data Entry Form Example

This MS-Access application allows users to input data into multiple database tables that have many-to-many relationship (see Figure 1). By using multiple subforms, users can update data in multiple tables on one master data entry form (see Figure 2).



Fig 11. Screen shot of many-to-many
relationship tables in the database.
Figure 12. Screen shot of the master data entry form

Navigation Menu

Quick Contact Box

Black-Scholes Option Pricing Model

The Black-Scholes model is a mathematical model of the market for an equity, in which the equity's price is a stochastic process. Its PDE is an equation which (in the model) the price of a derivative on the equity must satisfy. The Black–Scholes formula is the result obtained by applying the Black-Scholes PDE to European put and call options. The formula was derived by Fischer Black and Myron Scholes and published in 1973. They built on earlier research by Edward O. Thorp, Paul Samuelson, and Robert C. Merton. The fundamental insight of Black and Scholes is that the option is implicitly priced if the stock is traded.
More Info