Microsoft Access is another powerful tool for your BA tool belt. It offers several great features for data analysis and small scale personal data mining; if you’re willing to put in the time to learn you can even build some pretty great, fully functioning applications.
It’s been my experience that Access is great tool to use personally for your own purposes but is less user friendly to those less familiar and unless you possess the skill to build a user friendly interface, it tends to be too complicated for the average business user.
As DIY Data Analyst you can take advantage of features like external data connections, relational data querying, wizard driven user friendly queries, store your own tables of data without IT departments to get you a db server, create simple forms to track data for later reporting and Wizard driven reporting.
If you take advantage of building a data model in Access you can also use Excel to connect to your new Access data and take advantage of all the features of Excel as well (Chart, Pivot Tables, Power Query)
External Data Sources:
Much like in our previous modules for Excel, Access has many of the same external data connection options such as, other Access DBs, Excel, Flat Files, SharePoint and ODBC compliant DBs (SQL Server, Oracle, MySQL, Etc.)
When connecting to External Data you have the option of Importing a Copy or Linking. If linking then some files cannot be open by another program (eg. Excel files must be closed if linking to them)
Read through the following two tutorials on External Connections in Access. Setup a connection to any source of your choice (Excel is a simple starting point).
A query, by definition is a question. In Access, a query is the users way of asking a question about the data and getting a result. Example: What Sales data exists for customers in England –> Select * from SalesDataTable where Country =”England”
Like other relational databases, Access allows the user to perform queries against it’s data with SQL. For simplicity, Access offers friendly Wizard driven methods to create queries with no knowledge of SQL needed.
From the “Create” menu on the top Ribbon menu, Create a Simple Query using the Query Wizard (Note: you must have at least one Table)
Additional Reading on Queries:
Designing Simple Queries—>http://www.gcflearnfree.org/access2013/10
More Query Design Options—>http://www.gcflearnfree.org/access2013/12
Forms are an input method for users to add content to their tables. For the DIY Data Analyst, a form might be used to quickly enter data not easily retrieved from a source into a Table for later analysis or reporting. Ex: Tracking Adhoc feedback on employees received. Forms can also be used to Ask the user for input and then execute – such as prompt for Date variables and then run a report.
To Create a Form is quite simple – the Form Wizard makes it very straightforward.
*Note: If you create a Form on a Linked Table the Data is Read-Only
Additional resources on Forms:
Access reports are exactly what they sound like – reports. These are used to create canned reports for your data which can be regenerated at any time to represent refreshed data.
Reports can be very simple or very complex; reports can run on click or prompt users for parameters first (ex: Date values, Employee or Country); reports can group and summarize data or give a straightforward list.
Access offers a Report Wizard to get you started. The following screen shows a simple report created via the Wizard. Everything on this report can be customized and formatted to your own preference.
Additional Resources on Reports:
Advanced Report Options—>http://www.gcflearnfree.org/access2013/14
If interested in additional learning on Microsoft Access, the internet is not short on tutorials and searching for a specific topic will often return a YouTube video or Web tutorial on the topic. I also highly recommend looking into an online education site such as Lynda.com
if you are looking for more comprehensive approach to learning Access.