Using Fields in Text Objects in Crystal Reports

Fields, in Crystal Reports, are the way we bring data from a source into a report. Sometimes, we need to combine the data with text even more directly than placing them side by side.

Data source

The technique we can use is similar to creating a mail merge with fields in a couple of other programs. First, we pull up the basic data of a report. Choose the data source and table(s), deal with links between them if necessary, and OK out of the Database Expert.

Text object for fields

Next, create the text object. One thing to be careful of is making the text object large enough to accommodate the “sizes” of the data in the fields. For addresses, it’s usually not a problem, but because descriptions and bios can be lengthy, previewing the data can help. (Right-click the fields in Design view and selecting Browse Field Data.)

When we create the text within the object, another thing to be careful about is proper punctuation and spacing. Just as in word processing, lack of attention to this can make the report look shabby and unprofessional.

Fields placed

Dragging the fields into the text object is not hard at all; it simply requires a little practice to make sure we let go the mouse button at just the right spot. Make sure of where your pointer is. If you miss, just undo or delete the field and try again.

Result

Don’t be surprised if you need to go back and forth between Design and Preview a few times to do some fine-tuning of the fields/text object situation. This is quite normal. In addition, remember that the fields within the text object(s) can and should be formatted to match the text around them. (Selecting by dragging across them, or double-clicking them, will do it.) Otherwise, again, the final result won’t be professional-looking. Mismatches are visually inconsistent, and can actually make the document harder to read.

 

How to Create an Action in Adobe Photoshop

An Action is the Photoshop equivalent of a macro in programs like Word and Access. It allows consistent repetition of a sequence of steps. So it’s easier to do the same thing in different files, if one wants. As usual, the thing which helps most is rehearsal—practice a little to avoid frustration when you record the real thing.

(I’ll apply a layer effect here, the Drop Shadow, for demonstration.)

Record action

After opening your file and setting up whatever elements we want to affect, we bring up the Actions panel and click the New Action button.

Action dialog

We enter a name, a keyboard shortcut if desired, and a color (optional but sometimes helpful). When we OK out of that box, we record any mouse clicks or keystrokes from there on.

Action steps

So my double-click of the text layer, making various adjustments to the Drop Shadow after activating, and subsequent OK are all written down as performed. The recorder waits till I OK out to jot down the final numbers. When finished, I click the Stop Recording button (left of Record), and the action is ready.

Use action

To apply the same attributes to another item (not necessarily text), I select the layer in question, click the action, and hit the Play button at the bottom of the Actions panel. About the only hard rule on using layer-based actions like this is, they can’t be applied to a Background layer. But that’s a particular of the action I’m recording. A non-layer-specific action can work pretty much anywhere in a file.

One also wants to remember to select or otherwise tell the action what it’s going to work on.

The not-immediately-obvious advantages are:

Action changes

First, in the Actions panel, any step which involves a dialog box has a clickable icon to make the action bring the box(es) up during the run, to allow user changes. Which means they can be flexible.

Action folders

Second, actions can go in folders, and we can save them as separate documents. They can then be opened in other files, or sent to other PShop users, or kept as backups. Portability!

And third, though it’s not a common use for actions, they can apply company colors or fonts to a document. Which means actions can be helpful in maintaining product branding standards, if needed.

How to Use Quick Mask Mode in Photoshop

Of all the “beyond-the-basics” tools in Photoshop, the Quick Mask feature is probably one of the most nitpicky to understand. But it can provide the user with a fairly easy method of doing something complex, namely, making selections. So it’s worth the time.

Selection

It’s normally easiest to start by making a partial selection of the area. Using a conventional method such as clicking with the Magic Wand will work fine. Then, going over to the Tools panel/Toolbox, we simply click the button near the bottom for Quick Mask mode.

Quick Mask button

Now, the trick here is knowing what Quick Mask mode does, and how. It turns the selection into a mask—that is, a colored area which tells us what parts we’ve selected and which we haven’t. The colored part, which is red by default, is NOT selected. Anything not covered by the red overlay IS selected. And the mask (for non-selected) is partly transparent so we can edit the selection and see what we’re doing.

Quick Mask brush

