by Garry Robinson
This article shows you how you can use Microsoft Access 2007 as a basic data mining tool for exploring your valuable data. In the article we will illustrate how data filters, pivot graphs, queries in graphs and filters in reports can help this cause.
Professionals will tell you data mining is the use of automated techniques to establish useful trends/information in the database(s) that organizations have spent fortunes acquiring. Once derived, this information is then used to improve business practices or make better business decisions. The techniques that are involved are many and include analysis by association, clustering, classification, fuzzy logic, neural networks and data cleansing. These techniques then are coupled with visualization tools ranging from reporting, graphing, OLAP and others. Why not find out more by heading to one of the data mining aggregate sites such as http://www.kdnuggets.com/
Having a professional approach to data mining is great but what about the rest of the population (the other 99%) who neither understand data mining nor have the time and money to get professional data mining tools working on their databases. What can they do?
Why not use the popular Microsoft Access tool to explore our databases rather than a new tool. Firstly Access is tightly integrated with the charting tools used in Microsoft Office/Excel. (See Figure 1).The charting tools used by Access are similar the same as the product used by Excel, the main difference is that Access uses queries where Excel refers to cells. Secondly Access supports industrial strength SQL queries and through Links, can be used as a client application to any variety of databases ranging from text files, spreadsheets, SharePoint Lists, SQL server, Oracle, MySQL etc. On top of that, well designed forms & VBA can help you establish an interface that will give your users the confidence to start investigating their data by themselves.
In Access 2007 there have been some new innovations that will help people explore their data. These include the user interface used for data filtering has been made like for like to data filtering in Excel 2007; pivot charts, pivot tables are prominent in the user interface and interactive filters are now available in Access reports.
Figure 1 - A sample of the range of graph styles available in Pivot Charts
Before I start show examples of the different tools, Figure 2 show the sample data that I have used to create the Figures in this article.
Figure 2 - The sample data
So how are we going to use Access as a data mining tool ? Well the truth is that since Access 95 came out with Filter By Selection and Filter by form for both tables and queries, a basic set of data exploring tools has been available in queries and forms.
For example, Filter by Selection is a primary example of a how you can explore your data. By right clicking on the year (say 2008) and choosing filter by selection, you will show only sales for that month. This filter by selection is in essence one of the most important data mining techniques - a drilldown. It is into this framework that the changes to the filtering user-interface in Access 2007 to match Excel data filtering has made Access a more universal tool. This is demonstrated in Figure 3 where the new filtering interface is demonstrated.
Figure 3 - Filtering in Access 2007 is now very similar to Excel
If you like filtering data, then there is one great new innovation that will answer many of your data questions and that is Totals. When you have a query open, in the Home Ribbon you will find a Totals button. Click on this and an extra row will be added to your query as shown in Figure 4. Now you can right click in that new Row and add Sums or Averages or Counts like I have done. For me this innovation is really useful as I was for ever copying and pasting to Excel to compute totals after filtering.
Figure 4 - Adding a Totals row to an Access query reduces the need for extra reports or copying to Excel
In earlier versions of Access, when you looked at the data in a query, you could do little else in the query interface apart from Sort and Filter. In Access 2007, you now can turn the Query into an instant interactive Pivot grid view with lots of further data exploration options. If you have a look at Figure 5 you will see the interface that you get when you view the query in this manner. Also when you switch from an ordinary query view to the PivotTable view, the filters that you have already used are maintained.
Figure 5 - Viewing queries in Pivot Table View
As well as viewing queries directly as Pivot Tables, you can view them as Pivot Charts and use the same field and filtering interface as the PivotTable. Whilst this to is a valuable tool, it will require the user to understand queries to ensure that the data that is being extracted is suitable to the chart views. This is one area where it is good for the Access expert who understands the database model to sit down with the charting user to ensure that they are getting the information in the right way. Usually just one or two good samples is enough to fire the creative juices of the power user. Also if you tell the power user that the pivots views work the same as Excel Pivots, they will get motivated.
Figure 6 - Viewing queries as charts in PivotChart view
Note that you can incorporate Pivots into Forms and Reports as subforms but you have to set the default view property of the subform to the correct view and allow that type of view.
When it comes to software, I always find that the best innovations are ones which extend a popular technique rather than ones that force you to understand a new technique. In Access 2007, reports can now be filtered as they are being displayed, this allows you to make your reports more powerful with almost no changes. To make this work, you can bring up the filtering options (which work just like queries) by right clicking on a field in the report as I have done in Figure 7.
Figure 7 - Exploring data by filtering an Access 2007 report
Now the final way that you can explore data uses Microsoft Graph and this is more the realm of the professional programmer. With MS Graph, when you have a large amount of data, it is totally impractical to review data as individual records. So with MS Graph objects, you will almost always use consolidation queries (group by) for two dimensional graphs and crosstab queries which allow you to display the data in three dimensions.
For example, this consolidation query computes the Bottled Sales by Month for "The America's" and this query can be used to provide the row source for a Microsoft Graph.
SELECT Format([SalesDate],"mmmm") AS Months, Sum(Sales) AS SumSales
WHERE (((Region)='The Americas') AND
GROUP BY Format([SalesDate],"mmmm");
The trick for professionals to getting with these tools to work is understanding the data model, establishing appropriate queries and fitting them into the user interfaces. The trick for Power users is probably to get the developers to setup a good sample of the correct tool with a similar query and then using that as a prototype to explore your data. What ever your approach, you will agree after seeing these options that Access 2007 has added some really cool and useful tools for the amateur data miner.
Garry Robinson from Sydney, Australia
Garry Robinson runs GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues, visit his companies popular web site at http://www.vb123.com/ or sign up for his Access email newsletter by sending a blank email here. The web site features many Access resource sand software that are used by more than 10,000 readers a month. To find out about Garry’s book which is called “Real World Microsoft Access Database Protection and Security”, point your browser to http://www.vb123.com/map/. You can find Garry’s contact details at … www.gr-fx.com
Sample database is suited only to Access 2007
If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.
To experiment with graphs, open the database and start up a new form. Now choose the Chart Wizard along with a table that has some data that you want to analyze. You may find it frustrating trying to get out that exact chart that you want but the most important thing with graphs is first ensure that the query is delivering the right data before you invest time setting up the graph properly. Whilst you are exploring your graph, open it in design view and review the query in the RowSource property. You can change this query manually to see the effect on your final graph.
Once you have sorted out those queries, you can then start using the Access Graph wizard which you will find when you make a new form. Build the consolidation queries in this wizard to match those that you have just experimented with. The resultant graphs that you produce can be used in your production system.
Having mastered these skills, its time to get clever. After experimenting with the trial data that comes with grafFilters.mdb download, import the objects into your database. Change the names of the tables and queries in the TableReq object to match tables in your system and once the trial system works for your data, you will be free to expand the form by experimentation.
Forms![YourForm]![YourGraphObject].RowSource = SavedQuery1
So in conclusion, the power of this technology for developers is that you can feed the graph objects in a form with different SQL or queries built on the fly. This will give your application visual sophistication plus allowing your users to look for useful trends in their data.
Get Good Help Here
If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions
We have converted vb123.com to Expression Web, contact us if we can help you move to the latest Microsoft web tool.
About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia