Developing an integrated Sales Reporting System

About the Project

Over the next few weeks we'll be developing a Sales Reporting System for a Financial Services Company. The company, PFS, although not very large, has a lot of data to be analyzed to produce meaningful sales statistics. It's important to track the sales reps to see who is producing and who isn't. The products must be evaluated to see what the buying trends are and to be able to adjust rapidly to changing demands. And then there are all the various combinations of reps/products/suppliers that can offer meaningful information to help management guide the sales force.

The project will involve the use of several common tools. Although it's not always necessary in business to do it this way, we want the learning experience to be as complete as possible, so we'll make the project a little more complex than it might otherwise have to be.

Aren't you using Firefox, yet?
Why not?

The plan is this:

We'll develop the complete database in Access. We'll create all the tables, the forms, the queries and the reports as a complete functional project that could be used by itself to produce valuable sales statistics. The user interface will consist of Access forms to input the original sales data into the tables. Several queries will have to be written to extract the information in several ways: by sales rep, by product, by product line, by period, etc. All the sales reports will be Access reports generated from the queries.
Then, to make it more accessible by other applications we'll convert the Access database to MySQL. That's really not as complicated as it sounds. There are tools available to make the transition fairly painless. If you've read the other tutorials on database design and SQL you may have picked up that Access is not a database server. If you ever want to expand a database to serve several dozen clients, you'll have to move up from Access. The next step could be Microsoft SQL Server or, as we'll do, you could go with the open-source option, MySQL.
MySQL doesn't have a built-in user interface like Access does. So, we'll create a user interface using Visual Basic .Net. This will give us some practice with the new Visual Studio tools. Even if you're not yet into .Net, you could easily follow along by doing the same thing with VB 6.
Finally, we're going to put the project (at least part of it) onto the Web by creating a PHP interface between the database and your Web server. If you're not familiar with PHP, it's the scripting language that lets you connect to a database through a browser.

Hopefully, by the time we're done you're going to have a much greater appreciation for the power of relational databases. By using multiple tools with common data you'll see how it's possible to scale a project from a standalone user to a large group of users. You'll also be exposed to data access from the local client, to the intranet and then on to the Internet.

Top