The next point is how to edit the mask. Once we’re in QM mode, we switch to the Brush tool. The brush should have a 100% hardness, and be about half the size of the areas we’re selecting (give or take).

Paint in QM

The critical thing now is which paint color we use. We should set the colors to default (black/white), and remember that the FOREGROUND color is the one the tool is looking at. If we paint with black, we MASK (or DE-select). If we paint with white (we can use the Switch Foreground/Background button), we UNMASK (or SELECT). A quick way to remember which is which would be “Black Blocks” (i.e. masks or de-selects).

Quick Mask off

When we’ve made the edits we want, we go back to the Tools panel, click the Quick Mask mode button again, and return to Standard mode. We again see the selection marquee, and can proceed from there.

The main question most people ask, if they know the regular selection tools, is Why mess with QM mode? The main answer is that selection tools can be a little fickle. When we click with the Magic Wand, especially, the Tolerance can affect what we select. Quick Mask mode and the painting tools are a little slower, but more precise. Especially if we zoom in to work.

Using the Gradients in Photoshop—Additional Info

In an earlier post, I went through the basics of creating gradients in Photoshop. In this one, I want to mention a couple more details which might be helpful in their use.

There are five patterns, or appearances, which gradients can follow, shown to the right of the Gradient Editor in the Options panel. The one we use most, Linear, makes the color pattern appear as bars, or bands, in the selected area. But the others are potentially quite useful, and not just for decor. One selects the gradient and pattern, and drags from point A to point B, just as with Linear.

Radial gradients

The Radial gradient is a “sunburst”, the leftmost color in the center and moving outward in rings. This has been used with several real-life concepts, e.g. distances from the center of a city for travel purposes.

Angle gradients

The Angle gradient uses the direction of the drag to create a “wheel” effect. The starting color is on one side of the drag line, shading around in a circle to the ending color on the other side of the same line. This can create an object-lit-on-one-side effect by having black and white as the starting and ending colors. It’s often employed as sheer decoration. Of all the gradients, some people consider it the prettiest.

Reflected gradients

The Reflected gradient can do something interesting: Using metallic colors with it creates the illusion of specular, or pinpoint, highlights on metal. The “reflected” part indicates that the drag start point becomes a “mirror”. It becomes two “reversed” gradients, in bands centering on the start of the drag, a kind of “double linear” gradient. (One must therefore allow about twice the width of the drag for full visibility.)

Diamond gradients

The Diamond gradient makes the direction of the drag become one of the four points of a diamond version of the Radial gradient. Distance of drag works similarly as Linear. The start and end of the drag mark the extent of the color, and continue the last color out as far as possible.

Reversed gradients

One other element which occasionally comes into play is the Reverse checkbox in the Options. It reverses the direction of the gradients—start color and end color will flip around.

And don’t forget there’s an Opacity control in the Options, too.

How to Create an Adobe Photoshop Gradient

Setting up a Photoshop gradient works a little differently from its cousin, Illustrator. The concept is the same, but the tool involved, and how we create and fine-tune a gradient, changes.

Photoshop gradient options

The first thing to do is select the Gradient tool in the Tools panel/Toolbox. Looking up at the Options panel, we find the dropdown with the choice of gradients on the left. Usually there are a dozen to sixteen presets; clicking one will select it. But if we want to create one, we can click the gradient already visible to bring up the Gradient Editor.

Photoshop gradient editor

The next thing we frequently do is make changes to whichever gradient we see. The house shapes under the horizontal strip (the gradient ramp) show what colors are in the gradient, and we can add more by clicking where we want them. We can also move them left and right as needed. A couple of dozen seems to be the practical limit. Double-clicking any of these (color stops) brings up the color picker, which lets us change the color as we like. The diamond shapes between the color stops tell us where the halfway point is for any two colors, and can be moved as well.

The upside-down house shapes above the Photoshop gradient ramp (no, they’re not from Australia!) are transparency stops. One can therefore make parts of the gradient fade out as much as one likes. Clicking once on either a color stop or a transparency stop makes its “roof” black. Then it’s selected and allows the controls at the bottom of the box to modify it. Even clicking the diamond shapes allows us to use the percentage controls at bottom to position them.

