# Categories

Description:
Soft drinks, coffees, teas, beers, and ales


Description:
Sweet and savory sauces, relishes, spreads, and seasonings


# Customers

Address:
Carrera 22 con Ave. Carlos Soublette #8-35


Address:
Carrera 52 con Ave. Bolívar #65-98 Llano Largo


# Employees

Notes:
Education includes a BA in psychology from Colorado State University.  She also completed "The Art of the Cold Call."  Nancy is a member of Toastmasters International.


Notes:
Andrew received his BTS commercial and a Ph.D. in international marketing from the University of Dallas.  He is fluent in French and Italian and reads German.  He joined the company as a sales representative, was promoted to sales manager and was then named vice president of sales.  Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.


Notes:
Janet has a BS degree in chemistry from Boston College).  She has also completed a certificate program in food retailing management.  Janet was hired as a sales associate and was promoted to sales representative.


Notes:
Margaret holds a BA in English literature from Concordia College and an MA from the American Institute of Culinary Arts. She was temporarily assigned to the London office before returning to her permanent post in Seattle.


Notes:
Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree.  Upon joining the company as a sales representative, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London, where he was promoted to sales manager.  Mr. Buchanan has completed the courses "Successful Telemarketing" and "International Sales Management."  He is fluent in French.


Notes:
Michael is a graduate of Sussex University (MA, economics) and the University of California at Los Angeles (MBA, marketing).  He has also taken the courses "Multi-Cultural Selling" and "Time Management for the Sales Professional."  He is fluent in Japanese and can read and write French, Portuguese, and Spanish.


Notes:
Robert King served in the Peace Corps and traveled extensively before completing his degree in English at the University of Michigan and then joining the company.  After completing a course entitled "Selling in Europe," he was transferred to the London office.


Notes:
Laura received a BA in psychology from the University of Washington.  She has also completed a course in business French.  She reads and writes French.


Notes:
Anne has a BA degree in English from St. Lawrence College.  She is fluent in French and German.


# tblTopics

Description:
This example demonstrates how to combine the records in two tables into a single result set. You create a union query by typing the SQL statement in the SQL window. For further information, search the Help Index for "union queries" and display the topic "Combine data in fields from two or more tables using a union query."

ObjectsUsed:
Table: Customers, Suppliers; Query: qryUnionPhoneList


Topic:
How to Create a Join on a Range of Values

Description:
This example demonstrates how to create a join across a range of values when the number of values involved exceeds the practical capacity of the Switch() function or nested IIF() functions.

ObjectsUsed:
Table: Orders, FreightRange; Query: qryOrdersFreightRange


Topic:
How to Create a Parameter Query That Accepts a List of Values

Description:
This example describes how to use a Visual Basic for Applications procedure so that you can enter a list of values into a parameter prompt. In this example, the list contains no spaces, and the items are separated by commas.

ObjectsUsed:
Table: Customers; Query: qryInParam; Module: basInParam

Article Title:
How to Create a Parameter In() Statement


Description:
This example shows you how to call a user-defined function in a query.

ObjectsUsed:
Table: Employees; Query: qryEmployeeAge; Module: basAgeFunctions; Function; Age, AgeMonth

Article Title:
Two Functions to Calculate Age in Months and Years


Topic:
Referring to a Field in the Previous or Next Record

Description:
This example describes two ways to obtain values from the previous or next record for use in calculations. The first method uses the DLookup() function in an expression; the second method uses two user-defined functions.

ObjectsUsed:
Table: tblMileage; Query: qryNextRec_Dlookup, qryPrevRec_Dlookup, qryNextRec_VBA, qryPrevRec_VBA; Form: frmQryPrevNext;Module: basSelectQryExamples, basCommonFunctions

Article Title:
Referring to a Field in the Previous Record or Next Record


Topic:
How to Return All Records When Parameter Is Blank

Description:
This example describes how to create a parameter query that returns all of the records if you leave the parameter blank; otherwise, it returns only those records in which the data matches the value that you entered in the parameter prompt.

Article Title:
Showing All Records (Including Null) in a Parameter Query


Topic:
How to Create a Crosstab Query with Multiple Value Fields

Description:
This example displays the results of two separate crosstab queries in one select query, creating the effect of multiple value fields.

ObjectsUsed:
Table: Customers, Orders, Order Details; Query: qryOrderTotal, qryOrderCount, qryMultipleColumns

Article Title:
Creating a Crosstab Query with Multiple Value Fields


Topic:
How to Group Column Headings in a Crosstab Query

Description:
This example describes how to group values in a crosstab query using a fixed number of headings, rather than having a column for every value.

ObjectsUsed:
Table: Customers, Orders; Query: qryGrpCross

Article Title:
How to Group Column Headings in a Crosstab Query


Topic:
How to Create an SQL Pass-Through Query in Code

Description:
This example demonstrates how to create an SQL Pass-Through (SPT) query using a Visual Basic for Applications procedure. You can use SPT queries to pass SQL statements directly to an ODBC data source, avoiding the need to link tables. 

NOTE: This example assumes that you have a valid ODBC connection to a Microsoft SQL Server containing the Pubs sample database.

ObjectsUsed:
Table: Authors table in Pubs;Query: qrySPTVBA; Module: basSPTExamples; Function: CreateSPT, ObjectExists

