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

Data Quality Services

The importance of data management is often overlooked by many companies. They frequently underestimate the important contribution data management makes to the success or failure of their operations. Data quality is vital to business intelligence. Companies typically spend thousands and even millions of dollars setting up business intelligence systems to improve their operations, but the results generated by these efforts are only as good as the data that is fed into them.

Many fall short of their expectations because of poor data quality issues. Contradictory, inconsistent or inaccurate information exposes companies to many business risks that lead to increased costs, customer dissatisfaction, poorer decision making and lost business. Clean, high quality data helps company decision makers to accurately and correctly assess their business activities and avoid potential pitfalls that can significantly impair a company's profitability.

At Excel Business Solutions we offer companies with data cleansing, data integration, data enrichment and data mining services in support of accurate reporting, analysis and business decisions; and consequenentially, minimize risk and cost, enhance business opportunity and increase returns.
More Info