How to Format the Gantt Chart in Project

Recently, I had a question about formatting the Gantt chart from a student who mentioned the issue of color-blindness. Since various kinds of this problem exist, knowing how to get around it can be helpful, just in case. As critical tasks are highlighted in red, it could be an issue. The Gantt chart, after all, depends at least in part on color, or so we normally think. But there are ways around it.

Format gantt bars

To start the process, we go to the Gantt Chart Tools Format tab, and click the Format button in the Bar Styles group. We then have to go to the drop-down, and click Bar Styles there.

gantt bars dialog

Then comes the big box. The key is understanding how this box is laid out. In the upper half, the names of the chart elements are listed on the left, and the appearance and other attributes go in each row.

What one has to do is select an element name on the left, then look down at the lower half of the box, usually to the Bars tab (we’ll deal with Text in a sec). For the regular Gantt bars, the kind we usually concern ourselves with, the Middle section is the one we need to tweak. Shape and Pattern are important, but Color is the main thing. If we’re dealing with someone who has red-green color blindness, the ability to change color for critical tasks is certainly going to be a help. Better still, we can change the pattern—which lets us put color aside when we need to.

critical tasks gantt    critical check mark

Once we make the changes, we’re set.

The only downside I’ve run into is, once the Critical Tasks checkmark is turned on, it needs to stay on when one formats the critical task bar appearance. Turn it off, even for a second, and it loses the formatting we apply to critical task bars. (If anyone finds or has a solution to this problem, I’d be glad to hear it. 🙂 )

Adding text to the bars is done in the Text tab downleft. This can be a help too, but one doesn’t want to overdo it, since the left side of the Gantt chart has text aplenty.

How to Set Up Custom Signatures in Microsoft Outlook

There are several features in Outlook that I can only describe as “cool”, among which is the ability to create custom signatures for email. It’s a feature most people would think should be in a program like this, but that doesn’t make it any less cool in my eyes. And not only is it not hard to set up, but more than one signature can easily be made available. This is a very important business feature, if you think about it. Having business contact data in an email is, if nothing else, polite.

Signatures location

First thing is to get to the dialog box. It’s easy: File tab to Options command, then click the Mail category on the left side of the dialog box; third item on the right under Compose Messages is the one we want, and the Signatures button is plain as day.

Signatures box

Once into the actual Signatures dialog box, we usually click New on the left side to start setting one up. Give it a name—sticking to something suitable for business is a good idea—and start editing, in the main space downleft.

Signatures setup

Any text you like can go in there, formatted as you wish. And any pictures, too. There’s even a button for inserting pictures, if you don’t feel like copying and pasting. You may have to play around with the layout a bit, to arrange things as you want them. If you have a program like Photoshop or Illustrator, you could simply set up entire signatures as pictures, import them, and do the whole operation in one easy step. Either way works. The key things are just the usual—highly recommended to use company font and color choices, company logo if available, etc. Otherwise, keep it simple and informative. Just the essentials. Before OK-ing out, make sure you go to the top right of the box and choose which signature(s) you want for your defaults.

Using signatures

From then on, whenever you start a new email, your default signature will show up every time at bottom. But the dropdown at the top of the email window lets you change on the fly. And you can edit any of them anytime.

How to Use Dodge and Burn in Photoshop

Of all the tools in the last few versions of Photoshop, the Dodge and Burn tools seem to be among the least used, possibly since digital photography has reduced the need for them. But once in a while, especially in restoring scanned-in prints, one might still try them out.

Dodging and burning, in photography, are lightening and darkening parts of an image that show some detail but not quite enough; if dark parts can be lightened a little, or vice versa, visible detail might be improved.

Once we have a decent scan, we can decide which parts are a little too dark or light. We can then select an area to work on—the key is to feather the selection by a fairly high number of pixels, how many depending on the image. That is, to make the selection fuzzy-edged, so the dodge and burn we do won’t stop too sharply at the edge and give itself away.

Dodge dark

We then select the tool—they work pretty much the same other than which thing they do—and adjust the options. For dodging, select Shadows; for burning, select Highlights. With dodge and burn work, one can use Midtones for both, but the thinking is to affect the more “extreme” areas of the image, so experiment if needed. The Exposure control is for how much “oomph” the tool will have, or how much it will affect the area quickly. We want this fairly low, usually no more than 20-25% or so, since we want to work slowly up to the degree of effect we want. Using the Airbrush option to build up is okay to try; I find I don’t use it much, but some people have told me it’s good when you get used to it.

Dodge and burn done

The real trick, at least from my experience, is that “tapping” the mouse button to apply short bursts of effect seems to be one good approach in both dodge and burn. Though we can use Undo or the History panel to go back if we overdo it, one doesn’t want to have to do it too often.

And if the effect is not very obvious, that’s because it’s not supposed to be. Remember, the goal in many cases is to have the image look as if nothing was done to it at all—as someone once wrote, “the art of concealing art.”

Using the Mesh Tool in Illustrator-Basics

The Mesh tool gives the Illustrator artist a capability that some people think doesn’t exist in this program—the ability to shade an object, for example if one wants to make something look three-dimensional, solid, curved, etc. This is fairly easy in Photoshop; what a lot of people don’t know is it’s only a little more work in Illustrator, and has easier adjustability to boot. 😊

Shape before mesh

The first thing is to create the outline of the object. I’ve laid out a simple vase-ish kind of shape for demonstration. It’s supposed to narrow down at the neck and bulge out a bit near the base. Now, art theory says that if part of an object “protrudes” toward the viewer, we paint it a little lighter as more light would strike it (the end of the nose, for example). And a part that “recedes” (say, the space under the chin) would be shadowed, and therefore a little darker.

Adding mesh

So we select the object, select the Mesh tool, and click inside it, usually near the center. This puts one horizontal and one vertical mesh line on it. We can then add more by clicking on either of those, as many as we want. The real-life example might be looking at a globe of the Earth, seeing the latitude and longitude lines that make it “look” round even aside from seeing the highlights and shadows on it—the lines help us perceive its shape.

Using mesh

But that isn’t the real power here. We can now use the Direct Selection tool and click on an anchor point where mesh lines meet, and add another fill color that leaks or diffuses out from the selected anchor point(s). If you imagine a white towel absorbing fruit punch, or grape juice, a little here or there, you have the idea. And the control handles emerging from the anchor points not only control the shape of the lines, but how far the color spreads from the anchor. And the best part is, you can always go back and adjust some more. Add mesh lines, add colors, remove them, adjust the shapes, all as you like.

Some time back, the cover of the box for Adobe Illustrator showed a reproduction of Botticelli’s Birth of Venus (the “Venus on the Half-Shell”), done in the program. What I did here, with some refinement, is how all the delicate flesh tones and shadings were done to almost exactly duplicate Botticelli’s work. Can we do “real” art in Illustrator? With meshes, I think we can.

Using Layer Masks in Photoshop – Basics

When we put together a composite image in Photoshop, built from pieces of other images, using layer masks is a very helpful trick. Masking allows us to experiment with the appearance of the composite by letting us show or hide very specific bits of the layers in the image, and adjust what shows without editing or deleting any of the actual images in the layers.

Layers

First, we assemble the pieces of the composite. The number of layers is entirely up to the user, of course. (Recently found out the actual max number you can have is 8000. Wow.) Then, of course, arrange the layers in whatever order you need.

Selection for masks

Now, the real key step in masking in a layer is selection (which is why I’ve emphasized it in other posts, and will continue to do so). So using whatever tools and variations we like, we select the area(s) we want to un-mask after selecting the layer in question. That is, we select what the masks should not hide.

Activate masks

Next is to give the command to create the layer mask, simply by clicking the appropriate button at the bottom of the Layers panel. This is the easy part, but still important. When we do this, we can often see a few bits we missed in the selection part. And the good news is that we don’t have to start all over again—we can edit the masks themselves to include the bits. We do this by using the painting tools—which are often easier to work with than the selection tools.

Cleanup

Setting a brush to moderate size (depending on the image size), perhaps ten or twenty pixels here, with a hardness of 100%, gives a nice sharp edge with which we can clean up bits of mask/selection. We make sure the Layer Mask thumbnail is selected in the layer in question, set the Foreground color to black (which masks, or hides) or white (which unmasks, or reveals), and do our touch-up.

(FYI, just so it’s all step-by-step on this part:

1-Select the layer MASK in the layer. 2-Select and adjust the brush you want to use to fine-tune the mask. 3-Check that the foreground color in the Tools panel is black for adding to the mask, or switch to white for un-masking. 4-Paint the appropriate areas.)

Is it a little tedious? Yes, but not really difficult. Mainly zooming in to do a few bits and then zooming out to check. Photoshop may not take away all the hand-work when we create layer masks, but at least we don’t have to clean up all the paint smears.

How to Set Up Better Charts in Crystal Reports

Crystal Reports is capable of inserting charts almost anywhere the user likes, and the choice of chart type is nearly as wide as in Microsoft Excel. The main difference is, the chart has to be inserted before choosing chart type, layout specifics, and formatting. Though this can be a little daunting at first, it’s not really too different from the way other programs employ them.

One key element, generally, is to make sure the chart can be as self-sufficient as possible. As in programs like Excel, and rather unlike PowerPoint, a chart in Crystal has to be pretty self-sufficient, since we don’t know whether someone can email to ask questions about it. And mostly, users shouldn’t have to. So labeling, choice of chart type, and so on are always important.

Charts location

Another is, when inserting charts, to make sure to select the field one wants to chart first. Crystal looks for this. The chart can actually go in any part of the report, but usually works best in a header or footer area, especially the report header. Putting a chart in the Details section of the report gets a chart for every record, and this is not frequently needed.

Charts components

There are a few items which could be considered necessities when building charts. Unchecking the Auto-Text checkbox in the Text tab of the Chart Expert, and possibly one or two others there, to allow adding more meaningful titles, will help. Making sure every element of the chart is labeled in some fashion is a good thing, as long as it isn’t cluttered with (seemingly) dozens of bits of text. Formatting the charts’ axes properly (showing dollar-sign formatting for sales numbers, for example) may seem obvious, but sometimes it’s so obvious we miss it.

And interestingly, some people mention that a chart legend is not always necessary, or even helpful. If the chart can have labeling which would make a legend redundant, one can easily uncheck Show Legend under the Options tab of the Chart Expert to remove it.

Charts labels

One apparently lesser-known trick is to format one element, such as the labels one can put on columns or pie wedges. Left-click a single number-type label, then right-click it, and use the Format Data Labels command to specify the particulars, such as the labels’ locations or appearance.

The overarching point is to do a little planning on this in advance. What should we chart? Why? (Very important question.) Where should the charts go? And by implication, what level of data are we showing charts for? The key thing is to make sure the chart serves the purpose of clarifying the data, not drawing attention away from it and/or confusing the reader.

How to Select Precise Colors with Pickers and Libraries

More than ever in today’s business world, having one’s company stand out from the competition is important; product branding, and the use of color in particular, is integral to this.

Product branding is the term we normally use to describe a distinctive scheme of color choices, font choices, logo, etc. which give a company a unique “look”. Coca-Cola, John Deere, Five Guys (a burger chain), Panera—any company at all. So how can a company select—and use consistently—any of these, particularly color?

Access to colors     Choosing colors

There are at least two methods we can use, at least in a majority of programs. One, which is pretty universal in business software, is the so-called “color picker” in its various forms. In PowerPoint, for instance, one can create or select a shape, go to the Home tab in the Ribbon, and on the right side click the Shape Fill dropdown. From there, one can click the More Colors option, and in the Custom section, enter the numbers which represent the RGB (Red-Green-Blue) or HSL (Hue-Saturation-Lightness) values for a company color. We don’t see the other main color system, CMYK (Cyan-Magenta-Yellow-Black), as we print hard copy from PowerPoint slides comparatively rarely. But most companies can give someone CMYK values which can convert to RGB without too much trouble.

In other programs, such as Photoshop or Illustrator, we can indeed enter CMYK values directly; since these are often what businesses use for print ads, they work fine.

But we can go further; we can also use one of the many color libraries available, such as Pantone, Toyo, Trumatch, Focoltone, ANPA Color, and others. These are recognized color standards worldwide. They guarantee that printed material will be as accurate to particular colors as possible.

Photoshop colors     Color picker

Getting to the Color Picker in, say, Photoshop or Illustrator, is as simple as clicking the foreground or background color swatch. Once the dialog box is up, one can enter HSB (Hue-Saturation-Brightness), RGB, CMYK, or even Lab (a system in existence since 1931) numbers. And to get to the color libraries, simply clicking the button of that name on the right side of the picker will jump the user directly to them. That’s where most company colors are, nowadays.

Colors in libraries

Though Pantone is probably the most recognized name in the business, the important thing is one can select the appropriate library, then the color, usually by number or letter, and click OK. It  is then ready to use. In many cases, it will automatically add to the user’s swatches, or can be easily dragged into same. The main downside, as it were, is that the colors one brings in normally save at the document level, so a custom color library must occasionally be saved outside the document one is working in to be more widely available. But most programs that have color library selections allow for this.

Using the Direct Selection Tool in Adobe Illustrator

Of all the tools in Illustrator, among the most critical are the selection tools. And even though the Group Selection tool is a little more complicated in its function, the Direct Selection tool is used more frequently. So understanding its use is at least as important.

The Direct Selection tool’s job is to allow the user to work with individual anchor points and control handles, as opposed to the “main” Selection tool, which works with an object as a whole.

Direct Selection 1

The basic technique is simple enough. Once an object is created, one can switch to the Direct Selection tool, click, on an anchor point, and then either move it or work with the control handles on it, if any, that tell the curve of the line what to do. But what some people have said is, they have trouble selecting the anchor point even when they know where it is, because no matter how much they zoom in (64000%? Really?), it never gets bigger.

Direct Selection drag

Luckily, there are a couple of features which can help. One is the ability to drag a small selection box on a part of the shape near where the anchor point should be, using the tool; doing this will show nearby anchor points and make it easier to see where they are.

Mouse Over pref

Another is in the Selection and Anchor Display section of the program preferences: a checkbox called Highlight Anchors on Mouse Over. Having made some anchor points visible, if one slides the tool point within a few pixels of one, the program enlarges the anchor point, and lets the user click on it. So one’s aim need not be perfect.

Hover on anchor point

For a good number of things one needs to do in Illustrator, selecting all or part of a shape, or group of shapes, is a must-have technique. Therefore, knowing some of the tricks for the tools doing it, especially the Direct Selection tool, can make the job much easier.

How to Restore Pictures in Photoshop—Easy Fixes

Although the restore and cleanup business is not quite as much a boom thing as it was when Photoshop first came out, it still comes up pretty regularly. Even though detailed restorations take time, there are a few things you can do to get the most obvious problems solved without too much trouble.

The most common problems with most older photos have to do with age. Images yellow or fade with time, in which case doing a restore implies color correction or improving saturation. Others involve fading of contrast, or having areas too light or dark to see detail in. Recognizing the main problem(s) more or less on sight is often the key to the solution.

Restore color balance

One is correcting the white balance—getting rid of discoloration from time, or sodium lights, or greenish fluorescent light, so the parts that should be white, are white. You can start by adding a Curves adjustment layer and using the White Point sample tool, sometimes known as the White Balance tool. This lets you tell the program what parts of the picture should be white, and has the rest of the picture recolor accordingly.

Restore contrast

You can also add a Levels adjustment layer, to restore or bring back contrast. If the histogram in it shows blank spaces at either end, move the white and black threshold markers inward till they reach the beginning of the non-blank space, and the image should look less flat.

Detail in dark area

Bringing out detail in a dark area, or a light one, can also be done with a Levels layer. Select the area you want to lighten or darken, then add the layer; selecting first will define, or mask, the area, and you can then adjust only the parts which need it. (Shh—this is one of the Secret Awesome Inner-Circle Techniques for doing a restore in Photoshop—the ability to selectively adjust parts of the image. The only downside is having to learn to do careful selection.)

How to Create a Calendar in Project (and Why)

When setting up a plan in Project, it’s very important to get the calendar organized first, before adding tasks, resources, assignments, or almost anything else. Why? Because a project timeline dictates many of the scheduling details, and the calendar controls the schedule.

Calendar first step

Getting to the dialog is easy—we click the Project tab, slide to the Properties group, and click Change Working Time. Once there, we usually want to click Create New Calendar at the top right, since copying an existing one and modifying it leaves the originals for later use.

Create new calendar

We can then change the name of the copy, say, to the company name or the project name (since calendars are file-specific).

Calendar holidays

Then we need to add the usual holidays. The good news is, it’s rather like doing so in Outlook; if you use that program for scheduling, the technique is similar. Select the day, go down to the Exceptions tab, and put in the name. Selecting multiple days is fine, too.

Calendar repeats

Clicking the Details button on the right lets us set the holiday, Labor Day for example, to repeat. It’s a good idea to keep the number of repeats fairly low, no more than ten, because (unless the project is REALLY long-term) it’s not necessary…unless you want to have the file turn into a template for other projects. Even then, going for more usually isn’t required.

Although it’s possible to edit a calendar later, just before or even during the run of a project, it’s highly advisable to get it as polished as you can beforehand, and to avoid editing it later, if at all possible. Any changes during the execution of the project will almost certainly change the schedule, and often in a way we can’t predict.

This is one part of any project which really needs close attention, because it can critically affect almost every other part to some degree.

How to Use the Critical Path in Microsoft Project

The use of the critical path in Project is a vital part of getting a project to finish on time. This is especially true if any juggling of the tasks, resources, and allocations has to be done once the majority of tasks are in place. It allows refinements to be done where they can have the best effect—what is sometimes called a “force-multiplier” (no Star Wars jokes, please 😉 ); it simply means to make the most of what one has.

The critical path is that series of tasks in which there’s no slack, time-wise. Think of a group of boxcars on a railroad siding, all coupled together. If another comes down the track, and bumps into them, they all have to move. If the last one is right next to the bumper at the end, there’s only so much maneuvering room. This is sort of the situation on the critical path.

Turn On Critical Path

Once a good chunk of the project has been composed, showing the critical path is easy. It used to involve going through a three- or four-step wizard, but now it’s just turning on a checkbox. The critical tasks turn red on the right side of the Gantt chart—non-critical tasks stay blue. (These can be reformatted, if necessary.)

Critical Before

We can then see, if we start making changes to the project “flow”, that any changes not on the critical path may not affect the timeline much, or at all. And this is not good. We want changes to do something—shorten the timeline, or allow two or more tasks to run at the same time, or otherwise make things go more efficiently.

Critical After

If there are any tasks which we can inactivate, or reassign resources on, or otherwise fine-tune, again, always better to do it on the critical path. Making alterations to other tasks can be helpful, but this is the place to do it which will have the most effect on the schedule.

Using Dependencies, Lag, and Lead in MS Project

Setting up dependencies, or task relationships, is an integral part of working in Project. But many newer users ask, Which relationship should I use, and What are these “lag” and “lead” things about?

The choice of dependencies, as well as using the two other items, derives from the nature of the tasks. This is the one thing you have to bear in mind, because there’s no all-inclusive formula for determining these. That said, there are a couple of general pointers you can use to figure it out.

Usual dependencies

If Task A has to be completely finished before Task B can start, such as demolishing a house before building a new one, the relationship will be Finish-to-Start.

Say you’re writing a book and want to make sure the illustrator gets started more or less immediately along with the writing. A Start-to-Start relationship might be in order. The artwork can happen pretty quickly once you have a decent chunk written.

If you’re building a house or office, the framing has to be complete before the plumbing and electrical, the drywall, etc., can be finished. So a Finish-to-Finish relationship would work for this.

(The fourth type, Finish-to-Start, is so rarely used I won’t go into it here. Suffice to say in the twenty-plus years I’ve taught the program, I’ve seen maybe two occurrences of it. No offense.)

Okay so far. Now, what about lag and lead?

Those come into play when two tasks have to have a time gap between them (lag), or can partly overlap (lead); the dependencies still matter, but this is something else. Start-to-Start and Finish-to-Finish can overlap entirely, if the nature of the tasks allow, but lag and lead usually come in with a Finish-to-Start. (By the way, it’s pronounced “leed”, as in “lead or follow”, not “led”, as in a pencil.)

Dependencies - lag

If you pour concrete, even the quick-setting stuff needs a few days, minimum, to be ready to walk on etc. A lag—a delay—would go in there to indicate a setting time is required regardless. You could include it in the task time, but the lag makes the situation clearer to anyone who looks.

Dependencies - lead

In the book-writing example, if the author wants to get about a third of the book written before the illustrator starts sketching, it might help the artwork to have a solid direction to go in. So a Start-to-Start with a lag, or a Finish-to-Start with an overlap, or lead (sometimes known as negative lag) would be the natural thought here. (And the lag—or lead—can be written as a percentage of the predecessor task’s time, which is helpful as it doesn’t require one to do the calculation in one’s head.)

As you can see, the nature of the work, and of the tasks, and their “natural” relationship, will often determine how one hooks them together in Project. I’ve often pointed out that it can help to work on a legal pad and pencil or pen first—some people find figuring dependencies on paper before sitting at the computer can make it easier.

How to Create Custom Fields in Microsoft Project

MS Project has a LARGE number of fields set up by default, for almost anything the program can track. But even with all the feedback the design team gets, they can’t anticipate everything a user might need to monitor during a project. So the team built in the ability to create, or rather modify, “custom” (unnamed generic) fields which are held in reserve for just this situation.

Resource fields

One example might be which resources belong to which department in a company. The Group field in the Resource Sheet could be used to contain this information, but some users already employ it for, say, internal versus external, or a non-company-related item. So setting up a resource field for this would make some sense.

Custom fields dialog

Bringing up the dialog box is easy—Project tab on the Ribbon, Properties group, and click the Custom Fields button. We then choose from the fields what we want to create; in this case, the Resource (at top), along with what kind of data it will hold (Type: on the right), namely text (as in department name). We rename the field (Text1), call it Department or DeptName, or similar, as we like.

Department list
The next step is to bring up the place where we can write in the department names. Almost right in the center of the Custom Fields box, we click the “Lookup…” button. And in the second dialog box, the Value and Description columns make this part easy. Values and descriptions can match, or not, as we see fit; as long as we understand what we’re selecting, it’s fine.

Custom field inserted

Finally, we OK out of the boxes, navigate (in this case) to the Resource Sheet, and add the column on the end, or wherever we feel appropriate. A lookup table allows us to choose from a drop-down list, saving at least a little time for the user/manager, and giving the manager a little flexibility on what can go into a project in the way of fields.

Using Conditional Formatting for Sections in Crystal Reports

When we use conditional formatting, we usually think of it in terms of things like making better sales numbers green and lower numbers red. But we can be more subtle than that in Crystal.
No conditional formatting
A common example is where records are incomplete, or where data are filled in at some levels and not others. Sales databases might have country-level data, and city-level data, but not state-level if there are no states in a small-enough country. So if there are no states, or provinces, we don’t want to see blank spaces in the report.
Section expert
We can go into the Section Expert in such a report, and having set up grouping for the levels in question (country, state/province, city), we select the state/province group header. We then go to the Common tab on the right, and click, not the Suppress (No Drill-Down), as we don’t want this to be the norm, but the X-2 button next to it, as we want to have the suppression be as needed—that is, conditional.
Conditional formula
The formula we write to look at it asks, basically, if there’s anything to show. More specifically, in our example, are there fewer than two states or provinces? (This is the “conditional” part.) If so, we don’t need to see the (blank) space where the information would appear. And we can use the same formula in the section footer, too.
Conditional formatting done
This is a very useful point, which has application on a wider scale within the section organization. We can suppress sections, but make exceptions, so as to show something when it’s important; we can also have a section show, but suppress it when there’s nothing to show or the info is unneeded or redundant. The hard part is writing the formula, but if one can phrase the condition clearly in words, writing the formula is easier. Sketching the overall structure can sometimes help too. (May sound kind of old-fashioned, but it’s tried-and-true.)

How to Use Table Joins in Microsoft Access

The use of table relationships in Access allows tables to cooperate in the use of data, but table joins, while they look similar, serve a different purpose. The relationships, which allow coordination while organizing data, nevertheless don’t directly affect, say, the results of a query. Joins do.

A table join in a query allows for a filter effect. The thinking is that a query is a question one asks of the database. But redundant answers are no use, and a waste of space. So when we pull data from more than one table, which is frequently the case, we need to make sure we don’t get repeat answers, or every possible permutation of the answers/data.

No joins

When creating the query, we choose the tables (assuming we need more than one, which is normal) and insert them.

No join repeat data

If we run the query without a join, we get every possible combination of the field data for an answer…which often leads to repeat results.

Join

So we decide which field(s) to use in the join (this is often dictated by the nature of the database), and specify the nature of the join—usually an “inner” join, which shows only results both tables share. We create the join the same way as we create a relationship, by dragging from a field in one table to its counterpart in the other—the main difference to the program is, the join only gets “activated” when the query is run, saving time and memory usage. There’s also the fact that some joins are only useful at certain points in the database’s operation, so creating a permanent relationship outside the query is unnecessary.

Result with join

When we run the query with the join, to filter out repeats, the number of answers becomes more reasonable. In many cases, we can use a key field as one of the query result fields, which certainly cuts down on repeats, as a key field has the same effect in a query as it has in a database in general—unique identification of a record. But the important point is that table joins will “insist” on reducing if not eliminating repeats, and will do so even more with a key field involved.

It’s been my experience that very few business database-related queries will have no repeat results at all—the data are such that this seems almost impossible. But if we can eliminate 95+% of repeat data in a query, only the most massive sets of results will have a significant problem with them.

Using the Cost Tables in Microsoft Project

The Cost Tables feature in Microsoft Project reflects a point which is particular to this program—it is, basically, time-sensitive. Since a business project takes a minimum, usually, of several weeks to run, the things that happen in a project must take time into account. And, no pun intended on this phrase, accounting for costs which might change during the run of the project is therefore an integral part of the resource data we can (and often must) enter.

Fortunately, the first part is pretty easy. When creating a resource, one can (in the Resource Sheet) start by entering standard and overtime pay rates for a work resource (usually a person or group of people).

Resource Sheet

If, however, one knows the resource’s pay rate will change at some time during the course of the project, opening the Resource Information dialog box and going to the Costs tab gets us to the cost tables. (Just double-click the resource to get in.)

Cost Tables 1

There are no less than five Cost Tables in any resource’s info box. We usually look at just the first, as each contains twenty-five rows, so there are 125 slots for cost table data. If there are going to be that many changes of rate, it’ll be WAY out of the ordinary.

So in Cost Table A, the default, we enter the initial standard and overtime rates, if they aren’t already in the Resource Sheet. The assumption is, these rates are in effect at the start of the project.

Cost Tables 2

In the next row, we enter the date at which the rate will change, up or down, and then the changes themselves. We can even enter a percentage change (+15%, say), instead of a straight number, if someone doesn’t actually know the amount but does know how far up or down it should go. And repeat as needed.

The project manager has to be conscientious about entering the info ASAP, preferably before the project start date, to make sure the budget numbers stay current. But the actual entry of the data is pretty straightforward, thanks to the cost tables.

 

Using the Three Normal Forms in Microsoft Access

One of the more abstruse points of procedure in Access involves the three so-called normal forms. What, exactly, are they? If you think “guidelines”, or “protocols”, you’d be in the ballpark.

When building a database, especially a relational database, there are some “streamlinings” which allow it to function more efficiently. Knowing the how is important, but the why can be too. “Why” is what I’d like to go over here.

The first of the normal forms is basically this: In any field, in any record of the database, there should be one and only one piece of information. The simplest examples are things like phone numbers.

First Normal Forms Wrong

Since today computers can be hooked up to, and can dial, phones, if two numbers are in one field, the computer might not be able to tell which one to dial, or it might think they were one long number. And that would mess the process up.

First Normal Forms Right

It also allows easier querying, filtering, and other kinds of analysis of data. Another common example is entering addresses: If the street address, city, state, and Zip code are all in one field, sorting or filtering by any of those would be much more difficult. But give each one a separate field, and it’s quite straightforward. This is part of what’s known as normalization. If the data are divided into the smallest pieces which still have meaning, you have raw ingredients in the kitchen—you can do more with those than, say, premixed pancake batter, which is pretty much only good for pancakes.

The second of the normal forms is about key fields—the ones that act as unique IDs for each record, in tables which need them. (The usual example here is a Social Security number, or employee ID number, or SKU number for merchandise.)

Second Normal Forms

It is possible for a table to have two, or more, key fields per record, and occasionally this is necessary (as in a table of merchandise orders). But the uniqueness should then depend on the entire set of key fields (a composite key), not just one, because otherwise it will be possible to have duplicate data in the table, and this is a major no-no. (This is basically the second normal form.) Duplicate info wastes space, and can confuse query results. If any of the fields’ data don’t depend on the whole key (multiple fields), they should be in another table.

Third NF Redundant

The third of the normal forms is a little more complicated to explain, but the point is simple: Redundant information should be in a separate table. One situation in the real world has to do with employee databases including health plan info. There’ll be a number or letter indicating which plan someone is on, and then in the next field, the name of the plan. But if these are the same each time, why not use just the letter in the employee table, and put the detailed information in another table? It reduces the amount of typing someone has to do, and simplifies the table in question.

Third NF Right

It also offers a hidden benefit. If one puts the letters for the plans in another table as the primary key, and has plan names or other data in other fields in that same table, changing data in the plan table will affect everyone who uses the plan letters at once. This means that the subset of data regarding plan details is centralized, and can easily be changed for everyone with a minimum of work. So a sort of corollary to the third normal form is, one kind of data per table (if redundant data is a second kind per table, this falls into place pretty neatly).

One piece of info per field; one key field per table where possible, and use all the key fields for ID if not; split off redundant data to another table, which also offers easier changes to some kinds of data. Those are the essentials of the normal forms. There’re other data models, and other operating ideas, but since Access is so commonly used, understanding how to make it run better is good.

QlikView Keyboard Shortcuts

SkillForge SkillSheet

QlikView Keyboard Shortcuts

Click here to download the PDF version.

 

File Menu Command Shortcuts

Press

For

Ctrl+N Equivalent to New on the File menu.
Ctrl+O Equivalent to Open… on the File menu.
Ctrl+Shift+O Equivalent to Open in Server… on the File menu.
Ctrl+S Equivalent to Save on the File menu.
F12 Equivalent to Save As… on the File menu.
Ctrl+P Equivalent to Print… on the File menu.
Ctrl+Shift+P Equivalent to Print As PDF… on the File menu.
Ctrl+E Equivalent to Edit Script… on the File menu.
Ctrl+R Equivalent to Reload on the File menu.
Ctrl+Shift+R Equivalent to Partial Reload on the File menu.
Ctrl+T Equivalent to Table Viewer… on the File menu.

 

Edit Menu Command Shortcuts

Press

For

Ctrl+Z Equivalent to Undo Layout Change on the Edit menu.
Ctrl+Y Equivalent to Redo Layout Change on the Edit menu.
Ctrl+X Equivalent to Cut on the Edit menu.
Ctrl+C Equivalent to Copy on the Edit menu.
Ctrl+V Equivalent to Paste on the Edit menu.
Del Equivalent to Remove on the Edit menu.
Ctrl+A Equivalent to Activate All on the Edit menu.
Ctrl+F Equivalent to Search on the Edit menu.
Ctrl+Shift+F Equivalent to Advanced Search on the Edit menu.

 

View Menu Command Shortcuts

Press…

For…

Ctrl+Q Equivalent to Current Selections… on the View menu.
Ctrl+G Toggles layout design grid on or off.

 

Selections Menu Commands Shortcuts

Press…

For…

Shift+Left arrow Equivalent to Back on the Selections menu.
Shift+Right arrow Equivalent to Forward on the Selections menu.
Ctrl+Shift+L Equivalent to Lock on the Selections menu (locks all selections).
Ctrl+Shift+U Equivalent to Unlock on the Selections menu (unlocks all selections).
Ctrl+Shift+D Equivalent to Clear on the Selections menu (clears all selections).

 

Settings Menu Command Shortcuts

Press…

For…

Ctrl+Alt+U Equivalent to User Preferences… on the Settings menu.
Ctrl+Alt+D Equivalent to Document Properties… on the Settings menu.
Ctrl+Alt+S Equivalent to Sheet Properties… on the Settings menu.
Ctrl+Alt+V Equivalent to Variable Overview… on the Settings menu.
Ctrl+Alt+E Equivalent to Expression Overview… on the Settings menu

 

Bookmarks Menu Command Shortcuts

Press…

For…

Ctrl+B Equivalent to Add Bookmark… on the Bookmarks menu.
Ctrl+Shift+B Equivalent to More… on the Bookmarks menu.

 

Tools Menu Command Shortcuts

Press…

For…

Ctrl+M Equivalent to Edit Module… on the Tools menu.
Ctrl+Alt+A Equivalent to Alerts… on the Tools menu.

 

Object Menu Command Shortcuts

(List box, Statistics box and Open Multi Box)

Press…

For…

Ctrl+Shift+L Equivalent to Lock on the Object menu (locks selections in active object).
Ctrl+Shift+U Equivalent to Unlock on the Object menu (unlocks selections in active object).
Ctrl+Shift+D Equivalent to Clear on the Object menu (clears selections in active object).
Alt+Enter Equivalent to Properties… on the Object menu (opens the Properties dialog of an active object).

 

Script Keyboard Shortcuts

Press…

For…

Ctrl+G Go to line number in the script.
Ctrl+K,C Comment lines in script.
Ctrl+K,U Uncomment lines in script.
Ctrl+Q,T,A Add tab in script.
Ctrl+Q,T,P Promote active tab.
Ctrl+Q,T,D Demote active tab.
Ctrl+Q,T,N Rename active tab.
Ctrl+Q,T,R Remove active tab.
Ctrl+Q,Q Creates an autogenerated script.
Ctrl+Q,U,I Opens Unicode input utility.
Ctrl+Q,J,P,G Creates a script for reading attributes from jpeg files.
Ctrl+Q,M,P,3 Creates a script for reading attributes from mp3 files.
Ctrl+Q,W,M,A Creates a script for reading attributes from wma files.

 

F Key Keyboard Shortcuts

Press…

For…

F1 Activates context sensitive help.
F3 Enters search mode if a searchable object is activated.
F6 Activates the immediate left sheet tab of the currently active tab.
F7 Activates the immediate right sheet tab of the currently active tab.
F12 Equivalent to Save As… on the File menu.
Ctrl+F6 Activates the leftmost sheet tab.
Ctrl+F7 Activates the rightmost sheet tab.

 

How to Import and Export Data in Access

The good news is, the import and export processes in Access—and in general—are two sides of the same coin. And there really isn’t any bad news, other than making sure the data are set up in a way that Access can understand—namely, something row-and-column-ish when importing. There’s even an External Data tab in the ribbon, so it’s just a matter of knowing what kind of data you’re bringing in, or what format the recipient needs if you’re exporting.

Import Start

For an import, other than making sure you know where the data are, simply go to the External Data tab, and look in the Import and Link group. We’ll use a text document here.

Import Box

We can choose whether this will start a new table, be appended to an existing table, or link to the data source (which allows updates, but has some limitations).

We also have to specify where the document is. We can do this in any order, but we have to do both.

If (as in this case) we want to append the data to an existing table, we need to tell the dialog box which one. And, though the box doesn’t say, we have to make sure the incoming info is set up to fit in the same “slots” (fields) as the existing info. (Opening the file and checking in advance is a good idea here.)

Once this is done, we go through the File Import wizard, which lets us see how the incoming data will work.

Import Step 1

We choose “Delimited” or “Fixed Length” according to how the pieces of info are marked off (a delimiter is a marker telling us where things start and stop).

Import Step 2

We then indicate what kind of delimiter is used (commas, tabs, and semicolons are among the more common ones); and let the program do the rest.

Import Step 3

Exporting is even simpler. We select (but don’t have to open) a table, query, or other object, go to the External Data tab, and in the Export group, click the type of receiving file we need to create, and specify location and file name (the program will default to the object name).

Export Box

So, “Where are you getting the data?” and “Where are you putting the data?” are the only two questions we have to be specific about. Access pretty much handles it from there.

Using Photoshop — More Tips, Tricks, and Hints

Figured I’d mention a few other techniques which might be of use. Photoshop is almost a gold mine of cool tip after cool tip; I’d never deny it takes some practice to become comfortable with the program, so here’re some more.

Tip 4. Always be aware of the Layers panel if the image has more than one layer (not all images do). Particularly, which layer is selected. Whatever kind of edit you’re about to do, with whichever kind of tool, the layer selected is the one that’ll show the change. If it’s the “wrong” layer, you can undo, but it’s irritating. Better to know in advance, and choose the right layer up front.

Linking tip

Tip 4a. Once you’ve built a “structure” involving more than one layer, and want to keep all the pieces in the same places relative to each other, select the layers (Control-click on the PC, Cmd-click on the Mac—these let you select non-adjacent layers), and link them (using the panel menu at top right).

Fill tip

Tip 5. When adding color to an image, using the Edit > Fill command/dialog gives you easy access to both the blending modes (controlling how the color interacts with what’s already there) and the opacity (how solid the color you’re pouring will be). The blending modes could be a seminar on their own, but basically you can use them to produce all kinds of neat effects. And even just using the opacity control lets you add washes or “glazes” of color, if you need subtle changes in color. (Da Vinci would be delighted.)

Navigator tip

Tip 6. Don’t forget the Navigator panel. You can use the Hand tool and the scroll bars to get around in the file if you like, but the Navigator shows where you are and how close you’ve zoomed, so even if you prefer some other means of navigating, the panel is a good situation display.

Using Photoshop — Tips, Tricks, and Hints

Photoshop has been around for a while, and it’s a fairly sophisticated program, but there are some tips one can learn to work a little more efficiently no matter what your level of expertise.

1. A lot of the work we do depends on selection. Refining selections takes time; actually doing things with the selected area usually takes less. But when done working with a selection, don’t forget to DE-select it! Kind of like turning the oven off when finished cooking. (Ctrl- or Cmd-D is quickest.) Otherwise you might end up doing the right thing to the wrong part of the image.

Photoshop Channels

1a. If you have any idea you might be doing further work on the same piece of the image, save the selection (Select menu>Save Selection). You can store about fifty selections in there as selection channels (usually known as alpha channels), so there’s no need to skimp unless there’ll be a really big number of selected pieces that need lots of changes.

Photoshop Inverse

2. If you need to select all of something in an image, say a person, but the selection would take time, see if you can select the background or surroundings more easily—then go to Select>Inverse. Select everything except the hard part, then invert the selection. Voila. It isn’t always possible, but if the background of the image is relatively plain, give it a try.

Photoshop Hand

3. This is a really simple one, but I’ve had at least a few people tell me it was helpful: When finished working on something for the moment, switch to the Hand tool. The Photoshop equivalent of putting a stick shift in neutral, it means you won’t accidentally move something, or paint something, or otherwise edit something if you don’t mean to. When a Photoshop file is open, you have to have a tool selected, so if it’s a tool which won’t do any editing, it’s a safe choice when you want to pause. The Hand tool simply moves the image around in the frame if you’re zoomed in, but can’t edit. So it’s much harder to mess up the image with it selected.

Microsoft Project Keyboard Shortcuts

SkillForge SkillSheet

Microsoft Project 2010/13/16 Keyboard Shortcuts

Click here to download the PDF version.

 

Navigate Views and Windows

In order to…

Press

Activate the Control menu ALT+SPACEBAR
Activate the entry bar to edit text in a field F2
Activate the menu bar F10 or ALT
Activate the project control menu ALT+HYPHEN
Activate the split bar SHIFT+F6
Close the program window ALT+F4
Display all filtered tasks or all filtered resources F3
Display the Field Settings dialog box ALT+F3
Open a new window SHIFT+F11
Reduce a selection to a single field SHIFT+BACKSPACE
Reset sort order to ID order and turn off grouping SHIFT+F3
Select a drawing object F6
Display task information SHIFT+F2
Display resource information SHIFT+F2
Display assignment information SHIFT+F2
Turn on or off the Add To Selection mode SHIFT+F8
Turn on or off Auto Calculate CTRL+F9
Turn on or off the Extend Selection mode F8
Move left, right, up, or down to view different pages in the Print Preview window ALT+Arrow keys

 

Outline a Project

In order to…

Press

Hide subtasks ALT+SHIFT+HYPHEN or ALT+SHIFT+MINUS SIGN (minus sign on the numeric keypad)
Indent the selected task ALT+SHIFT+RIGHT ARROW
Show subtasks ALT+SHIFT+ = or ALT+SHIFT+PLUS SIGN (plus sign on the numeric keypad)
Show all tasks ALT+SHIFT+* (asterisk on the numeric keypad)
Outdent the selected task ALT+SHIFT+LEFT ARROW

 

Move in a view

In order to…

Press

Move to the beginning of a project (timescale) ALT+HOME
Move to the end of a project (timescale) ALT+END
Move the timescale left ALT+LEFT ARROW
Move the timescale right ALT+RIGHT ARROW
Move to the first field in a row HOME or CTRL+LEFT ARROW
Move to the first row CTRL+UP ARROW
Move to the first field of the first row CTRL+HOME
Move to the last field in a row END or CTRL+RIGHT ARROW
Move to the last field of the last row CTRL+END
Move to the last row CTRL+DOWN ARROW

 

Use a timescale

In order to…

Press

Move the timescale left one page ALT+PAGE UP
Move the timescale right one page ALT+PAGE DOWN
Move the timescale to beginning of the project ALT+HOME
Move the timescale to end of the project ALT+END
Scroll the timescale left ALT+LEFT ARROW
Scroll the timescale right ALT+RIGHT ARROW
Show smaller time units CTRL+ / (slash on the numeric keypad)
Show larger time units CTRL+* (asterisk on the numeric keypad)

 

Move in the Side Pane

In order to…

Press

Move focus between the side pane and the view on the right side CTRL+TAB or CTRL+SHIFT+TAB
Select different controls in the side pane if the focus is in the side pane TAB
Select or clear check boxes and option buttons if focus is in the side pane SPACEBAR

 

Creating Themes in Microsoft PowerPoint

When preparing business presentations using PowerPoint, one needs more than usual to make sure the show has a coherent, consistent appearance; themes are an easy way to do this. Because the brain is wired to start understanding and working with pictures from infancy, keeping the graphic elements (backgrounds, fonts, colors) uniform in a presentation allows the audience to pay attention to and absorb the actual content, without distraction from other things.

Themes dropdown

To use themes, one merely needs to open a presentation and, on the Design tab, click the theme one wants to use. There are normally a few dozen installed with the program.

But it’s fairly easy to create a theme, and there are several reasons, most notably product branding (a company’s official set of fonts, colors, logo(s), etc.) which can prompt the need.

On the right side of the themes collection are the dropdowns for Color, Fonts, and Effects. We can’t edit the last group, but we can use it. Fonts and Colors, though, we can go in and set up in whichever combination we like.

Fonts dropdown

In Fonts, we can pick any pair we like—the same for title and body, or different, as the need dictates. Usually we name the combination distinctly so we recognize it or can use it for something specific.

Colors dropdown

As for Colors, we have to select several, since colors get more use than fonts regardless of the graphic elements we start with, but we still need to keep something like a coherent color scheme. Try thinking of it like painting a house: All the colors need to work together. (If your company has product branding standards, just go with the colors in there and you’re set.)

And when you get to Effects, about the only downside is we can’t easily create them and store them as part of the theme. But we can use the built-in ones.

Save Theme

Once all this is done, click the More dropdown button on the right side of the Themes list, slide down to click Save Current Theme, and give the new one a name and location. You can use Browse for Themes right above it, later, if you need to find it again.

Best of all, anyone else can use the themes too. And if you (or your company) has prepped a theme, they can push it out on your network in a matter of minutes, so everyone can use the same theme(s) and be as close to 100% consistent as possible.

Using Specified-Order Grouping in Crystal Reports

As we get into some of the more subtle ways to use grouping in Crystal Reports, we sometimes find that either groups of groups, or arranging groups in a hierarchy, can improve the usefulness of the report. One of the overarching keys to a good report is that it be organized so as to make it easier to understand, and therefore more useful. This is even more important given the size of the batches of data we work with now.

A good example would be customer sales “levels”—gold, silver, etc. based on how much a customer spent with a company.

Basic Report

We can start by creating a report with the necessary data, in this case the company name, country, and sales amount. (In a real-world situation, one would include whatever data the grouping should be based on.)

Insert Grouping

Then, we can insert a group (Insert–>Group), but we use the In Specified Order capability to create arbitrary-but-logical groups based on (in this case) sales amounts. This is where we can customize the grouping using any reasonable criterion we want. The example here is sales levels, but any data from the database can be used. It can even be categories of items sold rather than quantities, just so long as there’s some way to tell one group from the other.

Set Up Group Names

We set up names for the groups using the Named Group box, and limits (in this case monetary) in the Define Named Group box which pops up for the purpose.

Other Customers

We also need to make sure we set up a space for the customers which don’t fall into one of the three “main” groups. (“Occasional”, “Other”, etc., are fine.)

Grouped Report

Once we rearrange the labeling a little for convenience, and maybe sort the customers by name, we’ve got a decent grouping set up. One thing to consider about this (and many other kinds of business documents) is to try and have a good idea of the final layout or structure in advance, if possible. It’s a lot easier if you know where you’re going, and you have a decent map.

Conditionally Formatting Multiple Sections in Crystal Reports

Alright, I admit it—this item on conditionally formatting sections has a little nostalgia for me, but it’s useful nostalgia. If you run a web search for “greenbar paper” you’ll see where this is going. The reason is a sound one, though. The horizontal stripes, sometimes called “banded rows”, let the user see what’s in which row of a report, or other printout, more easily.

Insert section below

The first step, after setting up the report and laying out the data, is to create a Details “b” section in the report—with no data in it. Right-click the section name, and “Insert Section Below”.

Borderless box

The only thing we want to do there is create a horizontal box, borderless, of any light fill color (since it’s going behind some of our data) in the same place as the data will be. Take your time when drawing, as the box should be the same height as the section, or just a hair less.

Conditionally formatting

The slightly more complex part comes with the conditionally formatting portion of the recipe. We want this color background to show up behind every other record. Luckily, the formula is simple. We go into the Section Expert, click the X+2 button for “Suppress (No Drill-Down)”, thus into the Formula Editor for the section, and type RecordNumber Mod 2=0. Basically, this function looks for even-numbered records/rows by doing the division, and if the remainder is 0, the color details section is suppressed. So…on, off, on, off. The conditionally part being, even- versus odd-numbered rows.

Underlay following sections

Finally, still in the Section Expert, we go to the Details A section and check Underlay Following Sections, so as to get them to overlap (or underlap, as the case may be).

Completed work

Granted, we don’t usually print much on 15-inch wide pinfeed carriage paper in high-speed dot-matrix printers anymore, but with the density of content in some reports, stemming from smaller print sizes and such, you never know when this trick might come in handy.

Ah, nostalgia. “Everything old is new again.” And still useful.  😉

Creating Report Alerts in Crystal Reports

In Crystal Reports, a feature called Report Alerts has become more important the last few years. It has to do with the fact that there are at least occasional exceptions to many kinds of report data. “Problems or conditions outside the norm” is a good way to phrase it, and when those happen, we often need to know about them ASAP. Hence Report Alerts.

Creating one isn’t hard—we just have to know three things going in. [a] What to call it, [b] what condition triggers it, [c] what message to show when it kicks in—this last is optional but strongly recommended.

Report Alerts box

Looking for a low order count is a good example. Having set up a field to count orders per company, we can now set up an alert to see who placed less than, say, thirty orders for the year in question. In the Report menu, we slide to Alerts, then click Create or Modify Alerts.

Create Report Alerts