Photoshop gradient name

Finally, when we’ve got the arrangement we like, we can type a name in the space at center, click New, and go ahead and use it. From here, it’s straightforward—select the area we want to fill with the gradient, click the Photoshop Gradient tool, and drag in the space. Length of drag tells the program how short or long the color transition should be. Direction of the drag gives the angle. And voilà. 😊

Using the Section Break in Microsoft Word

When putting together a complex document in Word, a section break can help us put together documents such as a book or manual, where one sometimes has to create multiple headers and footers, or allow for single columns, then multiple, then single again. A section break is like a fence between two farms—it tells the user “there’s a border and a change here”.

Section break command

Inserting a section break is very easy. Click at the end of whichever piece of text is the “previous” layout, arrangement, or whatever, then go to the Page Layout tab, Page Setup group, and click Breaks. There, we have a choice of four types of section break. The most commonly used one is Continuous, meaning there’ll be no visible break except what we decide on—the new section will start on the same page.

Columns command  Section break columns result

Once we insert the break, we can do several things after it. A frequent use is to change the number of columns in the new section. We click anywhere after the section break, go again to the Page Layout tab, Page Setup group, and click Columns. If we want a default number, say two, we just click on it. If we want something a little more customized, we click More Columns at the bottom of the menu, and decide on width, spacing, line between, and so on. Then click OK to get out, and there we are.

(The next bit here will refer to continuous section breaks, the touchiest kind, first.)

Show Hide Characters   Show section break

When it comes to headers and footers, we have to be a little more careful where we click once the section breaks are in place, but we can turn on the Show/Hide feature in the Home tab, Paragraph group to help. (I always do—makes life easy. Already did here, so you folks can see what I’m doing.)

Section break header footer

We then simply insert section breaks where needed. If we don’t do this, the program can’t do the rest. Once the breaks are in place, whichever page FOLLOWS the break(s) will show the next section number. So if one inserts a continuous section break on page 2, the next section header/footer will start on page 3. Then, to write a book with chapters whose starting places were based on continuous breaks, we’d be set. The other kinds of break are a little more predictable, as their names tell us what they’ll do. A next-page section break would, in fact, be better for chapter break-points. We could insert one of these at the end of a chapter and immediately start the next.

One last thing: Leave the Show/Hide feature on to remove breaks. Click at the beginning of the break, hit the Delete key (not Backspace—here you want to remove something after the insertion point), and it’s gone. No trouble.

How to Create a Gradient in Adobe Illustrator

The recent post on meshes in Illustrator kind of leads into a related subject, namely the gradient. Sometimes meshes aren’t necessary, or a little too complicated to set up, but there’s still a need for (at least simulated) shading, and gradients can provide some of this. They take a few steps, but they’re worth it.

(I will mention before I start that the process is a little different than in Photoshop; the nature of the use of gradients is different in each program.)

Gradient panel

Once the file is open, we select the shape we want to put the gradient in, bring up the panel (Window menu–>Gradient), and click the basic gradient to apply it. We can select either Linear (basically bands of color) or Radial (sunburst/circles of color) at the top right of the panel, but the main part of the process is the same from there.

Gradient ramp

Editing the gradient involves the horizontal strip near the bottom of the panel, called the gradient ramp. The house-like shapes below it are color stops; they tell us what colors are in it. The diamonds above are color midpoints, which show where the halfway mark is between two colors. Both of these can be moved, though the midpoints can’t be removed as they automatically show between any pair of colors on the ramp.

Edit gradient

To add colors, we click anywhere immediately below the gradient ramp to add another stop. Double-clicking on the new stop brings up the color controls, where we can use an existing swatch, or create a new color from the sliders. We can even choose other color systems from the dropdown menu at the top right of the controls. Editing existing colors works exactly the same way.  (Tried finding out how many colors I could put in. It got boring around fifty….) Then, if necessary, drag the midpoints to adjust where colors shade from one to another.

Removing colors is easy—just drag the color stop straight down about a half inch, and let go.

Gradient angle

