How to Normalize Data in Microsoft Access

The term “normalization” gets thrown about quite a bit in database circles, especially to try to explain to newer users a couple of principles of data organization, but seems rather vague to someone not acquainted with database-ese.

The idea of making data “normal” is not too far from the meaning used by database designers, when one understands what “normal” is. When we create, say, a batch of file cards with names, addresses, etc., we tend to lay out the information similarly—consistently—so as to make it easier to follow on each. If each piece of information is in its own place, a “field” or space designated for each, we’re already close to the idea of normalization. One wouldn’t want to put the first and last name in the same field, since we might want to sort by last name first. We couldn’t do it easily if the two were in the same place. Nor would we want to put street address, city, state, and zip code in the same field, for the same reason.

Bad Addresses

So one rule we try to follow regardless of what kind of database software we use, MS Access for example, is that we should put one piece of information in each field. A rough analogy would be, if one wanted to fill a glass jar’s volume completely, using something like golf balls or hard candies would not work too well, as they’re too big to really follow the shape of the jar. But using gravel, or sand, or better still, flour, would be better. The individual pieces are smaller, and can fit the space much better. The smaller the pieces, the more “flexible” they are in filling the space.


Secondly, it’s a good idea to divide the tables (or collections of data) into categories—an employee database might contain a table of personal data, a table of office data, one of health plan data, one of travel/transport data, and so on. Turns out it’s easier for most databases—and users—to work with a larger number of smaller tables than the other way round.

Finally, there is at least one exception. Some kinds of data, such as addresses, work better with a little de-normalization. (Access makes this fairly easy to see.) We don’t want to put a zip code in the same field with city and state, but putting city, state, and zip fields in an address table—even though slightly redundant—make the addresses much easier to understand and use.

Address Table

Using Effort Driven in Project

Even though Microsoft Project is fairly easy to use at the basic level–entering tasks and resources, assigning the one to the other, and fine-tuning a schedule–there are a few parts of the program that seem quirky, and can take a little practice to use comfortably.

One of these is the checkmark in the Task Information dialog box called “Effort driven”. The name is fairly self-explanatory, in that a task’s duration (the thing we normally concern ourselves with) can be affected by how much effort we want to put into the task, in the form of resources. Specifically, how *many* resources.

Effort driven checkbox

There is the quirk, though. This is how it works:

When the project manager first creates the task, it defaults to Fixed Units of work (stuffing envelopes for a wedding, or laying bricks for a wall would be good examples), and (if one chooses in the Program Options) Effort driven being on. The first time resources are assigned to the task, whether it’s one person, three, seven, or more, the duration won’t change. The program is assuming you know it takes, say, four days for six people to build a garden wall, and simply goes with the original duration. BUT…if, from then on, you change the number of resources (people, in this case), the program recalculates the number of days/weeks/etc. involved, as it assumes more people can do the task in less time, and vice versa. Not totally illogical.

Add resources and change duration

Nevertheless, there are some kinds of tasks for which the relationship doesn’t apply. If someone wants to interview a subject matter expert for three days prior to writing a manual, having one or two more people involved in the interview might help develop information, but probably won’t take substantially more time, or less.

So the trick, when one wants to add or remove resources without affecting duration in Project, is to open the Task Information box, go to the Advanced tab at top, and uncheck Effort driven FIRST. Then, go to the Resources tab, add or remove people or other resources, and OK out of the box. After which, if necessary, one can repeat the process and turn it on again.

On a more general level, depending on the task, the resources, and so on, one can remind oneself that it’s a good idea to go into the Task Information box->Advanced tab, and note the state of the checkbox before making changes.

Is there a suggested default? Hard to say. It depends on the nature of the projects and the tasks a project manager is involved in. A couple of guidelines might be:

  1. If the project will likely involve fairly frequent changes to resource assignment, leaving Effort driven off would probably cause fewer upsets to the schedule, though the manager would need to manually adjust it when needed.
  2. If the project resources are assigned and pretty much left in place, having Effort driven checked by default (again, this is an item in the Program Options which can be changed) would likely work in favor of the manager.

Program Options Effort Driven

Keeping the checkbox state in mind, then, can help avoid trouble when shifting resource assignments around.

Align Fields in Access Forms

Working with Microsoft Access can be kind of intimidating when you start, especially because there seem to be so many details to absorb and keep track of. And even fairly experienced database people have to watch out for the little things.
One which really drives some new Access users up the wall has to do with forms. Doing form layout is an art unto itself, and getting a good layout can take time. Even after you’ve got it mostly cleaned up, the program has one particular nitpick some people don’t even notice. At first.

Form Design View
When you work on a form in Design view, the most time-consuming thing is sizing and moving fields (officially known as text boxes, where data show up) and labels (to tell you what the fields contain). And making sure the Tab key gets you from field to field in a logical order is important. Luckily, we can use the Tab Order dialog box to do this.
Tab Order Box

At this point, the tab order doesn’t match the order of the fields because we rearranged them. We want to tab left to right, top to bottom. So we arrange our fields, bring up the box, and click Auto Order.

Auto Order Button
BUT!…if any of the fields are not aligned just right, by their top edges, with their buddies in the same row, whoever’s highest gets dibs in the tab order. (“D’oh!”)
So we select the fields and labels in question, go to the Arrange tab->Sizing and Ordering group, and click the Align button. In the dropdown, we click Top to get everything in a given row to line up.

