At Henry & Horne we often deal with requests that involve a heavy data analysis component. Most of these requests can be handled with Microsoft Excel. The multifaceted spreadsheets, in addition to the formulas and macros, allow for complete data articulation. However, Excel does have its limitations. In one recent project we had for a client it required to tabulate data on 600+ fields of information for an estimated 500 different entities. Not only were there size issues but the data had to also be meaningful and relatable. One of the more important fields was region. The client wanted to track the entities by region and, in addition to the large amount of data for each entity; this would create many problems within Excel. This required us to use the powerful functions of Microsoft Access. While Excel is limited to 256 columns of data, Access has an almost limitless amount of data that it can store while still being meaningful to the user.
For this particular project we created three different tables within Access to handle all of the line items and linked them together as relational databases. We linked all three tables together with a common ID tag that is seen in each table. This allows us to ensure the data with the same ID tag will still be related to each other while stored in different databases. Linking also helps to show the data in a meaningful way that is easily interpreted and reference specific columns to place the data.
Since the client was more familiar with Excel, we created a template that they could use in Excel to input the data. Access has a feature that allows the importation of data from many programs, including Excel. This allows a user with limited Access experience to enter data into Excel that can easily be put into Access. This is also useful if not all of the clients data entry personnel have Access installed on their computer. By creating this template it also helped us create the database because the data import feature will import column headings and formatting.
Being able to track the data was very important for the client. Using Excel by itself would require having to link numerous excel worksheets together to track data and create sophisticated functions to sort it. This creates a time consuming process to link each spreadsheet together and could cause Excel to crash or run very slowly if the spreadsheet has too many links to other sheets. It helps the client keep track of which data points are comparable to create averages with great ease of use. In addition, the reports and queries that Access has to offer produce a wonderful presentation of the data.
This is an important lesson of flexibility and being responsive to client needs. In the ever growing age of technology, it is important to be able to adapt to any project put in your path.
By Henry & Horne