Usually, once we set up the gradient colors, we change the angle if we want. The control is almost dead center, and allows use of the dropdown or typing the angle we want. It’s even possible to adjust the opacity of a color stop, using the control at bottom. According to my students, this is somewhat less common, but certainly invites experiment. Finally, the Location control positions the color stop precisely, if needed.

How to Create a Theme in Microsoft PowerPoint

In PowerPoint, themes are an important part of the presentation. Even if nobody directly notices them, they are as necessary to the slide deck as the foundation for a house. Consistent appearance throughout the show improves understandability, and its professional look. Creating a theme is fairly easy; simply build the pieces and put them together.

Theme elements

The first part is to set up  the fonts and colors needed. On the Design tab, looking to the right side of the Themes group, are dropdowns for Colors, Fonts, and Effects. We can’t edit the Effects, but the first two relate to many distinct business themes, so we’ll do those here.

Theme fonts

Selecting the fonts is pretty simple. Click the Fonts dropdown, select Create New Theme Fonts at bottom, and make the choices. One rule of thumb, barring anything else, is to use a sans-serif font for titles and a serif font for body text. In PowerPoint, one often uses sans-serif for both since the letter shapes are easier to read in slides. But it’s okay to experiment. Once the fonts are set, give the new combination a name, and click Save.

Theme colors

Setting up the colors for themes takes a little more work, since there are a dozen overall. Start by clicking the Colors dropdown and, at the bottom, select Create New Theme Colors. Unless your company has official colors, you might have to experiment a little. The colors for the Text/Background choices, should be related—shades of the same color for each pair, or black and white. For the others, there are many books and articles about color theory, but a general rule that seems to work is to keep to shades of the same two or three colors at most, if possible. (The show shouldn’t look like a circus.) One exception—if you’re creating a lengthy show involving several sections (or departments at work), changing the colors for each section can give a little variety.

More colors  Custom colors

If you company does have official colors (“product branding” is the term for this), you can go to the appropriate color dropdown, click More Colors, then Custom, and enter the RGB or HSL numbers for the color in question. Again, using variations of these is advisable for the accent colors. Think of how some uniforms or dressy outfits have one or two main colors, and you’ve got the idea.

Create theme  Theme document

After saving under a suitable name, the creation of the theme itself is no harder. One has to apply the colors and fonts just created. Then, just left of the Colors, Fonts, and Effects dropdowns, click the “More” button, slide to the bottom of the list of themes, and select Save Current Theme.

We then find out something pretty cool—themes can be saved as documents, which can be emailed or otherwise sent to other people! So someone can develop a theme, send it to the IT department, and have them “push” it onto everyone’s computer who needs it. And this means themes can be consistent throughout a company, which is always good in business.

Microsoft Excel Keyboard Shortcuts

SkillForge SkillSheet

Microsoft Excel 2010/13/16 Keyboard Shortcuts

Click here to download the PDF version.

 

Frequently Used Shortcuts

In order to…

Press

Close a spreadsheet Ctrl+W
Open a spreadsheet Ctrl+O
Save a spreadsheet Ctrl+S
Copy Ctrl+C
Paste Ctrl+V
Undo Ctrl+Z
Remove cell contents Delete key
Choose a fill color Alt+H, H
Cut Ctrl+X
Bold Ctrl+B
Center align cell contents Alt+H, A, then C
Format a cell from context menu Shift+F10 or Context key
Add borders Alt+H, B
Delete column Alt+H, D, then C

 

The Function Keys—What They Do

Key

Description

