Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Sep 2, 2003

MS Access for the Business Environment: Reporting in MS Access: Grouped Transactional Report Part I - Page 2

By William Pearson

Locate and Access the Data

Because we have created a preliminary design, we have a complete idea of the data that the report will need to access. In addition to our draft (shown in Illustration 1 above), we might want to list the data fields that the report will contain. For purposes of this exercise, we will note their identities as we build the query, from which we will entrain them into the report.

Let's get started by opening MS Access, and creating a query that will act as the source of data for the report.

1.  Go to the Start button on the PC, and then navigate to the Microsoft Access icon.

2.  Click the icon to start Access.

MS Access opens, and may display the initial dialog. If so, close it.

3.  Select File -> Open from the top menu, and navigate to the Northwind sample database (the file might also be accessed from the Open a File menu atop the task pane, if it has not been disabled previously, at the right side of the main window in Access 2002.)

4.  Select Northwind.mdb.

The splash screen may appear, depending upon whether we have suppressed it before.

5.  Click OK, as necessary.

The splash screen disappears, and is replaced by the Main Switchboard, as we have seen in earlier lessons.

6.  Click the Display Database Window, or get there by an alternative approach.

We arrive at the Database Window.

7.  Click Queries, under Objects in the Database window.

8.  Click New on the Database window toolbar, just above the Objects pane.

The New Query dialog appears, as shown in Illustration 2.

Illustration 2: The New Query Dialog

9.  Ensuring that the Design View option is selected, click OK.

The Select Query dialog appears by default, with the Show Table dialog appearing in front, as shown in Illustration 3.

Illustration 3: The Select Query Dialog (Compressed View), with Show Table Dialog Foremost

10.  Select the following tables, highlighting each, and then clicking the Add button, to add each successively to the Select Query dialog.

  • Customers
  • Orders
  • Order Details
  • Products

11.  Click the Close button on the Show Table dialog to close it.

The Select Query dialog, upper portion, displays the newly added tables, appearing as shown in Illustration 4.

Illustration 4: The Select Query Dialog, Selected Tables (Compressed View)

We notice that the joins / relationships have been placed automatically. This is because the AutoJoin feature is enabled. (AutoJoin can be disabled by going to Tools -> Options, selecting the Tables/Queries tab, and unchecking the Enable AutoJoin checkbox).

We review the joins for correctness, as always, and find them to be adequate. Next, we select destination fields and, thus, determine the result datasets that the query will generate.

For each of the tables that follow, double-click the indicated fields to place it in the corresponding field of the matrix in the bottom half of the Select Query dialog.

12.  From the Customers table, select:

  • Customer ID
  • Company Name
  • City
  • Country

13.  From the Products table, select:

  • Product ID
  • Product Name

14.  From the Order table, select:

  • Shipped Date
  • Order Date

15.  From the Order Details table, select:

  • Unit Price
  • Quantity
  • Discount

The Select Query dialog displays the newly added tables and fields, appearing as partially shown in Illustration 5.

Illustration 5: The Select Query Dialog, Selected Tables and Fields (Partial View)

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM