Ms Office Tune Up

The ProductCatalog report presents all the records from the Products table. However, reports often need to filter out just an important subset of information. For example, you may want to analyze the sales of products in a specific category or the orders made by customers in a specific city. In the case of the ProductCatalog, it’s logical to leave out discontinued items. After all, there’s no reason for Boutique Fudge to advertise items it no longer sells.

You can pare down the results that are included in a report in two ways. You’ve already learned about one option: creating a query that extracts the results you want, and then using that query to build your report. This option is a good choice if you already have a query that fits the bill or you plan to use this subset of data for several purposes (reports, editing, other queries, and so on).

Another choice is to apply the filtering through report settings. The advantage of this technique is that you can change the filter settings quickly and repeatedly. If you plan to use the same report to print several different subsets of data, this approach is best. For example, you could filter out the products in one category, print them, and then adjust the filtering to select products in a different category, which you could also print.

Report filtering works the same way datasheet filtering does (discussed in detail in Section 3.2.2). You have two options: Read the rest of this entry »

Popularity: 2% [?]

Here’s a simple but powerful formatting trick: Add a shaded background to every second row. Alternating row formatting gives a bit of polish to the plainest report, but it also serves a practical purpose. In dense reports, the shaded bands make it easier for readers to distinguish each row and follow a row from one column to the next.

To apply an alternating row format, you need to click immediately to the left of any row. At that point, the entire row becomes selected, and the Report Layout Tools | Formatting Font Alternate Fill button is turned on. (The Alternate Fill button looks like a mini-grid. It appears right under the Fill button.) You can click it, and then choose a color.

If you click one of the values in the row, the Alternate Fill button won’t be turned on, and you won’t be able to change the alternating fill color.

8.3.1.3. Gridlines

When you create a new report, your data is arranged in an invisible table. This table doesn’t include any gridlines, so your printouts look sleek and lightweight. But if you’re a closet gridline lover, you’ll be happy to know you can add borders to the report table. It’s up to you whether you want to add them everywhere to keep data carefully regimented in separate cells or just use them judiciously to highlight important columns. Read the rest of this entry »

Popularity: 2% [?]

You can use the Report Layout Tools | Formatting Formatting section of the ribbon to adjust numeric fields (like the Price field in the ProductCatalog report). You’ll find a drop-down list that lets you pick various options for formatting numbers:

• General Number gives a basic, no-frills number. Access gives each value the number of decimal digits it needs.

• Currency makes sure each number has two decimal points and gets the currency symbol that’s configured for your computer (based on its geographic locale).

  Large numbers get thousands-separator commas to separate the digits, as in $1,111.99.

• Euro is similar to Currency, except it shows the currency symbol for the euro.

• Fixed gives each number the same number of decimal places. (Initially it’s two, but you can use the Increase Decimals and Decrease Decimals buttons, shown in Figure 8-15, to change this.) Large numbers don’t get commas.

• Standard is the same as fixed, except large numbers do get the thousands separator comma (as in 1,111.99).

• Percent assumes each number is a fractional value that represents a percentage, where 1.0 is 100 percent. So if you have the number 48, Access changes this to 4800.00 percent. (You can change the number of decimal places with the Increase Decimals and Decrease Decimals buttons.)

• Scientific displays each number using scientific notation, so 48 becomes 4.80E+01 (which is a fancy way of saying 4.8 multiplied by 10¹ gives you the number that’s stored in the field). Scientific notation is used to show numbers that have vastly different scales with a similar number of digits. You can change the number of decimal places using the Increase Decimals and Decrease Decimals buttons.

You can also change the number of digits that are displayed to the right of the decimal point by clicking the Increase Decimals and Decrease Decimals buttons in the Report Layout Tools | Formatting Formatting section of the ribbon.

Popularity: 2% [?]

AutoFormats are a great way to get a bunch of formatting done in a hurry. However, sometimes you want to use more of a personal touch and format the different parts of your report by hand.

To apply more targeted formatting, you need to follow a two-step approach. First, select the portion of the report you want to format. Second, click a command in the Report Layout Tools | Formatting Font section of the ribbon (Figure 8-15).

Figure 8-15. The Report Layout Tools | Formatting Font section is packed with basic formatting tools.

The Layout Tools | Formatting Font section lets you adjust all the following details:

• The font and font size (11-point Calibri is the easy-on-the-eyes standard)

• The text alignment (left, right, or center)

• The text color and background color

Although you can format the title, date, or page number sections of the report, you’ll spend most of your time formatting the column headers and the column values. To format a column header, click it. To format the column values, click any one of the values in the column. Figure 8-16 shows an example. Read the rest of this entry »

Popularity: 2% [?]

So far, you’ve learned to create simple reports that show all the information you want in a compact table. The only problem with these reports is that they all look the same. If you’re working in a cubicle farm for a multinational insurance company, this drab sameness is probably a good thing. But those who still have a pulse may want to jazz up their reports with borders, exotic fonts, and a dash of color.

The quickest way to apply formatting is to use one of the prebuilt AutoFormats (shown in Figure 8-14) from the Report Layout Tools | Formatting AutoFormat AutoFormat list. Each AutoFormat applies a combination of fonts, colors, and border settings. AutoFormats let you transform the entire look of your report in one step, but they don’t give you the fine-grained control to apply exactly the details you want.

Figure 8-14. Click the drop-down arrow (circled) to see all the available AutoFormats. (Or, if you have a really large monitor, the AutoFormat previews appear right in the ribbon.) Each thumbnail preview shows the colors and a bit of the background that the format uses, but you need to apply it before you can really see what it looks like.

Read the rest of this entry »

Popularity: 2% [?]