F1 Displays the Excel Help task pane. Ctrl+F1 displays or hides the ribbon. Alt+F1 creates an embedded chart of the data in the current range. Alt+Shift+F1 inserts a new worksheet.
F2 Edit the active cell and put the insertion point at the end of its contents. Or, if editing is turned off for the cell, move the insertion point into the formula bar. If editing a formula, toggle Point mode off or on so you can use arrow keys to create a reference. Shift+F2 adds or edits a cell comment. Ctrl+F2 displays the print preview area on the Print tab in the Backstage view.
F3 Displays the Paste Name dialog box. Available only if names have been defined in the workbook (Formulas tab, Defined Names group, Define Name). Shift+F3 displays the Insert Function dialog box.
F4 Repeats the last command or action, if possible. When a cell reference or range is selected in a formula, F4 cycles through all the various combinations of absolute and relative references. Ctrl+F4 closes the selected workbook window. Alt+F4 closes Excel.
F5 Displays the Go To dialog box. Ctrl+F5 restores the window size of the selected workbook window.
F6 Switches between the worksheet, ribbon, task pane, and Zoom controls. In a worksheet that has been split (View menu, Manage This Window, Freeze Panes, Split Window command), F6 includes the split panes when switching between panes and the ribbon area. Shift+F6 switches between the worksheet, Zoom controls, task pane, and ribbon. Ctrl+F6 switches to the next workbook window when more than one workbook window is open.
F7 Displays the Spelling dialog box to check spelling in the active worksheet or selected range. Ctrl+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press Enter, or Esc to cancel.
F8 Turns extend mode on or off. In extend mode, Extended Selection appears in the status line, and the arrow keys extend the selection. Shift+F8 enables you to add a nonadjacent cell or range to a selection of cells by using the arrow keys. Ctrl+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized. Alt+F8 displays the Macro dialog box to create, run, edit, or delete a macro.
F9 Calculates all worksheets in all open workbooks. Shift+F9 calculates the active worksheet. Ctrl+Alt+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. Ctrl+Alt+Shift+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. Ctrl+F9 minimizes a workbook window to an icon.
F10 Turns key tips on or off. (Pressing Alt does the same thing.) Shift+F10 displays the shortcut menu for a selected item. Alt+Shift+F10 displays the menu or message for an Error Checking button. Ctrl+F10 maximizes or restores the selected workbook window.
F11 Creates a chart of the data in the current range in a separate Chart sheet. Shift+F11 inserts a new worksheet. Alt+F11 opens the Microsoft Visual Basic For Applications Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
F12 Displays the Save As dialog box.

 

Navigating in the Workbook

In order to…

Press

Move to the previous cell in a worksheet Shift+Tab
Move one cell up in a worksheet Up Arrow key
Move one cell down in a worksheet Down Arrow key
Move one cell left in a worksheet Left Arrow key
Move one cell right in a worksheet Right Arrow key
Move to the edge of the current data region in a worksheet Ctrl+Arrow key
Enter End mode, move to the next nonblank cell in the same column or row as the active cell, and turn off End mode. If the cells are blank, move to the last cell in the row or column End, Arrow key
Move to the last cell on a worksheet, to the lowest used row of the rightmost used column Ctrl+End
Extend the selection of cells to the last used cell on the worksheet (lower-right corner) Ctrl+Shift+End
Move to the cell in the upper-left corner of the window when Scroll Lock is turned on Home+Scroll Lock
Move to the beginning of a worksheet Ctrl+Home
Move one screen down in a worksheet PgDn
Move to the next sheet in a workbook Ctrl+PgDn
Move one screen to the right in a worksheet Alt+PgDn
Move one screen up in a worksheet PgUp
Move one screen to the left in a worksheet Alt+PgUp
Move to the previous sheet in a workbook Ctrl+PgUp
Move one cell to the right in a worksheet Tab

 

Selecting Items, Performing Actions

In order to…

Press

Select the entire worksheet Ctrl+A or Ctrl+Shift+Spacebar
Select the current and next sheet in a workbook Ctrl+Shift+Page Down
Select the current and previous sheet in a workbook Ctrl+Shift+Page Up
Extend the selection of cells by one cell Shift+arrow key
Extend the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, to the next nonblank cell Ctrl+Shift+arrow key
Turn extend mode on and use the arrow keys to extend a selection. Press again to turn off Turn extend mode on and use the arrow keys to extend a selection. Press again to turn off. F8
Add a non-adjacent cell or range to a selection of cells by using the arrow keys Shift+F8
Start a new line in the same cell Alt+Enter
Fill the selected cell range with the current entry Ctrl+Enter
Complete a cell entry and select the cell above Shift+Enter
Select an entire column in a worksheet Ctrl+Spacebar
Select an entire row in a worksheet Shift+Spacebar
Select all objects on a worksheet when an object is selected Ctrl+Shift+Spacebar
Extend the selection of cells to the beginning of the worksheet Ctrl+Shift+Home
Select the current region if the worksheet contains data. Press a second time to select the current region and its summary rows. Press a third time to select the entire worksheet Ctrl+A or Ctrl+Shift+Spacebar
Select the current region around the active cell or select an entire PivotTable report Ctrl+Shift+asterisk (*)
Select the first command on the menu when a menu or submenu is visible Home
Repeat the last command or action, if possible Ctrl+Y
Undo the last action Ctrl+Z