Align Top Command

Problem solved.
Then, go back to the Tab Order box, click Auto Order once more, and test. That should do it.
Yes, it’s kinda nitpicky. But it’s worth it, as getting this sort of thing right can improve data entry speed in Access, and other database programs quite a bit…and win you the approval and accolades of the people who do it.

Filtering Data in Crystal Reports

When you run a report in Crystal Reports, you have a lot of latitude as to what goes in, how it’s formatted and organized, and so on. But one consideration that doesn’t always get mentioned is how to leave data out.
Turns out, it isn’t difficult. In fact, the technique is similar to one we find in several other programs which deal with data and databases.

After opening the program, the database and the report in question, the feature that does the filtering is the Select Expert–specifically, the Record part.

Among the logical operators available to us is “Is Not Like” (sometimes seen in other programs as “< >” or “Not Equal To”). This is the one that says “DON’T show me…” whatever it is you want to exclude from a given field, like City.

But the next part is also easy–just a little different. If, let’s say, we want Crystal to show all cities in a shipping list other than Albuquerque (watch your spelling on this one! 🙂 ), we put that name between asterisks, like so: *Albuquerque*.

This means that every record whose city name does NOT include Albuquerque (beginning with, ending with, or containing) will show in the report.

It may seem a little odd at first, to want to exclude information in Crystal Reports, since usually we’re trying six ways from Sunday to figure out how to include information. But don’t forget that panning for gold meant sifting out what you didn’t want, and reducing a sauce involves removing water or other fluid to make it denser and more flavorful.
As a related point, doing Web searches can involve a similar idea. We often use Google to look for things, but the Advanced search capability lets us say, in essence, “Look for this and this but NOT that.” Hardly ever gets used, but it’s always there if we need it. Ditto in Crystal.

Slide Master View in PowerPoint

One of the few parts of PowerPoint that isn’t quite intuitive is the Slide Master view. Getting to it is easy–go to the View tab–>Master Views group, and click Slide Master.

Then it gets a little more complicated.
To some people it looks as if they never left the Normal view–all that stuff over on the left side looks kinda like their slide show, but it isn’t. So what is it?

Those are the slide layouts you see in the Home tab–>Slides group–>Layout dropdown. In other words, those are the basic layouts you can use to create or give structure to any slides in your show. They’re built-in and always available.

Okay, first question answered. Then what?
Can we change them, or add to them? Sure. Usually the best thing to do is right-click and duplicate one whose setup is close to what you want, and modify the duplicate. Saves time and work. The same right-click gives you the ability to rename or delete one, so there’s your freedom of fiddling.
Wait a sec, though…what about the bigger slide at the top, the one all the others are connected to by the dotted line? Well, that’s the actual SLIDE MASTER, the Big Kahuna, le Grand Fromage (sorry, it slipped in).

And what does he do? His job is to control the appearance of the others. More specifically, the background elements (graphics, company logos, etc.) and the text appearance (fonts, sizes, colors, and so on). NOT the layout of the stuff you put on the slides.
So if you want all your slides to have a consistent appearance (highly desirable in almost all shows, business or other), do the basic visual stuff in the big boy at the top, then create or modify the smaller ones for the way you want the layout of the elements in the slides. And best to do it before inserting content, if you can. Less work on the back end. (Build the foundation before you build the house.)
What about other slide masters and layouts? Can we have more than one set in a slide show? We can. Whether it’s a good idea is another matter–usually one doesn’t want to mix too many slide masters/color schemes/etc. in one show unless they’re variations on one master theme, because it shouldn’t look like a circus came to town, but the program will allow it. So be conservative about such things. But don’t let it stop you from experimenting.

PowerPoint Tips and Tricks

…or, Some Ideas for Good Business Presentations

There are people who believe the joke that says, “We have met the enemy, and he is PowerPoint.” I don’t think so. But it is true that some people, through no fault of their own, don’t know where to start when it comes to creating a good PP presentation. And there are some things anyone can do to create a decent one.


According to some sources I’ve seen, an officer in the USMC can brief a dozen subjects in under an hour. While not everyone is quite so succinct, it is a step in the right direction. Keeping the presentation simple and to the point is good. Less is more.

Not everyone in the audience may know the subject matter. Check on this if possible, and plan accordingly.

Format for readability, and for emphasis as needed. (If a company has product branding standards, they should be strongly evident.)

Keep to three or four bullet points per slide, max. More than that is like taking larger bites of food—one can choke. (Mentally, it’s similar to the Gary Larson cartoon wherein the student says, “Mr. Osborne, may I be excused? My brain is full.”) More slides with less content per is better than the opposite—a little at a time.

Make the text big enough to read easily. One of my former colleagues was a Naval aviator and had 20-20 perfect fighter pilot eyes. Not everyone does.

Use sans-serif fonts, like Arial, Helvetica, or Optima if possible—the less complicated letter shapes are easier to read on a monitor or projector screen. And stick to one or two fonts at most. Much less cluttered-looking.

Stick with as few colors as possible, too. Again, less busy-looking—and it won’t look as if the circus came to town. (That product branding thing can be helpful here.)


Graphics and audio can take up a lot of room on disk, and time to download, so use them sparingly, like seasoning on a meal.

(Audio can be a megabyte per minute if it’s good quality. DVD-level video can be four megs per second.)

