Green is for Go, Yellow is for Slow, Red is for No.

Calculated Values Stored in Fields Don't Pay, They Cost

Most people who spend a lot of time around relational database applications develop a phobia for calculated values stored in tables. I am aware of arguments for special cases that justify doing it anyway. However, the general rule is a sound one: Don't calculate and store values in tables.

I have repeated this rule innumerable times myself. At a recent family gathering, though, I heard a story that explains just how awful the consequences of ignoring this rule can be. I thought I'd share the story here, subject to the usual disclaimers about changing or concealing details to protect privacy of those involved.

This company is in the communications business in a general sense. They install hardware and software along with other supporting infrastructure in order to provide their particular services to both commercial and residential customers. They maintain a number of very large databases, one of which details existing customer accounts, another of which details existing infrastructure previously installed in homes and business locations. In other words, their technical people track where they've installed hardware and other equipment in their area of operations in one database. Their accounting  people track customers and the locations where they receive services. Two different databases built around similar, but different data.

Salespersons contact potential customers to negotiate contracts for those services. Part of that process involves requesting information from their technical side about what infrastructure might already be installed at a location, perhaps for a previous occupant of that location. That communication takes some time, slowing down the negotiation. And, as we all know, time is money, especially for a busy salesperson. Somebody came up with the great idea of creating a third system that draws data from both of the other databases, synthesized, of course, for the salesperson. That way, they could provide a simple dashboard style look at any given location that would compare existing hardware and software at a site to the requirement specified in the potential contract at that site and flash one of three or four statuses. Let's say, for the sake of discussion, that "green" means the location needs no modifications to work for THAT contract, "yellow" means minor upgrades or changes, while "red' means the costs of making the required changes would be too great for the potential revenue that would be generated by that contract (unless the customer is willing to pick up the tab).

With that tool in place, a salesperson (or more likely the staff tasked with data entry) can enter the location and the services requested on a potential contract and get a status back right away. They have the option at that point, to sign the contract if it's "green", renegotiate based on a "yellow" status, or inform the potential customer right away that they can't meet their request on "red". Great, saving time means the salesperson moves quicker and earns more. Everyone's happier. 

Unfortunately, that status is a calculated value and it is STORED ONLY in the salesperson's system . Not in the technical database and not in the existing accounts database (it's irrelevant to both of them). And of all of the people who use the system, the marketing and sales people are, perhaps, the least likely to understand why that's a problem for them.

In fact, that's where things went off the tracks one day. 

A salesperson negotiated a new contract with a potential customer moving into a new space. The proposed contract went back to the office where a data entry person entered it into their sales system. The location and services requested flashed "yellow", meaning it would be possible to accept the contract, but the location would need some modifications. The company was even willing to pay for those minor modifications in anticipation of the revenue the contract could be expected to generate. 

The salesperson goes back, explains the situation, gets a new agreement to delay installation until a crew could come and modify infrastructure. In this case, the customer would also have to accept a crew interrupting normal  operations. Agreed. That must be a good salesperson, right?

So, the company sends out a sub-contractor to make the modifications. They submit an invoice for the work done and get paid. 

Now, the data entry person tries to reenter the new contract, knowing the location now meets the "green" status. Uh, no. The SALESPERSON's system still shows "yellow". What? It seems that the subcontractor was paid, but there was no proof they actually DID the work. Either the infrastructure database was not updated properly, or the subcontractor scammed them. Hm.

So, another delay has to be negotiated with the potential customer while still another crew is sent out to inspect. And YES! the work was done. Good to go, except the keeper of the infrastructure database refuses to update it until they get the subcontractor's written summary of the work. Another delay while they contact the subcontractor to get the required paperwork. The subcontractor tells them it was already submitted along with the invoice, but they'll be happy to go out again to fill out the paperwork. After all, they can't be expected to blindly fill out the required document without an inspection by their own people. And, by the way, they gave the company the only copy of their document. Hm. Double-dipping, perhaps? Super cautious, maybe?

Anyway, the salesperson has a problem. Still another negotiated delay while the paperwork dance is working it's way through the bureaucracy, OR (sly wink), simply change the CALCULATED  value from "yellow" to "green", allowing the system to accept the contract so he can get paid. And that's what happened. All happy now, right?

