How to adopt MS Access and MySQL databases to drive your database applications.
By John Chee
ii) MORE FACT FINDING
Both Access and MySQL are readily available to you. Let's say you have bought a new PC (or notebook) and you bought a licensed copy of Microsoft Office. When you start your PC, you are ready to start using Access. Then you go to MySQL website and download MySQL database software.
Access is user friendly and easy to learn. You just can't wait to crunch through the Tables, Queries, Forms, Reports, Macros and Modules panels after you have attended a 3-day Access course. Very soon, you'll realise you are able to write simple applications which you could not do one month ago.
From simple programs, you are getting more adventurous and now developing programs for your company. Gradually you will be talking about large databases and multi-users access through the internet; you begin to realise that Access could not cope with these requirements. This is where MySQL comes to the picture. It is fast, reliable and easy to use. If you surf the internet, you'll see tons of sites written in PHP program running on MySQL databases.
If I remember correctly, the website where you check for your traffic summons is running on a MySQL database server.
iii) ACCESS AND MYSQL IN ACTION
a) Case study 1 – Create a Market Survey application
Business requirement
Let's say you want to create a market survey application on what suppliers / customers are selling / buying in the consumer photo imaging industry.
Typical scenario
In general, a market survey application is a simple one which involves data entry, update and deletion of records. The objective is to get as much data as possible to pump into your database.
Here, Access is a good candidate simply due to the easy creation of Windows-based tables, queries, forms, reports and modules. To create ad hoc queries and reports, you can use the wizards on the fly and send the reports (through email) in snapshot viewer format. During the data entry process, it is common to create ad hoc reports because the business managers in another location would like to see the quality of data collected. He couldn't wait to see the progress of the survey because it costs a lot of money to run the survey where salespersons all over the country are collecting the data by filling in forms, telesales personnel calling customers and suppliers to get information.
Now three months have passed. You have collected most of the data. The survey exercise is still going on though the volume of forms filling your in-tray is gradually tapering down. You look back and smile ….. you have selected the right tool (or application) in Access, to run your survey exercise because you have gone through the following obstacles:
1) During the market survey data entry stage, you will notice that the business logic changes very frequently. That requires you to change the user interface, that is, modifying the Access Forms and possibly, changing the database structures.
From my experience, when you write programs for surveys, please be prepared to listen to ad hoc but important requirements as requested by the business managers. Be prepared to modify your programs to meet more business logic. Don't shut them off! After looking at the ad hoc reports generated from the survey, it is very common for the business (information) managers to come up with new and innovative ideas because the ad hoc reports you handed him earlier, are giving him some leads.
After all, shouldn't we create a culture of Creative Thinking and Innovative Concepts? These are the buzz words when you are doing your MBA or MSC degree programs nowadays.
2) Because you have 50 business managers located all over Malaysia and Singapore, you are aware that some of them are sort of IT literate. They know how to work (and play) with simple queries.
And because they are so many ad hoc questions and requirements bombarding you during the survey exercise, especially when you are so busy with changing the user interface, you discover that you can send them queries (SQL statement) on the fly. The business manager will create a new query in the Queries and Copy/Paste the SQL text you email him earlier. He clicks 'Run' to see the output of the query.
In this scenario, it's very common for business (information) managers to analyse the information gathered by the survey, by creating new simple, summary or crosstab queries. With the help of queries, they can massage and dissect the information, sort them, create what-if analysis, etc, etc. Now you can see the power of using Queries in MS Access!
If you observe carefully in the Jobs Recruitment section of major newspapers, it's very common for a company to employ a Market Survey-based personnel who posseses Access and MySQL knowledge and experience.
3) Six months have passed. Your survey program is still running and there are forms trickling in your in-tray to be updated. The data collection stage is completed. Now you are in the Analysis stage.
For the analysis stage, the business requirements entail that you need to provide information on Sales, Inventory, Customers and Suppliers which happen to reside in your ERP server. Your are aware that the ERP server is as busy as ever.
You know that you'll be working with Gigabytes of data consisting of inventory, customers and suppliers databases. What should you do now? Get a Linux box up and running and dump all the data into MySQL database server which sits on the Linux box.
Your business managers are IT-inclined. On their PCs, call up Access. Configure for ODBC method to the MySQL database server located in the Linux box. Now, they can read the sales, inventory, etc, linked-tables which you have placed in the MySQL database.
Imagine I am the business manager. On my Access screen, I can have access to the market survey information and I can see the sales information, which is connected via ODBC to the Linux server. Now I am ready to crunch the data, sort the queries, create new tables and eventually, come up with some findings to be submitted to my superior.
So in this Scenario 1, I've shown how you can utilise the power of Access and MySQL. You don't need to be computer graduate to write applications. An Arts student could equally write a good program if he/she is determined to learn new ways to add value to his/her work.
a) Case study 2 – How to create a pricelist book of bearings items
Business requirement
Have you created a pricelist book before for your customers and dealers/re-sellers? Let's say you are a sole distributor running an international bearing business which supply world known brands of different categories of simple, tapered and heavy-duty bearings.
Every month you have to distribute the Recommended Pricelist Book for your customers and dealers.
Typical scenario
Generally to create the pricelist book, you will obtain the raw pricelist from your Principal. A raw pricelist can easily run to 300,000 to 500,000 line items. Next you need to apply a margin factor 1 for each line item. This margin factor 1 will include costs incurred for transportation by sea, insurance and sea freight charges.
Then you apply an additional margin factor 2 to include costs incurred from custom duties. You introduce additional margin factor 2 for custom duties because the custom regulations change very frequently and there are many variables involved in the calculation of custom duties.
Because you are distributing the bearings to different categories of businesses like dealer, re-sellers and end users, you have to apply different profit margins to the pricelist.
Before printing the pricelist book, you have to communicate with all the business heads located all over the country. For example, you need the business heads to decide on the margin factor of the bearing items. And for the business heads to make decision, he/she has to compute, summarise and analyse the individual price of the bearing items.
Finally, the pricelist database is ready and you have columns like this:
a) Part Number
b) Secondary Reference No.
c) 50% discount
d) 30% discount
e) 15% discount
f) and other columns relevant to your business.
The solution
a) When you obtain the raw pricelist from the Principal, dump the records to MySQL database, which can handle the 500,000 records reliably.
b) When you apply margin factors to the pricelist, do that in Access Queries. The advantage of Access Queries is that you can create and sort summarised and cross-tab queries on the fly. You can do analysis and paste the results to Excel easily if required.
c) You can send the Access .mdb file to all the business units and they will work on the Access interface. Some managers will require new queries for them to analyse the data. You can create the new queries as requested and send the SQL statement in text file. When the business head receives the text file, he will just copy/paste the text to a new query.
d) In the .mdb file, you will create ODBC method to connect to MySQL where the physical records are stored.
e) When the final copy of the pricelist is ready, use Reports to generate the Recommended Pricelist Book. The other alternative is send the 500,000-record pricelist in datasheet form to MS Word. From there, you can create professional looking pricelist to be distributed to your suppliers and re-sellers.
In this article, I've demonstrated how Access and MySQL can work together in a SMB environment.
The two case studies are typical requirements of SMB companies which are not prepared to invest substantially in IT but still want to get their businesses up and running like horses.
Read more articles here
About John Chee
Find out more at http://nextlevel.com.my/tutor/johncws.
Copyright Notice
This article is copyrighted by John Chee. You are allowed to reprint
this article on your web site as long as you include the full article text, the
author profile information above and this Copyright & Reprint Notices.
|
Change Currency