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

Monte Carlo Simulation

A Monte Carlo algorithm is often a numerical Monte Carlo method used to find solutions to mathematical problems (which may have many variables) that cannot easily be solved, for example, by integral calculus, or other numerical methods. For many types of problems, its efficiency relative to other numerical methods increases as the dimension of the problem increases. Or it may be a method for solving other mathematical problems that relies on (pseudo-)random numbers. Monte Carlo methods are useful for modeling phenomena with significant uncertainty in inputs, such as the calculation of risk in business. Monte Carlo methods have also proven efficient in solving coupled integral differential equations of radiation fields and energy transport, and thus these methods have been used in global illumination computations which produce photorealistic images of virtual 3D models, with applications in video games, architecture, design, computer generated films, special effects in cinema, business, economics and other fields. The advantage Monte Carlo methods offer increases as the dimensions of the problem increase.
More Info