Using Microsoft Access for Data Evolution Projects

​Data evolution can be related to data integration, but it contains other components. Unlike data integration projects, data evolution projects build a new core database from multiple sources of data. Typically information from spreadsheet applications or various database/software applications is actually merged into one dataset. The goal of a data evolution project is to decrease the number of data sources and increase efficiency.

​Integration techniques may be used to interact with sources of information that cannot &/or should not be merged into the new core dataset. Most often, when I work on data evolution projects – it is Outlook we use integration techniques with. But, other examples of data sources which aren’t merged into the new core database are Quickbooks/accounting software and CRM or other enterprise data solutions.
 
As an example of a typical data evolution project let’s look at a project for a simulated architectural firm. ABC Architects may start out with the following data sources:

1. Mailing lists – housed in Excel
   1. Multiple mailing lists across multiple sheets in the same workbook
2. Bid - Proposal Data housed between Excel and Access and word
3. Job Data housed mostly in Access – some in Excel
   1. Employee Time Data – housed in Excel
4. Vendor Information – housed in Access
5. Contact Lists housed in Outlook

Instead of five different data sources to manage the processes of marketing, proposals, job tracking and employee time information as affiliated with job tracking – a new comprehensive database solution will pull all data into one location. Outlook contacts will be updated regularly from the new database. In addition users will be able to email contacts directly from the new database.

Because Microsoft Access and Microsoft SQL are built to work with other Microsoft Office products, they serve as an excellent vehicles for data evolution. MS SQL does a superb job of storing large volumes of data. Microsoft Access is the best "rapid application development" tool on the market. It is possible to combine SQL and Access to build robust core database applications. There are a lot of other technical reasons that Microsoft Access is the “go to” product for on premise database solutions as well. But, let’s focus on how MS Access looks and feels when a data evolution project is complete. Since pictures are “worth a 1000 words”, following are some screen shots demonstrating what a complete ABC Architects data evolution project might look like.
 
In the Beginning

ABC Architects manages multiple mailing lists with a spreadsheet application. Each mailing list has its own spreadsheet within an Excel Workbook, as shown below:

ABC Architects experiences the following problems with their mailing list workbook:

1. Edits to same contact records shared across multiple mailing lists are cumbersome.
2. Data volume is pushing the limits of MS Excel. Since the Workbook application is several years old, the size of the workbook is forcing ABC Architects to pull some of the larger spreadsheets out and put them in their own workbooks. This makes maintenance even more cumbersome.
3. Analyzing information across multiple spreadsheets and workbooks has also become increasingly difficult. Spelling errors or variances of same individual names and addresses makes it impossible to trust analysis of 1000s of records for common contacts.

 
In addition to the above problems with the current Excel mailing list solution, there are also vendor companies and contacts in an Access database. Contact information is also stored with Bids & Proposals; it is stored with Job Tracking, with Vendors and also in Outlook. All this contact information shares common data points, Organization names, First Names, Last Names, Email addresses, Phone numbers, Street addresses, etc. By merging all contact information into one core file it will be easier to maintain, analyze and query the data.

After Evolution
 
The following screen shots show how some of the elements in a core ABC Architects database solution might look.
 

​Main Dashboard:

​Work with Contacts Module:

​Mailing Tab:

​Activity Log Tab:

​The above screen shots are simply examples. And they don’t encompass all that an evolved data management solution can do. For instance, in the example of ABC Architects, users can also work with bids and proposals, job tracking, invoicing, and do mass emailing directly from the core Access database. In addition integration with Outlook is built in so that Outlook contacts are updated when contacts in Access are edited. Users can email from Access and if they want to add a logged activity to the Outlook Calendar they can do that as well.
 
These are the advantages gained when data is evolved from multiple sources into one core Microsoft Access database. Nothing ever stays the same. That includes our work environments, work teams, clients, vendors, and the information needed to effectively manage our ever changing organizations.

• How many data sources are you managing?
• What types of information is shared across those data sources?
• How much work is it to keep same records in multiple data sources updated?
• Does your team trust the data in all data sources?

 
Answers to these questions can guide you to a more effective data management solution.

More Articles About 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.