Executive summary
My dad runs a piano tuning business out of his home. He operates a database, but he essentially uses it as a collection of tables since he doesn’t run any queries, forms, or reports. Excel has given him enough functionality for his purposes. He has a table that holds information about his customers: their contact information, address, and piano type. He doesn’t have Access, which would have made the project significantly simpler. However, the challenge of not having Access to work with was a good learning experience.
I created some functionality that can improve the way that Dad interacts with his customers and manages his “database.”
The first was to make all of the customer records searchable by multiple fields. His prior method of using ctrl+F and filters was sufficient, but it wasn’t very fast or thorough.
The second change was to allow my dad to view customer information in a nice form rather than on a long line in the spreadsheet. My dad has special abbreviations and lengthy notes for each customer; a textbox with wraparound function and the ability to tab and do line breaks is more conducive to his needs.
A third feature is the calendar management tab. On this tab, there is list of future dates and a column next to it of the area my dad would like to work in that day. When a customer calls in, he can see the next time he’ll be in the customer’s area and how many appointments he already has scheduled.
The fourth feature was to set up a way of sending simple mass e-mails from the spreadsheet. Using the search functionality, my dad can look up the records of customers who need their piano tuned within a specified period of time. A reminder e-mail, which incorporates table fields in the text, can be sent easily.
A fifth feature is a process that allows my dad to select up to five addresses—any U.S. addresses, in fact—and have the program tell him which route will be the shortest distance among them. This is done by having Excel interface with MapQuest and pull distance data after submitting the addresses selected. The great thing about the application here is that the number of my dad’s appointments is limited, so I could afford to write an algorithm that would look for the best route across all iterations and in any direction. I did this because I feel that the same code could be useful to others, and in areas where there are many one-way roads, the distance from A to B and B to A could be significantly different.
Some of the hurdles that made this project difficult included ensuring that the program was robust and managed unpredictable user input; creating database-like functionality without Access, managing data types, navigating web interfaces, and creating algorithms.
The customer information in the file below has been altered and bears no resemblance to the actual customer list.
Download the write-up
Download the project file
