One Size Fits All, Or All For One. NOT!

One of the most valuable aspects of Access Database development is that it is almost entirely custom.
That means nearly every new Access application is different in some non-trivial way from every one that preceded it--ever.

Need a database to track family addresses and phone numbers? Great. You'll find templates all over for that. But no two will be exactly alike and that's a good thing, IMO.

However, for a lot of new Access developers, that turns out to be a stumbling block to getting off the ground. If all you want to do is copy out a template and go to work, it's almost never possible to do so. Naming conventions, at the very least, are those chosen by the template designer. They may or may not be the same as those used in your organization.

And that’s just the trivial part.

You want to track one or more phone numbers for each contact. No problem, that’s a job for a related table of Phone Numbers. But what if the template maker decided every contact can have three Phone Numbers (Home, Work and Mobile)? That’s a limitation you’ll have to accept as a compromise. Or, you can modify the template.

At some point, such trade-offs and compromises become more of a hindrance to getting on with the task than simply starting out from scratch with your own design. And the more complex the business process, the more likely it will be that any template you find won’t stretch to fit it.

All of that can be summed up in the saying which is the title of this post. One Size Does Not Fit All.

You’ve been warned.

It's all about the data.

That's so obvious it seems silly to even comment on it in a page that's all about database design, doesn't it? But, every day, while addressing Access questions on my favorite Access forum, Utter Access, I run across questions can only be answered by looking at the specific data involved, not at the code used to manipulate it.

A recent example might help explain what I’m talking about. I’ve rephrased the question so as to avoid making it too easy to identify the source.

“My query raises a division by zero error. The query includes two calculated fields. The data is from three subqueries. The SQL for the final is shown below. How do I avoid the division by zero issue?”
Not picking on anyone, but the basic mathematician in me says “this ain’t a database question, it’s a math question. You avoid division by zero by not including zeros in the divisor.” But to the questioner, that simply hadn’t occurred, I guess. He or she was looking for “an Access solution” to the math problem.

There are, of course, two answers to this.

First, if records in the underlying tables have either Nulls or zeroes in one of the fields going into the calculation, then those values have to be resolved before you even START writing queries against that data. Exclude those records from the selection before you try to do any math on the remaining records.

There is, also in that sense, “an Access solution” to the question, "How do you handle Nulls and Zeroes in SQL so that they don’t blow up calculations?" The answers, of course, involve functions to convert Nulls and zeroes, as needed, when they appear. But that’s not the point of today’s comments.

Second, there is an even earlier, more fundamental, question to be addressed. If you are doing math (and a division by zero  error definitely signals that math is being attempted), then you have to decide whether Null is valid for the data at hand, and whether Zero is valid for the data at hand. If so, then why do you then want to do math on either of those values, knowing that your calculation is not valid under any circumstances?

For example, if you want to calculate the average number of days between the date an order is placed and the date it is shipped, you have to decide, right at the beginning, if you want to try to include orders without a ship date. Those orders are still being processed and not yet shipped. I submit the answer to that one is obvious. You must exclude them because it’s only meaningful to ask about average processing days for orders that were actually processed and shipped. So, by the time you write that SQL with a division in it, there are no nulls to fret over.

Another example would have to do with calculating error rates in a manufacturing process. Let’s say you want to calculate a percentage of errors detected in Quality Control versus errors reported by customers after products are delivered. Unfortunately, if the calculation is QA Errors divided by Customer Reported Errors, it’s entirely possible (and one would have to hope, likely) to have one or more errors found in QA and Zero reported by customers for a particular product. See what’s going to happen there? Yup, a divide by Zero error.

The answer to that one is equally obvious, I think. You don’t do the arithmetic that way in the first place. You come up with a more appropriate, mathematically valid, way to calculate this metric. And how you do that is as much a business rule as it is a math problem. Maybe the next approach might be, well, okay, lets SUM the QA and Customer Reported errors first and then divide the QA errors by that total to get a ratio of QA errors. Uh uh! If there are no errors of either sort, then you’re still dividing by Zero.

At this point, I will step in and acknowledge that there is an Access way to handle it, but you can only get there by understanding the math—and the business rule—behind it.

It's all about understanding your data first. After that, it's all just code.

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.

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.

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.