Turn data into information

Pro

1. What are data tables?

Data tables are depositories of all raw data in computers. No matter what application software you are using, the raw data is kept in tables. A print of one looks like a grid of rows and columns, not unlike the appearance of a spreadsheet. Each column contains data of a particular type, e.g. Date, Number, Text, etc. The first row in the grid contains the names of the columns. Each column must have a name and these names are referred to as field names. There are no blank columns or rows. The data in the individual cells in a column is sometimes referred to as Flags.

The raw data is entered into the rows; a row is known as a record.   A cell is never left blank just because the data is the same as the data in the cell above it. A blank cell means that there is no data for that cell.

A spreadsheet is not a data table, but a data table can exist within a spreadsheet. When making a data table in a spreadsheet, tuck it up into the top left hand corner as this facilitates the Wizard in Access.

2. Using data tables in Excel

In an Excel spreadsheet you will find a feature called PivotTable and PivotChart Report under Data on the main menu. This feature will cut and summarise the data in a table every, and anyway possible. It will extract every drop of information out of your data table and present it in a report on a spreadsheet. It can also automatically make a chart of the report.

You can drill down into any number in the report by double clicking on it, and it will show all the data behind the figure on a new sheet.

To get the information you want, simply follow the Wizard. First you tell it where your data is, then you tell it how you want the information presented. The latter is done in Layout where you drag and drop the field names into the boxes in the Wizard diagram.

There are four boxes presented: ‘Data’ are usually numeric fields, and you can put as many fields in here as you can manage. ‘Column’ is usually confined to the field with time periods, but not necessarily so. ‘Row’ usually holds the main summary category field. You can have a sub-category field, and a sub-sub-category field here. This box is usually limited to three fields. You can select or deselect any combination of Flags in any of the fields in this box. ‘Page’ is used to isolate the information to one particular ‘Flag’ in the field. You can have more than one field here, which gives you the opportunity to summarise on different combinations of a Flag from different fields.

3. Using data tables in Access

Access makes a new table from existing tables in either one of two ways.

The first is by matching fields and joining the records from two (only two) different tables. The other way is by selecting specified data and fields from one or more tables.

Access is a quick, cheap, and effective way of getting the data you want combined into a new table. You can export the new data table out of Access into Excel where you can turn it into information using Pivot Table.

For those who have not used Access before, just consider it as a ‘tables in, table out’ piece of software. You manage the ‘tables in’ under the Tables button, and you create a new table under the Queries button.

A spreadsheet user starting to use Access for the first time will find it very strange. The first thing you notice is that before you do anything with a new database, you have to name and save it.  This is necessary because data entered into a data table is automatically saved once you move to the next row, unlike a spreadsheet where the data is only saved when you save the sheet.  Also unlike a spreadsheet the number of rows you can have in a database table is limited only by the amount of memory available in your PC.

Before creating a new table by running Query, it is important to understand a technical feature called ‘Primary Key’. This is a pre-selected input table column, in which the data (flags) do not repeat and there are no blanks. If you try to match and join tables on columns other than on the primary key column in each table, you can get strange results.

Say you have two tables. Tables A is a list of stock parts and quantities, and Table B is a list of parts and quantities sold.  You want a new table with the parts in stock on the same row as the parts sold. For this to happen the same part must not appear more than once on each input table. If they do, you will get a crazy result because the PC will match records if a match is possible. It is not programmed to stop using a record when it has matched it once.

The easiest way to ensure that a flag does not appear more than once in a column is to summarise the data table in Excel using Pivot Table before importing or linking the table to Access and running the query.

As you can see, Excel and Access compliment each other. Anyone who works with data in spreadsheets and does not use data tools, such as Access, should seriously question their own efficiency. The next stage in the process of turning data into information is to link Access to the data tables in your application software.

4. Using application software data tables

As application software suites can be written in different languages, work under different operating systems, and the data tables managed by different database software, they can use one of a variety of table formats to store their data. Access offers a range of access providers to enable you to link to remote data held in various storage formats within the Windows operating environment. To find out which file formats your version of Access can link to, go into ‘Help’ in Access, and under the ‘Answer Wizard’ type ‘data sources’. One of the ‘Data Sources’ listed is ODBC (Open Database Connectivity). This is an industry-standard mechanism for accessing remote data tables. Access can link to tables that have an ODBC driver attached. You may need to check with your software supplier which data tools to use to link to the data tables.

If your application software data tables reside on a platform other than Microsoft Windows, you will need to use a proprietary data tool such as Cognos Impromptu rather than Access. Impromptu will do everything Access and Pivot Table in Excel will do, and you can decide on the format of the output, depending on what you are going to do next, read only, process in Excel, or publish on the Web etc.

When you first start to work with data in application software, you may find it a little confusing. This is because the column names are abbreviated and there can be many different tables.  Some of the abbreviations can be obscure, however when you see the populated tables, it becomes clear what the abbreviations mean.  Although there can be hundreds of data tables in an application software suite, it is unlikely that a single user will be interested in more than a small number of them. As a rule of thumb, you can take it that the primary key is the first column on the left.

5. Reports

So far we have concentrated on making new data tables from existing tables. The data in data tables can also be presented in readable report format. This is where data is omitted from a row when it is the same as the row above it, and there is usually an element of processing involved. The information in reports can be laid out to suit the reader. Reports are usually designed to present the information as clearly as possible. Because of their layout and construction, they are generally not efficient for processing further in spreadsheets, despite the fact that many software packages make them available for export to spreadsheets.

The most common tools used for making reports from application software data tables are Microsoft Access (http://support.microsoft.com), Cognos Impromptu (www.cognos.com), and Seagate Crystal Reports (www.seagate.com).

Before one starts to use any of these reporting tools, one should make a study of the populated data tables in their application software. When learning to use Access, it is a good idea to start by using ‘Tables’ and ‘Query’, before moving on to report making, as this gives one the understanding of what data is available in which table, and how to make a new table.

There are many enterprises that rely solely on the reports available on the menu that came with the software. This is not a good idea as it is generally less than all of the possible information and, cannot realistically cover all users needs at all times.

6. Conclusion

Turning data into information is not exclusive to IT professionals. The ordinary user can do it, and should be encouraged. Many people need information on the fly (while working on something else) and the ability to dip into the data tables and get it for oneself is very cost effective. In many enterprises, requesting information from the IT staff has become a thing of the past.

The process for which the information is required will dictate the format of the output. For example, Table format is best if the information is to be processed further in Access or Excel. HTML format if it is to be published on the Web, and Report format if the information is for visual display only.

The usability of the data tables should be checked out before one buys application software, not only for linking to but also the contents of the tables. This is particularly important if it is financial software. The tables and their contents will dictate how simple using data tools will be.

The business management advantages and efficiencies associated with turning data into information using data tools such as Access, Impromptu, and Crystal Reports should not be ignored. You can safely bet that your competition does not ignore them.

Read More: Hardware & Equipment