Joining Tables and Queries

In addition to joins based on permanent tables, Microsoft Jet supports joins between tables and saved queries, or even between two queries. Queries can be based on other queries, which can further be based on other queries, and so on. Microsoft Jet automatically resolves all references to queries and tables.

Microsoft Jet QueryDef objects can be treated, for the most part, as if they were tables in the database. A QueryDef object performs the function of a view in SQL. QueryDef and TableDef objects occupy the same “name space” within a Microsoft Jet database. This means that you can’t have a TableDef object and a QueryDef object with the same name, for example. Generally, wherever you can use a base table, you can use a query based on that table instead.

Suppose you want to find the percentage of your total sales of certain products that were generated by particular customers. One way to do this is to create the four queries shown in this section. The first query totals orders by customer and product:

SELECT 
	Orders.CustomerID, 
	[Order Details].ProductID, 
	Sum((UnitPrice*Quantity) - (Discount*(UnitPrice*Quantity))) AS ExtPrice 
FROM Orders 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID 
GROUP BY Orders.CustomerID, [Order Details].ProductID;

Save this query as a permanent QueryDef object in the database with the name SelectCustomersOrdersTotals. You can now treat this query as if it were a table. The sample output it produces may be:

Now you have to find the order totals for each ProductID value. Create a permanent QueryDef object called SelectOrdersTotals with the following SQL statement:

SELECT 
	[Order Details].ProductID, 
	Sum((UnitPrice*Quantity) - Discount) AS ExtPrice 
FROM Orders 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID 
GROUP BY [Order Details].ProductID;

Part of the output produced by this query may be:

Now you can create a third QueryDef object called SelectPercentOfTotal, which joins SelectOrdersTotals with SelectCustomersOrdersTotals:

SELECT 
	SelectCustomersOrdersTotals.CustomerID, 
	SelectCustomersOrdersTotals.ProductID, 
	SelectCustomersOrdersTotals.ExtPrice AS CustTotal, 
	SelectOrdersTotals.ExtPrice AS ProdTotal, 
	Format(CustTotal/ProdTotal, '#.###') AS PercentOfTot 
FROM SelectOrdersTotals 
INNER JOIN SelectCustomersOrdersTotals 
ON SelectOrdersTotals.ProductID = SelectCustomersOrdersTotals.ProductID;

Part of the output from this query may be:

Finally, you can join this third QueryDef object back to the Customers and Products tables to pick up the CompanyName and ProductName fields:

SELECT 
	SelectPercentOfTotal.*, 
	Products.ProductName, 
	Customers.CompanyName 
FROM (SelectPercentOfTotal 
	INNER JOIN Customers 
	ON SelectPercentOfTotal.CustomerID = Customers.CustomerID) 
INNER JOIN Products 
ON SelectPercentOfTotal.ProductID = Products.ProductID;