Charts are useful, but they too need to be simple—if there’s a lot of information, create more charts. And make sure you label everything—simply.

This is a pretty good chart.

This isn’t. 😉

Pick the right kind of chart for the data. Pie charts for percentages, column or bar charts for sales figures, line charts for trends, etc. Trying a few different kinds is okay if you want to, but knowing what works for what most of the time gives you a foundation to work from.

Other elements in a chart can help, too. The company logo should be in any company presentation.

Photographs of people involved are also a good idea. (If you need a picture of someone outside the company, find out if you need a release form. Better to find out the easy way.)


Be careful not to overdo these, either. If you go overboard here, the presentation can end up having the feeling of a Looney Toons cartoon. And that’s usually not desirable, even if the audience has a sense of humor, because it’s a distraction. One or two transitions, alternating, or a few variations on just one, and similar animations  throughout, will be more consistent, yet not boring.

And transitions should be “medium” or “fairly fast” speed—one to three seconds, so the audience can see the change but not be held in suspense (again, not really professional). This also give the presenter time to verbally segue from one to another.

There are a few more points that figure into good presentations, but these are pretty sturdy, and after hearing this sort of stuff bounced around in class for twenty-plus years, I think my students have the fundamentals solidly by now.

Track Changes in Microsoft Excel–Essentials

One of my wife’s colleagues recently asked about whether it’s possible to track changes in an Excel spreadsheet. It certainly is, though it works a little differently than in Word, say.
Turning the feature on in Excel is quite easy–simply go to the Review tab in the Ribbon, slide over to the Changes group, and click Track Changes.
Review Tab, Track Changes
Almost immediately, we see one of the differences between this and the Word version–we are told this will put the document into Shared mode, allowing others to open and edit the file at the same time. We also want to decide which specific changes to highlight.
Highlight Changes dialog box
Usually it’ll be When and Who. By not checking Where, we’re saying we want to see where *all* changes are being made, which is normally desirable. And we almost always want to track changes on screen, though it’s possible to list changes on a new sheet. (Sometimes, for business situations, it’s quite helpful to do the latter, but being able to track and accept or reject onscreen is often immediately useful.)
Once the feature is on, any changes we make will be tracked. A marker will appear at the upper left of the cell(s) in question, to let us know something’s different. Whenever we save, the markers disappear.
More names added
But the program still tracks changes. Whenever we’re ready, we can review them and decide to officially accept or reject them.
Accept-Reject dialog box
And if something changes after matter how many times, the feature will still follow along.
Uncle Vlad added to list

So we can definitely track changes in Excel. The nature of the program requires that it function a little differently than in other members of the Microsoft Office, but not so much that the average user would have trouble with it.

The one downside to tracking changes in any of the Office programs is the feature is a little memory intensive. This isn’t nearly as much of a problem as it might have been a few years ago, what with the faster processors and less expensive memory we can get now.

Access vs Excel—Which should we use for what?

Most people find Microsoft Excel fairly easy to learn—it has a fairly gentle learning curve, the fundamentals only take a short time to pick up, and the program is actually fairly versatile—it’s not just a ledger book on steroids. Microsoft Access, on the other hand, can be kind of intimidating. Some folks hear the word “database” and, to quote the Joker, “they start losing their minds!” The whole idea of a database program is scary because there’s more to learn, more to set up before you can use it, and more to manage even when it’s working fine.

So let’s just ask the question: When should we use each one?

To answer this, we need to understand what each one can do. Excel is a spreadsheet program. Even though information on one sheet can talk to info on another, it’s primarily meant to do math-related things. So if all you need is the equivalent of a sheet of graph paper to visually organize, say, a personal or house inventory, Excel does pretty well. You could use Word for this same purpose, and have about the same amount of work, if you don’t want to use Excel. Formatting and other aesthetic things are only about as hard as in Word, so it’s almost as if you built a giant table in Word and jotted stuff in it. Or if you want to budget monthly, quarterly, and annually, and have all of those update each other, great.

On the other hand, if you need to track something more complicated, such as information on the employees of a small (or large) company, you may run into trouble with Excel. The problem is, you may need to have three or four kinds of information that all relate to the employees, but are not directly related to each other: Personal info (home address, birthday, family), office info (building, floor, office number, phone number, business email), health plan (HMO, policy type, policy number), and maybe transportation (car make and model, parking spot, bus route, commute time). Yet all of these need to tie together, as it were. You might have each of these connect to the others through, say, the employee ID number or Social Security number—make that ID part of each of the four categories so they have a common element. This is where the term “relational database” comes from—the different categories of information nevertheless relate to each other, like a baseball team where each player has a separate job, but all cooperate to play and win.

If this idea makes sense, that we separate the types of information but allow them to relate through one item, then you understand one of the main concepts of the relational database. Like Access.

So the basic answer to the question is, If you have just one kind of information (or several that are unrelated), you can store each chunk on a separate worksheet in Excel and probably have no trouble. But if the different subsets (or as Access would call them, data tables) are related after all, using Access to store the information and work with it would actually be worth the extra time and effort. As a bicycle is good for getting around by yourself, but a pickup truck can haul some serious cargo by comparison, so Excel and Access. (Sort of.  🙂  )

As a postscript, the reason I’m not bringing up PowerPivot here—which lets us do some database-ish things in Excel—is that it’s a different tool again from Access, though it is useful. And not everyone knows about it, or how it works. We do teach it at SkillForge, but again, it’s a somewhat specialized tool, whereas Access is a more general database application and has more multi-user features, making it more suitable for business database work.

