Introduction

We have implemented a flexible querying tool for a client to help them search and retrieve their business data. This solution integrates the Microsoft SQL Server Reporting Services Web Service with a .NET Windows desktop application to provide a flexible and intuitive search, sort and export system for their data.

We are able to describe this business search wizard generically although we are unable to release specific details of our client’s customized product.

Requirements

To provide an application to allow staff to query their company’s data with the following specification:

Selection specification

• A simple and intuitive selection screen
• Several parameters to select from (15)
• Easy selection (and de-selection) of criteria with drop down choice boxes where possible and a text translation for all coded fields
• Ability to select more than one value from a parameter where appropriate
• Date picker settings for date ranges
• Changeable default settings for all criteria
• Easy resetting of criteria to defaults
• Ability to choose which field set to be displayed
• Buttons to run or abort a selection

Display specification

• Data displayed in a data sheet manner on a new screen (Microsoft Excel style) after the selection is run
• A pre-determined list of fields returned and displayed in a pre-determined order
• Ability to sort on all or most fields after they are displayed
• Navigation to return to selection screen in order to refine selection
• A count of the records displayed

Export specification

• Button to export displayed fields to Microsoft Excel or other specified package
• Ability to choose the file name and location of the exported file
• Navigation to return to another screen

Solution

We integrated the SQL Server Reporting Services web service with a Windows Forms .NET application to produce a fast, easy to use, interface for our client to perform ad hoc data searches. Two screens were provided:

• Search Criteria interface screen with a wide range of named fields to select: some with drop-down lists; some with data range options and some facilitating multi-choice selection.
• Results Display Screen showing the returned records in datasheet format with options to automatically export to Microsoft Excel.

Benefits

• Users will be able to perform queries themselves without waiting for the availability of a SQL programmer.
• Users will be able to refine and fine tune their searches enabling them to directly answer management questions.
• Users will be able to sort the data file on any field of their choice.
• Users will be able to export the data to a file of their choice.
• Users will be able to spot and report data inconsistencies before data is released.

Overview

A search wizard is a valuable tool for any organization that has a database at the core of its business. It enables staff to answer the majority of questions that occur from day-to-day without needing to understand the database structure or language.

Advent IT have implemented a reporting solution for Momenta to help them manage the DTI's Knowledge Transfer Partnership (KTP) Programme. This solution integrates Microsoft SQL Server Reporting Services with SoftArtisans OfficeWriter to provide a powerful, centralised reporting and mail merge system.

Please read the case study on the Microsoft site for more information.

Many people have said to us that they find Access Workgroup security impossibly difficult, and the more they read, the worse it gets! We have adopted a simple and yet effective approach, for which we are indebted to our colleague David Hay.

Although you can, if you wish, employ the full capabilities of the security mechanism which Microsoft supply (groups of users with allocated rights, and/or specific rights for individuals), we feel that this is not only a high-maintenance approach (you need to keep editing the settings as people join, move around and leave), but it also fails to encompass some of the features which we regularly need, such as reporting structure and telephone extension, so we prefer to include a users table in the database (we normally call this USysUsers, by the way, so that it is normally hidden in the database window. If you haven’t discovered the “USys” prefix, it is a very useful trick for all sorts of cases!).

Some observations about the non-secured Access installation are as follows:

• The default creator, and hence owner, of ALL databases and contained objects in the environment initially will be “Admin”.
• There is an Admin user on all installations of Access, and security is in place, but because there is no password for admin, no login screen appears.
• You can change the ownership of any object except the database itself – this is a common cause of frustration!
• The workgroup file includes by default two “Groups”: Admins and Users. The Admin user is a member of both; there must be at least one user in Admins (otherwise it would be impossible to administer databases…).
• Permissions (rights) are allocated for both of these groups in the default file. The permissions which apply are the least restrictive set available to a user. So, although they may be restricted as an individual, they may gain extra privileges through membership of groups. The Admin user has full rights to all non-Microsoft objects.
• Members of the Admins group, plus the owner of an object, can change the permissions on an object.

Our approach uses a new Workgroup (MDW) file in which the Admin user is joined by one other “Super-user” to whom all the powers are given. Poor old Admin is demoted to have only the permissions which this upstart decrees. The new workgroup file is used when you are creating and administering databases; but (and this is the cunning part) you can give your MDB files to anyone else in the world to use, and, because they don’t have a “Super-user” in the System.mdw which was created when Access installed on their machine, they are stuck with the settings you have dictated.

Step-by-step

