Our new book, Professional Access 2013 Programming, was written and published. The book contains two divisions, one on the Access web app functionality introduced in Access 2013/SharePoint 2013/Office 365, and the other providing advanced techniques for Access client databases. I worked with Ben Clothier on the web app component.
My previous employer closed its doors, prompting me to find new employment. I am now a Reporting Analyst in the Revenue Operations Department of Davita, one of the country's leading providers of kidney dialysis treatment. While I'm no longer doing the work I love most, I'm really proud to be part of an organization which can honestly say "we save lives every day".
At Davita, my job as a Reporting Analyst involves an Oracle Data Warehouse, Access 2007 to retrieve and manipulate data from the data warehouse, and Excel 2007 for reporting. While I no longer have much chance to work with Access as an interface design tool, it's certainly a crucial part of my toolbox in its data integration and manipulation role.
On January 1st, I was re-newed for the Microsoft Access MVP award. It's a humbling experience to be part of this group. They are my esteemed mentors and coaches and some of my best friends. It's an honor to be among you.
On the other hand, it's clear I've not lived up to the promise to provide follow-on blog posts on the vagaries of Lookup fields in tables, in Access. Well, on the theory that it's better to be late than never, I'm back to drop in a few follow up comments about the inherent difficulties in Lookup Fields, as used in traditional Access client tables. I've moderated my opinion a little--to the extent that I really no longer think they are evil. I just think they open the door to rookie mistakes that create damage and confusion, and are best left out of the picture.
If you want to take a quick look at what I posted last year, you'll see that I only briefly touched on the issue of ambiguity about the actual value stored in a lookup field. And, you'll also see that I really only touched on table views of the data, not what would happen in a combo or list box in a form. To say nothing of a lookup field used in a query or on a report. It's sufficient to say that ambiguity can be spread liberally around your database as soon as you let lookups in the door; just don't do it.
But, as I forecast in that last blog, there's a more sinister problem waiting for the unwary. Lookup Fields make it possible for a naïve user to store, incorrectly, the VALUE from the lookup table instead of its FOREIGN KEY. That, my friends, is as close to being evil as it can get, IMO.
Here are three screenshots illustrating this problem. I'll explain what happened after you look at the pictures. Most of you will, I'm sure, get it immediately from just looking at the screenshots.
 |
| Two Lookup Fields in a Table, Visually Identical but Logically Very Different |
 |
| Technically Correct Implementation of a Lookup Field |
 |
| Technically wrong Implementation of a Lookup Field |
Okay, so here's the deal. The two lookup fields in the first image look exactly alike. Both present the concatenated First and Last Name fields. Just looking at them in a table, you'd never know how differently they were constructed. Moreover, until you select one or the other, a new developer or naïve user would have no clue they are even lookup fields (although it is clear to seasoned developers that they must be either that or perhaps calculated fields.) The point is that they represent a subtle trap for inexperienced developers.The second and third images show how these two lookup fields were created. The second image shows a technically correct implementation, with the Foreign Key value being stored, not the concatenated name. It's still not ideal because it does drag in that display value, but at least it doesn't introduce real errors.
The third image reveals one of the all-too-frequent errors committed by naïve, inexperienced developers. They think, "why not just store the value I want. After all the Lookup field makes that easy. And before they know it, they have a real mess on their hands. Confusing, illogical, just plain wrong.
I think I'm going to move on from here, although I can think of at least one or misuse of Lookup fields I've seen in tables created by inexperienced users (Trying to use lookups across fields in the same table to set up a Self-Join field.) There are bigger fish to fry, and I can't afford to wait another 11 months to post another installment.
Let me just sum up by pointing out that Lookup Fields in Tables are pointless at best and flat-out dangerous at worst. I can't say you should NEVER use them, but it's my opinion that doing so should be left to the pros. It's sort of like dynamite and rubbing alcohol. Used properly by someone who knows what they are doing, all three can be useful tools. Using them improperly by inexperienced newbies is just asking for missing fingers, blindness, and databases that don't work right.