Article Title:
How to Create an SQL Pass-Through Query Using Data Access Objects


Topic:
How to Use a Subquery as Criteria in a Select Query

Description:
This example describes how to use a subquery as criteria; the query that contains the subquery returns any item whose price matches the price of the item that you specify in the parameter prompt.


Description:
This example demonstrates how to rank records within a query using three different techniques. The first example ranks records from a table. The second example ranks the results of a totals query. The third example demonstrates an alternative method of handling ties in ranking.

ObjectsUsed:
Table: Employees, Products; Query: qryRankQueryResults, qryProductCount, qryDistinctProductCount, qryRankTotalsQuery, qryProcessTieInRanking; Form: frmQryRankingSamples


Topic:
How to Simulate Parameters in an SQL Pass-Through Query

Description:
This example demonstrates how to pass parameters to an SQL pass-through query using a Visual Basic for Applications function.

NOTE: This example assumes that you have a valid ODBC connection to a Microsoft SQL Server containing the Pubs sample database.

ObjectsUsed:
Table: Authors, TitleAuthor, Titles in Pubs sample database;
Query:qryParamSPTQuery; Function: ParamSPT()

Article Title:
How to Simulate Parameters in an SQL Pass-Through Query


Topic:
How to Create a Make-Table Query with a Union Query

Description:
This example shows you how generate a table that contains the results of a union query by creating a make-table query based on the union query.

ObjectsUsed:
Table: Customers, Supplies, tblUnionResult (created by running qryUnionToTable); Query: qryUnion, qryUnionToTable

Article Title:
How to Create a Make-Table Query with a Union Query


Topic:
Query by Form (QBF) Using Dynamic QueryDef

Description:
This example demonstrates how to use a form to specify the criteria for a query that is dynamically built by a Visual Basic for Applications function. This technique is called query by form (QBF).

ObjectsUsed:
Table: Orders; Query: qryDynamicQBF; Form: frmDynamic_QBF, frmDynamicQBF_SQLView; Module: basCommonFunctions

Article Title:
Query by Form (QBF) Using Dynamic QueryDef


Description:
This example demonstrates how to create a running total of a field in a query.


Topic:
Using IIf() in Crosstab to Limit Column Headings in a Crosstab Query

Description:
You can use the IIf() function to group values under a small number of headings in a crosstab query. This example demonstrates how to group records by country and to count the number of orders placed for each customer.

ObjectsUsed:
Table: Customers, Orders; Query: qryUseIIF

Article Title:
How To Use IIf() in Crosstab to Limit Column Headings


Topic:
Changing Column Headings in a Crosstab Query Using Code

Description:
This example demonstrates how the sample function ChangeColumnHeadings() is used to dynamically modify the column headings of a crosstab query.

ObjectsUsed:
Table: Orders, Customers; Form: frmOrdersByCountry, frmViewOrdersByCountry; Query: qryOrdersByCountry_Before, qryOrderByCountry_After; Module: basOrdersByCountry; Function: ChangeColumnHeadings

Article Title:
How to Use Code to Change Column Headings in a Crosstab Query


Topic:
How to Sort and Group in a Query Using a Portion of a Field's Value

Description:
This example demonstrates how to sort on only a portion of an employee's last name. It ignores the first 3 letters of an employee's last name and instead sorts on the 4th - 9th characters.

Article Title:
Sorting a Query or Stored Procedure by Using Only a Portion of a Field's Value


Topic:
How to Use a Query to Filter Unique Data

Description:
This example demonstrates the use of the First() function to filter records.

Article Title:
How to Use a Query to Filter Unique Data


Topic:
How to Sort a Query Using an Option Group

Description:
This example changes the sort order of an SQL statement for a query dynamically, depending on which button is selected in an option group.


Topic:
How to Filter a Query Using an Option Group

Description:
This example changes the WHERE condition of an SQL statement for a query dynamically, depending on which button is selected in an option group.


Topic:
How to Use the DLookup() Function Inside a Query

Description:
This example demonstrates the use of the DLookup() function within a query to determine the value that will be used as a criteria.

ObjectsUsed:
Table: Orders; Form: frmDomain; Query: qryDomain

Article Title:
How to Use the DLookup() Function Inside a Query.


Topic:
How to Pull the Second Highest Value from a Table Using a Query

Description:
This example demonstrates the technique of using nested queries to find the second highest value in a field of a table. QryTop2 finds the top 2 values in the Quantity field of the Order Details table. Then, QrySecondHighest uses the results of QryTop2 and the Min() function to select the second highest value.

ObjectsUsed:
Table: Order Details; Query: qrySecondHighest, qryTop2


Topic:
How to Group on One Field and Sort on Another in a Query

Description:
This example demonstrates how to group on one field and sort on another field inside a query.

ObjectsUsed:
Table: Products; Query: qryFinalGroupSort, qryGroupByCategory


# Orders

ShipAddress:
Carrera 22 con Ave. Carlos Soublette #8-35


ShipAddress:
Carrera 52 con Ave. Bolívar #65-98 Llano Largo


# Suppliers

HomePage:
Mayumi's (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/mayumi.htm#


HomePage:
Plutzer (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/plutzer.htm#


Address:
Order Processing Dept.
2100 Paul Revere Blvd.


HomePage:
G'day Mate (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/gdaymate.htm#


