SQL stands for Structured Query Language. An SQL statement starting with “Select” is simply a standardized way to get information from database tables. It specifies what to show (SELECT) and where the data comes from (FROM). Optional clauses can be included, such as criteria (WHERE) and how to sort (ORDER BY). The basic syntax for an SQL statement is:
SELECT fieldlist
FROM tablename
WHERE criteria
ORDER BY fieldlist;
Figure 1 shows part of a form that contains a customer combo box (named CustomerID) and a list box (named listCustomerProducts) containing the products that customer has purchased. Figure 2, also from the same form, shows a list box (named fnd_PayDate_Customer) with the customer’s payment history.
The information that appears in a list box is specified by a property called the Row Source. The Row Source can contain a table name, query name, or an SQL statement. We want to build SQL statements for each list box to display the correct information for each customer.
PRODUCTS ORDERED
The SQL statement for the listCustomerProducts Row Source is:
SELECT Orders.OrderID, Products.SerialNum AS [Product SerialNum], Orders.DateOrder, qOrderBalance.OrderBalance AS Balance
FROM Products INNER JOIN (Orders LEFT JOIN qOrderBalance ON Orders.OrderID = qOrderBalance.OrderID) ON Products.ProductID = Orders.ProductID ORDER BY Orders.DateOrder DESC;
The text phrases in all caps are SQL keywords, which mark the beginning of a clause or have other special meanings. In the SELECT clause, Orders.OrderID is the OrderID field from the Orders table. Products.SerialNum AS [Product SerialNum] specifies the SerialNum field from the Products table, but we want to rename it as “Product SerialNum,” so the alternate name follows the AS keyword. The same happens with qOrderBalance.OrderBalance AS Balance—it’s telling Access to use the OrderBalance field from the qOrderBalance query and to rename it as “Balance.”
The FROM clause specifies where to find the data for the fields listed in the SELECT clause. Because we want data from multiple tables, JOIN is used to specify how each pair of tables is connected. INNER JOIN combines only records from the tables whenever the values in the matching field are equal. LEFT JOIN includes all records from the first table (even if there are no matching values in the corresponding field of the second table) and just those records from the second table where the values match the corresponding field. For example, the syntax using an INNER JOIN might be:
FROM table1 INNER JOIN table2 ON table1.field1 = table2.field1 (Note: The field to match on should be a primary key or a field with a unique index in at least one of the tables.)
We want a list of products that have been ordered and the order balances. The logical place to start, therefore, is with the Products table, which contains the list of available products. Since the only products we want are those that have been ordered, INNER JOIN is used to combine records that have matching values for the ProductID in both the products and orders tables.
The Products table is “table1” of the INNER JOIN. The “table2” part is more complicated because the data for orders also needs to include the balance, which is calculated in a query. Therefore, it’s actually a mixture of data from two sources. It’s enclosed in parentheses and also includes how the tables are joined (OrderID). The nested LEFT JOIN clause—(Orders LEFT JOIN qOrderBalance ON Orders.OrderID = qOrderBalance.OrderID)—combines the Orders table with the qOrderBalance query. Finally, ON Products.ProductID = Orders.ProductID indicates ProductID in both tables is the link.
The SQL statement concludes with the ORDER BY clause, which says the rows in the list box should be sorted by order date (Orders.Orderdate) in a descending order (DESC;).
PAYMENTS
The SQL statement for the Row Property of the fnd_PayDate_Customer list box is:
SELECT PayGroups.PayGroupID, IIf(IsNull([PayGroups].[DatePay]),Null,Format([PayGroups].[DatePay],
"yyyy-mm-dd"", ""ddd")) AS [Payment Date],
qPayGroupSum.SumPayment AS SumPmts,
Format(Nz([CountPayment],0),"#") AS [#]
FROM PayGroups LEFT JOIN qPayGroupSum ON
PayGroups.PayGroupID = qPayGroupSum.PayGroupID
ORDER BY PayGroups.DatePay DESC;
This list box displays the dates of payments, the sum of payments, and the number of products that payment was applied to. Data comes from the PayGroups table and the qPayGroupSum query, and it’s displayed in descending order by date. The SELECT clause contains functions that describe how to format the data when it’s displayed.
WHERE CLAUSE
Missing from both those statements is a WHERE clause. Each time a new or different customer is selected in the customer combo box, the list boxes need to change to show rows relating only to that customer. To do that, we can create VBA code that will read the customer box to construct a Where clause each time the customer changes. We’ll get into that next month.
Download this month’s database: SF_1801_SQL_RowSource.
SF SAYS
SQL statements must include SELECT and FROM clauses. Others, like WHERE and ORDER BY, are optional.
January 2018
