Carman Violin Studio - Jesse Carman - Winter 2010

http://files.gove.net/shares/files/10w/jlcarman/Final_Project_.xls
http://files.gove.net/shares/files/10w/jlcarman/Puzzle.xls
http://files.gove.net/shares/files/10w/jlcarman/Project_Write_Up.pdf

Carman Violin Studio is in need of a student database management system to enable the violin instructor (my wife) to easily create invoices and receipts for her students, to automatically generate personalized emails to her students, and to allow her to view students’ profiles in an organized manner (student information is spread across 30+ columns of data). The management system also needs to keep a concise historical record of each student’s charges, fees, credits and payments. It also requires the ability to easily add new students to the database and change their activity status (Active/Inactive) as students have a tendency to quit and then start-up lessons again from time to time. Because the instructor teaches both private and group lessons, the system needs the ability to differentiate between the two and enable the user to input, and make changes to, both types of lessons. Reschedules, cancellations, and no-shows are also a regular occurrence among students, thus the system will need to be able to account for such activities. Finally, the system will need to be able to track two periods of time for each student. First, how many reschedules individual students have requested over a rolling 3-month period. And second, what 2-month period each individual student is currently in (students must sign 2-month contracts which automatically renew until the student quits).

To address this situation, I created a Microsoft Excel workbook that includes an “Invoice-Receipt” sheet, a database sheet listing all students in alphabetical order, and a separate sheet for each student which tracks their respective transaction history. I created a user form that enables the instructor to view any student’s information in a concise manner. I created a “Go To” feature that enables to instructor to quickly move from one student’s file to another. I also created sub procedures enabling the instructor to perform the following tasks: add new students to the database; schedule private and/or group lessons; record 48-hour notices of cancellation, reschedules, no-shows, late payment fees, returned check fees, and payments; change student activity status; and generate emails to be sent from her violin studio email account (a record of the day and time emails are sent is also kept). The workbook includes a hidden template worksheet of how a student’s file should look which is copied when adding students to the database. Each sub procedure opens a user form to enable the instructor to input the required information for each student and then takes that information and records it accordingly. Finally, I created a separate macro toolbar, “Violin Tools,” that has each of these commands listed on it for accessibility on any worksheet at any time. The toolbar is automatically opened (and nested below the regular toolbars) upon open of the workbook and automatically hidden upon workbook close.

As time went on I added to my project a “Puzzle” game which I created in Excel and can be played during boring lectures in class. It is the file titled “Puzzle.” This game is a 4 x 4 block of 16 cells containing the numbers 1-15 and a blank cell. The numbers 1-15 are randomly placed within the 4 x 4 block and the user is supposed to rearrange them in the correct order, reading left to right starting in the top left cell, using the blank cell. By simply clicking on any cell in the 4 x 4 block the numbers will shift as allowed.