BN.com Gift Guide

MCSD Training Guide: Microsoft Acess

Overview

MCSD Study Guide: Microsoft Access 97 provides the reader with all of the latest information needed to pass this exam. Combining the real-life knowledge of professionals with the expertise of MCSEs and MCSDs, this book contains important insider tips and notes on successfully passing the MCSD exam.
  • Offers complete coverage of the objectives for this component of the MCSD certification
  • A matrix guides readers ...
See more details below
Available through our Marketplace sellers.
Other sellers (Hardcover)
  • All (10) from $1.99   
  • New (2) from $39.99   
  • Used (8) from $1.99   
Close
Sort by
Page 1 of 1
Showing 1 – 1 of 2
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$39.99
Seller since 2008

Feedback rating:

(172)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
1562057715 BRAND NEW NEVER USED IN STOCK 125,000+ HAPPY CUSTOMERS SHIP EVERY DAY WITH FREE TRACKING NUMBER

Ships from: fallbrook, CA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing 1 – 1 of 2
Close
Sort by
Sending request ...

Overview

MCSD Study Guide: Microsoft Access 97 provides the reader with all of the latest information needed to pass this exam. Combining the real-life knowledge of professionals with the expertise of MCSEs and MCSDs, this book contains important insider tips and notes on successfully passing the MCSD exam.
  • Offers complete coverage of the objectives for this component of the MCSD certification
  • A matrix guides readers to exactly the information they need
  • Pre-chapter quizzes and post-chapter test questions help readers assess their understanding of the technology
Read More Show Less

Product Details

  • ISBN-13: 9781562057718
  • Publisher: New Riders
  • Publication date: 10/28/1997
  • Edition description: BK&CD-ROM
  • Edition number: 1
  • Pages: 750
  • Product dimensions: 7.65 (w) x 9.46 (h) x 2.00 (d)

Table of Contents








[Figures are not included in this sample chapter]


MCSD Training Guide: Microsoft Access


Contents



  • Introduction

    • Who Should Read This Book
    • How This Book Helps You
    • Understanding What the Microsoft Access for Windows 95 and the Microsoft Access
      Developer's Toolkit Exam (#70-69) Covers
    • Hardware and Software Needed
    • Tips for the Exam
    • New Riders Publishing




  • Chapter 1 - Working with Visual Basic for Applications



    • Storing Visual Basic Code in Modules
    • Using VBA Loop Statements
    • Understanding User-Defined Functions
    • Understanding Comparison, Logical, Concatenation, and Pattern-Matching Operators
    • Built-In Functions
    • Declaring Variables in Modules and Procedures
    • Declaring Arrays and Initializing Elements of Arrays
    • Declaring and Using Object Variables and Collections
    • Declaring Symbolic Constants
    • Lab Exercise
    • Review Questions




  • Chapter 2 - Database Design



    • Understanding the Concepts of Normalization
    • Understanding Cascade Update
    • Understanding Cascade Delete
    • Key Terms and Concepts
    • Lab Exercise
    • Review Questions




  • Chapter 3 - Microsoft Access SQL



    • Understanding the Types of Queries
    • Creating Common Queries
    • Creating Action Queries
    • Creating Union Queries
    • Key Terms and Concepts
    • Lab Exercise
    • Review Questions




  • Chapter 4 - Programming with Objects



    • Understanding Object Hierarchy
    • Understanding Objects and Collections
    • Referencing Objects
    • Using the !(Bang) and .(Dot) Identifiers
    • Declaring Object Variables
    • Creating an Instance of an Existing Object with the New Keyword
    • Understanding Properties and Methods
    • Key Terms and Concepts
    • Lab Exercises
    • Review Questions




  • Chapter 5 - Debugging and Error Handling



    • Using the Errors Collection and the Error Object to Trap Errors
    • Writing Error Handlers
    • Handling the Error
    • Debugging Code Samples
    • Using Debugging Tools
    • Using the Debug Window to Monitor the Value of a Variable
    • Key Terms and Concepts
    • Lab Exercises
    • Review Questions




  • Chapter 6 - Working with Sets of Records



    • Choosing the Appropriate Recordset Type
    • Creating Recordset Objects
    • Setting Recordset Properties
    • Locking Records with Recordsets
    • Manipulating Data by Using Recordset Methods
    • Working with QueryDef Objects
    • Working with the QueryDef SQL Property
    • Working with TableDef Objects
    • Using Workspace Transactions
    • Key Terms and Concepts
    • Lab Exercises
    • Review Questions




  • Chapter 7 - Working with Forms and Reports



    • Resolving Sort Order Conflicts with Underlying Queries
    • Setting Form and Report Properties
    • Setting Control Properties
    • Using Form Methods
    • Creating Form and Report Modules
    • Creating Event Procedures
    • Choosing Me or Screen
    • Understanding Form Event Order
    • Choosing the Appropriate Scope
    • Using Multiple Form Instances
    • Property Get, Property Set, and Property Let
    • Choosing Documents versus Forms or Reports
    • Lab Exercises
    • Review Questions




  • Chapter 8 - OLE Automation



    • Controlling Other Applications by Using OLE Automation
    • Controlling Microsoft Access from Other Applications by Using OLE Automation
    • Key Terms and Concepts
    • Lab Exercise
    • Review Questions




  • Chapter 9 - Custom Controls



    • Customizing OLE Controls
    • Setting Properties for Custom Controls
    • Key Terms and Concepts
    • Review Questions




  • Chapter 10 - Using Windows DLLs



    • Declaring Windows API Functions
    • Passing Arguments to the DLL Using ByVal and ByRef
    • Processing Strings Returned from DLLs
    • Key Terms and Concepts
    • Lab Exercises
    • Review Questions




  • Chapter 11 - Database Replication



    • Making the Most of Synchronization
    • Choosing the Appropriate Replication Method
    • Keeping Objects Local
    • Choosing the Appropriate Synchronization Topology
    • Scheduling Synchronization
    • Understanding Replicated Databases
    • Understanding the ReplicationID
    • Resolving Synchronization Issues
    • Key Terms and Concepts
    • Lab Exercises
    • Review Questions




  • Chapter 12 - Implementing Database Security



    • Choosing the Appropriate Level of Security
    • Implementing Basic User-Level Security
    • Choosing the Appropriate Permission Strategy
    • Setting Security Through Code
    • Analyzing Security Code
    • Encrypting a Database
    • Key Terms and Concepts
    • Lab Exercises
    • Review Questions




  • Chapter 13 - Client/Server Application Development

    • Choosing Access as a Client/Server Front End
    • Understanding Access in the Client/Server Architecture
    • Utilizing Open Database Connectivity
    • Setting Up the ODBC Connection
    • Working with SQL Pass-Through Queries
    • Understanding the Server and Access
    • Designing an Efficient Client/Server Application
    • Using the Jet Errors Collection
    • Lab Exercise
    • Review Questions




  • Chapter 14 - Improving Database Performance

    • Differentiating Between Single-Field and Multiple-Field Indexes
    • Optimizing Queries by Using Rushmore Technology
    • Restructuring Queries for Faster Execution
    • Optimizing Performance in Distributed Applications
    • Optimizing Performance for a Client/Server Application
    • Key Terms and Concepts
    • Review Questions




  • Chapter 15 - Distributing an Application

    • Preparing an Application for Distribution by Using the Setup Wizard
    • Choosing the Best Way to Distribute a Client/Server Application
    • Distributing OLE Custom Controls with an Application
    • Providing Online Help in a Microsoft Access Application
    • Key Terms and Concepts
    • Lab Exercise
    • Review Questions




  • Chapter 16 - Extending Microsoft Access

    • Implementing Error Handling in Add-Ins
    • Testing and Debugging Library Databases
    • Describing the Purpose of the USysRegInfo Table
    • Microsoft White Papers
    • Review Questions




  • A - Overview of the Certification Process
  • B - Study Tips

    • Pre-Testing Yourself
    • Hints and Tips for Doing Your Best on the Tests




  • C - What's on the CD-ROM

    • The TestPrep Test Engine
    • Exclusive Electronic Version of Text
    • Copyright Information and Disclaimer




  • D - All About TestPrep

    • Question Presentation
    • Scoring




  • Index