RGB vs CMYK–Which should I use?

Having taught and worked with Adobe Photoshop for many years, I get asked many basic but good questions about it by new users. One has to do with color, and the color systems we use in a picture. There are several that Photoshop can use, but the two most common are RGB and CMYK. The question: Which is best?

The fundamental difference between the two is, RGB is meant for use on screen, and CMYK for print. The terms used to describe how they work are “additive primaries” and “subtractive primaries”, which refer to how these systems show white. For RGB, imagine standing in a dark room with a white wall. Take three flashlights, with color filters (red, green, and blue) and shine them on the wall. Where the three colors overlap, they seem to make white light (the opposite of what a prism does with white light—see Pink Floyd’s album THE DARK SIDE OF THE MOON, specifically the cover—and the back cover.) So the three additive primaries add up to white.

Additive Primaries

With CMYK, the example is even simpler—a piece of paper going through a color printer may have areas where no ink or toner has landed. And the color of the paper we usually use? White, of course. So when the colors are held back, or subtracted, from a spot on the paper, that spot stays (or is) white.

Subtractive Primaries

But we need black ink too, because the dyes or pigments only sorta make black, and a normal eye can see this. So the printing folks added it for completeness.

In doing this, though, we get a couple of problems, and it takes a little thought to get round them. First, because the RGB system normally involves a screen that illuminates itself, it can show more subtle shadings of color than a piece of printed paper (which, unless you’re using radioactive inks—shame on you!—does NOT glow in the dark). The term Photoshoppers and graphic artists use is the “gamut”, which is wider/larger for RGB than for CMYK (where the ink/toner can smear a tiny bit and mess up the shadings). And a printout can’t show all the shades that a screen can. By definition, therefore, printout will always look a little less intense (“saturated”) than onscreen images.

Color Gamuts

And second, any Photoshop image saved in the CMYK system will use 33% more space on disk, regardless, than if saved with RGB. Why? Because the number of color “channels”, how many kinds of color there are in the picture at minimum, is three for RGB, and four with CMYK.

So which should we use?

If you’re scanning in a photograph you want to clean up and reprint (say, from the early 1900s), CMYK will work better, because what you’ll see on screen is what will come out of the printer. But if you’re scanning for archival purposes, scan in CMYK if possible to get a realistic version of the image, THEN change to RGB. Since RGB’s gamut is wider than CMYK’s you won’t lose any subtleties or shadings, but you will get a smaller file on disk. And you can scan in CMYK, do all your work, then save in RGB for later. Best of both worlds. Just have to make sure we understand the tradeoff of size, gamut, and storability.

How to Use COUNTBLANK in Excel

Some of the Excel functions, such as IF, come into play all the time, even outside business. They’re versatile and can do a lot. But others seem a little abstruse, or out in left field.

One question I occasionally get from business people in Excel is something like, “How do I make sure someone has filled in all the cells where I need data to, say, calculate an average correctly?” (Think “tax form” or W-4, for example. You want to make sure certain spaces are filled in regardless.)

In some situations, you might use a function like ISERROR, and incorporate it with IF to test if there IS an ERROR when you perform a calculation:


…so that if performing the calculation glitches, you can have the formula show a message to this effect.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”…

Otherwise, perform the calculation.

=IF(ISERROR(AVERAGE(D2:D10)),”There’s a blank cell there”,AVERAGE(D2:D10))



But this doesn’t quite solve the problem, because even if just one of the cells has data in it, there won’t be an error as far as Excel is concerned.

Formula finds one cell with data and calculates average


So we need to call a function that looks for ANY cells being empty, and lets us know. It’s called COUNTBLANK. (Sounds like some kind of strange nobleman–Count Blank, from some tiny hamlet somewhere….)


=IF(COUNTBLANK(D2:D10)>0,”Missing Data in D2 thru D10″,AVERAGE(D2:D10))

COUNTBLANK finds missing data and tells user


What it does is pretty simple, though. It COUNTs the BLANKs in a range, and can let us know how many there are. IF (as you see in the above formula) there are any (“COUNTBLANK(D2:D10)>0”), we want to see the TRUE result from the IF (the message about missing data); if not–that is, if the test is FALSE–then we want it to calculate the average. We’ve told the program to perform the calculation only IF the COUNT of BLANK cells is NOT greater than 0, and let us know if there are any blanks so we (or the user) can correct this.

COUNTBLANK finds no missing data


Of course, guaranteeing that only numbers get filled in is another matter. But there are a few different ways to take care of this, such as Data Validation. The important thing is, MISSING data are a problem, as the result isn’t an accurate one. And letting the user know about this is the big thing.

Microsoft Project: Formatting a Gantt Chart for Summary Task

Recently a student posed a question during a Microsoft Project class. How does one format the bars of a Gantt chart to look the same for each unique summary task? Something that may look like this.


It begins with creating a custom flag field for each summary task. In Microsoft Project a flag field is a basic Boolean value. A simple yes/no field to identify further action. They are created using the Custom Fields button on the Format tab within the Gantt Chart Tools of the ribbon.


The Custom Fields button opens a separate dialog box with many options. Our first choice will be to select flag from the drop down list of data fields in the upper right-hand corner. We are presented with twenty (20) flag fields. Our example uses five (5) and each one needs a unique name.


