Query Workbook

Three query logic steps

1. SELECT - the attributes you need for the query 2. FROM - the tables that hold the attributes you need for the query 3. WHERE - criteria to restrict the query output to include or exclude certain records

Totals Queries

A SELECT query can include a feature that will Total, or Aggregate a column's data (Sum, Count, etc.). Not all attributes are appropriate choices for aggregation and so the query design will require extra planning. To enable the Totals feature, click on the Σ Totals symbol in the Show/Hide area within the right side of the Query Design ribbon tab. Once enabled, the third row of your QBE grid becomes a Totals row and the cells in that row under every attribute say Group By. Click on the Group By row under the attribute that you wish to aggregate and from the drop-down menu choose the function desired. In BA342 we will primarily use the Sum and Count aggregate functions. Remember the Totals tool is a toggle feature so click on it when you wish to turn off. To ensure the SQL written by Access is efficient (includes only code necessary), be sure the Totals tool is off for any query that does not need it.

Is Null

A null value in a database is an empty field. A primary key is, by definition, unique and non-null, therefore primary key attributes will never be null, but other attributes may be. There are tables in the BA342 Company database with attributes that contain null values. Developing a query that takes advantage of null values as criteria is a quick way to include or exclude certain records. The operator Is Null is useful to use in the query criteria to achieve that goal.

Parameter Queries

A parameter query is a special form of query that will prompt the user for input every time the query is run. The input received from the user is then the criteria for the query. This can broaden the use of certain queries. A parameter query is developed by entering square brackets in the criteria position of the QBE and including the end-user text prompt within those brackets. A parameter query that expects the complete criteria to be input from the customer, for example the Customer ID, should include the user prompt in the QBE grid criteria position under the Customer ID column. The prompt the user will see needs to be within square brackets, like [Please enter a Customer ID].

AND / OR

A table at the end of this document lists common operators useful to query building. Both AND and OR operators are useful when you have more than one criteria. In the case of AND, that operator is used when you want both conditions in the criteria to be met. The operator OR is used when you want either condition in the criteria to be met. Keep in mind that all attributes in the BA342 Company database are only one value, therefore a simple rule of thumb for using AND versus OR is to use OR when you have multiple criteria for a single attribute, and to use AND when you have a single criteria for each of multiple attributes.

Expression Builder

Creating calculated fields or adding complex criteria is simplified with the tool in Access called Expression Builder (Builder). For example, when you want to add a calculated column to a query, while developing the query you select an empty column and enable Builder. A window opens up with tools to help you easily develop the expression you want.

Date attributes

Date attributes are a special type of attribute requiring special treatment when used as criteria or as part of a calculation. While you may find you can treat a date like text or like a number, you should use the special operators and functions made specifically for dates. For example, when you want to tell Access that you are entering a date value as criteria, you begin and end that value with the # symbols, i.e. #1/2/2012#.

Parameter Wildcard Queries