Read More Show Less

First Chapter

[Figures are not included in this sample chapter]

MCSD Training Guide: Microsoft Access

- 3 -
Microsoft Access SQL

By the end of this chapter, you will be able to execute the following test objectives:

  • Refer to objects using Access SQL

  • Use Access SQL to write common queries

  • Use Union queries

1. You are writing a query in SQL based on the Customers, Orders, and Employees tables. Both the Customers and Employees tables have fields called Name. How would you select the Name field from the Employees table in the Select clause so that Access would be able to differentiate between the two?

2. The ProductID field of the Orders table uses five- or seven-digit numbers to identify products. By using SQL, how would you search for ProductIds that begin with 23 and are followed by 3 digits?

3. You are in charge of sending out this year's holiday greeting. You would like to create one recordset that combines information from the Employees table and the Customers table. The two tables are compatible, (for example, they each track name, address, and so on, but they have different field names). What type of SQL statement could you use to combine the data from the two tables?

Answers are located at the end of the chapter...

Structured Query Language (SQL) is the language used to communicate with the Microsoft Jet Database Engine. The Query by Example grid (QBE) allows a user to create a query without writing a SQL statement. When a query is created using the QBE grid, Access generates the SQL statement.

Certain types of queries, however, cannot be created using the QBE griD. Union, Pass-Through, and Data-Definition queries must be written directly in SQL.

For the exam, you should be familiar with the language and syntax of SQL statements. One way to become familiar with SQL statements is to view a query that you created in the QBE grid in SQL view. From the View menu in a query, select SQL. This enables you to view the SQL statement Access generated for the query. You should also be very familiar with Union queries.

Understanding the Types of Queries

This chapter discusses several types of queries that can be created in Access. We will begin with the most common type of query: Select. Select queries select rows of data from tables and return them as a dynaset recordset. (The results of a query are always referred to as a dynaset.) We will also discuss action queries that update data (Update, Delete, Insert Into, and Select Into) and action queries that define data (Create Table, Constraint, Create Index, Alter Table, and Drop). And finally, we will look at Union queries, which cannot be created in the QBE grid; they can only be created using Access SQL.

Referring to Objects by Using Access SQL

When writing SQL statements in Access, you always have to refer to fields, tables, and queries. If the name does not contain any spaces or illegal characters, you can simply use the actual name. However, if there are spaces or illegal characters in the field, table, or query name, you must enclose the name in square brackets. Note the following examples: EmployeeID [Employee#] In addition, if the same field name is used in more than one table used in the query, you must precede the field name with the name of the table or query followed by a perioD. Following are two examples: Customer.CustomerID Order.CustomerID This enables Access to differentiate between the two fields.

Using General SQL Statement Syntax

The syntax of the SQL statement follows:


SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]} 
FROM tableexpression [, ...] [IN externaldatabase] 
[WHERE... ] 
[GROUP BY... ] 
[HAVING... ] 
[ORDER BY... ] 
[WITH OWNERACCESS OPTION]

Any arguments in square brackets are optional. All other arguments are requireD.

Creating Common Queries

Although common queries can be created using the QBE grid, you must be familiar with the language and syntax of SQL statements. For the exam, you will probably have to be able to select the correct SQL statement from four choices, and/or determine the results of a particular SQL statement. In this section, we go over the keywords of SQL statements, and the syntax required with each one.

TIP If you are not familiar with SQL, you may want to view the SQL window for queries that you have created using the QBE griD. This enables you to see the SQL statement that Access has generated from the information entered in the QBE griD.

As mentioned earlier, Select queries are the most common type of query used in Access. A Select query is used to choose specific columns (fields) and rows (records) from a particular recordset and to choose what order to put the records in. All queries begin with the basic syntax of Select queries; therefore this is the most important thing to learn in SQL before learning about action queries or Union queries.

Select Clause

The Select statement is used to determine which fields and which rows of records from the underlying recordset will be included in the dynaset. The basic syntax of the Select statement is as follows:


SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}

The Select clause and the From clause must be included in the Select statement. The Where and Order By clauses are optional.

The Select clause is used to specify which column should be included in the dynaset. This is the equivalent of adding the field name to the QBE griD.

To include more than one column in the output of the query, simply place a comma between each fielD. Note the following example:


SELECT[Customer #], LastName, FirstName

To include all fields from a particular table or query, use an asterisk (*):


SELECT *

Table 3. 1 lists the arguments for the Select clause. Table 3. 1

Select Clause Arguments

Argument Description
Predicate The predicate is an optional argument used to restrict the number of records returneD. The following keywords can be used as predicates: All, Distinct, DistinctRow, or Top. If no predicate is specified, the default is All. See the section "Using Predicates" for more information.
* The asterisk specifies that all fields from the table or tables are selecteD. This is used in the same way that the asterisk is used in the QBE griD.
Table This is a required argument, specifying the name of the table containing the fields from which records are selecteD. If the table name uses spaces or other illegal characters, you must enclose the name in square brackets.
Field1, Field2 These are the names of the fields to include in the dynaset. If the field name is included in more than one table, precede the field name with the table name and the dot (period) operator. If the field or table name contains any illegal characters, enclose the name in square brackets: [Customer Information].[First Name]
Alias1, Alias2 These are optional arguments that are used to rename the columns in the dynaset.
TableExpression This is the name of the table or tables containing the data you want to retrieve.
ExternalDatabase If the table or tables in the TableExpression are not contained in the current database, you must include the name of the database that contains the tables.

Remember, most of the above arguments are optional. The Select statement only has to contain the Select statement, the fields to include, and the table that contains those fields. Note the following example:


SELECT FirstName, LastName FROM Customers

Using Predicates

The predicate is an optional argument used to restrict the number of records returneD. Four valid predicate arguments exist in Access SQL. Table 3. 2 lists these arguments. Table 3. 2

Predicate Arguments

Predicate Argument Description
All This is the default predicate (assumed if no predicate is specified). The dynaset will include all rows that meet the specifications. This is the equivalent to setting both the UniqueValues and the UniqueRecords properties to No.
Distinct This predicate causes Access to eliminate duplicates in the dynaset, based on the columns selecteD. In other words, no two rows that contain exactly the same data in the fields specified in the SQL statement are duplicateD. This causes the dynaset to be read-only. This is the equivalent to setting the UniqueValues property to Yes in the QBE griD.
DistinctRow This predicate corresponds to the UniqueRecords property in the QBE griD. DistinctRow tells Access to eliminate any duplicates in the dynaset, based on all columns in the source tables, not just the columns in the recordset. It has no effect on single-table queries, or on multiple-table queries, where there is at least one column from each table included in the dynaset. Although it is not necessary on these types of queries, it will not affect the performance of the query. In these situations, DistinctRow has the same result as Distinct, yet the recordset is updateable. In most instances, DistinctRow is preferable to Distinct.
Top Returns the top n rows, or top n percent from a recordset. The top predicate should be used only when the OrderBy clause is useD. Otherwise, Access will simply return the first n records in the recordset. Because Nulls are considered the smallest value, they will be included in the dynaset. To eliminate Nulls from the resulting dynaset, be sure to use criteria to exclude them from the recordset. The Top predicate is unique to Access SQL. It is the equivalent of using the TopValues property in the QBE griD. Access processes this after all criteria and Order By clauses have been applieD.

TIP The Distinct predicate is not necessary if you have included the primary keys of each table in a query. If the primary keys are included, the records will automatically be unique. Not only is Distinct unnecessary, this predicate will make your query less efficient. Only use the Distinct predicate when necessary.

The following examples show the results of queries based on the Products table and the OrderDetails table from the Northwind database. The query returns the names of products from orders in which the quantity was greater than 50. Each query is identical, except for the predicate useD. Figure 3. 1 uses the All predicate, figure 3. 2 uses Distinct, figure 3. 3 uses DistinctRow, and figure 3. 4 uses Top. Notice that Distinct and DistinctRow each return 65 records (there are no duplicates). The dynaset resulting from the DistinctRow query is updatable, however, while the dynaset resulting from the Distinct query is not. Remember, you can always create these queries in the QBE grid and then view the SQL statement. This is an easy way to become more familiar with the syntax of SQL statements.

Figure 3. 1 Results of the Query Using the All predicate.

Figure 3. 2 Results of the Query Using the Distinct predicate.

Figure 3. 3 Results of the Query Using the DistinctRow predicate.

Figure 3. 4 Results of the Query Using the Top predicate.

From Clause

The From clause is used to specify which tables or queries you want to select records from. When more than one table or query is used, you must specify the tables that will be joineD.

You can temporarily rename (alias) a table using the As keyworD. Simply follow the name of the table with As aliasname. If the original table name is very long, an alias is sometimes created to make it easier to refer to the table in SQL. An alias must be created if you are using a self join.

Using Joins

Most queries are based on more than one table or query. In order to produce meaningful results in your dynaset, you must properly join the tables or queries by creating a relationship between them.

When using the QBE grid, any relationships that were established in the Relationship window will automatically be created when those tables are used in the query. However, when writing an SQL statement, you must join the tables in each query.

The join is created in the From clause. There are three join types: inner joins, outer joins, and self-joins.

Inner Joins

An inner join specifies that the query will return only records in which there is a match between both tables. If a customer is entered in the Customer table, for example, but has not yet placed an order (and therefore does not have an entry in the Order table), a query based on Customers and Orders using an inner join does not include that customer in its dynaset.

Outer Joins

An outer join is created when you need to include all records from one table, but from the related table need only those that include the matching recorD. For example, if a dynaset must include all customers (regardless of whether or not they have placed any orders) and the dates of any orders, an outer join is requireD. The outer join includes all customers and any order dates. The OrderDate field is Null for those customers who have not yet placed an order.

A left-outer join returns all records from the first (left) table, and only those from the second (right) table in which there is a match. In this case the left table is preserved, meaning that all of its records are returneD.

Conversely, a right-outer join returns all records from the second (right) table and only those from the first (left) in which there is a match. In this case, the right table is preserveD.

The following statement returns the name of all customers in the Northwind database, whether or not they have placed an order. If a customer has placed an order, the order date appears as well.


SELECT DISTINCTROW Customers.CompanyName, Orders.OrderDate 
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Figure 3. 5 shows the QBE grid view for the above Select statement.

Figure 3. 5 This query uses a left-outer join to list all customers in the Northwind database, regardless of whether they have placed an order.

A table that has been preserved cannot be a part of another relationship in a query. This situation results in the error message "Query contains ambiguous outer joins." If you need to include more than two tables in your query and have an outer join, you must first create the query with the outer join, and then use that query as the basis for another query.

Self Joins

A table sometimes needs to be joined to itself. For example, suppose the Employees table contained the EmployeeID for an employee's supervisor. In order to show employees and their supervisors' names, a self join would be needeD.

To create a self join you must create an alias of the table and then join the alias to the original table. To create an alias, use the keyword As after the table name in the join statement. In order to avoid confusion, it is usually a good idea to also create an alias for the field from the table alias that is used in the query.

The following SQL statement returns the first and last name of an employee and the last name of the employee's supervisor (Reports To field). An alias of the Employees table (Copy_of_Employees) is useD. In addition, an alias (Manager) has been created for the Last Name field from the Copy_of_Employees table.


SELECT DISTINCTROW Employees.FirstName, Employees.LastName, Employees_Alias.LastName AS [Reports To] 
FROM Employees INNER JOIN Employees AS Employees_Alias ON Employees.EmployeeID = Employees_Alias.ReportsTo;

Where Clause

The Where clause is an optional clause that is used to restrict the rows returned in the dynaset. This clause corresponds to the criteria row in the QBE griD. If a Where clause is not included, all records are returneD. A field referred to in the Where clause does not need to be included in the dynaset. (Using the QBE grid, you would simply remove the check from the Show box.) The Where clause can contain up to 40 columns or expressions.

The syntax of the Where clause is as follows:


WHERE Expression1[And/Or Expression2{,...]]

When referring to a field name that includes spaces or punctuation characters, enclose the field name in brackets. When referring to dates, you must have a pound sign (#) at the beginning and end of the date, and the date must be in standard U.S. format. String expressions must be enclosed in quotation marks.

Using Like in Criteria

There most likely will be a question on the exam using Like and a wildcard in a Where expression. Be sure to understand the wildcard characters and the records that each returns.

Like is used to compare a string expression to a pattern in an SQL expression. Like is often used with wildcard characters in a Where expression. The wildcard character is used to replace certain parts of the expression. Table 3. 3 lists the valid wildcard characters used in Access SQL. Table 3. 3

Wildcard Characters

Wildcard Character Description
? Any single character
* Zero or more characters
# Any single digit (0-9)
[charlist] Any single character in charlist
[!charlist] Any single character not in charlist

The [charlist] argument can contain a list of characters. For example Like "F[ia]t" would return Fit or Fat. The following statement will return all records that have data in the CompanyName field which begins with "S," followed by either "A" or "E," and then followed by any string:


SELECT DISTINCTROW Customers.CompanyName 
FROM Customers 
WHERE (((Customers.CompanyName) Like "S[ae]*"));

Figure 3. 6 shows the dynaset returned by the query created in the above SQL statement.

Figure 3. 6 A query that will return all records with data in the CompanyName field which begins with "S," followed by either "A" or "E," and then followed by any string.

The following SQL statement returns all records in the Clients table that have data beginning with "S" in the CompanyName fielD.


SELECT DISTINCTROW Customers.CompanyName 
FROM Customers 
WHERE (((Customers.CompanyName) Like "S*"))

;

Figure 3. 7 shows the dynaset returned by the query created in the above statement.

Order By Clause

The Order By clause is optional. It is used to sort the records in the dynaset by one or more columns. The keyword Asc (ascending) or Desc (descending) is used with this clause. The Order By clause corresponds to the Sort line in the QBE griD. The precedence in sorting is left to right, as it is in the QBE griD.

The field used in the Order By clause does not have to be included in the Select clause. This would be the equivalent of removing the check from the Show box in the QBE griD. You cannot use Memo or OLE-object type fields in an Order By clause.

Figure 3. 7 A query that returns all records in the Clients table that have data beginning with "S" in the CompanyName fielD.

Aggregate Queries

Aggregate queries (sometimes referred to as Totals queries) are used when you need to perform a calculation on a group of records. If you need to determine the average price of a product in several categories, for example, an Aggregate query can be useD.

Aggregate queries use two special arguments: Group By and Having. To create an Aggregate query in the QBE grid, choose Show Totals from the View menu. This adds a Total line to the griD. You then must enter an argument in the Total row for each field in the query.

Group By Clause

The Group By clause is an optional clause used in Aggregate queries. It is used to define the rows that should be grouped together in order to perform the Aggregate function. The expressions in the Group By clause can reference columns in a table, a calculated expression, or a constant.

The following SQL statement creates an Aggregate query in the Northwind database. This query determines the average price of products in each category.


SELECT DISTINCTROW Categories.CategoryName, Avg(Products.UnitPrice) AS AvgOfUnitPrice 
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID 
GROUP BY Categories.CategoryName;

Figure 3. 8 shows the results of the query created in the above SQL statement.

Figure 3. 8 A query that determines the average price of products in each category.

You can use more than one field in the Group By expression. The groups are defined from left to right and are automatically placed in ascending order.

The following SQL statements create an Aggregate query, which uses two fields in the Group By clause. This is the same query created in the above example, with one more Group By fielD. This query shows the average price of products in each category, grouped by supplier.


SELECT DISTINCTROW Categories.CategoryName, Products.SupplierID, Avg(Products.UnitPrice) AS AvgOfUnitPrice 
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID 
GROUP BY Categories.CategoryName, Products.SupplierID;

Figure 3. 9 shows the results of the query created above. Notice that there are now 49 records, as opposed to eight records from the first query. Grouping by more than one field will increase the number of records returned in the dynaset.

Figure 3. 9 A query that shows the average price of products in each category, grouped by supplier.

Having Clause

The Having clause is an optional clause used in Aggregate queries. Like the Where clause, it is used to enter criteria that will restrict the records returned by the query. Criteria entered in the Where clause is applied before grouping the records. Criteria contained in the Having clause is applied after grouping the records. Therefore, the Having clause is used to filter records based on the aggregate calculation, rather than filtering individual records before applying the criteriA.

When you use the QBE grid, any criteria placed in a field that uses Group By in the total row is translated into a Having clause in the SQL statement. Any criteria entered in a field that uses "Where" in the total row is translated into a Where clause in the SQL statement.

The following SQL statement creates a query that uses the Having clause. This query returns records whose order totals (sums of product totals) were over $100.00. There are 793 records in the dynaset.


SELECT DISTINCTROW Sum([UnitPrice]*[Quantity]) AS ItemTotal, Orders.OrderID 
FROM (Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID 
GROUP BY Orders.OrderID 
HAVING (((Sum([UnitPrice]*[Quantity]))>100));

Figure 3. 10 shows the QBE view of the above statement.

Figure 3. 10 A query that returns records whose order totals (sums of product totals) were over $100.00.

Figure 3. 11 shows the QBE grid for a similar query. This query, however, applies the criteria before performing the Aggregate calculation. In this case, only ProductTotals greater than $100.00 are included in the dynaset. This dynaset includes only 782 records.

Figure 3. 11 Aggregate query that applies the criteria before performing aggregate function.

The following statement is the SQL statement for the above query.


SELECT DISTINCTROW Sum([UnitPrice]*[Quantity]) AS ItemTotal, Orders.OrderID 
FROM (Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID 
WHERE ((([UnitPrice]*[Quantity])>100)) 
GROUP BY Orders.OrderID;

Notice the third column that is created in Figure 3. 11.

Allowing With Owner Access

The With Owner Access Option declaration is an optional declaration used in a multi-user environment with secure workgroups. It can be used only with a saved query. It has no effect when used in a SQL statement in a form's RecordSource property.

With Owner Access Option enables the users of the query to inherit your security rights while running the query. If a user does not have the necessary security permission for one of the underlying tables, you must use this declaration so that the user can run the query.

This declaration is equivalent to setting the RunPermissions property to Owner's in the QBE griD. If you leave out this declaration, it is equivalent to setting this property to User's.

For more information about multi-user environments and security, please see Chapter 12, "Implementing Database Security."

Using Parameters in Queries

You can specify parameters in a SQL statement, just as you can in the QBE griD. Parameters are used to enable the user to enter criteria at runtime.

The Parameters declaration is used at the beginning of a query to specify a parameter in SQL. If more than one parameter is required, be sure to separate the parameters by commas. A semi-colon is used to separate all of the parameters from the rest of the statement.

The syntax for a Parameters declaration follows:


PARAMETERS parameter1 datatype1 [,paramatr2 datatype2 [,...]]: 
sqlstatement

The following statement enables the user to enter the order number and then returns the specified information from the Orders and Order Details tables.


PARAMETERS [Enter Order #] Long; 
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Orders.OrderDate, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity 
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID 
WHERE (((Orders.OrderID)=[Enter Order #]));

The preceding query prompts the user to "Enter the Order #" when the query is run.

Specifying parameters in queries allows the queries to be much more flexible. The criteria can be changed each time the query is run, without changing the structure of the query.

Creating Action Queries

Action queries begin like Select queries, in that they select particular columns (fields) and rows (records) from a table or tables. Action queries then add an additional step of performing some type of action on those records. There are two different types of Action queries: those that update data and those that define new datA.

When you run Action queries, the records are immediately changed, but a result set is not generateD. If you want to know which records will be changed, create and run a Select query, which uses the same criteria as the Update query.

Access SQL has four commands that allow you to update datA. These commands can also be entered in the QBE grid by choosing them from the Query menu. Table 3. 4 lists the four Update commands and their counterparts from the Query menu. Table 3. 4

Access SQL Update Data Commands

SQL Statement QBE Grid (Query Menu )
Update Update
Delete Delete
Insert Into Append
Select Into Make Table

The following sections cover each type of SQL statement commanD.

Update Statement

Update queries are used to change values in one or more columns in a table or query. If you are updating values in a query, the query must be updateable. The Update statement is used to create an Update query.

The syntax of the Update statement is as follows:


UPDATE table 
SET newvalue 
WHERE criteria;

Table 3. 5 explains each argument of the Update statement. Table 3. 5

Arguments of the Update Statement

Part Description
Table The name of the table whose data you want to modify.
NewValue Expression that determines the value to be inserted into a particular field in the updated records.
Criteria Expression that determines which records will be updateD. Only records that satisfy the expression are updateD.

The following statement increases the prices of all products in the beverage category (CategoryID 1) by five percent:


UPDATE DISTINCTROW Categories INNER JOIN Products  ON Categories.CategoryID = Products.CategoryID  SET Products.UnitPrice = [UnitPrice]*([UnitPrice]*0.05) 
WHERE (((Products.CategoryID)=1));

Delete Statement

The Delete statement is used to delete rows from tables. The syntax of the Delete statement is:


DELETE [table.*] 
FROM table 
WHERE criteria

Table 3. 6 explains each argument of the Delete statement. Table 3. 6

Arguments of the Delete Statement

Part Description
Table.* This argument is optional. The name of the table from which records are deleteD.
Table The name of the table from which records are deleteD.
Criteria An expression that determines which records to delete. Only records that meet the criteria will be deleteD.

The following statement deletes from the Northwind database all customers who have never placed an order.


DELETE DISTINCTROW Customers.CustomerID, Orders.OrderID  
FROM Customers 
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID  
WHERE (((Orders.OrderID) Is Null));

Access does not allow you to delete records if doing so violates the rules of referential integrity. If you tried to delete a customer who had a related record in the Orders table, for example, you would receive an error message unless you had turned on Cascade Delete for these tables. (For more information about Cascade Updates and Deletes, please see Chapter 2, "Database Design.")

If there is more than one table used in the Select statement, you must use the asterisk (*) to select all of the fields that you would like to delete. Otherwise, Access will display the error message "Must Specify Tables to Delete From."

Insert Into Statement

The Insert Into clause is used to copy rows from one table or query into another query. It can also be used to add a single row to a table using a list of values. The Insert Into clause is the equivalent of choosing Append from the Query menu in the QBE griD.

EXAM TIPBecause the Insert Into statement is considered to be one of the "Commonly Used Queries" it may well be included on the exam!

To use the Insert Into clause to add a single row to a table or query using a list of values, use the following syntax:


INSERT INTO target [(field1[, field2[, ...]])] 
VALUES (value1[, value2[, ...])

If the column references are omitted in the Insert Into clause, you must include a value for each column in the target table, and they must be in the exact order that they appear in the table definition. If you do include column references, make sure that you include the primary key and any other required fields.

To use the Insert Into clause to add multiple rows to a table based on criteria, use the following syntax:


INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])] 
SELECT [source.]field1[, field2[, ...] 
FROM tableexpression

Table 3. 7 explains each argument used in the Insert Into statement. Table 3. 7

Arguments in the Insert Into Clause

Part Description
Target The name of the table or query to append records to.
ExternalDatabase The path to an external database. For a description of the path, see the In clause.
Source The name of the table or query to copy records from.
Field1, Field2 Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.
TableExpression The name of the table or tables from which records are inserteD. This argument can be a single table name or a compound resulting from an inner join, left-outer join, or right-outer join operation, or a saved query.
Value1, Value2 The values to insert into the specific fields of the new recorD. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in double quotation marks (" ").

Any Select statement that produces a recordset can be used with the Insert Into statement. This includes Group By clauses, joins, Union operators, and subqueries.

The following statement appends records from a table called MarketingList to the existing Customers table. Only those customers from the U.S. are addeD.


INSERT INTO Customers (CustomerID, CompanyName, Address, City, Region, PostalCode, Country ) 
SELECT DISTINCTROW MarketingList.CustomerNumber, MarketingList.Company, MarketingList.Address, MarketingList.City, MarketingList.State, MarketingList.Zip, MarketingList.Country 
FROM MarketingList 
WHERE (((MarketingList.Country)="USA"));

Select Into Statement

The Select Into statement is very much like the Insert Into statement. The Select Into statement creates a new table and appends the data, rather than appending the data to an existing table. The syntax of the Select Into is as follows:


SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase] FROM source

Table 3. 8 lists the parts of the Select Into statement. Table 3. 8

Parts of the Select Into Statement

Part Description
Field1, Field2 The names of the fields to be copied into the new table.
NewTable The name of the table to be createD. (Must follow standard naming conventions.) If NewTable is the same as the name of an existing table, a trappable error will result.
ExternalDatabase The path to an external database.
Source The name of the existing table from which records are selecteD. This can be a single or multiple tables or a query.

The fields in the new table inherit the data type and field size of the fields from the original table. However, no other field or table properties (such as default values, validation rules, and so on) are transferreD.

Using Subqueries

Subqueries enable you to embed one Select statement into another. Subqueries can be nested into Select Into, Insert Into, Delete, or Update statements or inside another subquery. They are used to filter the query based on the values in another query. Subqueries are placed in the Where clause of an SQL Select statement. You may nest several subqueries into one Select statement. There is no documented limit to the number of subqueries that can be nesteD.

There are three forms of subqueries: comparison [ANY | ALL | SOME] (sqlstatement)

expression [NOT] IN (sqlstatement) [NOT] EXISTS (sqlstatement) Table 3. 9 explains each of the parts of a subquery. Table 3. 9

Parts of a Subquery

Part Description
comparison An expression and a comparison operator that compares the expression with the results of the subquery.
expression An expression for which the result set of the subquery is searcheD.
sqlstatement A Select statement that follows the same format and rules of any other Select statement. It must be enclosed in parentheses.

A subquery may be used instead of an expression in the field list of a Select statement or in a Where or Having clause. In a subquery, the Select statement provides a set of specific values to evaluate in the Where or Having clause.

To use a subquery in the QBE grid, place the Select statement in the Criteria or Field cell of a query.

The following statement finds all customers who have placed an order in January of 1995 in the Northwind database.


SELECT ContactName, CompanyName, ContactTitle, Phone  FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate BETWEEN #01/1/95# AND #01/31/95#);

Comparison Subqueries: Using ANY, ALL, SOME

A subquery can also be used to compare a value against rows in another query. This type of query also returns a single column. This form of the subquery simply uses another table or query in the WHERE clause. For example, the following SQL statements returns the ProductID of items from the Order Details table which are more expensive than Ikura in the products table.


SELECT * FROM Products 
WHERE UnitPrice > ANY 
(SELECT UnitPrice FROM [Order Details] 
WHERE Quantity >= 50);

Expression Subqueries: Using IN and NOT IN

The In statement is used to retrieve only those records in the main query that have a match in the subquery. Using the In statements causes Access to check the value of a single column against the values in another table or query. This type of query only returns a single column. The following statement returns a list of all of the products that have been ordered in quantities of 40 or more.


SELECT Products.*, * 
FROM Products 
WHERE (((Products.ProductID) In (SELECT ProductID FROM [Order Details] 
WHERE Discount >= .15)));

Exists Subqueries: Using Exists and Not Exists

Using Exists and Not Exists in a subquery allows you to check for the existence of a particular value in another table or query. The following SQL statement returns any items from the Order Details table that have been discontinueD.


SELECT DISTINCTROW Products.ProductID, Products.Discontinued 
FROM Products 
WHERE ((Not (Products.ProductID)=Exists (Select * from [Order Details] Where ProductID = Products.ProductID)) AND ((Products.Discontinued)=Yes));

Creating Union Queries

The Union operator is used to join together two or more queries that are compatible, but not necessarily relateD. If you wanted to send a Holiday letter to both employees and customers, for example, you could use a Union query to join these two tables into one recordset. Union queries can be created only in SQL; there is no equivalent in the QBE griD. The resulting recordset is always read-only.

When referring to an existing query in a Union query, you must enclose the name of the query in square brackets. The basic syntax of the Union query is as follows:


[TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] query
n [ ... ]]

query1 can represent a query or a table. If it represents a table, you must precede the name with Table.

The columns from each table or query are matched by their positions in the Select statement, not by their names. Therefore, all queries in a Union operation must request the same number of fields; however, the fields don't have to be of the same size or data type.

Using the All Predicate

Unless you specify the All predicate, no duplicate records are returned when you use a Union operation. Using the All predicate also makes the query run more quickly, even if there are no duplicates. This is because Access does not have to compare the recordsets to check for duplicates.

Using the TABLE Option

If you want to include all fields from a table or query you can use the TABLE option. This is the equivalent to using Select * From table-or-query. These statements can be used interchangeably. The basic syntax follows:


TABLE table-or-query

Sorting in Union Queries

The Order By clause can only be used in the last Select statement. Although each Select statement in a Union query can have an Order By clause, Access ignores all but the last one. If the column names differ, however (such as in EmployeeName and CustomerName), you must reference the name assigned to the column in the first Select statement.

When you combine fields with different names, Access uses the column name from the first query. If the columns have different data types, Access converts the column to a single data type that is compatible with both columns. Text combined with a number produces a text column, and two number columns will always use the larger data type of the two. Memo and OLE fields cannot be used in Union queries.

The following statement is used to combine records from the Employees table and the Customers table. Because the Customer table uses ContactName, which includes the contact's first and last name, the FirstName and LastName fields of the Employees table are combined into the field name. Notice that in order to sort by the Name field, it must be included in the last Select statement, but it references the Name field, which is created in the first Select statement.


SELECT FirstName & " " & LastName as Name, Address, City, Region, PostalCode, Country FROM Employees 
UNION 
SELECT ContactName,Address, City, Region, PostalCode, Country 
FROM Customers 
Order By Name

Figure 3. 12 shows the dynaset that is created by the preceding Union query.

Figure 3. 12 A Union query that combines records from the Employees table and the Customers table.

Key Terms and Concepts

Table 3. 10 identifies key terms from this chapter. Review the key terms and make sure that you understand each term for the exam. Table 3. 10

Key Terms: Microsost Access SQL

Term Covered in Section...
Select Clause "Creating Common Queries"
From Clause "Creating Common Queries"
Where Clause "Creating Common Queries"
Predicates "Using Predicates"
Distinct "Using Predicates"
DistinctRow "Using Predicates"
Top "Using Predicates"
Inner Join "Using Joins"
Outer Join "Using Joins"
Self Join "Using Joins"
Like "Using Like in Criteria"
Group By "Aggregate Queries"
Having Clause "Aggregate Queries"
With Owner Access "Allowing With Owner Access"
Parameter "Using Parameters in Queries"
Update "Creating Action Queries"
Delete "Creating Action Queries"
Insert Into "Creating Action Queries"
Select Into "Creating Action Queries"
Union Queries "Creating Action Queries"

Lab Exercise

This exercise provides practice for you to create queries using SQL statements. To complete the exercise, you must be able to create queries using outer and self joins. In addition, you must be able to create a Union query.

Exercise 3. 1:  Creating Queries Using SQL Statements 

Objectives:

Create a query based on an outer join.

Create a query based on a self join. Create a Union query.

Time Estimate: 30 minutes
Steps:

Using the LabThree.mdb, write SQL statements that will accomplish the following:

1. Create a dynaset that shows all customers (CustomerID) and the dates of the last orders they have placeD. Make sure that you also include customers who have not yet placed an order.

2. Using a self join, create a dynaset that shows each employee's name and department, and the name of the person that each reports to. (ReportTo, not the ReportsTo #).

3. Create a Union query that joins the name and address information from the Employees and Customers tables.

Comments:

Although the first two queries can be created using the QBE grid, this will not give you any practice in creating SQL statements! Try to create these directly from a SQL statement.

Reading Reference:

For more information to help you complete these lab exercises, please refer to the sections "Creating Common Queries" and "Creating Union Queries."

Review Questions

1. Which of the following statements correctly include Customer# (from the Customers table) in the resulting dynaset? Choose all that apply.

A. SELECT Customers.Customer# FROM Customers
B. SELECT Customer# FROM Customers
C. SELECT Customers.[Customer#] FROM Customers
D. SELECT [Customer#] FROM Customers

2. Which of the following Select statements correctly creates an alias of Name for the ClientName field?

A. SELECT Customers.ClientName ALIAS AS "NAME"
B. SELECT Customers.ClientName ALIAS AS NAME
C. SELECT Customers.ClientName AS Name
D. SELECT ALIAS ClientName of Customers.ClientName

3. If no predicate is indicated in the Select statement, which predicate is returned?

A. All
B. Distinct
C. DistinctRow
D. End

4. Which of the following statements will return all customer numbers from the customer table, and the OrderIDs of those customers who have placed orders?

A. SELECT DISTINCTROW Customers.CustomerID, Orders.OrderID Right JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

B. SELECT DISTINCTROW Customers.CustomerID, Orders.OrderID LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

C. SELECT DISTINCTROW Customers.CustomerID, Orders.OrderID Right OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

D. SELECT DISTINCTROW Customers.CustomerID, Orders.OrderID LEFT OUTER JOIN Orders ON Cus tomers.CustomerID = Orders.CustomerID;

5. Which of the following statements creates a self join in the Employees table?

A. SELECT DISTINCTROW Employees.FirstName, Employees.LastName, Employees2. LastName AS [Reports To] FROM Employees SELF JOIN Employees AS Employees2 ON Employees.EmployeeID = Employees2. ReportsTo;

B. SELECT DISTINCTROW Employees.FirstName, Employees.LastName, ReportsTo SELF JOIN ReportsTo;

C. SELECT DISTINCTROW Employees.FirstName, Employees.LastName, ReportsTo INNER JOIN ReportsTo;

D. SELECT DISTINCTROW Employees.FirstName, Employees.LastName, Employees2. LastName AS [Reports To] FROM Employees INNER JOIN Employees AS Employees2 ON Employees.EmployeeID = Employees2. ReportsTo;

6. A query is created to find last names in the LastName column of the Customer table that match the following condition: S[ma]*y. Which of the following names would be included? Select all that apply.

A. Smith
B. Sandy
C. Smythe
D. Salamander

7. Which of the following statements will show the average price of products in each category?

A. SELECT DISTINCTROW Categories.CategoryName, Avg(Products.UnitPrice) AS AvgOfUnitPrice FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID GROUP BY Categories.CategoryName;

B. SELECT DISTINCTROW,GROUP BY Categories.CategoryName Categories.CategoryName, Avg(Products.UnitPrice) AS AvgOfUnitPrice FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID;

C. SELECT DISTINCTROW Categories.CategoryName, Avg(Products.UnitPrice) AS AvgOfUnitPrice FROM Categories INNER JOIN GROUP BY Categories.CategoryName Products ON Categories.CategoryID = Products.CategoryID;

D. SELECT DISTINCTROW,GROUP BY Categories.CategoryName Categories.CategoryName, Avg(Products.UnitPrice) AS AvgOfUnitPrice FROM Categories OUTER JOIN Products ON Categories.CategoryID = Products.CategoryID;

8. Assume that the field ProductTotal is a calculated expression based on [UnitPrice] * [Quantity]]. Which of the following statements will only include orders which have a total of over $500?

A. SELECT DISTINCTROW Orders.OrderID, Sum([UnitPrice]*[Quantity]) AS ProductTotal FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE ((([Order Details].UnitPrice)>500)) GROUP BY Orders.OrderID;

B. SELECT DISTINCTROW Orders.OrderID, Sum([UnitPrice]*[Quantity]) AS ProductTotal FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE ((([Order Details].UnitPrice)>500)) GROUP BY Orders.ProductTotal;

C. SELECT DISTINCTROW Orders.OrderID, Sum([UnitPrice]*[Quantity]) AS ProductTotal FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.ProductTotal HAVING (((Sum([UnitPrice]*[Quantity]))>500));

D. SELECT DISTINCTROW Orders.OrderID, Sum([UnitPrice]*[Quantity]) AS ProductTotal FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.OrderID HAVING (((Sum([UnitPrice]*[Quantity]))>500));

9. The Order Information form is based on a query. When you open the form, you need to be able to find a specific order number by entering the criteria of OrderDate (Date/Time field) and CustomerID (text field). Which of the following SQL statements would enable the user to enter the CustomerID and the OrderDate?

A. PARAMETERS [Enter Customer ID]as Text, [Enter Order Date] as DateTime; SELECT DISTINCTROW Customers.CustomerID, Orders.OrderID, Orders.OrderDate, Orders.EmployeeID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (((Customers.CustomerID)=[Enter Customer ID]) AND ((Orders.OrderDate)=[Enter Order Date]));

B. PARAMETERS [Enter Customer ID] Text, [Enter Order Date] DateTime; SELECT DISTINCTROW Customers.CustomerID, Orders.OrderID, Orders.OrderDate, Orders.EmployeeID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (((Customers.CustomerID)=[Enter Customer ID]) AND ((Orders.OrderDate)=[Enter Order Date]));

C. SELECT DISTINCTROW Customers.CustomerID, Orders.OrderID, Orders.OrderDate, Orders.EmployeeID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (((Customers.CustomerID)=[Enter Customer ID]) AND ((Orders.OrderDate)=[Enter Order Date])); PARAMETERS [Enter Customer ID] Text, [Enter Order Date] DateTime;

D. SELECT DISTINCTROW Customers.CustomerID, Orders.OrderID, Orders.OrderDate, Orders.EmployeeID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (((Customers.CustomerID)=[Enter Customer ID]) AND ((Orders.OrderDate)=[Enter Order Date])); PARAMETERS [Enter Customer ID] as Text, [Enter Order Date] as DateTime;

10. Which of the following SQL statements would find all records that have the data "UK" in the country field and update that data to "United Kingdom"?

A. UPDATE DISTINCTROW Customers SET Customers.Country = "United Kingdom" WHERE (((Customers.Country)="UK"));

B. Select DISTINCTROW Customers WHERE (((Customers.Country)="UK")) UPDATE Customers.Country = "United Kingdom";

C. UPDATE DISTINCTROW Customers.Country = "United Kingdom" WHERE (((Customers.Country)="UK"));

D. UPDATE DISTINCTROW Customers ALTER Customers.Country = "United Kingdom" WHERE (((Customers.Country)="UK"));

11. Which of the following statements deletes those customers who have not yet placed an order? (There is a one-to-many relationship between customers and orders; Cascade Delete has not been set.)

A. DELETE DISTINCTROW Customers.*, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (((Orders.OrderID) Is Null));

B. DELETE DISTINCTROW Customers.*, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID <> Orders.CustomerID

C. DELETE DISTINCTROW Customers.CustomerID, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (((Orders.OrderID) Is Null));

D. DELETE DISTINCTROW Customers.*, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (((Orders.OrderID) Is Null));

12. Which of the following SQL statements copies records of customers in the USA from Customers, creates a new table (Table1), and copies these records into Table1 without including the Country field in the new table?

A. SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName, Customers.ContactTitle, Customers.Address, Customers.City, Customers.Region,Customers.Country INTO [Table 1] FROM Customers WHERE (((Customers.Country)="USA"));

B. SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName, Customers.ContactTitle, Customers.Address, Customers.City, Customers.Region MAKETABLE [Table 1] FROM Customers WHERE (((Customers.Country)="USA"));

C. SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName, Customers.ContactTitle, Customers.Address, Customers.City, Customers.Region INTO [Table 1] FROM Customers WHERE (((Customers.Country)="USA"));

D. SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName, Customers.ContactTitle, Customers.Address, Customers.City, Customers.Region, Customers.Country, MAKETABLE [Table 1] FROM Customers WHERE (((Customers.Country)="USA"));

  1. 13. Which of the following SQL statements is used to select all records from Table1 and append them to Table2?

A. INSERT INTO Table2 SELECT Table1.* FROM Table1;

B. APPEND INTO Table2 SELECT Table1.* FROM Table1;

C. INSERT INTO Table2 SELECT FROM Table1.* FROM Table1;

D. UPDATE Table2 SELECT Table1.* FROM Table1;

14. You need to create a Union query that joins similar information from two different tables. The data should be sorted by LastName, and there should not be any duplicates. Which of the following SQL statements accomplishes this?

A. SELECT FirstName & " "& LastName as Name, Address, City, Region, PostalCode, Country FROM Employees UNION SELECT ContactName,Address, City, Region, PostalCode, Country FROM Customers ORDER BY LastName;

B. SELECT FirstName & " " & LastName as Name, Address, City, Region, PostalCode, Country FROM Employees ORDER BY LastName UNION SELECT ContactName,Address, City, Region, PostalCode, Country FROM Customers;

C. SELECT FirstName & " " & LastName as Name, Address, City, Region, PostalCode, Country FROM Employees UNION JOIN SELECT ContactName,Address, City, Region, PostalCode, Country FROM Customers ORDER BY LastName;

D. SELECT ALL FirstName & " " & LastName as Name, Address, City, Region, PostalCode, Country FROM Employees UNION SELECT ContactName,Address, City, Region, PostalCode, Country FROM Customers ORDER BY LastName;

Answers to Review Questions

1. C and D are correct. When a field name contains a space or other illegal characters (such as an asterisk) you must enclose it in brackets. The name of the table is optional in this statement. See the section "Referring to Objects by Using Access SQL" for more information.

2. C is correct. The keyword As is used to create an alias name. See the section "From Clause" for more information on this topiC.

3. A is correct. ALL is the default predicate. For more information, see the section "Creating Common Queries."

4. B is correct. For more information, see the section "Using Joins."

5. D is correct. For more information, see the section "Self Joins."

6. B is correct. The first letter must be "S," followed by either an "m" or an "a," and the last letter must be a "y." See the section "Using Like in Criteria" for more information.

7. A is correct. The Group By clause is placed at the end of the Select statement. For more information see the section "Group By Clause."

8. D is correct. The Having clause applies the criteria after the aggregate function has been performed. See the section "Having Clause" for more information.

9. B is correct. The Parameters statement must come at the beginning of the SQL statement. The parameter is followed by the datatype; the word "as" is not useD. For more information, see the section "Using Parameters in Queries."

10. A is correct. The words "update" and "set" must be used in an update query. For more information, see the section "Update Statement."

11. D is correct. For more information, see the section "Delete Statement."

12. C is correct. The Select Into statement is used to make a new table and copy records into it. For more information, see the section "Select Into Statement."

13. A is correct. The Insert Into statement is used to append records from one table into another. For more information, see the section "Insert Into Statement."

14. A is correct. Unless the All predicate is used, a Union query does not return duplicate records. For more information, see the section "Creating Union Queries."

A. RegisterDatabase
B. HelpContextID
C. HelpFile
D. HelpDatabase

5. What is the property that determines the text displayed when the mouse is over a control?

A. Pointer
B. ControlTip
C. ControlPointer
D. MouseTip

6. What's This information displays from where?

A. The compiled Help file
B. The What's This text defined on the control's property
C. The ControlTipText
D. The ToolTip description

7. What is the HelpContextID property number indicating the entire form?

A. 0
B. 1
C. 2
D. 255

  1. 8. For the best results, Microsoft recommends placing the Help file in what directory?:

A. A Help subdirectory
B. The WINDOWS directory
C. The same directory as the application
D. The root directory

Review Answers

1. A, B, and C are correct. Typical, Compact and Custom are legitimate types of installs. Please refer to the section "Preparing an Application for Distribution by Using the Setup Wizard" for more information.

2. B is correct. Data Source Name is used to refer to ODBC data sources. Please refer to the section "Choosing the Best Way to Distribute a Client/Server Application" for more information.

3. C is correct. OCX extensions identify Custom Controls. Please refer to the section "Distributing OLE Custom Controls with an Application" for more information.

4. B and C are correct. HelpContextID and HelpFile are two properties used to attach Help files to a database. Please refer to the section "Providing Online Help in a Microsoft Access Application" for more information.

5. B is correct. ControlTip is the property that determines the text displayed when the mouse is over a control. Please refer to the section "Providing Online Help in a Microsoft Access Application" for more information.

6. A is correct. The compiled Help file provides the What's This information. Please refer to the section "Providing Online Help in a Microsoft Access Application" for more information.

7. A is correct. Use the HelpContextID number 0 to indicate the entire form. Please refer to the section "Providing Online Help in a Microsoft Access Application" for more information.

8. C is correct. Microsoft's recommendation is for you to place Help files in the same directory as the application. Please refer to the section "Providing Online Help in a Microsoft Access Application" for more information.


Answers to Test Yourself Questions at Beginning of Chapter...

1. Wzstp70.mda is the file constituting the Setup Wizard for Access for Windows 95. See the section "Preparing an Application for Distribution by Using the Setup Wizard. "

2. RegisterDatabase is the method by which you specify database connection information. For more information, see the section "Choosing the Best Way to Distribute a Client/Server Application."

3. OCX is the default extension for Custom Controls. For more information, see the section "Distributing OLE Custom Controls with an Application."

4. 255 is the highest number of characters for ControlTipText. For more information, see the section "Providing Online Help in a Microsoft Access Application."

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)