Creating Advanced Queries and Custom Forms

An understanding of SQL is not necessary to build database applications in the Access development environment. An understanding of SQL is beneficial, however, as queries and applications become more complex. JOIN is a very powerful extension to define a syntax for retrieval from multiple tables to resolve some very complex queries. The simple logic behind SELECT is: SELECT what FROM whatobject or SELECT what FROM whatobject WHERE expression The only required key for the select is the FROM key, but queries can become much more involved, even to the point that a single SQL command might almost equate to a program. The "what" in the SELECT logic refers to the field names or possibly some aggregate or derived value. The "whatobject" refers to tables. The "expression" qualifies the records of interest. An object reference in the SELECT statement often needs qualification, because names are only unique within a table when multiple tables are used. The "." is used to qualify references by concatenating the table name, followed by a ".", followed by the field name. AS is used in several components of the SELECT statement to create an alias name that is more meaningful or possibly shorter to simplify the command understandability and programmability. An alias can be defined for fields and tables, typically in the "what" for fields and the "whatobject" for tables. The scope of the alias is only within the SQL command. Syntactically, the AS keyword follows an object name, and the new alias name follows the AS. In the remainder of the SQL command, the alias can be substituted for the original object name. DISTINCT is included in the "what" string to convey the request that any duplicate occurrences of an object be displayed or reported only once. The COUNT() function is also often part of the "what" string for retried record counts. Using these two together, ad hoc summary information is easily retrieved. There are many basic components that can be added to extend and enhance the query power of SELECT, JOIN being one that can be very simple or very complex. It identifies multiple table sources and how their data can be merged for new table views. Table design is very important when using JOIN. The table key selection is important. A table must contain only information about that table entity. JOIN will bring together the detail information. Successful table joining can be achieved by equating table keys with the WHERE extension. This merging, however, will check every row in one table with every row of the other table (Cartesian product of two tables), and usually is not the desired result. The JOIN keyword following the FROM tables is more generic. The JOIN keyword is part of "whatobject" in SELECT. If the two tables are being joined using fields with the same name and the join condition is equal, then the following simplified syntax can be used for the "whatobject" value in SELECT: "whatobject" = tableobject1 JOIN tableobject2 USING ( join_field_name ) (Multiple keys in USING are separated by ",") If the joined fields have different names or the join condition is not equality, then the following syntax can be used: "whatobject" = tableobject1 JOIN tableobject2 ON tableobject1.name1 = tableobject2.name2 SQL: has defined several types JOIN operations; INNER JOIN is the default if the type is not specified. INNER JOIN – Only records in left table and right table with matching keys LEFT OUTER JOIN – All records of left table, with info from right table only for those with matching keys RIGHT OUTER JOIN – All records of right table, With info from left table only for those with matching keys FULL OUTER JOIN – All records of both tables, with info from both table only for those with matching keys, unmatched keys added from each, but with NULL for merged fields SELF JOIN – join between two fields of the same table Records with no matching keys for OUTER JOINS receive NULL values for the merged fields. OUTER JOINs differ from the INNER JOIN only when the minimum cardinality of a relation is 0.