Next thing is to set up the actual alert. We click New on the right side of the box, give the alert a name, and write a user message—some people skip this, but having at least a brief message is important, as you never know who’ll be using the report.

Report Alerts condition

Then, most important, set up the condition which triggers this alert. (Back to our old friend, the Formula Editor.) In our case, we’re looking for suppliers who placed fewer than thirty orders this year, so the formula reflects this.

Refresh for Data

When done, we OK or Close out of all the boxes, save (desirable), and refresh (F5).  We should see the alert kick in, with the option to let us know which records triggered it.

Report Alerts result

Here we can see the number of records has narrowed down to only eighteen pages’ worth, rather than fifty-nine, and at least one supplier’s data confirms only twenty-three orders (less than thirty). So the alert lets you know something’s different, then allows you to see what the changes are.

As is often the case, the hard part is getting the details clarified. Someone said, “Computers don’t do vague.” It’s a point to bear in mind with Report Alerts.

Creating and Using Parameter Fields in Crystal Reports

When setting up layouts in Crystal Reports, the parameter field can be a tremendously helpful feature. As with a parameter query in Microsoft Access, it gives the report a significant degree of flexibility—the report’s basic structure and purpose are the same, but the ability to change, say, the range of dates involved, means a single report can be used to answer a larger number of related questions with reasonable efficiency. Since the user can input anything the field allows, parameter fields simply need to be set up for the necessary data type.

New Parameter Field

Once you have a file open, you start by going to the Field Explorer, right-clicking the Parameter Fields item, and clicking New.

Name Type Static Dynamic

The dialog box which opens will ask for the name, type (of data), and whether the field should be dynamic or static—meaning whether it should get choices from the database, or from a separate list you load in.

The critical thing is, if you want to hook the type to a value from somewhere, regardless, the type of the field and the type of the value have to be the same—number to number, string (text) to string, and so on, for the same reason that two railroad cars have to ride on the same gauge rails and have the couplers at the same height. If they don’t, they can’t work together.

Prompt Text

Once you’ve told the field what kind of data, there’s one other thing you should do—enter a prompt text or phrase. Most times a user will know what the input should be about, but setting up a prompt is still a good idea, since even someone who uses the report may not use it for a while.

Design View Preview

Parameter fields can even be used in or next to text objects, as labels which are specific to the report you’re running. You can put them almost anywhere you need the viewer to see what the report is reporting on. So even your labeling is flexible.

Creating a Cross-Reference in Microsoft Word

The process of setting up a cross-reference in Word, frankly, can take a little time to get comfortable with. So don’t worry if you need a couple run-throughs.

The recipe goes like this:

Bookmark

First, create a bookmark (there are a couple other things you can use, but a bookmark is simplest) wherever it is you want the reference to point. (Insert tab—>Links—>Bookmark) THIS IS ACTUALLY THE KEY STEP. Give the bookmark a name that makes sense for what it’s marking—what you’re pointing to. This helps with the other part.

Text

Next, go to the part of the text you want the cross-reference from (that is, where it starts). Type the appropriate text (e.g., “See the reference to so-and-so on page”, plus a space, and any other punctuation as needed). If you visualize the way such a thing normally looks, you can see all it’s missing is the page number.

Cross-reference

With the insertion point at the spot right after the space, go to References—>Captions—>Cross-reference. Although there are other items you can use in this situation, the thing most people insert is the reference to the page number which the bookmark is on.

And that’s it. Again, it is possible to use this feature for a few other things, but the idea of a cross-reference in most cases is to refer a point or item in the text to another item somewhere else.

The benefit is, if you reformat the text, add or remove some, or otherwise make changes, you don’t have to go through your document retyping up the wazoo as far as redoing cross-referenced items. Instead (ALERT—BONUS FEATURE AHEAD!), simply press Control-A (to select all the text), then the F9 key, which will refresh everything selected in the way of tables, references, etc. (Hint, hint.) You may have to specify the whole document in a dialog box. Oh well. And if you inserted any of those other things, you just made sure they’re updated, too.

Creating Cost Resources in Microsoft Project

There’s a quirky thing about Microsoft Project, having to do with resources—more specifically, Cost resources. The other two types, Work and Material, are pretty easy to understand and use, but Cost takes a little bit of extra work to make do its thing.

A Work resource is a person or a piece of equipment, someone or something that stays around. A Material resource is a consumable, such as reams of paper or toner cartridges—something that gets used up. Even gasoline might be looked at this way. But Cost resources, at first, seem a little more indescribable. And where to put the actual cost?…

The basic definition, though, is not too hard once you understand it. A Cost resource is an intangible, like an airfare, a hotel rental, or a software download. It’s something you use, but can’t exactly hold in your hand.

Cost Resource

So how do we use it? First, we have to create the resource, say, Advertising Money. We can’t insert any numbers in the Resource Sheet, because—and here’s the key thing—a Cost resource has to be attached to a task in order to enter a number. In other words, you have to specify where the money is going.

Assigning Resource

Then, in the Task Information dialog box, we set up the assignment of the resource, and there we can enter the amount. Since the program tracks all the costs, it’s not really too big a deal where we enter the numbers, as long as we enter them.

Total Cost

Later, when we need to know what all the Cost resource numbers add up to, the information is pretty easy to get. One simple way to check it is to add the Cost field to the Gantt chart. The Project Summary Task gives us the overall amount in that column.

Once you understand it, dealing with it is routine. It’s a good thing, because it gives the project manager a means of keeping better track of the money. Never hurts….

Creating Calculated Fields in Microsoft Access

When setting up reports in Access, it is sometimes necessary to learn something which wasn’t in the original data, whether calculated, derived, or otherwise figured out from the existing information. It’s fairly easy to do, though there are a few steps involved.

After determining the requirement—a sales tax or a shipping fee, and what calculation is needed to find it—we create the field for the calculation.

Text box

With the report open in Design view, clicking the Design tab under Report Design Tools gives us the Controls group. There, we click the Text Box tool and click or drag where we want the text box (which shows field data). This normally creates a label too, which one can use or delete as desired.

Calculated field

Once the text box is in place, we leave it selected, open the Property Sheet, and click the Data tab. One of the Data properties is Control Source, where we can tell the text box how to get its data. On the right of this property, we bring up the Expression Builder with the Build button […].

Expression builder

In the Expression Builder itself, we can either double-click the fields we need for data or type in what we want manually. We also add the other items (operators, additional figures, and so on) to perform the calculations—essentially, we build a formula much like those in Excel. (Don’t need the equals sign at the front, though.) This makes it a calculated field.

Field and Label

Once we OK out of the box, we can fine-tune the position and size of the text box, and either move the label or create one of our own so the users of the report will know what it’s showing. (This is more important than some people think—in a business report it’s very important not to make assumptions about who’s going to see it, and no guesswork should be involved in understanding it.)

The term “calculated field” seems kind of unofficial—one usually sees it called an “unbound field”, as it isn’t directly connected to any of the actual database data. But since we often create such fields to derive info from other info, and it’s often mathematical, saying calculated is at least logical.