The link between databases and developers

Now let’s query the Product table for all the products painted mauve. Write a query with the Query Design tool (on the Create tab in the Other group ­ poor queries, the heart of data extraction and relegated to Other…) or in SQL, the code being:

SELECT Product, ColourID
FROM Product
WHERE ColourID=”mauve”;

Click View or Run and all you get is an error message: ‘Data type mismatch in criteria expression’. This is the ‘why not’ referred to earlier.

What’s required here is the numeric ID for the colour, like this:

SELECT Product, ColourID
FROM Product
WHERE ColourID=2;

Despite looking like text strings, the ColourID column contains numbers. The numbers are those used as primary key values in the ColourID column in the Colour table.

That’s what Access ‘sees’ when it queries the column and so you are left with a situation where a Lookup has been used to improve user-friendliness of the Product table but has the effect of leaving anyone who wants to query the table with a much higher workload.

An understanding of primary and foreign keys is required, plus knowledge of how a Lookup behaves.

This is the problem I was asked about by corporate users who had no knowledge of how the database had been constructed ­ queries didn’t run properly and sorting didn’t work as expected either.

When an alphabetical sort of the column was requested, results appeared to be random, because they were being sorted by primary key value and not text string.

My advice has to be, don’t use lookup fields.

They do, of course, ‘contravene’ the relational model, which states that the table design should be entirely separate from the mechanisms that interrogate, manipulate and display that data.

Should you care about this? Well, it depends. If you believe the model is an abstract bit of theory that has no bearing on the real world then of course you shouldn’t.

But this case actually illustrates why the theory is worth obeying.

When we (or rather, the designers of Access) break it, something in the real world (in this case querying) breaks.
DBCJune09.mdb on this month’s cover disc contains a Product table with a normal foreign key field and a ProductLookup table with a Lookup.

We live in testing times
Recently, I solved a problem for a reader (Toby) who wanted times to be rounded to the nearest half an hour.

So anything between 11.15 and 11.44 inclusive becomes 11.30, 11.45 to 12.14 becomes 12pm and so on.

All Toby’s times were between 8.30am and 6pm, so we didn’t have to worry about times around midnight.

My solution worked but I felt it was over-complicated and asked if any readers could find a neater way to do it, and several did.

All of them were based around the premise that Access stores times as a fraction of a day.

So, for example, 6am is stored as .25 which is 6/24 of a day. If we multiply the time by 48 we convert the fraction into the number of half-hour units.

We can then use the Round function to round that number to the nearest integer, which is essentially the nearest whole half-hour.

Finally we divide by 48, which converts the number of half-hours back to a fraction of a day.

Here are three of the solutions:

UPDATE Production SET Production.RoundedTime = Round([EndTime]*48)/48
= Format(Round([EndTime]*48,0)/48,”Short Time”)
= [EndTime]*0+Int( [EndTime]*48+0.5 ) / 48

The first shows the complete SQL, the remainder just the variation in the use of functions.

Many thanks to all who replied, especially John McClenahan, Mike Woods and Andrew Kaye.

The top two work fine for almost all cases but not a quarter past the hour.

The third one works fine as far as my testing showed.