Join tables and queries
When you include multiple tables in a query, you use joins to help you get the results you are looking for. A join helps a query return only the records from each table you want to see, based on how those tables are related to other tables in the query. This article provides conceptual information about joins, discusses the various types of joins, and shows you how to use them.

---

Using SQL to Generate SQL Statements
Learn the benefits of generating SQL statements from a query, how to make the output from a query appear in the form of another SQL statement and how to use the data dictionary, database tables, or both to form SQL statements.

---

Joining Tables
One of the most powerful features of SQL is its capability to gather and manipulate data from across several tables. Without this feature you would have to store all the data elements necessary for each application in one table. Without common tables you would need to store the same data in several tables. Imagine having to redesign, rebuild, and repopulate your tables and databases every time your user needed a query with a new piece of information. The JOIN statement of SQL enables you to design smaller, more specific tables that are easier to maintain than larger tables.

---

Microsoft Access SQL Tutorials
Here you will find articles relating to SQL (Structured Query Language) and SQL use and implementation in Microsoft Access. You will find articles and topics relating to SQL query designs, from planning what tables and fields will be used to selecting criteria to be used in the SQL Statements. There are articles on SQL basics, SQL Select, Insert, Update and Delete Statements and topics on why use SQL.

---

Introduction to Data Analysis
After creating one or more tables in a database and populating it (them) with values, one of the next steps you would take is to examine or analyze the values in the database. Analyzing the values in a table is also referred to as querying. TOC: Querying a Database, Introduction to SQL, The Query Wizard, Query Design, Accessing the SQL Code of a Query, Removing a Column From a Query, Replacing and Moving a Column, A Query or a SQL Statement as a Record Source.

---

SQL Queries
You need to become familiar with SQL (Structured Query Language) because you will need it if you ever develop a Microsoft Access application and have to issue queries through code. You also need SQL to query other database systems, such as Oracle and MySql. Finally, once you know SQL, it is often easier to write a query directly in SQL than to design it using Access's QBE interface.

---

SQL Queries
You need to become familiar with SQL (Structured Query Language) because you will need it if you ever develop a Microsoft Access application and have to issue queries through code. You also need SQL to query other database systems, such as Oracle and MySql. Finally, once you know SQL, it is often easier to write a query directly in SQL than to design it using Access's QBE interface.

---

Microsoft Office Access 2007: 13 Pages
Contents: Tables and Relationships, Using Related Tables, Types of relationships, Viewing the Relationship window, Adding a Table in the Relationships Window, Setting Referential Integrity, Queries, Using Queries and RecordSets, Using the Simple Query Wizard, Creating a Query in Design View, Opening a Query, Adding a Table to a Query, Running a Query, Sorting a Query, Adding Criteria to a Query, Using Comparison Operators, Using an OR Condition, Creating a Parameter Query, Creating a Calculated Field, Creating Aggregate/Function Query, Creating an Action Query, Using Multiple Tables in a Query. (PDF file, install Acrobat Reader to read this Microsoft Office Access 2007 tutorial)

---

The Structured Query Language (SQL)
So far, to create a query we have learned to use either the Query Wizard or the Design View of a query. In both cases, we were selecting fields and adding them to the query. This concept of building a query is based on the Structured Query Language abbreviated SQL. In fact, all queries are based on SQL and this language is used by most database development environments, including Microsoft Access.

---

I get the error message "Could not delete from the specified tables"
When you run a delete query (delete query: A query (SQL statement) that removes rows matching the criteria that you specify from one or more tables.), Microsoft Office Access 2007 may display the error message Could not delete from the specified tables. This article lists cases in which you can use a delete query, explains why the error message appears, and provides steps for correcting the error.

---

Nest a query inside another query or in an expression by using a subquery
Sometimes you may want to use the results of a query as a field in another query, or as a criterion for a query field. For example, suppose that you want to see the interval between orders for each of your products. To create a query that shows this interval, you need to compare each order date to other order dates for that product. Comparing these order dates also requires a query. You can nest this query inside of your main query by using a subquery (subquery: An SQL SELECT statement that is inside another select or action query.). You can write a subquery in an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) or in a Structured Query Language (SQL) statement in SQL view (SQL view: A window that displays the SQL statement for the current query or that is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, Access constructs the SQL equivalent in SQL view.).

---

Action Queries
We have used queries so far only to create a list of fields that would be considered for a set of records. We also mentioned that a query in Microsoft Access is simply a means of graphically representing data. Indeed, a query is based on a SQL statement. As SQL is its own, fully functional language, we can use it perform far more operations than to only select columns for a query. The SQL can be used to create tables, perform data entry, modify records, etc. Some of these operations can be performed visually in the query Design View. They can also be performed using SQL statements. To know what is going on behind the scenes, you can write your own code. You can also use the Design View to start a query, and then open the code to customize the statement. Topics: Queries and Table Creation, SQL and Table Creation, SQL and Data Entry, Queries and Record Appending, Queries and Records Updating, Queries and Table Deletion, Queries and Record Deletion.