For added flexibility, wild card characters can also be added to a parameter query, allowing the user to be able to provide a partial input for the query. Example: if you want to run the query based on criteria in the Customer Last Name column, and want the user to provide the first character(s) of the last name, then in the QBE grid criteria position under the Customer Last Name column, enter this text within square brackets: Like [Enter the first characters of the Customer's Last Name] & "*". Example: if you want to run the query based on criteria in the Product Name column, and want the user to provide any part of the product name, then in the QBE grid criteria position under the Product Name column, enter this text within square brackets: Like "*" & [Enter part of the Product Name] & "*".

IIF function

In Access, you can include logical arguments, such as IF statements, in expressions and calculated fields. The IF argument in Access is IIF - click on the Help question mark in the upper right corner of the Access window and search on IIF to learn that "IIf always evaluates both truepart and falsepart, even though it returns only one of them" which means that it works much like the Excel IF function you are familiar with.

Report creation, Grouping and Sorting, and Output to PDF

In BA342, we will only create a few simple reports and they will be created from a query. Access provides you with four views of a report; The Layout View, Design View, Report View, and Print Preview. By default, after requesting that a report be created from a query, Access opens the report in Layout View and shows the Design tab of the Layout Tools. The report title and column headers can be easily edited at this time. The option to add grouping and sorting is also available and is achieved by clicking on the Group & Sort tool in the Grouping & Totals section of the Report layout Tool Design tab.

Show or No Show

In the QBE grid of Access, each column of the grid that contains an attribute, calculation, or expression will, by default, be visible in the query output. This is because the default of the SHOW row is checked so that the column will be visible in the output. If you do not want a column to be shown in the query output, simply uncheck the box in the SHOW row of the column and that column will not be shown in the query output.

Query Operators >, <, >=, <=

Query criteria may include value comparisons and the use of operators such as greater than (>) or less than (<) may be needed. Calculated columns created in Builder also may use these and other common operators that are shown in Table 1 at the end of this document.

Expression Builder for Calculated Columns

Recall that Expression Builder (Builder) simplifies the adding of calculated fields or complex criteria. With Builder, you can easily look up and insert functions, operators, constants, and the names of fields, tables, forms, and queries. To add a calculated column to a query simply select an empty column and enable Builder. Under Expression Elements expand the list under the database name to choose the table that contains the attributes you need to build your expression. Include any necessary operators, such as +, -, *, /, etc., or text strings (within quotes) to build your expression. Remember too that a calculated column will need to have the Expr1: text replaced with the text you want as a title or header for your column. You may also need to make a format change to the Property Sheet of the column for it to display correctly in the query output.

Object Property Sheets

Recall that queries, tables, attributes, etc., are all objects in Access. With that in mind, you may occasionally need to make changes to the properties of some objects. One important example is in the case of an aggregate column in a query. The column title of the aggregate column in the query output will reflect the type of aggregation that took place such as Sum of or Total of. That column title is not useful and should be changed. To change it to a more meaningful title, enter the desired title in the Caption cell of that column's property sheet. You can access a property sheet in the Show/Hide section of the Design tab, next to the Totals feature. Other common changes you may make to a column's property sheet is the format of the column, or to add something called an input mask. For example, for a numeric field you can change the format of the column output to be currency by selecting that format from the drop-down menu of the Format field in the Property Sheet. Another example is to choose a telephone number input mask for a numeric field that contains telephone number data but isn't displaying like that in the query output. Note that the format choices provided will depend on the data type of the attribute.

SQL Syntax

SQL (structured query language) is a powerful database language. When you use the QBE Grid to develop a query using Microsoft Access, the software writes the SQL syntax for you in the background. Three query views are available in the Views section of the Home tab; the Design view, the Datasheet view, and the SQL view. Access will toggle between the Design and the Datasheet views when you click on the icon symbol in the Views section. To see the SQL view you need to click on the down-arrow and select to look at that view.

Concatenation

Sometimes you want the attributes included in the database output to be in a different format than they are in the table, or you want to add characters to an attribute. You can use Builder and a technique called concatenation that uses the ampersand symbol, &, to be able to "add" text attributes and or to append text characters such as spaces or commas to the attributes, and change the format of the attributes in the query output. Remember that SELECT queries are temporary views of data, so the data in the tables is not being changed, you are simply creating a temporary column in your query that will product the query output you want.

Yes/No fields (also true/false, binary fields)

Special fields that are of a logical format (binary, Yes/No or True/False) are common in an Access database. There are just two possible values for these attributes, so when applying criteria on this type of field you will use Yes or No (or you may use True or False) as the criteria. Be careful - these are logical fields, so the field values are NOT text and therefore do not need quotes. Also be sure to confirm the meaning of any of this type of attribute and confirm the default value by reviewing the attribute description and the field properties for the Yes/No attribute in the design view of its table.

Access QBE Grid

The QBE (query by example) grid in Microsoft Access is a tool that simplifies query development. The tool allows you to drag and drop tables and attributes, and writes the more complicated SQL syntax for you in the background. To open the QBE grid, click on Query Design on the Create tab in Access. The design view will open and the QBE grid will be at the bottom of the screen. The Show Table box will pop up providing the list of tables (and queries if there are any) that exist in the database. After making a choice, close the Show Table window and continue to design your query.

Is Not, <> (not equal)

The criteria used in queries so far has been criteria that limits the query view output to include those records that meet the criteria condition. But sometimes you want the query output to include all records except those records that meet the condition. The way you do that is to use the operator NOT which can be combined with IS. For example in the case where you wish to include all records that do not have a null value in a particular attribute, you can enter the criteria as Is Not Null. Another common operator is the symbol, <>, that means not equal. For example when you want all records except those where an attribute's value is 5 you could enter the criteria as <>5.

Criteria (WHERE)

The definition of query criteria are the restrictions that are placed on the query output. Therefore, when valid criteria are included in a query, the number of records that are in the output is reduced in comparison to the number of available records in the tables. Criteria Rule of Thumb - When a query contains attributes from a single table (no other tables are in the query) and valid criteria is applied, the resulting query output will contain fewer rows than there are records in the table. Therefore, if a query containing attributes from just one table produces an output with the same number of records (or more than the number of records) in that table, either the criteria is not necessary or there is something wrong with the query.

Changing your Windows Explorer Folder options

The file extensions in Windows Explorer are hidden by default, but if you wish to be able to see the file extensions you can change that setting. The steps may vary slightly between Windows versions, but in general 1) Open File Explorer, 2) Click on the View tab and select Options to change folder and search options, 3) in the Folder Options dialog box select the View tab, uncheck the box that says "Hide extensions for known file types" and apply the setting to folders.