To export a report as a PDF file, you need the “Save As PDF or XPS” add-in. To get it, surf to www.microsoft.com/downloads, and search for “PDF”. The links will lead you to a page where you can download the add-in and install it with just a couple clicks.

Once you install the add-in, all your Office applications will have the ability to export their documents in PDF format. In an Access report, you work this magic by choosing Print Preview Data PDF or XPS while you’ve got a report in Print Preview mode. Or, you can right-click your report in the navigation pane, and then choose Export PDF or XPS.

When you export a PDF file, you get a few extra options in the “Publish as PDF or XPS” dialog box (Figure 8-13). PDF files can be exported with different resolution and quality settings (which mostly affect reports that have pictures). Normally, you use higher-quality settings if you’re planning to print your PDF file because printers use higher resolutions than computer monitors.

Figure 8-13. The “Publish as PDF or XPS” dialog box looks a lot like the Export As dialog box, except it has a Publish button instead of an Export button. You can turn on the “Open file after publishing” checkbox to tell Access to open the PDF file in Adobe Reader (assuming you have it installed) after the publishing process is complete, so you can check the result.

The “Publish as PDF or XPS” dialog box gives you some control over the quality settings with the “Optimize for” options. If you’re just exporting a PDF copy so other people can view the information in your report, choose “Minimum size (publishing online)” to save some space. On the other hand, if there’s a possibility that the people reading your PDF may want to print it out, choose “Standard (publishing online and printing)” instead. You’ll export a slightly larger PDF file that will make for a better printout. Read the rest of this entry »

Popularity: 6% [?]

The Print Preview tab is a bit of an oddity, because it includes a few commands that don’t have anything to with printing your report. The commands in the Print Preview Data section let you take a snapshot of the current report data, and then export it into some other type of file so you can view it outside of Access or work with it in another program. This technique is a great one to use if you want to share some data with other people (read: impress the boss).

Although Access supports many different formats for exporting a report, you’ll use just a few with reports. (The others are more useful when you’re exporting pure data from a table or query, as explained in Chapter 10.) The useful formats for exporting reports include:

• Word. This option transforms your report into a document you can open in Microsoft Word. However, the format Access uses is a bit clumsy. (It separates each column with tabs and each line with a hard return, which makes it difficult to rearrange the data after the fact in Word.) A nicer export feature would put the report data into a Word table, which would make it far easier to work with.

• HTML Document. This option transforms your report into a rich HTML document, suitable for posting on the Web or just opening straight from your hard drive. The advantage of this format is that all you need to view it is a Web browser (and who doesn’t have one of those?). The only drawback is that the formatting, layout, and pagination of your report won’t be preserved exactly, which is a disadvantage if someone wants to print the exported report.

• Snapshot Viewer. This option creates a .snp snapshot file, which anyone can open to view and print the fully formatted report. In order to view the snapshot file, you need Microsoft’s free Snapshot Viewer program. (To download it, surf to http://office.microsoft.com and search for “Snapshot Viewer.”) Although the Snapshot Viewer works perfectly well, most people prefer to use the more standard PDF format (next in the list), which provides the same features. (Truthfully, the Snapshot Viewer is a bit of a holdover from earlier versions of Office.)

• PDF or XPS. This option lets you preserve your exact report formatting (so your report can be printed), and it lets people who don’t have Access (and possibly don’t even have Windows) view your report. The only disadvantage is that this feature isn’t included in the basic Access package. Instead, you need to install a free add-in to get it (you’ll see how in Section 8.2.3). For more information about the PDF and XPS formats, see the box “Learning to Love PDFs” below.

Read the rest of this entry »

Popularity: 2% [?]

To get a preview of what your printed report will look like, right-click the report tab title and then choose Print Preview, or choose Office button Print Print Preview. Print Preview mode doesn’t let you make any changes or select any part of the report. You’re limited to zooming in and out, and moving from page to page (see Figure 8-11). When you’re finished looking at your print preview, choose Print Preview Close Preview Close Print Preview.

Figure 8-11. In Print Preview mode, to zoom in, click once with the mouse. Click again to zoom back out to the full page view. You can also use the page navigation buttons at the bottom of the window to move from one page to the next, and the zoom slider (not shown) for more precise zooming. But the most useful commands appear in the ribbon, which lets you tweak the print settings and export your report results to another type of file. Read the rest of this entry »

Popularity: 2% [?]

So far, you’ve learned how to quickly create a report based on a table or a query. However, you have another choice you can start with a blank slate and explicitly add each field you want. Both approaches are equally valid. You may prefer to use the quick creation technique when you want to build a report that closely follows the structure of an existing table or query. On the other hand, if you plan to create a report that uses just a few fields from a table, you may find it’s easier to start from scratch.

Here’s how you create a report from the bottom up:

1. Choose Create Reports Blank Report.

   A new, empty report appears in Layout view. The Field List appears on the right, with all the tables in your database.

2. Add the fields you want from the appropriate table, either by dragging them from the Field List onto the report surface or by double-clicking them.

   You can also use fields from related tables. For example, you can create a report that combines product information and the category details for each product. In this case, the report automatically uses a join query (Section 6.3) to get the results.

3. Format the columns. Read the rest of this entry »

Popularity: 2% [?]

Just like tables and queries, you can use several different views to change a report. When you create a report using the quick creation technique described earlier, you begin in Layout view, which is an ideal starting place for report builders. But depending on the task at hand, you may choose to switch to another view. You have four viewing options:

Figure 8-5. The top portion of the Field List window lists the fields from the table (or query) on which the report is based. The middle portion lists the fields in any related tables, and the bottom portion lists unrelated tables (which you probably won’t use). To add a field, drag it from the Field List pane and drop it on your report. Read the rest of this entry »

Popularity: 2% [?]