Hands on - Databases - Reporting revisited. A solution is improved, and we go back to database basics to explain normalisation.(Column)

Publication: Personal Computer World

Bookmark this article Print this article Link to this article Email this article Digg It! Add to del.icio.us RSS

COPYRIGHT 2005 VNU Business Media Europe

In the June issue I provided a solution for a reporting problem, which has now been improved upon by a reader. Imagine we manufacture items to which different engineering processes (casting, milling and painting) can be applied - any component can have any number of processes. We store this information in a four-table database - the first stores information about each component, the casting table stores the ID of every product that is cast, together with details about the different casting processes applied. There are two other similar tables, one for milling and the other for painting (see screen 1).

This is a good, normalised structure (see box overleaf for more on normalisation), but suppose we need to produce a report where information from the three different tables appears under one heading. The solution I provided used several queries to create a materialised table, ReportHolder, which has a single column (Process) that contains information from the three different processes. Given the data in this format, it is simple to write the report we need. My solution worked, but Ian Williams has a much neater one.

He solved the problem using a single UNION query (called qryUNION) to combine data from the three process tables like this:

SELECT 'Casting' AS Header, 4

ComponentID, Description, 4

Process

FROM Casting

UNION ALL

SELECT 'Milling' AS Header, 4

ComponentID, Description, 4

Process

FROM Milling

UNION ALL

SELECT 'Painting' AS Header, 4

ComponentID, Description, 4

Process FROM Painting;

(Key: 4 code string continues)

Then...

Read the full article for free courtesy of your local library.