So... You Inherited Some Spreadsheets ....

A few months ago, your co-worker retired, and you’re the one who ended up with all her spreadsheet applications. Yes, she took the time to teach you what she was doing with her spreadsheets, but it’s all pretty cumbersome and time consuming.
​
Just to maintain it all, you have to regularly export a lot of the data out of your proprietary records management system. Then, of course, there are all those columns of information that have to be hand entered, because your records management system doesn’t have the data.

In addition, the information in all those spreadsheets is related. So… beyond all the copying/pasting of data and importing information from your records management system, you also have to edit data across the multiple spreadsheets. If there is a change to the data, you have to make sure that change is reflected in every affected spreadsheet.

On top of it all, you didn’t create the spreadsheet system, and so you don’t always understand the logic behind it. This causes a lot of problems when you have to copy/paste/ and start new spreadsheets for new datasets, or new time periods. You’re concerned because there are so many different spreadsheets and so much copying and pasting of data, that there may be errors.

Worse, is the time you spend building reports from the data in various spreadsheets. Just trying to bring it all together, synthesize it in a way that reflects reality, and makes sense to those who want the report takes hours of your time.

Data is dynamic and managing it requires dynamic solutions

You’re not alone; this problem is quite common in offices. And there are productive ways to deal with it. Firstly, you’ll want to keep in mind one reality when it comes to managing information. Data is dynamic and managing it requires dynamic solutions

Look at your data through the lens of evolution. Since data is dynamic, proper management requires the ability to evolve and transform. Managing the information with spreadsheets may have made sense when your co-worker built them years ago. But, if you’re spending an inordinate amount of time maintaining them, or you’re concerned about data accuracy, then it is time to move to a new stage in data evolution.

Microsoft created its data management products to accommodate information evolution. The first stage of data evolution is the spreadsheet stage. One person may need to track data specific to his/her job and just starts a new spreadsheet. As time passes, and more people need access to the information, the original spreadsheet application grows and multiplies. Before long the situation has evolved into the system you inherited.

Actually this situation can be viewed as an opportunity. Since the spreadsheets have been passed from one owner to another, the potential to take your information management to the next stage in data evolution is high. The second level of data evolution is the local database level. At this level, data from various sources are often combined and streamlined into one core local database.

​Managing Local Data with a Database instead of a Spreadsheet
​
Local databases are unlike your system wide software solutions. Local databases are “local” to one group of users. These types of databases manage information that doesn’t quite “fit” into system wide proprietary software solutions. Local databases are a step-up from spreadsheet applications. When you move away from spreadsheets to databases, you gain the ability to manage your data in a more streamlined fashion. Databases allow you to organize data in such a way that you can eliminate duplicate data entry and the necessity to reproduce spreadsheets for various datasets. Database structure also allows for building standardized reports that can be used for multiple datasets.

Moving away from a spreadsheet solution, to a local database solution, will solve a lot of the problems you’re experiencing with your inherited spreadsheet applications. You’ll be able to manage related information in one core database, rather than distributed across many different spreadsheets. It’s still possible to import data from proprietary software. But, since databases are built off of a table structure, with relationship capabilities, you will be able to store imported data in the same database. Database relationships will eliminate the need copy/paste and start new spreadsheets for new datasets. With tables and table relationships the information can all be stored in the same database.

If a local database is properly built, the need for average users to understand database logic decreases. Properly built databases will dramatically reduce the need for average users to build and rebuild reports, or other output vehicles. Since standardized reports, exports, and datasheets can be built into the system and reused over and over again, only the person programming the database really needs to comprehend the underlying logic.

Database table structure allows for more control over data integrity, reducing duplicate records, and keeping your data clean. Spreadsheets are fraught with the capacity to produce errors. One of the reasons errors occur so much in spreadsheet applications, is because end-users copy/paste and reproduce the same spreadsheet “template” for multiple datasets. This dynamic can cause breakdown in data integrity when users forget to check calculations during the reproduction process. It can cause breakdown in data integrity when users forget to check for data duplication in the reproduction process. With a database solution, this problem is completely eliminated because proper database construction eliminates the need to reproduce databases for new datasets.

The Microsoft Office Suite is the most popular software on the market. This is because the Office Suite comes with different software packages for managing different office jobs. Microsoft Word manages word processing. Microsoft Outlook handles all the email and calendar tasks. Powerpoint is great for professional presentations. Excel is best used for data analysis. And Access is the database portion of MS Office Pro, it is best used for data storage and management. Within the Microsoft suite of products, Excel is the first stage in data management and Access is the next step up from Excel.
​
Microsoft gives most offices the tools they need for daily operations. But, Microsoft Access is the most underutilized portion of the Microsoft Office Suite. This can be a costly mistake for any office, because using Microsoft Access to collect, store and manage your local data can save you hours of time, a great deal of headaches with your spreadsheet applications, and it can save you money. 

Learn More About Microsoft Access​​

• Microsoft Access and Data Integration
• Yes – Microsoft Access works in a Multi-User Environment
• YES – Microsoft Access Can be Used Securely
• Deploying Microsoft Access in a Remote Environment
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....
• Do you have questions about your own data management project? Contact Michelle.