Tips, tricks, tools, and techniques for Microsoft Office

Columns Available Here: Queries 103 Making Queries Prompt for Input Queries 102 Simple Date Calculations Queries 101 Using VBA in Queries Report Design 101 Report Page Numbering

In our last Queries column ("Queries 102"), I closed by pointing out that there are six different types of queries in Access: Selection, Crosstab, Make-Table, Update, Append, and Delete. This time I’ll conclude our three-part series on queries by discussing these other types and then we’ll be ready to move on to the fun part of Access -- creating reports.

In previous discussions, I’ve used the Northwinds sample database to illustrate examples because it comes with Access and it’s probably already on your computer. Beginning with this discussion, I’ll be using a database named Wizards.mdb that I will design and maintain specifically for the Access Wizards series. Whenever I make significant changes to this database I’ll ask you to download an updated version, but for now it just contains a few tables I borrowed from Northwinds, along with a form, query, and report I developed in a previous discussion. Before you read further, please download a new copy of the Wizards database from

   http://www.officeletter.com/samples/accesswizards/wizards.mdb

Save the database (it's about 700KB) to a convenient location on your hard drive and double-click to open it.

When the Wizards database opens, you should see a smaller window titled “Wizards:Database (Access 2000 file format)” somewhere within the larger Access window. Hereafter I will refer to this smaller window as the “Database window” and it will serve as our main menu for now.

In the Database window, click Tables in the Objects section to the left and then double-click Customers in the right panel to open this table. The grid view that is displayed provides full add/change/delete access to the table’s contents, so be careful what and where you poke! As a database designer, you will use the grid view frequently to add test data, verify update results, and search for “lost” data.

Scroll down through the rows and notice that the column labeled Country appears to be in no particular order. By default, this table is displayed in order of its primary key, which is Customer ID. Now let’s suppose that you want to delete all the customers from Austria. One way to accomplish this is to click the Country column heading to select the entire column, click the Sort Ascending icon on Access’ main menu (it’s the one with A above Z and an arrow pointing down), select the two Austrian customer records, and press the delete key.

While this method certainly works, it’s not the type of process you’d want an end user to use because record deletes are immediate and irreversible. As a developer, you never want the end user to open a table in grid view, so let’s create a query for this delete operation.

QUERY FOR DELETING RECORDS

1. Close the Customers table (if prompted, DO NOT save changes to the table’s design).

2. Click Queries in the Database window and then double-click Create a Query in Design View.

3. In the Show Table window, click Customers, click Add, and click Close. This query will use only the Customers table.

4. By default, new queries are created as Select queries (see the top border of your new query window), so on the main Access menu click Query and then click Delete Query.

5. Scroll down through the list of fields in the Customers table until you see Country. Drag and drop it into the first column of the top row of the grid in the lower part of the query window.
   
   Figure 1

6. In the Criteria row of the first column, type Austria and press the Tab key to exit the field (see figure 1).

You are now ready to test your query, but remember that any changes to tables are immediate and irreversible, so check your work before you run your query by clicking the red exclamation mark on Access’ main menu.

Click the red X on the query design window to close it and save your query as qryDeleteAustria.

Open the Customers table in grid view and sort by country (see above) to verify that your query worked as planned.

Now that you understand how to create a Delete query, most of the other types should be easy. There isn’t room here to present a detailed description of each type, but here’s a summary:

Crosstab: We’ll save this for another time (after I figure it out myself!)

Make-Table: This type of query is very similar to a Select query except that the results are written to a permanent table instead of to a results view. Personally, I don’t use Make-Table queries very often but you could use one to make a backup copy of an existing table before running a large delete or append query.

Update: This type of query is very useful if you need to make mass updates to a table. When the telephone company decides to create a new area code in your region, the resulting database maintenance can be a huge job. Since area code changes always affect entire prefixes (the first three digits of the seven-digit phone number), you can write an Update query to select all telephone numbers in area code XXX that have prefixes in a specified list and update the area code to YYY.

Append: The Append query is a good way to add a large number of existing records from an outside source to an existing table. For example, if your company acquires another, you might be asked to add the other company’s customers to your existing table. Once you figure out how to map their fields onto your table design, the Append query will make quick work of the processing.

In a later installment, I’ll show you how chain queries together using a macro that your users can run at the click of a button.

Your comments and suggestions are welcome! Send them to access@officeletter.com

-- Dick Archer

^ Top of page ^

In this column, we build on Queries 103, explaining how to create a query that prompts for imput.

This column is for Premium Edition subscribers only. To read this column, you must have a userid and password.

To read the column, visit http://www.officeletter.com/prem/v05/n15.html#awqueryprompt

To become a Premium Edition subscriber, visit http://www.officeletter.com/subscribe.html

^ Top of page ^

In our last installment ("Queries 101" two weeks ago), we created a very simple query to access data in the Shippers table of the Northwind Traders sample database. If you’re just joining us, this database is normally found on your hard drive at:

   C:\Program Files\Microsoft Office\OFFICE11\Northwind.mdb

You can also download a copy from the Microsoft Web site by browsing to: http://office.microsoft.com/en-us/assistance/HA010546951033.aspx and following the instructions found there.

We’ll be using the Northwind database again this time, so locate it on your hard drive and double-click it now. Click OK on the Welcome splash screen to make it go away.

The Current Shipper List query that we created last time simply displayed the existing data in the Shippers table (Premium readers received a more complex query). As I mentioned, we could have accomplished almost the same result by just double-clicking the table itself, but we have a broad range of readers and I wanted everybody to understand the basic mechanics of creating a query.

I also mentioned that I always base my reports on queries -- NEVER on the tables themselves -- even if that means creating a simple query like Current Shipper List. My reasons for this will become apparent when we get back to reports in a few weeks. For now, just trust me on this point.

This time we’ll create a more complex query and you’ll begin to see why queries are such powerful tools for manipulating data.

Figure 1 - Click to enlarge

Figure 2 - Click to enlarge

Open your Current Shipper List query from our last column and click the Show Table icon (the one with a yellow plus sign next to a grid). Click the Orders table, click Add, and then click close. The Orders table will be displayed in the upper portion of the query window and you’ll notice that a line automatically connects it to the Shippers table. This line is called a Relationship and it was created by the database developer. We don’t have time to go into Relationships here, but this one implies that for every ShipperID value in the Shippers table, there can be zero, one, or many corresponding ShipVia values in the Orders table. This is called a one-to-many relationship.

One at a time, drag and drop the OrderID, OrderDate, and ShipDate fields from the Orders table into empty cells on the top row of the design grid (see Figure 1).

Run this query by clicking the red exclamation point on the upper menu bar. The results grid (see Figure 2) will include one row for every row in the Orders table, along with the corresponding values from the associated Shipper table.

You have just created your first multi-table query! Based on our work last time, you should be able to get the results to display in order by ship date with the most recently shipped orders first. [HINT: Change the sort order for CompanyName to (not sorted) and change the sort order for ShippedDate to Descending.]

A subtle, but very important, characteristic of queries is the way in which they access data. In our example above, Access retrieved each row from the Shippers table, one at a time, and then located all the corresponding rows in the Orders table where ShipVia matched ShipperID. Because there are three entries in the Shippers table, this process would have occurred three times and any unmatched rows in the Orders table would not have been selected. It’s also possible to construct a query that reads the Orders table first, and then locates the appropriate matching row in the Shippers table, but we’ll have to save that exercise for another time.

To finish up, click the red X on the Query window (not the Access window) and click Yes to the save changes prompt.

So far, we’ve only discussed relatively simple selection queries -- those used for selecting data from one or more tables and providing the results, in the form of a view, to reports, or to other queries. If you’d like to examine a more complex selection query, open (in design mode) the Invoices query in the Northwind database. Here, columns from six different tables are combined in a variety of one-to-many and many-to-one relationships to create the view necessary to print invoices.

In addition to the selection query, there are five other types of queries (Crosstab, Make-Table, Update, Append, and Delete) and we’ll get to some of these in the coming weeks. You can see the complete list of query types by hovering your mouse pointer over the word Query on the main Access menu bar.

Your comments and suggestions are welcome! Send them to access@officeletter.com

--Dick Archer

^ Top of page ^

In this column, we build on Query 102, modifying the Current Shipper List selection query to include the OrderID, OrderDate, and ShippedDate, let’s use this information to do something useful: we'll calculate the number of days it took to process each order.

This column is for Premium Edition subscribers only. To read this column, you must have a userid and password.

To read the column, visit http://www.officeletter.com/prem/v05/n12.html#calcs

To become a Premium Edition subscriber, visit http://www.officeletter.com/subscribe.html

^ Top of page ^

Next to the tables that hold your project’s data, queries are probably the second most important class of objects in Access. They can be used to locate, view, change, and analyze data, and they can be very, very complex. I can’t cover every feature of queries here, but hit some of the (more useful) high points during the next several installments of this column. Then we’ll come back and revisit reports using the knowledge we’ve gained about queries.

We’ll be using the Northwind Traders sample database that comes with Access 2003, so locate it on your hard drive and double-click it now. Click OK on the Welcome splash screen to make it go away. Typically, the Northwind database can be found at:

   C:\Program Files\Microsoft Office\OFFICE11\Northwind.mdb

I’ll be using the Northwind database in most of my discussions so if you can’t find it on your computer, use the Help command from the main menu, then choose Sample Database, and choose Northwind Sample Database. Access will install the database for you. You can also find a version for download on the Microsoft Web site in Access 2000 format. See

   http://office.microsoft.com/en-us/assistance/HA010546951033.aspx

QUERY CONCEPTS

Let’s take a minute to talk about some general concepts.

If you click Tables on the main Access database window and double-click on a table name, Access opens that table and displays its contents in what’s called a grid. This is simply a row-column format that looks a lot like an Excel spreadsheet.

Try this with the Shippers table -- the simplest table in the Northwind database (see Figure 1). You should see three columns: Shipper ID, Company Name, and Phone. You should also see three rows, one for each unique shipper in the table. The number of rows increases and decreases as you add and delete shippers from the table. The number (and name) of the columns can only be changed by modifying the table’s structure in Design mode.

After you’ve examined the Shippers table, close it by clicking the X in the upper right corner of the table window. Do NOT close Access. I wanted you to see the grid view first hand because it will help in this discussion. This week, I’m going to concentrate on a very basic query for locating and viewing data. In future installments, I’ll show you how to use queries to combine data from multiple tables, and explain how to update or delete existing data with a query.

When a basic query is run in immediate mode, it typically displays its results in a grid very similar to the table view you just looked at. In fact, it will help you understand what’s going on behind the scenes if you think of a basic query as a device that uses rules provided by you to create a temporary, memory-resident table. If you’ve worked with SQL Server or other relational databases, you may have heard the term view -- the output from a basic query is nothing more than a view.

CREATING THE FIRST QUERY

Let’s create our first query. On the main database window, select Queries in the list of Objects. The right pane will display a list of the queries already in the database. You can take a look at these later (be careful not save any changes!). For now, click New on the menu bar at the top of the window.

On the New Query window, select Design View and click OK. Since your new query doesn’t have any data sources yet, the Show Table dialog box is displayed automatically.

Select Shippers, click Add, and click Close. If you need this window later, you can reopen it by clicking its icon in the menu above. It’s the one with a small grid and a yellow plus sign.

One at a time, drag and drop each of the three field names from the small table window onto the top row of the grid (see Figure 2).

Congratulations! You have just created a fully functional Access query. To run this query, click the red exclamation point on the upper menu bar. The results (see Figure 3) should look familiar because you’ve essentially recreated the Shippers table as a view.

Notice that your query results are sorted in order by Shipper ID. This happened because Shipper ID is the table’s primary key. Suppose you want the list sorted alphabetically by Company Name instead. Click the Design View icon on the button bar. This is the one that looks like a pencil, a triangle, and a ruler and it should be at the far left end of the menu.

Access switches you out of run mode and back into design mode. Now click into the sort row of the Company name column (the cell located three rows down and two columns from the left), click the down arrow at the right side, and click Ascending. Run your query again (click the red exclamation point) and your list is now organized by company name rather than ID.

To finish, click the red X on the Query window (not the Access window), click Yes to the “save changes” prompt, name your new query Current Shipper List, and click OK.

This may seem like a trivial exercise if you are already familiar with queries, but it gives us a foundation to build on next time when we’ll create a view that includes data from two different tables.

Your comments and suggestions are welcome! Send them to access@officeletter.com.

-- Dick Archer

^ Top of page ^

In "Queries 101" we showed you how to create a simple locate-and-view query using data from the Shippers table, and how to sort the retrieved data by company name instead of the default order of Shipper ID. In our basic query, we dragged each field of the Shippers table onto the grid, one at a time, to define the columns of the view. In this column we explain how to add a column that’s not in the table -- at least not the way we want it to be displayed.

This column is for Premium Edition subscribers only. To read this column, you must have a userid and password.

To read the column, visit http://www.officeletter.com/prem/v05/n10.html#vbaqueries

To become a Premium Edition subscriber, visit http://www.officeletter.com/subscribe.html

^ Top of page ^

One of the most valuable uses of Access is as a report-writing tool. I’m a VB programmer, and I find Access much easier to use than Crystal Reports because its VBA is similar (but not identical) to Visual Basic. Crystal and its derivatives have some design features and mathematical functions that always throw me because I don’t use them enough.

The other thing I like about Access is that when your simple report mushrooms into a full-blown application (as they always seem to do!), you have the built-in power of queries, forms, macros, and VBA to help you out.

Several years ago I was hired to create an extensive Access-based reporting system for an existing real estate management package and I developed the two formats I’m going to discuss in this article. These two templates, one for portrait (8.5 x 11) orientation and one for landscape (11 x 8.5) orientation, became the basis of more than 100 finished reports that the client still uses today.

Because of their visual nature, reports are subject to a wide range of personal preferences, styles, and formats. You may find the reports created by the Access Report Wizard perfectly adequate for your needs, but I’ve always thought they were somewhat “horsy” -- odd fonts of varying sizes and colors that didn’t look professional.

Figure 1: The report heading Click to enlarge

Since my client was converting from a legacy mainframe computer system, he was used to the old 17”x 11” blue-bar computer paper and many of his existing reports were formatted for landscape orientation. In addition, his old data printers only had a single font and most reports had been designed using all capital letters. Ugh! Nevertheless, this is what he was used to seeing and my mission was to translate this into the TrueType font/laser printer era as closely as possible.

Figure 1 (see illustration) shows the top section of the portrait template. The date the report was printed is in the upper left corner and the page number is in the upper right. I always use the “Page X of Y” format because it’s so easy to do in Access and it provides some very useful information. For example, if I walk up to the laser printer and see that someone else has started printing a report that’s on page 3 of 721, I know I can go have lunch and come back for my report later!

Centered on the top line, between the date and the page number, is the client’s name and directly below that is the name or title of the report in bold caps. Next there’s a blank line and then the top row of the column headings. When some column headings require more than one line, I “bottom justify” all the headings:

Heading One
Goes Here		Heading Two
                  

This forces the last line of each heading to be positioned directly above the horizontal line that stretches across the page. The first row of report data goes immediately below the line and I tend to keep the rows fairly close together. You’ll also notice that I use a small, 8 point font for everything except the report title, which is 10 point. Here’s where the personal preferences really start to show. You may feel that the print is too small and bump everything up a notch, but I like the smaller type face. Speaking of type face, you’ll notice that I chose Arial rather than the standard Times Roman. Again, this is a personal preference, but I tend to use Arial for number-related things and Times Roman for correspondence.

The footer of each page contains a simple disclaimer that provides the client with a little leverage if he ever has to take legal action against an employee for disclosing sensitive information. It also gives the report an “official” look.

The only difference between my two templates is the paper orientation and the margins. I find that many reports printed on a laser printer find their way into 3-ring binders, so my templates have extra margin space on the left side (portrait) or the top (landscape) to accommodate the punched holes. My margins (click File/Page Setup) for the portrait style report are: Top=0.5, Bottom=0.5, Left=0.75 and Right=0.5. The extra one-quarter inch on the left is for the holes. The settings are the same for the landscape style except that Top is 0.75 and Left is 0.5.

The first thing I do after I start a new project is to add these two templates to my collection of report objects. Since I always use the same general format, from my new project, I click File/Get External Data/Import and then I browse to any of my existing projects. Next, I click the Reports tab, select (Sample Landscape Report) and (Sample Portrait Report) and click OK. Once the templates are copied to my new project, all I need to do is change the client name in each template and they’re ready to go to work. I use the parentheses around the names to force them to sort to the top of my list of reports so they’re always easy to find.

To create a new report based on one of my templates, I:

1. Go to the database window, click Reports, and select the template to use.

2. At the top of the Access window, click Edit/Copy, and click Edit/Paste.

3. Enter the name of the new report in the Paste As dialog box and click OK.

4. Open the new report in design mode, click the Properties icon on Access’ menu, select the Data tab and click the down-arrow in the Record Source field to select the query or table to use for this report. Click Access’ Field List icon and begin to drag-and-drop fields onto my report.

Obviously, there’s a lot more to creating good reports than just have a presentable template and we’ll get to those topics in the coming weeks. I’ll focus on the things I use the most, such as group headers and footers, subtotals and grand totals. But before we create a good report, we need to create a good underlying query -- more on that next time.

Your comments and suggestions are welcome! Send them to access@officeletter.com

-- Dick Archer

^ Top of page ^

In the Report Design 101 column, I discussed a pair of report templates that I reuse in almost every Access project I work on. I described how I copy the templates from one database to the next and I also discussed some of the fields and why I put them on the templates in the first place. I've made the templates available to our Premium Edition subscribers.

This column and templates are for Premium Edition subscribers only. To read this column, you must have a userid and password.

To read the column, visit http://www.officeletter.com/prem/v05/n08.html#awpgnumbers

To become a Premium Edition subscriber, visit http://www.officeletter.com/subscribe.html

^ Top of page ^