Ms Office Tune Up

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% [?]

If you’re tired of merely rearranging columns, you may want to try adding ones that aren’t already included or removing existing ones that you don’t want. Removing a field is easy: just click to select it, and then press Delete. (You can try out this technique with the Discontinued field in the ProductCatalog report.)

When you create a simple report using the quick creation technique described in Section 8.1.1, you usually end up with all the fields you need. However, there are two reasons why you may need to add an additional field that isn’t already in the report:

• You want to add a field that’s hidden in the Datasheet view (Section 3.1.4). When you create a new report, hidden fields are left out.

• You want to add a field with related information from a linked table. For example, you could add fields from the ProductCategories table to show information about the category that each product is in.

To add a new field, you need the help of the Field List pane (see Figure 8-5). To show it, choose Report Layout Tools | Formatting Controls Add Existing Fields.

When you add a new field, Access uses the field name for the column heading, which isn’t always what you want. Maybe you’d prefer Product Name (with a space) to ProductName. Or maybe you’d like to shorten ProductCategoryID to just Category. After all, the report shows the name instead of the numeric category ID, because the ProductCategoryID field uses a lookup (Section 5.2.5). Fortunately, renaming the column headers is easy. Just double-click one to switch it into edit mode. You can then edit the existing text or replace it altogether. Read the rest of this entry »

Popularity: 2% [?]

You’ve already learned how you can shuffle columns around in a report. However, that’s not all you can move. You can also add space between the rows (see Figure 8-4) and adjust all the following elements:

• The logo (in the top-left corner). In a new report, the logo looks like a notebook with a circle around it.

• The report title (right next to the logo). To start out, this is the name of the table or query on which the report is based (like Products).

• The date and time (which is updated every time you open the report). Initially, this appears in the top-right corner.

• The page number. This appears at the center-bottom of each page. In Layout view, Access treats the report as though all the data occupies one page, so you need to scroll to the end to find this element.

  
  Figure 8-4. Top: To add space between the rows, click a value in one of the rows, and drag it down.
  Bottom: All the rows are adjusted to have the same spacing.

• The report data (after the title). To change where the table in the report first appears on the page, click one of the column headers, and then drag it down (to add space between the title and the report data) or up (to remove the space).

• The totals (at the bottom of some columns). Access automatically adds calculations for numeric fields. For example, when the ProductCatalog report is first created, Access adds a total at the bottom of the Price column that indicates how much it costs to buy one of each product. (This total is of dubious valueto change it, select the column, and then pick another summary option from Report Layout Tools | Formatting Grouping & Totals Totals menu.)

Popularity: 3% [?]

It takes just two steps to create a simple report, and a few more to fine-tune it. If you want to try out this technique for yourself, open the Boutique Fudge database (included with the downloadable content for this chapter, explained in Section 03.3.4) or a database of your creation, and follow these steps:

1. In the navigation pane, select the table you want to use for your new report.

   This example uses the Products table from the Boutique Fudge database. You can also create a report that’s based on a query. See the box “Doing the Heavy Lifting with a Query” (Section 8.1.2) for more about this trick.

2. Choose Create Reports Report.

   A new tab appears with a simple, automatically generated report. This report arranges information in a table, with each field in the table (or query) occupying a separate column. The Report view looks somewhat like the datasheet, except for the fact that it has nicer formatting and uses space more efficiently, as shown in Figure 8-2.

   When you first create a report, the fields are arranged from left to right in the same order that they live in the table. It doesn’t make any difference if you’ve rearranged the columns in the datasheet. However, any columns you’ve hidden in the datasheet (Section 3.1.4) are left out of the report.

   Read the rest of this entry »

Popularity: 4% [?]

• Report Basics
• Printing, Previewing, and Exporting a Report
• Formatting a Report
• Filtering and Sorting a Report

There are many reasons to create a hard copy of your lovingly maintained Access data. With a good printout, you can:

• Carry your information without lugging your computer around. For example, you can take an inventory list while you go shopping.

• Show your information to non-Access users. For example, you can hand out product catalogs, order forms, and class lists to other people.

• Review details outside the office. For example, you can search for mistakes while you’re on the commuter train home.

• Impress your boss. After all, it’s difficult to argue with 286 pages of raw data.

In Chapter 3 you learned how to print the raw data that’s in a table, straight from the datasheet. This technique is handy, but it provides relatively few features. You don’t have the flexibility to deal with large blocks of information, you can’t fine-tune the formatting of different fields, and you don’t have tools like grouping and summarizing that can make the information easier to understand. As you’ve probably already guessed, Access provides another printing feature that fills in these gaps. It’s called reports, and it allows you to create a fine-tuned blueprint that tells Access exactly how it should prepare your data for the printer.

Reports are specialized database objects, much like tables and queries. As a result, you can prepare as many reports as you need, and keep them on hand indefinitely. Life isn’t as easy if you stick to the datasheet alone. For example, if you’re using the bobblehead database, you may want to print a list of bobblehead dolls with the doll’s name and manufacturer information for your inventory list, and a separate list with prices for your budgeting process. To switch back and forth between these two types of printouts using the datasheet, you have to manually rearrange and hide columns every time. Reports don’t suffer from this problem, because each report is saved as a separate database object. So if you want to print your inventory list, you simply run the DollInventory report. If you want the budgeting details, you fire up the Doll-Prices report. Read the rest of this entry »

