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.

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.

GENERAL IDEAS

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, AUDIO, CHARTS

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.)

TRANSITIONS AND ANIMATIONS

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 that..no 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.