Introducing Jet Queries

Queries are an essential tool in any database management system. You use queries to select records, and add, update, and delete records in tables. Most often you use queries to select specific groups of records that meet criteria you specify. You can also use queries to combine information from different tables, providing a unified view of related data items. In this chapter, you learn the basics of creating your own select queries, including specifying selection criteria and using the results of your queries to generate reports and create new tables. You create queries using more than one table in Chapter 11, "Creating Multitable and Crosstab Queries," after you learn the details of how to use operators and create expressions in Chapter 10, "Understanding Jet Operators and Expressions."

This chapter covers queries that apply to Jet databases and to client/server databases, such as SQL Server, that you link to a conventional Access front-end .mdb file. When you link client/server tables to an Access front end, your application uses the Jet query engine to process the back-end data. Jet has its own dialect of SQL, called Jet SQL, which, for the most part, conforms to the ANSI SQL-92 standard but has several extensions that aren't included in SQL-92. All query techniques you learn in Part III, "Transforming Data with Queries and PivotTables," apply to Jet databases and client/server tables linked to an Access front end that stores application objects queries, forms, reports, and modules in an .mdb file.

In contrast, Access data projects (ADP) use SQL Server's query engine. ADP offers three types of queries views, functions, and stored procedures and uses the SQL Server design tools, called the da Vinci toolset in this book, for designing queries. (Microsoft used the da Vinci codename for the toolset during its beta cycle, and it stuck). SQL Server uses Transact-SQL (T-SQL), another flavor of SQL-92 that has many proprietary extensions to the language. Most of the Jet SQL SELECT query examples in this chapter also work for creating ADP views. Chapter 21, "Moving from Jet Queries to Transact-SQL," provides detailed coverage of SQL-92 topics and explains the differences between Jet SQL and T-SQL.

Jet SQL

The chapters of Part III include examples and brief explanations of the SQL statements the Jet query engine generates from your graphical query designs. The objective of these examples is to encourage learning "SQL by osmosis," a process similar to learning a foreign language by immersion rather than from a grammar textbook. By the time you complete Chapter 13, "Creating and Updating Jet Tables with Action Queries," you'll have a working knowledge of basic SQL syntax.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net