Matching Data for Analysis

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > May-2002 >

Matching Data for Analysis


Rickard Olsson


In this article, Rickard Olsson shows how to compare rows in

SQL by loading the desired data into two tables for easy

comparison. In fact, he shows two different methods and tries

to figure out which method will give the best performance.


I was inspired by Mike Westcott’s article in the January

2002 issue about OrderedCalculations with SQL


He ended up with a philosophy about taxi drivers

that stressed why we have to accept that handling

comparisons between rows using SQL isn’t problemfree.

I found myself with exactly this problem with one

of my customers, and I had to solve the comparison

problem reliably. If I couldn’t do it, the application would

be worthless.


My customer was teaching air-traffic controllers

using a Unix-based simulator that re-creates the work

environment for air-controllers. They also use the

simulator for creating situations that don’t yet exist but

will arise (for example, adding a third runway at Arlanda

in Stockholm or increasing the traffic load in Landvetter,

Sweden). The simulator logs everything that happens in a

simulation exercise and loads all logged data into a large

Oracle database, running on Unix.


One of the most important factors that influences the

stress put on controllers is how many radio calls they

have to make (and how long each takes) to carry out their

mission. The data produced by the simulator (radio call

start time, radio call end time) isn’t much help in

analyzing the level of stress. My clients needed more

analysis than the software provided.


My clients wanted a fast way of getting the

simulation results presented in a graphical format that

could be shown to the people involved in the exercise. At

the debriefing session held after each exercise, the

participants discuss the outcome of the exercise. They

wanted some facts to be added to their subjective

experiences. I suggested an Access solution with an

interface that would support simple selection and

grouping of data. My client was expecting my solution to

have a response time in the range of one to two seconds to

produce the graph so that drawing on the numbers

wouldn’t slow the discussion. Figure 1 shows the kind of ...





Figure 1. This is

what they want.


Read More Here:

Matching Data for Analysis