|
|
Sales Incentive Claim Tracking |
A large manufacturing company planned to launch a new sales
incentive program. The current program was managed through
Excel spreadsheets exchanged between the corporate office
and regional offices, a time-consuming and error-prone
process. The company realized that an automated system was
needed to properly administer the new program, encourage
participation by regional offices and generate management
reports.
A new Access-based system was rapidly designed and
developed, consisting of a front-end database deployed to
users in the corporate office and regional offices linked to
a central back-end database. The front-end user interface
provided basic data import and claim processing
functionality in the form of transactions. A follow-on phase expanded on the basic
functions to provide reporting and several additional
features. Shortly after launch, the back-end Access database
was migrated to SQL Server to improve performance and
position for future report distribution through SharePoint.
The new system has gained rapid acceptance by the regional offices. Regional and
corporate managers are able to
quickly obtain detailed information to monitor the sales
incentive program and ensure its success. Several months
after the system was implemented, additional functionality
provided an efficient method for the regional office users
to update certain information displayed on the corporate
website, previously a manual spreadsheet and e-mail based
effort. The company has recovered its investment in the
new system many times over.
Since the system was launched,
over 15,000 transactions have been processed
with a value in excess of $4,000,000. There are 7 user screens and 11
administrator screens. Users are able to run 12 reports, many of them
at varying levels of summary and detail information. A monthly upload
process imports information from a central mainframe database and calculates credit
and roll-off incentive funds for over 8,000 customer accounts. The system
continues to evolve with the business and further enhancements are planned.
|
Donation Tracking and Letters |
A non-profit animal shelter used an Access database to track
financial donors. They planned to retire this database when
a comprehensive shelter management system was implemented.
However, that project was not due to be complete for at
least a year, so they decided to make the existing donor
database more user-friendly and add functionality to
automate thank-you letters, labels and newsletter lists,
thereby saving a significant amount of time.
 To
improve safety and management of the donor information, the
data tables were placed in a back-end Access database on the
main server. The front-end user interface was redesigned for
ease of use and to allow for rapid addition of new
functionality. In preparation for transferring the donor
information into the new shelter management system, new
fields were added to the data tables and selected data
reorganized. An ongoing clean-up effort by shelter staff
will facilitate import of the name, address, contact and
donation detail into the new system by the time it is ready.
One screen from the new database is shown at right.
A company maintained product pricing information in an
Access database. The process to update the price data
consisted of several complex queries and manual verification
steps, taking about 30 to 45 minutes each week. To reduce
the time required to apply the pricing updates, a Visual
Basic for Applications (VBA) module was created to read
in the pricing update spreadsheet and display a validation
report. When the user validates the update information,
the data is applied to the master price table
in a matter of seconds. By replacing the process with an
automated method to import and review the pricing data, the
total time required to perform the updated was reduced to less than two minutes.
|