Well, not quite yet. The salesperson sets a date for the final install, gets his commission check, and moves on to the next sale.

Not so fast. When the company's install crew arrives on site to do the final work, THEIR database still shows deficiencies. And they refuse to install. Everyone insists it's all good, but if you get in the habit of overriding your system, sooner or later you're going to be sorry. No install until they get the document that shows the required work was done.

The salesperson has no choice but to ask the potential customer to delay once more and allow another crew to come out and verify their first crew did their work. At this point, the customer said, "enough is enough" and the deal was cancelled.  The commission had to be clawed back in the salesperson's next check.

So, this story involves a number of problems, and the storage of a calculated value is just one. The subcontractor may, or may not, have been playing fair. The bright bulb who adjusted the status from "yellow" to "green" probably didn't even know the trap they had just set. A combination of rigid rules and the need for accountability combined to sabotage things.  But none of it would have happened except for that short-cut that allowed the calculated value to be stored--and updated manually.

Your Proposed Relational Database Table Design is Inappropriate

During many years of participation at UtterAccess.com, I've had a chance to review many, many "First-Timer databases". After a promising beginning, these new comers run into a blocker like a query that doesn't return the right records, or a form that won't accept new records, or a report that doesn't work the way they want. They run into a puzzling or challenging problem which seems like it ought to be simple. Instead they get stuck and have to turn to online forums like ours for help. We actually love that, because it gives us a chance to help them clean things up and get off to a better start. But all too often, I have to tell new members, "You don't have a query problem. You have an inappropriate table design." Or "You don't have a report problem or a form problem.Your tables are not appropriately designed. Put the forms on hold while you fix your tables."

I admit "inappropriate table design" is a euphemism for the more blunt assessments I'm tempted to state. I favor it over "you have created non-normalized tables", or worse, "Your table design is wrong." Here's why.

Often, the limited information we get as part of the question reveals the underlying tables are not designed according to the Rules of Normalization, but that's not the question originally asked by the newcomer. Instead of offering SQL or VBA to work around the problem, we have to start out questioning the very basis of the database application itself--The tables.

Ouch. No one likes to hear that they broke the rules, especially when they didn't even know those rules exist. Moreover, Access readily, and even gleefully, aids and abets the creation of those inappropriate, non-normalized tables. Sometimes I can hear the newcomer asking their computer screen, "Who is this George Hepworth guy" they wonder, "to second-guess Microsoft?  They let me do it this way, so it can't be wrong, can it?"

Well, yes, there are many ways a table design can be wrong, and all too often it is wrong the first time. It's my responsibility, I think, to let the new comer know when their first step leads down the path to the swamp, not the garden.

Anyway, recently I've been wondering if maybe my approach is on the right level.

Am I too harsh in telling new members their query doesn't work because it's based on inappropriate, i.e. poorly designed, tables that need to be changed?

Or is calling it "an inappropriate table design" too subtle ? Should I be stating outright that the table design is wrong and doesn't make sense? Should the new comer have a dose of shock therapy the first day? Or maybe I should ignore the problems at first and try to offer a query that works with the flawed tables  in hopes we can fix the real problem later? Maybe this is really just a minor thing that doesn't matter all that much.

Time after time I have come to the same conclusion.

In the long run, ignoring flaws in a table design means the problems are only going to get bigger, more intractable and even become risky. Members at UtterAccess and other forums come up with all sorts of metaphors, usually trite and well-worn, to explain that concern.

• It's like putting a band-aid on a broken leg. 
• It's like asking your doctor to give you aspirin for a broken leg. 
• It's like putting up walls on bare dirt without a solid foundation. 

 And on and on. They all boil down to the same thing: work-arounds don't work in the long run. In the short-run, sure, you can force Access to work with a poorly designed table architecture. The result is fragile, inflexible and inefficient. I like to point out that there's nothing heroic about creating massively complex queries or wads of code to compensate for an inadequate table design. Sort of like putting in flying buttresses on a garage wall to make up for leaving out the foundation.

As one of my favorite people used to say, "Let's face it. We have a problem.  I know what it is, and it's not a query problem. Your table design is inappropriate."

Let's fix that.

It's About Time

My YouTube Training Videos Line-Up is Growing.

