Mining For Data With Access

So you excel at Excel and find Access more than accessible. You are a confident user of the whole Microsoft Office suite, at home with its user interface and comfortable with its functionality. You have utilised the programs to collate data and efficiently organise your business projects and budgets. Where do you go from here?

Well, why not dig a little deeper into your data sets to uncover patterns and trends that could well prove invaluable. Why not go 'data mining'? Data mining is used in marketing and surveillance amongst other professions and a full understanding of its finer points would take in fuzzy logic, neural networks and other complex theoretical conundrums.

But the good news is that, if you use Microsoft Access, you don't need to understand all the theory behind data mining. Access' close relationship to Excel, especially its charting tools, means that it is the ideal application with which to explore and analyse spreadsheets and data sets. The thing that makes Access special is its use of queries as opposed to the cells of Excel; these allow for data filtering.

The most important types of filtering in regard to data mining are filtering by selection and filtering by form. Filtering by selection is one of the building blocks of data mining as it pinpoints a specific aspect of data and highlights their inter-relationships.

Filtering by form is useful if you do not know which subset of data is in need of investigation. With this option you can filter by column and cross-reference the data until you discover any appropriate patterns. To view this data in another form you can turn it into either a pivot table or chart; this more visual alternative can often result in users spotting a trend that remained hidden when viewed as raw data.

Additionally, a new line of totals can be added to queries in Access, which can further illuminate unseen trends in your data. The totals button can be found in the Home Ribbon tab and, as well as its uses in data filtering, eliminates the cutting and pasting necessary to find solutions in Excel.

All of this filtering is very well if you are data mining on an ad hoc basis but if you want to run regular queries doing it manually each time could become quite infuriating. Fortunately there is the option within Access to save queries so that ones you use regularly are only a click away.

Access also allows for queries in which the required data is more generalised. Instead of entering specific strings to analyse certain columns you can create a prompt to enter the data subset you want. This is known as an interactive query and is great for discovering wider ranging patterns within your data.

A surprising number of professionals just enter data into spreadsheets or tables without playing detective and looking for important trends. This is a big mistake as a really thorough understanding of the way data works can reap huge rewards in the business world. The wonders of Access coupled with a high quality training course can unlock the benefits of data mining.