That can be accomplished by selecting a flag, clicking rename, and supplying the new value.


Once our five (5) flags have been named we can click OK to close the Custom Fields window.


Next we need to mark our tasks as active (yes) or inactive (no) using the flags we just created. Insert a new column in the sheet view of the Gantt chart using one of our flag fields.


The entire column defaults to no but we will select the first task of our named summary and change its value to yes.


Once selected we can use the auto-fill handle (just like in Excel) to copy our yes across the remaining subtasks.


We will insert a column for each of our summary tasks and repeat the process described above.


The great thing is, once the tasks have been flagged we can hide the additional columns we just inserted.


At this point we are in the home stretch. There is only one more step necessary to finish. We need some custom bar styles to match our flagged tasks and that is done by choosing the Format tab within the Gantt Chart Tools of the ribbon and selecting Bar Styles from the Format button in the Bar Styles group. You can also double-click a blank area within the chart side of the Gantt Chart View.


In the Bar Styles dialog box we will scroll to the bottom of the list and add a new entry for each of our summary tasks using the name we gave to each flag. Shape, pattern, and color can be applied to each group and of course the flag number will be selected in the Show For … Tasks column.


Once complete, clicking OK will supply our newly flagged and formatted Gantt bars with the shape, pattern, and color we just set up.


Access 2013: Creating a Calculated Field

Often times the information you need isn’t stored as a field within a database. As a matter of fact, certain fields make more sense to calculate whenever they are needed instead of storing the value in a table.

Microsoft Access 2013 makes this very easy.

To create a calculated you will need a query in Design View. Select the Create tab and in the Queries group click Query Design button. The QBE (Query by Example) window opens and the Show Table dialog box is ready for you to add the table or tables you need.



Once the table / tables are in place you can insert the fields necessary for your query.


The calculated field will be entered in the next available column. It is often easier to see what you are typing if you open the Zoom window. That can be accomplished by Right-clicking the open field and selecting Zoom from the Context Menu.


With the Zoom window open we can enter our calculation. The information to the left of the colon represents the caption we want at the top of the column. The information to the right of the colon is our calculation. In this case it is the curUnitPrice field. No table name is necessary because we are only using one table. The field name is wrapped in a pair of square brackets and is being multiplied by 0.1.


Once the calculation is completed click OK on the Zoom window, save the query, and it is ready to run.



Access 2013: Saving a Filter as a Query

You find yourself working in an Access table filtering out some unnecessary data and it dawns on you this filtering is something you’ll end up doing frequently. It would make sense to store this process as a query in the database.




Okay, so now I have to switch over to the Create tab and design a new query based on the filter I just applied. Or do I? You can actually save the filter you just applied as a query. The process is simple.

Go to the File tab and choose Save As > Save Object As > Save As.


Once you click the Save As button you will be prompted to name the results as either a query, form, or a report.


It really is that simple. The filter you just ran is now a query and can be used whenever necessary.



HTML: A Brief History

Long ago in the far off land of Switzerland something very interesting began, and Al Gore wasn’t there.

1989 Tim Berners-Lee proposes an Internet based hypertext system for sharing documents between disparate operating systems while working as a contractor at CERN (European Organization for Nuclear Research). If you’re interested CERN is derived from Conseil Européen pour la Recherche Nucléaire.
1991 The first publicly available description of HTML. It consisted of 18 elements greatly influenced by SGML (Standard Generalized Markup Language).
1993 First proposal of HTML as a specification to the IETF (Internet Engineering Task Force). At the same time Dave Raggett was working on what he referred to as HTML+ (Hypertext Markup Format) while working for HP (Hewlett-Packard) in Bristol, England.
1994 IETF creates the HTML Working Group.

World Wide Web Consortium (W3C) was also formed by Berners-Lee at the Massachusetts Institute of Technology Laboratory for Computer Science (MIT/LCS).

1995, November 24 HTML 2.0 published as the first specification of the language.

1995, November 25 Form-based file upload
1996, May Tables
1996, August Client-side image maps
1997, January Internationalization
1996, December 16 CSS Level 1 published as W3C Recommendation
1997, January 14 HTML 3.2 published as W3C Recommendation.

The HTML Working Group had been closed in September of 1996 and all future development fell under the auspices of the W3C.

1997, December 18 HTML 4.0 published as W3C Recommendation.

There were three variations:




1998, May 12 CSS Level 2 published as W3C Recommendation
1999, December 24 HTML 4.01 published as a W3C Recommendation.
2011, June 7 CSS3 Color Module Recommendation
2011, September 29 CSS3 Selector Level 3 Module Recommendation
2011, September 29 CSS3 Namespaces Module Recommendation
2012, September 19 CSS3 Media Query Module Recommendation
2014, October 14 HTML5 published as a W3C Recommendation.

The large gap between HTML 4.01 and HTML5 saw the W3C strike off in a new direction incorporating elements of XML with HTML thus creating XHTML. They continued to work in this direction until a fissure arose in 2004 and members from Apple Computers, Mozilla Foundation, and Opera Software formed the Web Hypertext Application Technology Working Group (WHATWG). They pursued a specification they referred to as HTML5. On April 10, 2007 WHATWG proposed that the W3C adopt their HTML5 specification as the starting point for the newest recommendation. On May 9, 2007 the W3C’s new working group accepted the proposal.

2016, November 1 HTML 5.1 published as a W3C Recommendation.

