The Record Source tab of the Data Control OLE Properties dialog box includes the SQL Command Text box. You can enter SQL statements directly into the text box or paste a statement from another application. Queries built by the SQL Wizard also appear in this box, where you can refine them further.
Users comfortable with coding SQL statements and who know the table and column names of their database may prefer to enter the statement manually. You may also want to create an SQL query in another application, such as MS Query, and then paste it in.
Joining Tables
When you want to create a query that combines data from two or more tables, you are joining the tables. The tables used in the query must share a common column. For example, a Customer table and an Orders table both have a column of Customer IDs.
With the SQL Wizard, you can easily combine tables using the most common type of join operation, which is an inner join. An inner join selects only the records from each table where the values of the common column match. For example, this Select statement joins the Customer and Orders tables by equating the CustomerID column in both tables. It returns information about customers and orders that have the same customer ID value. It does not return information about customers with no associated orders or information about orders with no associated customer.
SELECT
Customers.CustomerID,
Customers.ContactName,
Orders.OrderID,
Orders.ShipName
FROM Customers, Orders
WHERE (Customers.CustomerID = Orders.CustomerID)
ORDER BY Customers.CustomerID
The following figure shows a sampling of data retrieved by joining the Customers and Order table based on the preceding Select statement.
Results of an Inner Join
The other type of join is an outer join, which must be created by entering or pasting a Select statement. An outer join returns all the records for one table, but only the records from the second table where the values of the common column match. For example, this Select statement uses an outer join to return all the records in the Orders table, but only the records in the Customer table where the Customer ID value in both tables is the same. That is, it displays each Order record, with or without associated customer information.
SELECT
Customers.CustomerID,
Customers.ContactName,
Orders.ShippedDate,
Orders.ShipName
FROM
(Orders LEFT OUTER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID)
ORDER BY Customers.CustomerID
This Select statement does the reverse; notice the only difference is the sequence of tables in the From clause. This statement returns all the records in the Customers table and only the records in the Orders table where the Customer ID value in both tables is the same. That is, it displays each Customer record, with or without associated order information.
SELECT
Customers.CustomerID,
Customers.ContactName,
Orders.OrderID,
Orders.ShipName
FROM
(Customers LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
ORDER BY Customers.CustomerID
The following figure shows a sampling of data retrieved by running the preceding Select statement. Notice the PARIS customer ID record has no associated order information.
Results of an Outer Join
