... GPG On MS Access

It's all about the data.

Saturday, June 28, 2014

Estate -- Autunno

On a lazy Saturday afternoon in June, I've been remoting into an office on the other side of town to do a bit of Access work for a long-time client and listening to my rotating Pandora stations. One of the songs of my youth started me thinking about the next part of my life. Right now, Stevie Nix is asking if I can handle the changes in my life.

Well, I've been afraid of changing
Cause I've built my life around you.
But time makes you bolder,
Children get older,
And I'm getting older too

The truth is that I'm pretty close to retiring. Age-wise, I'm shy a year or two of being the "right" age, but emotionally and physically, I'm a lot closer to it. I still have a few clients--for whom I'm really grateful, by the way--and a full time job that's pretty interesting. But they are exerting less and less of a pull on me every day. To be honest, I never thought I'd get to the point where doing things with Access would become routine and not so challenging and exciting, but there it is. Access is an old friend who makes it easy and comfortable to work together, but the spark of discovery and exploration are slipping away.

So, feeling and thinking that way, something happened today that hasn't happened to me for many long months. I broke down and cried while listening to one of those songs that made me think about the woman I've loved for nearly 40 years. (Oscar D'Leon still has that power over me, commanding memories of the good times not forgotten.) Yolanda and I divorced years ago, but that can't change our past together. Moreover, it can't change my future. I know that now, and I've accepted it. It turns out I fell in love once and for all, and that is that. It's long past time to move on, but moving on is not possible.

Moreover, my wonderful daughter graduated from Seattle University on Father's Day. Along with the rest of her diverse family (including all four of her "mothers"), I got to watch her walk across the stage, shake the Dean's hand, and carry her (still empty) diploma cover proudly down the steps from the university stage into her own future. What an emotional day for all of us. It was mostly pride, lots of pride. Anticipation and excitement. Some anxiety, some relief. 

We gathered at her mother's house for a post-graduation party. Her adoptive mother, Yolanda, and Stacey, her birth mother, were both there, along with Bertha, who was like a second mother to her growing up, and Marcie, her future mother in law. All four women comfortably under one roof and all as pleased and happy and proud as they could be. It takes a powerful personality to make that happen, but Lyndsey made it seem effortless. I've rarely experienced a day like that. What fun.

But that day also marked the completion of that phase of our lives. She's now an employed professional woman with an M.A.Ed in school counselling, working in her chosen field with Diplomas and Certificates to hang on the walls of her new home. And me? This part of my job as a Dad is largely done. Not totally of course, but the remaining milestones are fewer and marked by more of an equal status between us. One big event is left, of course, walking her down the isle. I've mixed emotions about that one. I look forward to it and dread it in almost equal measure. What will I do with myself when my baby leaves and cleaves to her new family? We'll see.

So, my future with Access.... No, it will never be over as long as I can see well enough to clatter away at the keyboard.  And as long as someone is willing to pay me to keep their Access tools up-to-date, I'll find a way to make a living at it. Still, I'm getting older, too.

Right now, Summer's still warm and wonderful, but Autumn.... Autumn is just around the corner.

Saturday, January 4, 2014

Look out for Lookups--Part II

A lot has happened in the 11 months since I last posted here. Here's the top 3 events, I guess.

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.

Sunday, February 10, 2013

Look Out for Look Ups

For a long time now, I've argued that Lookup Fields in tables are One Bad Idea. I've even  Blogged about it.

Recently it occurred to me that maybe it's not enough to TELL people Lookups are almost always bad. There's  a world famous page that offers eight significant problems with Lookups. My complaints are actually focused on the fallout from Number 6 on that list. Lookup fields in tables simply make it far too easy for inexperienced users to screw up royally.

Let's look at just one example, which is all too common. First I'll show how it should be done, if you really, really just have to do it yourself. The following discussion is based on a three table database of employees, departments, and employee assignments to departments. You can find the originals of these tables, btw, on GPCData.com

The first table, shown in design view and datasheet, is pretty straightforward, a Primary Key, a native key used by the company to identify departments, and the name of the department. No surprises here.

Department Table in Design View

Department Table in Datasheet View

In the next two screenshots, you see the employee table in both design view and datasheet view. Again, nothing out of the ordinary here. 
Employee Table in Design View

Employee Table in Datasheet View
In the next two screenshots things get interesting. This table is a junction table that sits between employees and departments to track the history of department assignments. It's a history table because a department can have one or more employees and an employee can be assigned to different departments at different times during the course of their employment. Look at the first screenshot, and then I'll explain how it was set up.
Employee Department History Table in Design View.

Because it's a history table it has from and to dates, in addition to the foreign keys that "point back" to an employee and to a department.

The first four fields in this table are really all that is needed. The last two fields, EmployeeIDLKP, and DepartmentIDLKP are only there to illustrate why Lookup fields in tables are One Bad Idea.

The primary key on this table is actually a compound key on EmployeeID, DepartmentID and FromDate. That allows us to designate a unique combination for each assignment. Combined with other rules, it keeps us from duplicating assignments inappropriately, while allowing overlapping assignments. That's all outside the scope of the current discussion though.

So, as you know. This design stores PKs from department and employee tables exactly as they appear in the parent tables with no ambiguity,

I added two lookup fields  to illustrate how they work. These are, btw, designed so that the proper PKs are stored as FKs in the look up fields. The screenshot above shows the row source for the employeeID. It is the first column in the row source, which is also hidden. That means when we flip this table to design view, we'll see the employee name, not their PK. A win for the person who wants to see the name in a table.

Employee Department History in Datasheet View
Here we see that the Employee Lookup field not only displays a value, it actually displays a concatenated value, "LastName, FirstName". How cool is THAT? The department Lookup field shows, not the department name, but the text value for the department Number. Hm. That's kind of cool, too, but doesn't it raise a question about WHAT you're really planning to store here? is it really the (appropriate) DepartmentID Primary Key, or the (inappropriate) Department Number, which is not a Primary Key.
No crisis yet, but this sort of ambiguity is precisely why I object so strenuously to lookup fields in tables. Even when used correctly, they throw a layer of ambiguity on top of the process, and that's something we can all do without.
In future installments, I'll go beyond this initial problem and show you even more insidious problems caused by Lookup fields in tables. Trust me, it gets worse than relatively harmless ambiguity, much worse.

Tuesday, January 29, 2013

Ambiguity Is For Poets, Politicians and Comedians

Access developers already know this, of course, but sometimes we run into situations where we need to be reminded just how important it is.

The human mind thrives on ambiguity. Poetry is the art of making one multi-faceted word speak with many voices. Politicians couldn't survive in office more than a few minutes without the deft word play that conceals as much as it reveals about their true intentions. Comedians depend on the double-entendre to make their audiences roar at the absurd contradictions in their patter.

Yes, without ambiguity, much of human interaction would be drab, colorless and boring.

Yet, as Access developers all know, ambiguity in a database is a short path to data loss, corruption and misleading results. None of which is a good thing.

I was reminded of this yet again in a couple of recent discussions involving dates in forum posts.

Ironically dates are probably the most subtle of the ambiguity problems in Access precisely because  Microsoft put a lot of work into making Access pretty good at "guessing" about dates. You can try this yourself by asking Access to evaluate expressions that just LOOK like they should be dates.

Put this in the immediate window and see what you get.

?Datevalue("Jan  3, 2013")
?Datevalue("Janu   3, 2013") also returns a correct value.
?Datevalue("Jan  3. 2013") does not. I guess you can only push it so far.

Access has become quite good at interpreting and using "date" values. For that reason, we sometimes don't get errors from "bad" dates; we just get wrong or incomplete answers. And that, in my mind is even worse.

One of the problems is that Access actually stores both a date and a time portion for ALL dates, even when one or the other is not supplied.

In other words, if you look under the covers at a field which displays as 01/29/2013, for example, you may find it's actually something like this: 01/29/2013 10:12:23AM.

How does that happen? We'll, let's say you have code that inserts a default value into a date field, like this:

Me.txtStartDate =Now()

Now() as you probably know, means right now, this second. I.e. 01/29/2013 10:12:23AM

And that means in turn, that when you just look at a display of that value, formatted as mm/dd/yyyy, you're going to see only 01/29/2013, which is NOT what is actually in that field. Ambiguity just snuck in the side door through this combination of stored values displayed in an ambiguous fashion.

Most of the time that kind of hidden ambiguity doesn't cause problems, but there are times when it makes a huge difference. Let's say you're trying to match records from two tables on that date field. Inspecting the tables, displayed as mm/dd/yyyy, you'll spot some matches. However, when you ask Access to match the two tables on that date, you're quite likely to get zero, or a very small number of matches. Confusing? You bet.

Maybe you're already ahead of me, but the explanation is quite simple: To Access, 01/29/2013 10:12:23AM is not equal to 01/29/2013 10:12:24AM

Access doesn't stop with the formal display of the field; Access compares the values in their entirety. Reporting on Sales Results which matches dates--with times--of sales against dates--without times--of shipping is going to make you look pretty bad because almost nothing is going to match.

Wait, I just realized, there is some humor here after all. If your Shipping Timeliness report depends on matching Order Dates, including the time, against the Shipping Dates, not including the time,  for those orders, the joke's on you, isn't it?

Saturday, January 19, 2013

What Does a PCV Do Anyway?

My daddy taught me a lot about living a good life. I wish I'd been smart enough to pay better attention when I was 18, but I survived long enough to be able look back and pick out the important stuff through the prism of my own experience. For example, he taught me not to try to fix a PCV without the right tools, the right parts, and the right kind of training. Let me tell you about that.

My father and brothers ran a fuel distribution business in the late '70s and early '80s. That included what was still called a "service station". You may remember them, the corner gas station with a half dozen pumps and a service bay or two where the station operator fixed flat tires, did oil change and lubrications, and other minor mechanical repairs. My dad loved working there because he got to talk to dozens of people every day. My dad loved to talk to people about everything under the sun; he was very smart, and he read a lot. He knew a little bit about many subjects and a lot about a few subjects. And he was always eager to learn more, or just tell or hear a funny story. Running a service station was a good fit for him.

Anyway, I happened to be working there one summer. I was looking for a full-time job and my family let me work at the station in order to make some money, rather than go on unemployment. So it worked out that he and I were alone in the station late one Saturday night. It was summer, so it was still fairly light out.

A car pulled into the station and up to the service bay door, not stopping at the gas pumps, so we knew he had some problem. The driver hopped out and hurried into the station. He was obviously agitated. He told my dad that he had a bad PCV and he wanted it fixed, replaced, whatever. He went on to tell us he had an important meeting Sunday morning in a town two or three hours on down the road, and he couldn't wait. He demanded Dad fix the PCV immediately. Demanded, yes, that's the right word.

I was bit surprised that my Dad refused. Instead, Dad suggested he get a motel room and wait until Monday morning, when the car dealership would be open, and have them fix it. He was usually accommodating to such requests, and I'd even known him to get out of bed in the middle of the night to deliver a five-gallon can of gas to a stranded traveler. I thought maybe it was the attitude of this stranger that raised the stubborn streak in him. It was there, too.

Eventually, the driver left, after tossing a few nasty comments our way, and slamming his car door loud enough to signal his displeasure even further.

We watched him drive across to the convenience store on the other side of the street. Now, I ought to pause to explain that, in those days, service stations like ours were still common, but they were being pushed out by the convenience stores that sold gas, pop, hot dogs and other junk food. Cheap gas and a Big Gulp were beginning to win out over old-fashioned service and a candy machine by the door.

So, we watched as the counter clerk came out this gentleman's car, popped the hood and proceeded to whank on parts with a crescent wrench and a pair of pliers.

It was about then that my Dad shared his insights into fixing PCVs. He pointed out that he really didn't mind trying to fix a PCV, IF, and only IF, he could run over to the local dealership or the parts store to get a replacement, if he needed it, and if he could call his friends at the dealership for advice if he couldn't figure it out on his own. That wasn't going to happen in a small town on a Saturday night. But since he figured he might also need a special tool to replace it, and because he'd never worked on such things, he didn't want to risk making the problem worse.

You see, my Dad was a fair mechanic up until the time the vehicle manufacturers starting putting electronics and things like PCVs and so on in their cars. He knew his limits, and tackling that PCV on a Saturday night with no chance to get the right tools or parts or advice was on the wrong side of those limits.

Well, you can probably guess the rest of the story. On Sunday morning that car was still sitting right where it had been the night before, only the hood was closed. And it was still there Monday morning when the dealer sent his tow truck over to get it. Apparently the Saturday repairs had gone wrong in a bad way. To his credit, my Dad said little; however, he DID catch my attention and nod over at the car as it was being towed away. He wasn't a saint, after all.

So, once again, you're wondering what in the heck a PCV has to do with Access, aren't you? Let me see if I can tie them together.

Long-time Access developers have become really good working with the tools of our trade: Access and related Office Automation. And some of us have included SQL Server in our tool kits. But now, we're faced with a real choice. We can go on fixing flats and changing oil and even doing some fairly complicated mechanical repairs. But there's a new game in town called "the cloud" and we have to decide whether we're going to go on running the local service station, or if we want to acquire the tools and knowledge that will take us on to the next phase.

Looking back at that day, I think I'm pretty close the age my Dad was when he decided he didn't want to learn the new stuff. He didn't complain much about it, that I recall. He just recognized where the line was and chose not to cross it. He may have been disappointed; I don't know. But he did NOT complain that the new technology was leaving him behind. After all, a PCV, whatever that is, was a useful thing on new cars and that made people's lives better. He was always a believer in making people's lives better, but that is another whole story. If the mood strikes I might tell that one, too. I just can't figure out a way to tie it to Access development.

So, here I am staring at that same technology line. Unlike my Dad, I have already committed to stepping over it into the new world where Access Web Apps will start making people's lives better. It's easier for me, in one way. The technology is far more accessible to me than it would have been to my Dad. We have Bingoogle, for goodness sake. And the tools I need to do my work are readily available and relatively cheap. Still, the thought that I could just coast on through the next few years, fixing flats and changing oil in the random Access databases that come me, is never far from me.

Here's my question for my colleagues in the Access Development business. What's it going to be?  Go on fixing flats or learn how to change PCVs (whatever the heck they are)? We have the choice and the web tools are easily available. I am pretty sure it would been really bad if my dad had gone out onto the curb to throw lug nuts at the kid from the convenience store.  The one thing I think we can't afford to do, as I see it, is to go on throwing lug nuts at the people who are making and installing the "PCVs" in Web Apps. That does no good to anyone, does it? And if we hit them in the back of the head enough times, they're going to start putting distance between them and us. And that would be a real tragedy, IMO.