Changing Microsoft Projects Default Task Duration

In a recent Microsoft Project class a question arose concerning the default task duration, which is set in days. This student had recently been working on a deployment project and the default duration was too broad. It needed to focus on a smaller time-frame and it made perfect sense to set the task duration to hours.

An excellent question and the answer couldn’t be easier.


To change the default duration for the tasks in a project we need to use the backstage options Microsoft Project makes available. By choosing the File tab and selection Options we are taken to the Project Options dialog box.


Once there we are presented with eleven options along the left side of the dialog box. Scheduling is the third option on the left and the one we are looking for.


In the Schedule section we will find Scheduling options for this project, and Duration is entered in will reveal the five choices we have:  minutes, hours, days, weeks, and months.


Also notice that the Scheduling options for this project can be set for only this project or for All New Projects.

Once the duration has been changed simply click the OK button in the lower right corner and you will be returned to you open project. Any value entered in the duration column will now default to hours unless you specify otherwise.



Photoshop CC 2017 Property Panel Update

November of 2016 has seen an update to the Adobe Creative Cloud Suite which includes Photoshop. I have already posted one article on the changes to the New Document window and another article on the updates to the Liquify Filter.

The focus of this article will be on the changes to the Properties Panel in Photoshop CC 2017. The last couple of updates have seen improvements to the Property Panel, but this by far is the biggest change.

Take a look at this screen capture of the 2015.5 Property Panel in use.


The Properties Panel contains absolutely no information about the selected layer.

The same layer selected in Photoshop CC 2017 now includes x and y coordinates as well as width and height data.


Here is another example this time with a text layer selected. First in the 2015.5 release.


And now in the 2017 Update.


In addition to the x and y coordinates available previously we now can change the font, font size, alignment, and color. Additionally there is an Advanced button that will open the Character Panel.

All of these changes are designed to accomplish one thing, making each task more efficient by limiting the number of panels we need to open.

The Properties Panel update is a welcome change to Photoshop CC 2017.

Photoshop CC 2017 Face-Aware Liquify Update

If you remember the last update to Photoshop (Summer 2016) we shared a post about the new Liquify Filters Face-Aware feature. It turned out to be a wonderful addition.

The filter addition would naturally recognize the facial region and let you modify eyes, nose, mouth, and face shape. Working on a face or multiple faces became much easier.


But there was a drawback…there was only one set of adjustment tools for the eyes. Which meant the changes would be applied equally to both eyes.

Photoshop CC 2017 has addressed this issue and has fixed it. The Liquify Filters Face-Aware settings include two sets of controls for the eyes; one for the left and one for the right. Eye size, height, width, and tilt can be set for each eye independently.


Thank you Adobe. A great filter is now even more powerful and an easier to use utility.


Photoshop CC 2017 New Document Window

Sometimes the changes to an application are subtle, sometimes not so much. Last year’s Start Workspace in Photoshop was one of those not so subtle changes. Just a few weeks back Adobe’s Creative Cloud application suite updated to its 2017 version and Photoshop has added another one of those not so subtle changes.

Selecting File > New in the menu bar or the New button within the Start Workspace has resulted in dialog box similar to this for a number of years.


If you expanded the Document Type menu you were presented with several choices including; Clipboard, Default Photoshop Size, U.S. Paper, International Paper, Photo, Web, Mobile App Design, Film & Video, Iconography, Art & Illustration, Artboard , and Custom.


This is no longer the case. The New Document window is much larger and although some of the old menu categories remain the window is completely revamped.


Each of these presents a screen with a series of default sized blank documents, and also pre-built templates available through Adobe Stock.


Many of the templates are free and there is a search option that will take you to Adobe Stock online so you can look at the other options available.


Once downloaded the resulting file will contain a series of scenes, layers, or artboards and each item will be on its own layer ready to be used as you see fit.


All the custom options are still there but Adobe has added through Adobe Stock many start-up options. You don’t have to start with a black slate if you don’t want to.



The HTML q Tag

Somewhere in the neighborhood of 18 to 20 years ago I sat down one weekend with a book, a computer, and a few images and put together my first basic website. It was about artists from the Impressionist movement and was not anything to brag about with one exception, I built it myself, using Notepad.

That was the era of HTML 3.2 moving toward HTML 4.0. Netscape was my browser of choice and most pages used tables for layout.

A lot has changed in the following years but one thing remains…most people don’t know that HTML has a q tag to wrap things in quotes. If something is supposed to be in quotes most people will just type them in their code editor, or allow their visual design application to insert the entity characters to put quotes in place. Like this:


Which results in this page.


One of the main reasons I didn’t use the q tag early on was lack of browser support. Netscape supported it but IE didn’t, and IE was on the rise. The next group of browsers (Firefox, Chrome) weren’t any better. When you don’t use something for an extended period of time you tend to forget about it. That’s what happened to the q tag in my brain.

Another reason may be the fact that it is a specialized tag. It serves one purpose, wrapping text in quotation marks as part of a citation. Why learn about a tag that does one job? Mostly for SEO (search engine optimization). Giving credit where credit is due.

Using the q tag is simple and straight forward. Whatever you want quoted…wrap that text within a pair of q tags.



As for browser support in 2016; all current browsers support the q tag. IE 9 forward offers full support. So maybe it’s time to look at the small but useful q tag and give credit where credit is due.