Popularity: 3% [?]

Delete queries are the simplest and most dangerous of the action queries. A delete query works much like a normal select query. You specify a set of filter conditions, and then Access finds the matching records in the table. But the delete query doesn’t just display the records instead, it erases them from the database.

Delete queries are great for clearing out a huge number of records at once after you’ve finished transferring them to another table. In the append-query example described earlier (Section 7.3.1), you probably want a way to remove the original records once you’ve copied them to the new table. A delete query fits the bill perfectly.

To create a delete query, follow these steps:

1. Create a new query by choosing Create Other Query Design.

2. Using the Show Table dialog box that appears, add the table that has the records you want to delete. Then click Close to close it.

3. Change your query to a delete query by choosing Query Tools | Design Query Type Delete.

   The Sort and Show boxes disappear from the column list, and the Delete box appears.

4. Add the fields you want to use for filtering, and then set your filter conditions.

   Your filter conditions determine what records are deleted, so make sure you define them carefully. If you don’t include any filter conditionsgulpAccess will delete all the records when you run the query.

5. Add any other fields that you want to inspect to confirm you’re getting the correct records in the datasheet preview.

   Read the rest of this entry »

Popularity: 4% [?]

The following steps show you how to create an append or make-table query. You’ll transfer records from the Contacts table in the Marketing.accdb database to the PotentialClients table in the Sales.accdb database. (You can find both these databases on the “Missing CD” page at www.missingmanuals.com.)

1. Open the source database.

   In this example, that’s the Marketing.accdb database that has the contact information.

2. Create a new query by choosing Create Other Query Design.

   The Show Table dialog box appears.

3. Using the Show Table dialog box, add the source table that has the records you want to copy. Then click Close to close it.

   This example uses the Contacts table.

4. Change your query to an append query by choosing Query Tools | Design Query Type Append (or choose Query Tools | Design Query Type Make Table to convert it to a make-table query).

   The destination table (the PotentialClients table in the Sales.accdb database) already exists. For that reason, you use an append query instead of a make-table query.

   When you change your query to an append or make-table query, Access asks you to supply the destination table (the place where you’ll copy the records), as shown in Figure 7-6.

5. If you want to transfer the records to another database, then choose Another Database, and then click Browse. Browse to your database file, and then click OK to select it.

   You’re transferring records to the Sales.accdb database. Read the rest of this entry »

Popularity: 6% [?]

An append query selects records from a table, and then inserts them into another table. (Technically speaking, appending is the process of adding records to the end of a table.)

You may create an append query for a number of reasons, but usually you do it to transfer records from one table to another. You may want to use an append query to transfer records from one database to another. This trick’s handy if you have duplicate tables in different databases (perhaps because different people need to use the database on different computers).

Append queries also make sense if you’re working with a super-sensitive database. In this case, you might enter new records in a temporary table so someone else can look them over. When the inspection’s finished, you can then use an append query to transfer the records to the real table.

Append queries are a bit stricter than other types of action queries. In order to transfer the records, you need to make sure the two tables line up. Here are some guidelines: Read the rest of this entry »

Popularity: 4% [?]

An update query searches for some records, and then modifies them. Usually, you’ll limit your modifications to a single field, but Access lets you change as many fields as you want. You also have a fair bit of flexibility in how you apply the update. The simplest option’s to stuff an entirely new value into a field. You could create a query that moves all the products in one category into another by entering a new value in the CategoryID field. Alternatively, you could take the current values in a field and change them, using an expression (a specialized database formula that can perform a variety of different calculations). You could increase all your product prices by 10 percent, or add a week to the due date of every outstanding project.

The example that follows uses the Products and ProductsCategories tables from the Boutique Fudge database (which is described in Section 5.4.2.1). The query updates all the products in the Beverages category, increasing their prices by 10 percent. You can try this example for yourself by downloading the examples for this chapter from the “Missing CD” page at www.missingmanuals.com

Read the rest of this entry »

Popularity: 3% [?]

In the wrong hands, action queries are nothing but a high-tech way to shoot yourself in the foot. They commit changes (usually to multiple records), and once you’ve applied the changes, you can’t reverse them. Some database fans avoid action queries completely.

If you do decide to use action queries (and there are plenty of handy tricks you can accomplish with them), then you need to take the right precautions. Most importantly, before you use an action query, make a database backup! This step’s especially crucial when you’re creating a new action query, because it may not always generate the result you expect. To make a backup, you can copy your .accdb database file (just like you would any other file; one way is to right-click it, and then select Copy). If you don’t want to mess with Windows Explorer, then you can create a backup without leaving Access by selecting the Office button Manage Back Up Database (Section 1.3.1).

Backups are great for disaster recovery, but it’s still a good idea to avoid making a mistake in the first place. One safe approach is to start by creating a select query. You can then make sure your query’s selecting the correct records before taking the next step and converting it into an action query (by choosing one of the action query types in the Query Tools | Design Query Type section of the ribbon).

The Action Query Family

Access has four types of action queries:

• An update query changes the values in one or more records.

• An append query selects one or more records, and then adds them to an existing table.

• A make-table query selects one or more records, and then creates a new table for them.

• A delete query deletes one or more records.

  In the following sections, you’ll try out all of these queries.

Popularity: 3% [?]