The Power of Access as a Small Business Solution

Microsoft Access application development has been a favorite of mine for many years, because it lends itself to a rapid application development cycle and can provide a cost-effective custom solution for small businesses.  I want to illustrate this with two examples of recent projects I have completed. 

Client 1 - 2nd generation retail business taking over from 1st generation businesses 

Problem - Payroll commission computation was time consuming and error prone. Inputs came from 2 different enterprise sources and were not in a clean format. 

Solution - Access is terrific for merging Excel based exports from enterprise databases like point of sale and employee timecard systems.  

1. Prior to importing, the Excel workbooks were massaged with VBA code to cleanse and reformat. 
2. Saved Access imports were then used to import the data from Excel.   
3. Data was then merged via Access queries.   
4. A simple form was developed to step the payroll officer through the commission calculation process.  This included 2 imports and 3 reports.   
5. The first report was designed to allow the payroll officer to review the computed commissions for accuracy.   
6. The second report split the first report up by sales rep and emailed the reports to the individual reps.   
7. The final report was an Excel based export in the format the accountant needed to complete the payroll.   

Impact and Learnings – The payroll officer absolutely loved the end product! The steps of the solution follow the same process she was familiar with, but saved her hours of tedious work and provided greater accuracy in the resulting reports.  

A strength of Access is its ability to easily integrate with other products in the Office platform and make use of the strengths of products like Excel and Outlook, and this project shows that the combination of Access queries and VBA code can be used to apply even complicated business rules. 

Client 2 - Professional business needing to generate lots of client based letters 

Problem - Letters have lots of data fields in them. Word mail merge was routinely failing and often error prone to set up. It also resulted in one document that included all clients’ letters. Documentation of what was sent to whom was problematic. 

Solution - Word mail merge can be difficult to work with. This was further complicated in this instance since the data resided in an encrypted Access database due to the sensitive nature of the client data.  The simpler solution was to push the data TO Word instead of pull it FROM Access.   

1. Since the business used a wide variety of letters, the solution allows them to add Word Templates (built in Word) to a table in Access 
2. They then map named Content Controls from the template to fields in an Access client query.   
3. A form was developed to allow the professional to run a premade Access Query, or filter on predefined fields in the client query, to find the clients whom they wished to create a letter for.  
4. Then they select the letter they want and the directory they want the resulting letters to be placed in.   
5. With VBA code behind the Form, the Word template is opened, and for each client selected, a Word document is produced with merged data from that client record using the previously setup mapping.   
6. Each letter is saved to the chosen directory with a standard naming convention. 

Impact and Learnings – The business users of this solution love the ease at which they can now generate and archive client letters.  Gone are crashing, error prone, or time consuming Word mail merges.  The super user who sets up the templates and mappings finds the solution easy to use and right on target with their goals.   

Again, this project shows the strength of Access' easy integration with other products across the Office platform; this time with Word. Although Word mail merge allows you to select and filter data, Access is much stronger at organizing and querying data. Understanding and using the best tool for a project is key. This project also enables the user to self-serve by creating additional Word templates on their own, and then easily set those up to be used from the Access application.