HTML5 Form Date Attribute

Getting corporate America to make the move to HTML5 seemed to take longer than any other transition we’ve seen in the past. In part I’m sure due to the long development process. From first working draft in January of 2008 to stable recommendation in October of 2014, that’s almost 7 years.

There are many compelling reasons to make the change but the one we will focus on today deals with forms and an easier way of inputting a date.

Early on the primary means of capturing a date was supplying a simple textbox and a visual example of the formatting.



It was simple but had to have validation to guarantee its formatting.

Another approach would have been using select menus. A lot more code, both to build the lists and to concatenate the values on the server.



Another option would be to use a JavaScript library like jQuery and plug in their Datepicker UI component.


It would all be so much easier if date were just on option for a form element. Well it is in HTML5!


That simple input type results in a UI structure that looks like the Datepicker from jQuery.


This screen capture is using Chrome but it looks exactly the same in Opera. If you use Edge on Windows 10 it looks like this.


On an iPhone using Chrome within iOS 10 it looks like this.


That’s all the good news, now for the downside. It doesn’t work in any version of IE, Firefox or Safari and doesn’t look promising for the last two in the foreseeable future.

But don’t let that stop you. Remember, if the browser doesn’t recognize the input type it defaults to a textbox, our old standard method. The benefits to the remaining users and all those on mobile devices are all in the plus column.

Adding Power View to the Excel Ribbon

In recent years Microsoft has included several add-ins to boost Excels usage in the world of Business Intelligence. They include Power Map, Power Query, Power Pivot, and our topic of discussion today, Power View for Excel 2013.

Each of these ship with the Professional Edition of Excel 2013 but need to be added through the Options Panel. After going through this process I was more than surprised to find Power View was still unavailable. So this posting will help those of you experiencing the same dilemma I faced recently.

First things first. This is what my Insert tab looked like after the Power View add-in was…added in.


Normally there would be a Reports group that included one control, Power View. As you can see it doesn’t appear on this tab or any other tab.

Here are a couple of additional screen captures to show that it had been selected and does show up in the Add-In area of the Options Panel.



I had gone through the necessary steps just as I had with Power Pivot, Power Map, and Power Query. They were all working yet Power View wasn’t available.

It took a while but the solution was found in a posting dealing with Excel 2016. Those steps are what we will go through now.

First we are going to customize the ribbon by adding a Reports group to the Insert tab. That is done by going to the File tab and selecting Customize Ribbon.


Make sure Insert is expanded and selected under Main Tabs, and then click the New Group button under the Customize the Ribbon section on the right side of the panel.


Now we will rename the group by clicking the Rename button in the same area as the New Group button.


Now we need to find Power View in the Choose Commands from section on the left. It will be in the Commands not found in the ribbon menu option. It is Insert a Power View Report.


That’s it. Click on OK at the bottom of the Options Panel and head back to the Insert tab. There it is on the far right side of the ribbon.

One additional thing, you will need Silverlight installed for Power View to work.




Styling the Humble Checkbox Using CSS

The checkbox has been a staple in the world of HTML forms since day one and for the most part has remained unchanged. HTML 4 introduced the label tag which can be associated with a checkbox using an id attribute in the checkbox and the for attribute within the label. That way you don’t have to click directly on the checkbox, simply clicking the label will select or deselect it.

If the checkbox looks any different today compared to 20+ years ago it’s only because the browser renders it that way.

A typical form with several checkboxes would look like this:


And the code to generate the page looks like this:


Four simple checkboxes with labels. Nothing fancy but they could be simply by applying a little style using CSS.

Take a look at the same four checkboxes after a CSS makeover:


The HTML to generate the page has changed a little, but not all that much:


You’ll notice the label is still there but it has been emptied and moved after the input tag. We’re using it to create the circles and it is an easy task because the id associates the label with the checkbox. In place of the standard label we’re using a span tag to tell us what the checkbox represents.

All the heavy lifting is being done by an external stylesheet using one class selector, one element selector, and a few attribute selectors.


First up we shape the div tag that contains each checkbox. We size it, color it, round off the corners, and position it. The relative positioning is necessary because items within it will be absolutely positioned and need a point of reference.

Next up we create the narrow gray line the circles move along. It’s nothing more than empty content that has been sized, absolutely positioned, and colored.

Now we can start working on the circles, both outer and inner, and we will use our empty labels to accomplish this. They will be positioned, sized, stacked and shaped. Oh yeah, and one last item, a CSS3 transition that will allow the circles to shift when they are clicked.


The :after pseudo class will be the vehicle used to create the inner circle.


The last three selectors are all attribute selectors aimed at the [type=checkbox]. The first one hides the regular checkbox. The second one moves the label (our circles) 34 pixels from its current position when the checkbox is clicked. The last selector changes the inner circle color when clicked.

A pretty amazing change and all done with a little CSS slight-of-hand.

CSS Clearfix: Three Lines and a Breakdown

Occasionally the learning curve in web design can be gentle. Other times it’s a sharp right angle and once in a while it’s just a nasty hair-pin curve. How you perceive these curves is just that, your perception. You know, this morning you feel as sharp as a tack and late in the afternoon you feel as dull as a butter knife.

Clearing floated elements has been one of those butter knife curves for me. True confession time…it hasn’t been a gentle or sharp curve, it’s a I know it works so I haven’t devoted brain time to understand it curve. Was that out load? They say confession is good for the soul.

