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?
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.
Database Project Tutorial Content
- Defining the project
- Describing the Sales Reporting System
- Analysis and design of the system
- Creating the model for the Access database
- Download the sample PFS database
- Creating the tables
- Defining the relationships
- Creating the table maintenance forms
- The Master/detail model
- Creating the SRS transaction form
- Using Combo boxes
- VBA code with the DLookup( ) function
- VBA code to validate data input
- Creating Sales reports
- The Left join query
- Using the IIF( ) function
- Sorting and Grouping
- Calculating summaries
- The Main Switchboard
- Creating the Application Menu
- Editing the Switchboard form
- Creating an Autoexec macro
- ...coming soon!