---

SQL - Multiple Conditions; using the AND and OR conditions
SQL allows us to combine two or more simple conditions by using the AND and OR or NOT operators. Any number of simple conditions can be present in a single SQL statement, to allow us to create complex WHERE clauses that allow us to control which rows are included in our query results.

---

Access SQL: basic concepts, vocabulary, and syntax
When you want to retrieve data from a database, you ask for the data by using Structured Query Language, or SQL. SQL is a computer language that closely resembles English, but that database programs understand. Every query that you run uses SQL behind the scenes. Understanding how SQL works can help you create better queries, and can make it easier for you to understand how to fix a query that is not returning the results that you want. This is one of a set of articles about Access SQL. This article describes the basic use of SQL to select data, and uses examples to illustrate SQL syntax. In this article * What is SQL? * Basic SQL clauses: SELECT, FROM, and WHERE * Sorting the results: ORDER BY * Working with summarized data: GROUP BY and HAVING * Combining query results: UNION

---

Learn Database SQL Technology
Structured Query Language (SQL) is a language that was developed by IBM for processing data in mainframe databases. The relational nature of databases and the ease of SQL usage has made SQL the standard language for databases. SQL technology is also standardized which makes it ideal for usage across multiple fields. Learning SQL database technology will enable you to program and maintain any database with ease.

---

Multiple Table Queries
Most of the queries you create in Microsoft Access will more that likely need to include the data from more than one table and you will have to join the tables in the query. The capability to join tables is the power of the relational database. As you know, in order to join database tables, they must have a field in common. The fields on which you join tables must be the same or compatible data types and they must contain the same kind of data, however they do not have to have the same field name (although they probably will). Occasionally, the two database tables that you want to bring the data from may not have a field in common and you will have to add another table to the query with the sole purpose of joining the tables.

---

The Structured Query Language
So far, to create a query we have learned to use either the Query Wizard or the Design View of a query. In both cases, we were selecting fields and adding them to the query. This concept of building a query is based on the Structured Query Language abbreviated SQL. In fact, all queries are based on SQL and this language is used by most database development environments, including Microsoft Access.

---

Splitting table into multiple table
I need to split one large table into two other tables plus the original. Each will have a one to one relationship with the original. I have several forms that will work off these tables. What are the steps involved? Make table query to create the tables...etc. Should I use a foreign key for each of the sub tables? Also, what's the easiest way to keep the form functionality through the change? I appreciate any advice you could give on this matter.

---

Basic queries using SQL
Query-By-Example (QBE) and Structured Query Language (SQL) are both well-known, industry standard languages for extracting information from relational database systems. The advantage of QBE that it is graphical and relatively easy to use. The advantage of SQL is that it has achieved nearly universal adoption within the relational database world. Learning objectives: understand the difference between QBE and SQL; create an SQL query; use SQL as a data definition language. (pdf file install Acrobat Reader to read this tutorial)

---

Add a subdatasheet
When two tables have one or more fields in common, you can embed the datasheet from one table in another. An embedded datasheet, which is called a subdatasheet, is useful when you want to view and edit related or joined (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) data in a table or query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.). In this article * Introduction to subdatasheets * Open and close a subdatasheet * Add a subdatasheet

---

Create a SQL Query
SQL queries use Structured Query Language (SQL), a standard scripting language, to make requests from databases. You can use four types of SQL queries in Access 97: union, pass-through, data-definition and subquery.

---

Copy SQL statement from query to VBA
Rather than typing complex query statements into VBA code, developers often mock up a query graphically, switch it to SQL View, copy, and paste into VBA. If you've done it, you know how messy it is sorting out the quotes, and the line endings. Learn how to build a form that massages the SQL statement from a query, so it can be pasted into a VBA procedure in Microsoft Access.

---

Action Queries
We have used the concept of a query so far only to create a list of fields that would be considered for a set of records. We also mentioned that a query in Microsoft Access is simply a means of visually representing data. Indeed, a query is based on a SQL statement. As SQL is its own, fully functional language, we can use it to perform far more operations than to only select columns for a query.

---

Using queries to get the information you need
At first glance, it appears that splitting information into multiple tables and relationships creates more of a headache than it is worth. People generally like to have all the information they require on one screen (like a spreadsheet, for instance); they do not want to have to know about multiple tables, foreign keys, relationships, and so on. Saved queries address this problem. Queries allow the user to join data from one or more tables, order the data in different ways, calculate new fields, and specify criteria to filter out certain records. The important thing to keep in mind is that a query contains no data it merely reorganizes the data from the table (or tables) on which it is built without changing the "underlying tables" in any way. Once a query is defined and saved, it can be used in exactly the same way as a table. Because of this, it is useful to think of queries as "virtual tables". Indeed, in the majority of DBMSes, saved queries are called views because they allow different users and different applications to have different views of the same data. Learning objectives: create different types of queries; understand how queries can be used to answer questions; develop a naming convention for queries; understand the difference between an 'updatable' and 'non-updatable' recordset. (pdf file, install Acrobat Reader to read this tutorial)