Let’s break it down and see why it’s necessary and why it works.


The code above results in the page below. Two nested containers inside an outer wrapper. That outer wrapper should be 100% of the pages width, as tall as whatever it contains (height: auto), and it has a light gray background color.

Inside the wrapper are two boxes that should each be 30% of the wrappers width, 100 pixels in height, and have a phthalo green background color which is 70% opaque.


But there is a slight problem, the background color of the wrapper is missing. That’s because floated elements don’t follow the pages normal flow rule. They are removed to float left or float right which means the wrapper now believes it has no content. The height: auto of nothing is…nothing. No content, no background color.

The fix, or clearfix as it has been designated comes to the rescue. Three simple statements that make things right based on a pseudo class, a display property, and the clear property.

CSS has introduced many pseudo (false) classes in its last two versions :before and :after being included in that list. Both of these pseudo classes are useless without its content value. What are we inserting before or after? In the typical clearfix code the content can either be an empty string or a single space. We just need some content in our wrapper that is not floated and it will be placed after any existing content.

That leaves our display: block property-value pair. Using the rules established in the Block Formatting Context by the W3C block formatted elements will contain their floated children and cannot extend past the bottom edge of the block.

The code below shows our added clearfix and below it is the resulting web page showing the missing background color of the wrapper.




Using Qlikview’s Date Functions

When putting together a data model for a Qlikview application you will occasionally run into a field that is not formatted properly, both from a numeric and presentational perspective. Behold the transformed junk file that Qlikview organized into meaningful data.


Meaningful yes, recognizable by the end user…not so much. Don’t get me wrong, it all works as you can see from this example.


Selecting a date will match up with the Plant #, Production Line ID, and Estimated Production, but the average user doesn’t what to stare at the date value and disassemble so they can mentally reassemble it as a logical date.

That’s where Qlikview’s Date() function steps in, or is it the Date#() function? You see, they are both legitimate, the first function is a formatting function and the second is an interpretation function.

A formatting function takes a numeric value as input and converts it to text whereas an interpretation function uses text as input and converts it to a number. Either way, the output is a dual which means it can be displayed as text but utilized as a number.

The first thing we’ll need to do to make it recognizable as a date is use an expression instead of the lonely date field. Right clicking the Listbox and choosing Properties from the context menu will let you change the field associated with the Listbox…or build an expression using the field.


The Date#() function will allow us to convert a string such as 20120528 into the serial date number 41057. Qlikview uses a serial date number based on December 31, 1899. 1/1/1900 being day 1, 1/2/1900 being day 2 and so on for each day. Today’s date 9/28/2016 would be day 42641. The format ‘YYYYMMDD’ tells the function what part of the number represents what part of the date. By the way, the word Date (in red) after the opening parentheses represents the actual date field.


Next we’ll nest the existing Date#() function within the Date() function. It will also be supplied with a format to present the date in a recognizable manner.


Our completely formatted date is now not only usable, but also recognizable courtesy of both Qlikview date functions.


Qlikview Chart Styling Using Expression Attributes

Qlikview makes it quick and easy to assemble your data into a professional BI application. But sometimes the information needs a little boost, that extra touch that makes the difference.

One way to accomplish this is using Expression Attributes. These attributes can be found on the Expression tab of your chart object.

Let’s take a look at a couple of examples. The line chart in the first image serves its purpose but might benefit from a style change.


By right-clicking on the chart we can enter the objects properties window. Selecting the Expressions tab allows us to see the expressions that have been used to build the chart. There is a plus sign in front of each expression that when expanded allows us to see the Expression Attributes.



There are several different line styles that can be used:

=’<S1>’ Continuous

=’<S2>’ Dashed

=’<S3>’ Dotted

=’<S4>’ Dotted & Dashed


For our example we chose the dotted line.

Our next example eliminates the line of a Combination Chart and replaces it with a Diamond Symbol.


A little change of color for the symbol should add that extra touch. We’ll use the RGB Function to make the Diamond Symbol red.



These are just two simple examples of using Expression Attributes to enhance our Qlikview BI application. There is much value to be gained in subtle changes to our visualizations.

Using Character Mapping to Enhance a Crystal Report

It is possible to dress up a report using nothing more than a font and some color. But not in the way you think. There are some specialty fonts installed on a Windows computer know as Wingdings. Each letter is a small picture (like a phone, scissors, knife, etc.) that can be used in a report to enhance the appearance.

The first step in formatting the report is to find the characters you intend to use. They can be found in the Character Map dialog box within Accessories > System Tools or by typing Character Map in Windows Search.


Next you will need a formula to determine when a specific character will be displayed.



In this example when sales drop below $40,000 a cross will appear otherwise a check mark will take its place. The letters “Q” and “R” represent the values you’ll need in Wingdings2.

We can dress it up even more by conditionally changing the font color.



The final step is formatting the formula font to use Wingdings2. This is accomplished by selecting the Formula object in Design View and opening the Format Editor, going to the font tab and selecting Wingdings2 from the font list.


The finished report now contains a “Cross” or “Check Mark” in red or green where there once was just a capital Q or R.



InDesign Data Merge with Images

In this Adobe InDesign tutorial you’ll learn how to merge multiple image files into Adobe InDesign to create form letters, postcards or other custom InDesign documents that use custom images for each page in the document. To learn more, visit our InDesign Training Course page to see our InDesign Training classes.