​Controlling Your Own Office Data

​Well… back when this whole computer thing entered our offices, we were told that electronic records would make our lives easier. Like many of you, I was there; I remember offices pre-PC. I remember file rooms large enough to make a person’s head spin. I remember 4X6 prospect cards used in marketing, to write contact information and notes about the prospect. It’s a rare thing to see a rolodex on a person’s desk anymore; they used to be common place. They’ve gone the way of those old accounting and book-keeping ledgers.

I wouldn’t go back to those days for anything. In many, many ways this whole computer thing has made our lives easier. But… it’s not all roses and sunshine either. Two decades + into this, we’re still struggling with controlling the information in our offices. Those old filing rooms seemed overwhelming with pure volume of information, and no way to use it for decision making purposes. But, it’s not much different from many office environments I see today.

Now, instead of the massive filing rooms, with rows and rows of filing cabinets, we have a different problem. We have a computerized record management system in which large proprietary databases dominate. This reality makes it difficult for the average decision maker to get accurate data because of two things:

1. His/Her organization has multiple proprietary records management systems. For instance, they may have an Accounting Software Package, and a Project Management Software package. Synthesizing information from multiple proprietary software systems is labor intensive, time consuming and prone to error.
2. The multiple proprietary software systems within any given office do not make it easy to extract raw data. Proprietary software companies make a good chunk of their income from custom report writing. So… building user-friendly data extraction capabilities into their systems works against their bottom line.

There are ways for decision makers to take control of the data in their offices. Firstly, it is very important to recognize the difference between system wide data and local data.

System wide data is information used throughout an organization. Typically speaking, financial information is system wide. Every department in an organization is affected by financial information.

Local data is data local to one department or subset of users within an organization. The data is not typically important to anyone else outside this subset of users. An example might be grant data. Grant data is (in part) financial data, but there is a lot of grant information that just cannot fit into your typical financial records keeping system.

A large part of getting control of the data in your office, means finding ways to pull the various system wide information pieces together and also include the local data pieces. Exercising control over local data, is one of the first steps to a comprehensive and integrated data management solution.

Local data is often dispersed across many different spreadsheet applications or smaller database applications. Moving your local data to an internally controlled and integrated database solution will really clean up a lot of inefficiencies. Microsoft products are well suited for the shift, for instance:

• Your local data can be stored in a SQL database
• SQL is accessible to a lot of frontend options. Sharepoint can be used to build data entry screens so folks outside the office can still enter data into your database. Or view data they need to see outside the office.
• SQL is also accessible from Microsoft Access. Microsoft Access is fantastic for building complex database frontends at faster and at a lower cost than any other frontend option on the market. That's why it's the top selling desktop database application on the market.
• Because SQL can serve as a common backend for frontend interfaces in Sharepoint and Access, users can edit data through either Sharepoint, or Access and those updates can be viewed real-time in both places.
• This makes the combination of SQL (as a data storage engine), Sharepoint (for online capabilities) and Access for those internal administrative functions (such as complex report writing and sensitive data that you don't want available online) the most often pursued option when integrating your various local data solutions into one core database.
• SQL database storage can also be brought into play with proprietary systems as well. Although proprietary systems do not make data access and reporting easy. It is usually possible to set up regular export routines and store legacy data in locally owned and controlled SQL tables. This way the data can be integrated with local data for reporting purposes.

​The above points are a very simplistic example of ways to exercise control over the data in your office. But the basic outline holds true to many of the offices I’ve worked in over the last 20+ years. I use the Microsoft line of products because I’ve not found any other line of products that makes it so possible to integrate and control the various data sources within an office environment.

​Learn More about Building Custom Data Management Solutions with Microsoft Access​​

• 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.