Hands on: Blasts from the past

Time doesn’t necessarily lessen the relevance of database solutions

I’ve been writing the database column for very nearly half PCW’s lifetime - since September 1993.

Looking back at that first column I find names both current and those that are distant memories.

I reported that Approach had been bought by Lotus: it’s now in the hands of IBM but still called Lotus Approach. It’s part of Smartsuite and has reached the dizzy heights of version 9.8.

I also reported on a Borland database conference and offered a tip for Paradox for Dos users. However, there was also a tip for Microsoft Access users - no change there, then - and the keystrokes needed to show the Access gang screen in Access 1.0.

It was an animation that showed two ducks floating peacefully upon a pond. A cloud trundled out over the water and blam! It hurled a bolt of lightning at the unsuspecting water fowl. As I said at the time: “The initial graphic makes sense as long as you know that the code name of Access was Cirrus and remember that a brace of water fowl can also be referred to as a pair o’ ducks”. You also, of course, have to pronounce pair o’ ducks with an American accent to come up with the name of the then major competitor, Paradox. I always did like a good pun.

So Access was barely a year old in September 1993 and already had a presence in the column. My second column included a review of Access 1.1 - an upgrade from 1.0 cost £14.95. From its launch I felt that Access was a huge improvement on the PC database products that had gone before - Dbase, Paradox, Foxpro, Filemaker etc - because it was relational in a meaningful way, unlike the others. However it very nearly wasn’t, as some little-known history (which I didn’t publish at the time) shows.

When the development team was put together and given the remit of creating a database management system for Microsoft, it developed a product codenamed Omega. During the development, of course, the team learnt a great deal about databases and relational database theory. So much so that by the end of the process, they felt they were finally in an excellent position to create a great product, but that Omega wasn’t it. They also knew that if Omega was released, future developments would forever be burdened and constrained by Omega’s less than optimal design.

The team presented this argument to Bill Gates and, to his great credit, he gave the OK to start again, a decision which must have written off a small fortune in development costs. The team started again on a product codenamed Cirrus - and the rest is history (as were the ducks).

Generically speaking
Most of the problems now sent to the column are about Access but, whenever possible, I try to give generic solutions to database problems so that readers using other relational databases (SQL Server, Oracle etc) can benefit. There are, of course, subtle differences in implementation, but the approach to the solution is often transferable. The database question in this month’s Question Time is a good example: Access syntax requires dates in queries to be wrapped up inside hash (#) symbols but the crux of the solution is the ‘Between… And’ operator. This is part of the SQL querying language and will work in any RDBMS that supports SQL (and effectively all RDBMSs do).

The generic nature of databases also means that most problems and topics covered in the column over the years are as applicable today as they were at the time.

Warning - book plug!
“Yes, that’s right, Michael, I do actually have a book coming out. Yes, now that you ask, I do have a copy with me to show the viewers…”

It’s like this, you see. A while ago I started to look back at the past columns and realised that there was a rich vein of material that might interest people - articles about databases in general, Access-specific questions and so on.

Of course, my immediate thought was: “How can I make money out of this?” and naturally my thoughts turned to a book. The easy solution was to publish the columns, as written, in chronological order; easy, but inefficient for the reader.

For a start, sometimes I publish a problem and solution, whereupon a reader writes in with a better solution. Yes, I admit it, I don’t claim to be smarter than the combined intellect of the entire readership of the column. So I publish the better solution a couple of months later. It seemed to make far more sense to bring those answers together.

The rewrite also allowed me to remove material that is no longer relevant and to bring all the sample databases (there are about 70 of them) up to date and to check that all the code and examples still work (a non-trivial task: some of the examples are in Access 1.0).

Finally, the rewrite meant that I could try to group similar information together. For example, there are multiple problems associated with dates, so all of these can go together, as can all the discussions about the relational model. The bad news is that it doesn’t work; there is too much overlap.

Should a solution that uses a complex query to solve a date problem be in the section on dates or complex queries? The answer, of course, is obvious - use a database as the index. Each article, section and problem is classified in several ways and the reader can either read the book in series or query the database to find the relevant bits. To give you a flavour, the headings so far are:

Access specific
Introductory
Intermediate
Programming
Database general
Introductory
Intermediate
Advanced
Date and Time
Sorting and Ordering
Mixed Bag

By the time you read this the snappily titled Essential Database Stuff - Collected Database columns from PCW should be nearly finished. If you are interested, check out: www.penguinsoft.co.uk for more details.
And to prove that questions from the past are still relevant…

From January 1994: Maximum values
Question: I have a list of people, together with the dates upon which they start, finish and are paid, for projects that they undertake. I want to be able to extract the most recent date in each field for each person.

The problem is that the dates for each person will not necessarily come from the same record. For example, given the following two projects for Brian:

Start Finish Paid
Brian 12/12/92 15/12/92 6/5/93
Brian 19/12/92 23/1/93 3/2/93

I want the output:
Brian 19/12/92 23/1/93 6/5/93

Answer: Open a new query, select the appropriate table and place all the field names onto the grid. Press the Totals button in the toolbar and a Total: row will appear in the grid. Select Group by: under the name field and Max in the remaining three. Then run the query.
The query grid is shown in the screenshot at the top-left of this page and this is the query in SQL:

SELECT Person, Max(StartDate) AS MaxOfStartDate, Max(EndDate) AS MaxOfEndDate, Max(PaidDate) AS MaxOfPaidDate
FROM WorkDates
GROUP BY Person;

The .mdb file is DBCAPR08.MDB containing the WorkDates table, the query is called Brian.

From January 1998: Modifying your startup
Access typically starts up with the words Microsoft Access in the top left of the screen (in the title bar). You can configure this to read anything you want; boringly the name of your application, more excitingly a short description of your boss (don’t blame me if you get fired). Simply right-click the title bar of the database container (not the title bar of Access itself) and select Startup. In the dialogue box that appears you can type the heading of your choice into the Application Title box. You can even change the icon.

From May 2008: The AUG
Yes, we have fast-forwarded to the future. The Access User Group is one of my favourite associations.
Run by the indomitable Rod Gordon of Gordon Associates, its meetings are always hugely enjoyable because the members delight both in the product and in sharing information with each other. New members are always welcome and the next meeting is scheduled for 15 May. Details are available from www.ukaug.co.uk. I like the group so much I’ll be there, talking about exotic querying...