I've been adding videos to the line up. I'm also getting better at it; I think. I published Numbers Five and Six yesterday.

So far, the series includes

• An Overview of  Display Formatting options for date/time fields in Access tables. It emphasizes the point that the VALUE stored doesn't change regardless of the DISPLAY FORMAT applied to a date/time field.
•  That video includes an introduction to Display Formatting for dates. The key take-away from that one is that ALL dates in Access are stored as decimals with both a date component and a time component. See the video for details.
•  A More Detailed View of Display Formatting options for date/time fields in Access tables. It includes examples of date comparisons illustrating the underlying values are not always apparent from the display format applied. See the video for details.
• Improving Appearance and Effectiveness of Forms by using custom display formats for dates and dates with times. The key take-away from that one is that custom display formats are an easy, effective way to make your Access forms better. See the video for details.
• Effective Date and Time Filtering in Access Forms uses Date Display Formats along with basic date logic to filter records returned to Access forms.  See the video for details.
•  Default Dates and Date Formatting & Grouping in Access Reports  carries the discussion on to Access reports. In this video we see how date logic and formatted dates can support grouping within an Access report. It also introduces simple date logic functions necessary to select Default Values for controls on Menus. See the video for details.
• Date Formatting and Filtering in Access Reports Filter Criteria for filtering, grouping and sorting in Access reports. In this video I show you how to use TempVars as criteria in the query which provides the records to be displayed in an Access report. See the video for details.

I'd love to start getting feedback from readers and viewers.

• Are the videos useful to beginners?
• I realize that experienced Access users and developers should know much of this information already, but my target audience is really the beginners. Am I on target?

•  Are the videos too long (or too short)?
• How long should videos like this actually be? Is ten minutes enough? Twenty minutes?

• What do you want to see next?
• I will continue the current series on Dates and Date Logic because it is such a fascinating and extensive topic. Where would similar videos be helpful?

Thanks. Looking forward to hearing from you.

Got a Minute?

Check out my YouTubes on Dates and Formatting.

I've been busy learning how to create videos for YouTube.

It's harder than it looks, especially if you want to do it right.

My first attempts are up, though, and I'd love your feedback.

GroverParkGeorge's YouTube Channel.

Did I Mention I Have a Thing Against Lookup Fields in Tables?

Where do Lookup Fields in Tables Come From Anyway?

Whether you consider them to be a useful idea or not, lookup fields in Access tables came from somewhere and we have to deal with them. My candidate for their dubious origin is SharePoint, but I have only a hunch. I know they were already in Access 2003, but I no longer have earlier versions of Access available and can't recall prior to that.  (I'd appreciate being set straight on this point, if anyone knows more about it.) Suffice it say they made their way past the door wardens and are now firmly ensconced in MS Access tables, alongside the other dodgy characters cadging drinks from the regular patrons and whistling at the wait staff as they squeeze past to deliver the next round to the table behind.

We're not talking about combo boxes, or drop downs, on forms

I want to be clear before we get into this in depth.

We are talking about lookup fields in tables. Combo boxes on forms work almost exactly the same way in some regards. They can also be abused in the some of the same ways. It's just that the more pernicious implementations I've seen were born in tables.

 What do Lookup Fields in Tables Do?

The basic concept of a lookup field sounds pretty good -- at first look. Make it easy for users to see the values, not the foreign keys, for fields. You have probably had the experience of opening a table and seeing columns of foreign key values in one or more fields. Trying to equate what you see there, e.g. CompanyID of 210, with the value to which that key value relates, i.e. "The Big Box Company", which is CompanyID 210 in tblCompany.

By converting the foreign key field to a lookup field, you magically allow users to see "The Big Box Company" even though the table stores Foreign Key 210.

Done right, and fully understood, they can be benign and even handy for a developer. So far, so good. But like so many of life's little problems, it doesn't end there.

 What Could POSSIBLY GO Wrong?

Actually, more than you might expect.

Here's an example of one of my least favorite attempts to use Look up fields in tables.

Storing the Value, not the Key. 

This is the most common mistake I see. The best way to get to the point is with some images. So, here's a typical Static Lookup Table. This table has a Primary Key and a Value field. Many Lookup Tables are like this.

Lookup Table of Months

It is a Static List; there are twelve months in a year. Many Lookup Tables are more dynamic. Consider a list of approved vendors, for example. Vendors are added and removed from the list as the organization's needs and priorities change. Such dynamic lists might have a number of other fields and be used in other ways, in their own right. In fact, many history tables can double as lookup tables. Here's another example of a query (based on a table with a Date field), in which all of the previously entered dates serves as a lookup list for events in a combo box on a form. You could do this in a lookup field in a table too, I suppose, but, please, please, please, don't

SELECT DateValue([WD].[WorkDate]) AS WD
FROM tblWorkDetail AS WD
GROUP BY DateValue([WD].[WorkDate])
ORDER BY DateValue([WD].[WorkDate]) DESC;

tblWorkDetail contains several other fields, including the start and stop times for the work, a description of the work, and the WorkID itself to which that detail relates. To select all work by date completed for an invoice, this list needs only the relevant dates, grouped on Date. Selecting one date from the combo box adds all of the work for that date to an invoice. Double duty. But I digress. Back to lookup tables which have been infected with Lookup Fields.

Different considerations apply to static and dynamic lookup tables. With a Static list, like Months, it's fine to dispense with the surrogate key. I've seen many tables like this that work just fine.

Static Lookup Table with a Single field

Because it's static, you won't have to revisit the values in this table. You can also count on any of these values stored as a related field in a different table being stable. Many developers follow this practice with static lookups, despite the prevalence of Surrogates (i.e AutoNumbers) as Primary Keys in most tables.

With a Dynamic list, like Vendors, the issues are different. What happens, for example, if you misspell a name as "The Doctar Company" instead of "The Doctor Company". After a week of data entry someone finally notices the misspelling. By now there are multiple purchases made from the company. How hard is it going to be to correct the spelling mistake?

It depends.

If your Vendors Lookup table is like the first example, of course, all of those related records were stored in the purchase table under a surrogate key, (i.e. the Primary Key of the Vendor table.)  No problem, then, correct the Value field in the vendor table and you're done. The related records are not impacted at all.

Surrogate Primary Key to Foreign Key 

If your Vendors table is like the second example, it gets a bit stickier. First, you may or may not have defined the parent-child relationship with Cascade Updates. If so, changing the spelling in the lookup table should cascade the correction to all of the related records child records. Quite doable and safe enough, if not really ideal.

Cascade Update Saves The Day With a Single Value Lookup Table

If you neglected to check the Cascade Update Related Fields property, though, Access won't let you correct the spelling because changing the Primary Key would orphan any child table records with the original value as the Foreign Key. This is not an end of the world kind of problem for an experienced developer, but for the newcomer, who doesn't know how to work around the problem, it could be pretty upsetting. (There are a couple of ways around this problem, neither of which I want to show you. I'd rather you stayed on the straight and narrow to begin with, eh?) 

The long way round to the point, but here we are at last

With that background on the basics of relationships and lookup tables, let's go back to the one in our original table. It looks like this in design view.

Defining a Lookup Field in a Table

 And it looks like this in datasheet view.

Resulting "Lookup List

The Foreign Keys for the Month names are hidden and the user sees only the Month names. Many newcomers are pleased by this, but unaware of the trap they just laid for themselves.

In fact, on the surface, there's no way to differentiate the two in datasheet view. Consider these four screenshots.

Datasheet View of Lookup fields in tables, closed and dropped

 Can you spot the difference? I can't, not without looking at them in design new. (No fair using the table names as a clue.)

It's not surprising then, that many times newcomers are confused when they try to work with such tables in their queries. "Why do I see the Names when I look at the table, but when I use them in a query,  get the numbers instead?"


 So, now, here are some queries based on these tables.

Query Using Tables with Lookup Field based on Surrogate Key

 Access very helpfully includes the lookup field, hiding the fact that ParentTableLookupID is, in fact, a number. Not clear? Try this.

Valid Filtering on Lookup Field using Foreign Key

 Now try this.

Invalid Filtering on On lookup Key using visible, Value Field

 Is it any wonder that so many newcomers flounder when they find that these sweet, sweet lookup fields in their tables are laden with hidden pitfalls? 

And it gets worse from here. I promise.

Come back later for a followup on other problems I've seen.