Use the Workgroup Administrator tool to create a brand new Workgroup file. Ensure that you enter a unique and non-guessable “WorkgroupID” (like an extended PIN number) and give the file a recognisable name. A good tip is to take a screen dump of the confirmation screen which appears towards the end of the process; paste the picture into Word and print it out for retention in a safe place: you’ll need the information should you ever need to re-build the workgroup file.

• “Join” the new workgroup file (ultimately it is a good idea to have two desktop icons to launch Access; one has the standard command line, and the other includes the “/Wrkgrp xxx.MDW” flag to join your new group for the session of using Access: you naturally employ this icon when you are going to do some development).
• Start Access and, via Tools/Security/User and Group Accounts, set a password for Admin.
• Also create a new user: we’ll call it SuperUser here, but please choose something for yourself! Again, ensure that you note the PIN Number which you chose for this user. SuperUser may have a password, but does not actually need to.
• Add the new user to the Admins Group, then close Access.
• Start the program again, this time logging-in as SuperUser.
• Remove the Admin user from the Admins group – vital!

You are now ready to begin creating secure databases! Just remember to log-in as SuperUser when you start a new project or whenever you are working on the database structure (rather than the data).

• Under Tools/Security/user and Group Permissions, we recommend that you remove ALL permissions from both Groups (i.e. no-one gets any spurious permissions just because they happen to belong to a specific group).

It is easiest to set the default permissions for users before you create any objects within the database; for each type of object, simply select the “” entry in the list and reduce the permissions as you feel appropriate. Then you can forget the whole issue!

Retro-fitting security to a database

If you need to apply restrictions to an existing database, the first thing to remember is that you cannot change the ownership of the database. So, you need to create a new database and import into it all of the objects from the original file. Change their owner to be SuperUser, and then adjust the permissions of all of the objects as above (it is easy to select all of the members of the lists, but don’t try to change the ownership or permissions on Microsoft’s MSys objects!).

The User-level Security Wizard will do all of this for you, but in Access 97 do remember that you must be logged-in as your SuperUser, otherwise you’ll see no effect! Access 2000 automatically prevents this possibility.

One other tip is that, if you use the common “two-database approach” with a data file and a “front-end” file, you’ll need to grant Admin permissions on the tables in the front-end file if there will be the need to change the connection string (i.e. if the back-end file may move or be mapped to a different drive on users’ machines).

The other, vital tip is to remember to take with you a copy of your developer’s MDW file when you go on-site – otherwise you won’t be able to make any changes to the database structure!

Self-joins are useful in SQL select statements and are often used to identify duplicate entries or entries linked in other ways such as customers who share the same telephone number or patients who share the same address.

A self-join is an internal table (or view) join. The self-join is between fields in a table and fields within a virtual copy of the table. For instance in a hospital database the patients’ address and postcode details are kept in the table tblAddress with the identifier of the patient in the field PatientID.

The following SQL statement can be run in Query Analyzer in order for the hospital administrators to list the patients who share an address and patients who have been erroneously registered twice under different PatientID’s:

SQL statement

Note: aliases are used to distinctly name fields or tables with the same name to clarify ambiguities.

This returns the following results set:

The Patients corresponding to these ID pairs can then be investigated to test whether they are duplicates registrations or different patients, possibly relatives, with the same address.

Self-joins can also be written as outer joins.

When joining two tables in a SQL database it is necessary to consider what type of join is required.

There are 4 possible ways in which these tables can be joined; Inner Join, Left Join, Right Join and Full Join (all of which may give different outcomes). Left, Right and Full Joins are all outer Joins. The mystery of outer joins often puts people off them but this article seeks to explain left and right joins with a set of simple examples.

The following examples use two tables in a hospital database. The patient table tblPatient (11 records) contains a code (PatientDisease) for their disease and the codelist or lookup table tlkpDisease (12 records) contains a list of disease codes (DiseaseID) and their meaning in words (DiseaseText).

1. Inner Join

This is where only those records from the table on the Left of the Join that match those from the table on the Right of the Join are returned.

SQL statement

Note: The word Inner may be omitted as Inner joins are assumed by default.

This returns the following results set:

Note: Only those 9 patients who have a patientdisease which is in the disease codelist table are returned in this results set.

2. Left Join

This is where all records from the table on the Left of the Join are returned and only those that match from the table on the Right of the Join.

SQL statement

This returns the following results set:

Note: All 11 patients from the patients table and all 12 diseases from the disease codelist table are returned in the results set.

3. Right Join

This is where all records from the table on the Right of the Join are returned and only those that match from the table on the Left of the Join are returned.

SQL statement

This returns the following results set:

Note: All 12 diseases from the disease codelist table are returned in the results set whether or not there is a patient with that disease in the patient table.

4. Full Join

This is where all records from the table on the Left of the Join are returned and all those from the table on the Right of the Join are returned.

SQL statement

This returns the following results set:

Note: All 11 patients from the patients table and all 12 diseases from the disease codelist table are returned in the results set.

Summary

The above SQL statement returned four different results sets using the same join criteria on the same tables with different join types.

Everyone wants the performance of their database to be optimal. In particular, there is often a requirement for a specific query or object that is query based, to run faster.

The performance of a query is affected by the tables or queries that underly the query and by the complexity of the query. Table based forms are faster than query based forms and attached tables are slower than integral tables. Sometimes it may be wise to import rather than to attach frequently accessed foreign tables.

Access uses Rushmore techniques borrowed from FoxPro, to automatically optimise queries that contain two or more indexes. (Rushmore supports some, but not all foreign formats of attached table. For example, it supports FoxPro but not Btrieve. As Rushmore is an automatic process there is no need to understand it except to remember to index two or more query fields for all but the simplest of queries.)

Here are some of the methods that Advent IT use to optimise the speed of Access queries:

• Display the minimum number of fields in a query. Set criteria dependant fields that are not required in the dynaset to "not shown".
• Index all restriction based fields, all fields included in expressions, all sorted fields and all join fields.
• Use primary keys or unique indexes wherever possible.
• Use numeric rather than text primary keys.
• Use non blank unique fields.
• Avoid the use of IIf() function in queries.
• Avoid domain aggregate functions such as Dlookup().
• Make careful use of Between and Equal to, rather than > or < speeds up queries.
• Use fixed column headings in Crosstab queries.
• For reports based on queries use Portrait view in preference to Landscape and select Fast Laser Printing to Yes (View,Options,Other Properties).
• Use Make table queries for running reports on static data. These are called snapshot reports.
• Use Count (*) rather than Count(Column).
• When creating restrictions on a joined column in one-to-many relationships, test out the comparative performance when placing the restriction on the "one" side or the "many" side. The "one" side is not always the fastest — the "many" may have markedly fewer records.
• Short table and field names run faster than long names.
• Normalise tables — join strategies execute more quickly on smaller tables.
• Denormalise tables — reduce the number of joins. Get the balance right between normalisation and denormalisation by experiment.
• Avoid the use of Distinct Row queries — Union queries do not need the distinct row feature as they are automatically return unique fields unless set to Union All.

What is a database?

At the heart of all businesses, whether they are scientific, medical, educational, research-based or commercial, are data. Companies acquire data from their clients, patients, subscribers, customers and suppliers and from their own products and services. Businesses often require data from other sources as reference data for their own products or services. This reference data includes information such as: population and demographic data; stocks and share prices; consumer profiles; medical code lists; telephone and email directories. The key to a company’s success is how they organise their data.

When business data is kept in several disparate formats, manual intervention is needed in order to cross reference data or link one or more datasets to each other. When a company has lots of separate datasets it is challenging for any one individual to fully understand the company’s business. By analysing the company’s business with a view to bringing datasets together it is possible to document and display all the facets of a company’s data. Design methods are then used to arrange the different datasets in a manner that any grouping, search or calculation can be made across all a company’s datasets and the power of the data can be harnessed and fully exploited. This designed arrangement of data is called a database design. Once the design is agreed a database can be developed to provide an integral business solution.

The benefits of databases

Implementing a database solution for a business will enhance productivity and accuracy. Duplicated processes can be eradicated; data can be entered by several people simultaneously, data can be validated on data-entry; data-entry can be reduced by providing pre-typed lookup lists and duplicate records can be exposed and removed. Data can be displayed dynamically to anyone who has permitted access in a wealth of different formats and reports. When web based solutions are used, data can be securely, entered and viewed anywhere in the world. A company’s web site can be kept up-to-date from their database facilitating marketing and sales.

Interrogating databases empowers organizations to achieve their targets. Knowing which customers are also suppliers; calculating the effect of international exchange rates on product or component prices; deriving population based incidence rates or calculating the potential size of a company’s client base can be keys to marketing success; better sales margins; achieving research goals or business plan acceptance.

Who needs databases?

In short all businesses need to store their data in fully relational databases which will enable them to utilise and exploit their data. For some business models there is excellent ‘off the shelf’ software which will be perfect for their purposes, while for specialist and research businesses it will be necessary to request customised software developed by specialists such as Advent IT.