Database application design, maintenance and support.
Throughout my working career, one of my specialties has always been to look at processes taking place in the office (paperwork, data entry, workflow, etc) and look for ways to make them more efficient. In many cases this was accomplished by building a custom Access Database application.
I don’t just create a database with a few tables and queries, and call it a day. I build complete user friendly applications that any user can learn very quickly and easily. I also provide training and reference materials and conduct training sessions.
Below are some examples of the applications I have built over the years, and how they have helped streamline office operations:
Note that while some projects were completed for my employers (past and present), all are referred to as 'clients'. They are not named for reasons of confidentiality.
Contractor / Landscaper / Lawn Service Scheduling and Invoicing
Originally designed for a lawn service, this application can be adapted for just about any type of contractor or maintenance service provider. I can deploy this for you at a relatively low cost, since it is not as custom as many of my other apps. Used for maintaining contracts, scheduling of jobs, invoicing, and receiving payments. It can even remind you if a customer's contract is about to expire. If you want this to replace an off the shelf app such as Quickbooks, just show me what reports you need the app to produce and I can make it happen.
Returned Materials Authorization (RMA) System
Problem: Client needed functionality to approve returned product. This firm took returned materials very seriously and had a detailed process in place in which returned materials would be reviewed by multiple departments including Quality Control and Engineering. While their order entry / production control software package allowed for items to be returned to stock, and credit / refund to be issued, it did not have the capabilities to track the returned materials through this extensive process. The return process was tracked solely via a paper trail, using a 5-part form!
Solution: The application I created eliminated the 5 part form and tracked the returned / defective materials through each department.
Cumbersome 5-part form, filled out by hand.
To track status, customer service rep had to call multiple departments to determine what stage of the process the returned item was at.
Historical data tracking was near impossible.
Lost paperwork was a frequent occurrence.
Customers often waited excessive amount of time for refund.
Paperwork generated electronically, and easily replaced if lost.
Status could be tracked with the click of a mouse.
Historical data now tracked via reports
Customers received refund promptly.
Each department can enter their own comments and disposition, using the menu (below). Data entry is a breeze:
Order Backlog Tracking
Problem: Client had a proactive customer service group who diligently tracked their backlog of open orders to ensure on-time delivery. Since this was a manufacture-to-order operation, this was much more involved than simply checking stock in a warehouse. Customer Service reps would review open order reports weekly, and in many instances, had to look up component part#s to track them in the mfg process. THIS had to be done manually, one part# at a time. (Not to mention, the individual customers’ reports had to be generated and formatted in Excel individually).
Solution: I developed a database application for this firm that not only looked up these component part#s for them but also associated each rep's name with their assigned customer numbers. Then each week, each rep simply needed to click one button in the database which would produce one report showing all of their customers’ orders, with component part #s. So instead of spending a day sorting though reports, looking up numbers, and formatting multiple spreadsheets, they simply ran their report, and began follow-ups on problem part #s immediately!
Results: a savings of 8 hours per week per employee. For the 5 reps in the department, this was a 40 hour saving, one whole full time position!
Data Analyst performance stats
Problem: Client had limited means to track data entry analysts’ performance. The firm’s internal engineering resources were already stretched thin due to many high priority projects. The only performance data available to managers was a simple text file that the system generated, which stated nothing more than the times that employees logged in and out of the database, and how many items they processed. To evaluate employees’ performance, this text file had to be analyzed by hand. Attempts were made to place this data into spreadsheets, only to find that substantial manual calculation was still needed.
Solution: I put together an application for this firm which, when fed the aforementioned text file, could then track productivity and accuracy statistics for each employee. Managers were able to view data for one or all employees, or just their direct reports. They could view employees’ performance for any date range – one day, or more than a year.
Before: 1 hour calculating, manipulating, and analyzing spreadsheet.
After: 10 minutes reviewing and analyzing data.
Bottom line: 83% time savings!
Operational Interruption Tracking
Problem: Client who collects data from servers in multiple locations, occasionally experienced operational interruptions with their equipment. They needed to disclose these interruptions to their clients, and also have the information readily available to internal data analysts. Reports had to be created BY HAND, typing into a Word doc (reading from an Excel sheet provided by IT), each city, date & time that the interruptions occurred, (along with other pertinent proprietary information). This process took an analyst about 2 hours per week.
Solution: I developed an application that generated reports automatically in seconds! This application included a searchable database for internal analysts to refer to when needed.
Results: Labor savings well above 90%.
Data Quality Control
Problem: Client needed an efficient method of verifying data accuracy in one of its client-facing web-based databases.
Solution: I developed an application that automatically detected certain types of errors, and featured an easy to read interface for analysts to view data and detect other types of errors and discrepancies. Once a dataset was reviewed completely, a report was generated for management for error tracking and individual error reports were given to analysts to correct their own errors. This application had a workflow management feature where the user would “sign out” a dataset to review, and would mark it “complete” when done. This feature can be made available with nearly any application I build.
Results: A better quality product, as errors were detected that would have otherwise gone unnoticed.
CRM (Customer Relationship Management) application
Problem: Client needed a simple and efficient method to track client questions and issues. Off the shelf CRM applications did not meet their needs because of the unique nature of the product line.
Solution: I created an application where the user inputs the client’s question, and the answer that was provided. Fields were later added where the nature of the problem was noted. Reports could then be created for management to identify problem areas.
I was later called upon to upgrade the application to include tracking of reports sent to clients. This upgrade involved transitioning functionality and data migration from an older system. Extra steps were taken to ensure the new software had a similar look and feel to the old, so as to minimize the need for training.