Sort order

The output of an Access query is made up of columns and rows. The column order is controlled by the order that the attributes are placed in the QBE grid. The order in which the DBMS outputs the rows must also be controlled. Adding a sort to your query will ensure your query output is ordered in the way you want it, such as alphabetic or numeric order. The Sort row in the QBE grid of Access includes a drop-down list to choose your sort order - ascending (A-Z, low to high numbers) or descending (Z-A, high to low numbers). Without a sort command in a query, the output order of the rows is unpredictable. The Access DBMS reads columns from left to right and therefore will sort from left to right. This means that when you use multiple sorts in one query, the left-most column will be sorted first, then the next column is sorted but it is done so "within" the first sort. This sorting method results in an output with characteristics similar to grouping.

Zip folders

The project assignment requires that you submit the deliverables in a .ZIP folder. All modern operating systems include the capability to create a .zip folder, so do NOT acquire and install any other application to achieve that requirement (do NOT use WinRAR, 7-zip, or any other proprietary application). If you need help, see the assignment instructions for links to online assistance with zip folders under Windows 7 or 10. A link was not included for the Mac OS because the project zip folder needs to be created in a Windows environment.

Intro

The queries written in BA342 are SELECT queries and a few will also be PARAMETER queries. These queries are temporary views of data, and the data in the tables will not be changed by those queries. While there are other query types, such as UPDATE or DELETE queries that will change data in a database, we focus on SELECT queries in BA342, so NEVER change the data in the tables! Instead, learn to develop a query that will produce the output to answer the business question. If at any time you think you may have changed the data, simply download the database file again from the course web site.

Objects in Access

The tables, queries, reports, etc. that are in Access are Objects. Objects are saved individually, they have their own name, and they have their own properties. Objects can be copied, edited, deleted and their properties can be viewed by choosing those actions from the quick menu that is enabled when you perform a right-mouse click on the object. For example, to change the name of a query, perform a right-mouse click on it, and choose Rename from the menu. The query's name will then be highlighted, you can edit it, and the name will be saved once you click anywhere else on the screen. Another example is when you need a new query that is like one you have already developed. To copy a query, perform a right-mouse click on it, choose Copy from the menu, then in the blank area below the query objects perform a right-mouse click and choose Paste from the menu. Give the new query object the name you want, and then open the query design and edit that new query as desired.

Column Titles in Builder

When Builder is used to create a column for a query, Access gives it a placeholder title of Expr1: which is not very descriptive. Simply replace the Expr1: text with the text you want as the column title, but be sure you keep the colon. The query output will then show the column title you want instead of the Expr1 title.

Access lock file

When an Access database file is open, a temporary file called an Access Lock file is created. It is critical that you CLOSE your Access database file before you attempt to move it, copy it, upload it, or bundle it into a Zip folder. Once your Access file is closed, you can be confident that it is fully updated, the lock file will no longer exist, and you will not move, copy, upload, or bundle the lock file by mistake. (You should really follow that procedure for any kind of data file.) To see what the lock file looks like, do the following. While you have Access running and have the BA342 Company database file open, in Windows Explorer navigate to where the database file is saved. You will see another file next to your database file that has the same name as shown in the picture below, but with some important differences: Four differences exist between these two files. First, the lock file is TINY in size compared to your database file. This is because the lock file contains no data - its purpose is to lock the database while you are editing it (i.e., not allow anyone else to edit it at the same time). Second, the files have very different descriptions (see under Type column heading). Third, the file icons are different, with the lock file showing a tiny padlock in the upper right corner. Fourth, there is a difference in the file extension of the two files. The Access database file has the extension .accdb, and the lock file has .laccdb (notice the "l" for lock). Note, I have my file extensions visible in Windows Explorer, so you may not see them on your computer. If you wish to have them visible, follow the next steps to change your folder options in Windows Explorer.

Multiple Table Queries

When developing a query in a relational database that includes attributes from more than just one table, make sure that the tables in your design are directly related. If any unrelated table is included in your query design, the query will NOT produce an output with any meaningful answer to the question you are asking of the data. To correct this problem, simply add the table that will provide the relationship, and no attributes from the linking table are needed.

Wildcards

When developing a query you may use a special character in your criteria that acts as a placeholder for an unknown number of characters. The special character is called a Wildcard. Wildcards are useful to locate more than one item that shares similar but not identical data. You can also use Wildcards when you are searching for records that match a specific pattern. One way to increase the flexibility of a query is to add a wildcard to the criteria of the query. The wildcard used in BA342 is *, the asterisk character.