---

Process SQL on a database server by using a pass-through query
You can use Microsoft Office Access 2007 as a tool in which to create and modify a database and work with its data, but you can also use Office Access 2007 as a front end (interface) for a server database management system, such as Microsoft SQL Server. Generally, when you use Office Access 2007 as a front end application, you link to tables that are in the server database management system, and then use those linked tables as if they were located in the Access database. The Structured Query Language (SQL) processing is performed on your local machine by Access.

---

Create a Query That Uses Multiple Criteria in One Field
When you create a query that uses multiple criteria in one field for Access 2003, you restrict the number of search results that you will receive from that query. The multiple criteria can be either inclusive or exclusive depending on how the criteria are joined.

---

Create a SQL Query
To create a SQL (Structured Query Language) query in Access 2003, you’ll need enough information to create a SQL statement. Fortunately, the program will create the statement for you, so it’s not necessary to write it.

---

Aggregate Functions of SQL For Beginners
The data manipulation side of SQL (Structured Query Language) allows for various types of queries. Very often it will be necessary to retrieve summarized information from the data contained in the database tables, and it is then we turn to the SQL Aggregate Functions. When we need the sum or the average of a set of values for instance. or when we wish to know how many records match given conditions, or which is the lowest or highest value in a particular set or subset of data.

---

Creating a Simple Query in Microsoft Access
Have you ever wanted to combine information from multiple tables in your database in an efficient manner? Microsoft Access offers a powerful query function with an easy-to-learn interface that makes it a snap to extract exactly the information you need from your database. In this tutorial we'll explore the creation of a simple query.

---

Create a Query That Uses Multiple Criteria in One Field
Creating a query that uses multiple criteria for a field is like running a filter on your query results. For example, if you have a table that contains complete addresses of all your friends, you can run a query that will display brief information for those who live in New York, or for those who live in New York and whose surname starts with the letter "B".

---

SQL Aggregate Functions
Using the SQL aggregate functions, you can determine various statistics on sets of values. You can use these functions in a query and aggregate expressions in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.

---

How to Write SQL Statements
You don’t have to use the Select Query window in Microsoft Access to extract information from your database. Using Structured Query Language (SQL) is often quicker and easier. Here are the basics.

---

Exploring Multiple Active Record Sets with MS Access
This tutorial explains how to access multiple recordsets from a single connection to SQL Server 2005 in Microsoft Access. We will also explore the properties of the recordsets. TOC: Exploring Multiple Active Record Sets with MS Access; Connecting to SQL 2005 using ADO; Executing SQL Queries on the Open Connection; Interleaving Data from Two Recordsets.

---

Creating Union Queries
Microsoft Access allows you to create various query types using the QBE (Query By Example) pane. There are also various other queries that you can create in Microsoft Access, however you will need to create these by typing in the appropriate SQL (Structured Query Language) directly into the SQL view window when in query design.

---

Introduction Structured Query Language
This page is a tutorial of the Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet. SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data. This page will describe how to use SQL, and give examples. The SQL used in this document is "ANSI", or standard SQL, and no SQL features of specific database management systems will be discussed until the "Nonstandard SQL" section.

---

Introduction to Structured Query Language
This page is a introductory tutorial of the Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet. SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data. This page will describe how to use SQL, and give examples. The SQL used in this document is "ANSI", or standard SQL, and no SQL features of specific database management systems will be discussed until the "Nonstandard SQL" section.

---

Query Joins - Inner & Outer Joins
You will often need to join tables in a Microsoft Access query where you need to view information from two or more separate database tables. For this you will need to use query joins to accomplish the task. When you run a database query to find data in related tables, by default Microsoft Access will look for records that have a matching value on both sides of the relationship. Whilst this is what you may want to do most of the time, by knowing how to use the different types of query joins, you can control which records will be displayed as the output. This enables you to again find the exact data that you want - not more and not less.

---

View SQL for a query
In Access 2003/XP/2000/97, I've created a query and I want to view the SQL for this query. How do I do this?

---

SQL Query with subqueries
Iam having trouble writing this SQL query.....see if anyone can help...

---

Remove a subdatasheet
A subdatasheet is a datasheet that is nested within another datasheet and contains data related or joined to the first datasheet. Microsoft Office Access automatically creates a subdatasheet when you create a table that is in a relationship (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.). However, you can always add a subdatasheet to a table, query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) or form (form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.) (if the form is saved to be displayed in Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.)). This article describes how to identify a subdatsheet and remove it. Removing a subdatasheet does not delete the table, query, or form that it is connected to, nor does it modify any relationship between the associated tables. What do you want to do? * Identify a subdatasheet * Remove a subdatasheet

---