Crystal Reports Keyboard Shortcuts

SkillForge SkillSheet

Crystal Reports Keyboard Shortcuts

Click here to download the PDF version.

 

Menu Bar Shortcut Keys

In order to…

Press

Create New Report Ctrl + N
Open Report Ctrl + O
Save Report Ctrl + S
Print Report Ctrl + P
Cut Ctrl + X
Copy Ctrl + C
Paste Ctrl + V
Delete Del
Select All Ctrl + A
Find Ctrl + F
Go To Page Ctrl + G
Design View Ctrl + D
Refresh Report Data F5

 

Formula Editor Shortcut Keys

In order to…

Press

Browse selected field Alt + B
Check for Errors Alt + C
Toggle the “Shows Field” tree Alt + F
Comment the current line Alt + M
Sort tree content Alt + O
Toggle the “Shows Operator” tree Alt + P
Save formula Alt + S
Toggle the “Shows Function” tree Alt + U

 

Formula Editor Shortcut Keys, cont’d.

In order to…

Press

Select all Ctrl + A
Copy Ctrl + C
Move to end of last formula line Ctrl + End
Find Ctrl + F
Set a bookmark Ctrl + F2
Clear all bookmarks Ctrl + Shift + F2
Move to beginning of file Ctrl + Home
Move to start of word at left Ctrl + <-
Select through start of word at left Ctrl + Shift + <-
Open a dialog to create a new formula Ctrl + N
Save and close Formula Editor Ctrl + S
Focus to the syntax name list box Ctrl + T
Switch to previous control box Ctrl + Shift + Tab
Switch to next control box Ctrl + Tab
Paste Ctrl + V
Cut Ctrl + X
Undo Ctrl + Z
Repeat Ctrl + Shift + Z
Keyword Auto Complete Ctrl + Space
Move to end of line End
Copy object from list to formula box Enter
Go to next bookmark F2
Find next item F3
Go to previous bookmark Shift + F2

 

How to Edit Contacts and the Business Card in Outlook

Outlook 2016 has implemented a slightly different scheme for contacts and the business card in them, but the differences are mainly cosmetic. The basics are similar enough that users of the earlier versions won’t be at a loss.

Contact List

Once the program is open, the first thing is to switch to the Contact List (now also known as the People list). One can double-click where indicated to create a contact, and fill in the blanks, or edit an existing one.

Edit Business Card

The thing some people have a little bit of a time getting just right is the business card part. Right-clicking and selecting Edit Business Card gets one in the proper box. Most of the work is then done in the Fields area at bottom left. It’s rather like playing with Lego blocks—one can add or remove various places for information with the buttons at bottom, move items up or down with the arrow buttons just to the right. Then re-position the blank lines as spacers to get the card to look good.

Assemble Business Card    Edit Business Card Setup

One of the tricks is not to overdo the amount of content in the card. They’re supposed to carry just the essentials, as a real business card would. So basic contact info should be the majority of what’s on there. And making the text on the card a little bigger—which can be done with the controls in the Edit area at right—won’t hurt, provided the amount of content isn’t too much.

Another thing which helps is to be careful with the graphics. Having a company logo on the business card is about as far as one need go; much more would be a distraction. And getting a graphics person to set up a “watermark” style logo (high-brightness, low-contrast, rather like a watercolor) is fairly easy. In fact, many companies will have such a thing to hand in the PR office.

Business Card Graphics

Positioning the graphic can be done with the Card Design controls at top right. Experimenting a little is a good idea, but if your company already has a layout for your own card, use it by all means. Layout for others is fine too. Just bear in mind that you might need do nothing more than scan in someone’s card and import or paste it in.

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.