Hands on: Trawling engines

Indeed, if we try to solve this problem with SQL then we are using the wrong tool. The database engine is not designed to present data other than in the most basic form (as tables with rows and columns) and SQL doesn’t have any constructions for presentation.

This is the kind of task a reporting tool should undertake (and the one in Access might require you to delve down into Visual Basic to produce the desired result).

If your reporting tool won’t do it, change your reporting tool. Don’t change the database engine because doing so won’t help.

Incidentally, I’m not saying that it is impossible to solve Rodney’s problem using SQL - all manner of Byzantine constructions can be dreamed up to force it to do things for which it wasn’t designed; Cartesian joins and make-table queries usually feature in them. But you’d still be using the wrong tool.

A question of fish
Fran Cobden has an Access 2000 database for storing fishing activity on sea trips and has normalised the data structure but has met a stumbling block when designing new forms. Fran wants to duplicate the look of the form from the old non-normalised regime, which used a form with an embedded sub-form.

The form shows all the details about a single trip; the sub-form had one row for each day of fishing activity on the trip, which showed the number of each species of fish caught. This was fine except that there are 45 species of fish, identified by three-letter codes, of which on average 10 are caught per trip, so the sub-form was always jam-packed full of nulls. In the new, normalised database Fran hoped to lose all of the nulls.

The normalised database has two tables - one stores details about each trip (Trip) and one records the catch made on each day of fishing (Catch). This sample database has a much simplified version of the data.

Basing a form on the Trip table is so simple that we can use the wizard. The data for the sub-form is also easy: we can use this crosstab query called CrosstabCatch:

TRANSFORM Sum(Catch.Weight)AS SumOfWeight
SELECT Catch.TripID, Catch.4DayID, Sum(Catch.Weight) ASTotalWeight
FROM Catch
GROUP BY Catch.TripID, Catch.DayID
ORDER BY Catch.DayID
PIVOT Catch.Species;

So, we’ve got the data. This is the point for Access users where the limitations of the user interface begin to bite.

The obvious approach is to embed the crosstab query as a sub-form on to a form, but Access won’t allow that. It’s possible to proceed by making the crosstab query a parameter query (remembering to specify the data type by going to Query on the main menu, Parameters). A button can then be placed on the form which runs the parameter/crosstab query. Click the button and you’ll be prompted for the ID of the trip for which you want to see the records.

Type it in and the data is displayed. It’s a kludgy solution and a work-around typical of the type constructed when the UI runs out of gas. You can also attack the problem with VB code.

The workaround solution is in the database on this month’s cover disc, using a query called Par and the form, Trip. You’ll also find the database here. You can move through the records in the main form, click the Run Query button and enter the trip ID. The crosstab displays the results.