Microsoft access week 6 homework/ database

week_6_pdf4.pdf

Home >Computer Science homework help >Microsoft access week 6 homework/ database

Week 6 - What We'll Be Working On This Week In this week's lessons we'll be looking at Select Queries in Access – creating them with the help of the Wizard and from scratch in Design View, incorporating Sort conditions into queries, and applying criteria as a means of filtering the data. Queries can save you lots of time, and as a result there's a tremendous amount of information that you need to be aware of.

Goals:

 Using the Simple Query Wizard to Create a Select Query on the basis of a single table page 9

 Resizing the areas of the Query Design window page 15

 Basing a query on multiple related tables page 17

 Adding, moving, and deleting fields on the QBE Design grid page 20

 Sorting the dynaset (recordset) by a single field in Datasheet View page 25

 Sorting the dynaset (recordset) by multiple fields in Datasheet View page 26

 Specifying a Sort order in the QBE Design grid page 28

 Hiding fields in the dynaset (recordset) page 31

 Specifying different criteria page 33

 Changing the dynaset (recordset) page 36

 Adding a table to the Query Design window page 36

 Using a query to find records with blank fields page 40

 Using wild cards in criteria page 43

 Creating a Select query without the wizard page 46

 Converting filtered data in a table to a query page 49 So get comfortable, and let's get to it!

Overview of Queries Queries are an essential tool in any database management system. A query is an Access object that provides a custom view of data from one or more tables, or other queries. It is a set of specifications that tells Access what information you want to see in the database, and how you want that information to be organized. You can define queries to select, update, insert, or delete data. You can also define queries that create new tables from data in one or more existing tables. Up to now you have learned how to create tables, modify them, and link or import tables from other data sources (Excel). When you define and run a select query, which selects information from the tables and/or other queries in your database, Access creates a dynaset (recordset) of the selected data. You can work with a dynaset (recordset) the same way that you work with a table: you can browse through it, select information from it, print it and even update the data in it. BUT unlike a real table, the recordset doesn't actually exist in your database. Access creates a recordset from the data in the source tables of your query at the time you run the query. You will see that queries are the best way to focus on the specific data you need for the task at hand. To create a query, you must specify the following:

 The record source This refers to the object that contains the records that Access will use for the contents of the query.

 The fields to be included in the query The fields that you can choose for the query will come from the object that was used as the record source - that is, from the table or query on which the current query is being based. However, it isn't necessary to use all of the fields from the record source in the query.

For example: Suppose you're basing a query on a table that contains the following 6 fields:

strCustName strCustAddress strCustCity strCustState strCustZip strCustPhone

In your query, you want to see only the Customer Name and phone number, so you include only those two fields. Access is aware of the fact that there are six fields in the record source, but it only displays two of the fields when you open or run the query.

 The desired Sort order for the query results The Sort order will be stored with the query, so that the query results will always be sorted as specified, until you change the Sort order. When Access solves a query, it tries to do it in the most efficient way. When you first construct and run a query, Access might return the records in the sequence you expect (for example, in primary key sequence of the table). However, if you want to be sure Access always returns rows in this order, you must specify sort criteria. As you later add and remove rows in your table, Access might decide that fetching rows in a different sequence might be faster, which in the absence of sorting criteria, might result in a different row sequence than you intended.

 Any criteria that you want applied to the data Herein lies one of the great powers of queries. Entering selection criteria in a query is similar to entering a validation rule for a field. By applying one or more criteria to the data in the record source, you enable Access to apply filters to your data. Once the query is saved,

those filters are stored with the query, and will automatically be applied to the data every time the query is run.

 Whether or not a given field in the query is displayed In a query you can include as many fields as necessary for sorting or filtering purposes, but all of the fields in the query do not need to be displayed. Let's go back to the hypothetical Customer query we were talking about a moment ago. Suppose you want to list only your customers in New Hampshire. In this case, you would have to include a criterion that tells Access that records are to be included in the query results only when the State field contains NH. Suppose, also, that you only want to display the customer name and phone number in the query results. In order to do this, you need to include three fields in the query:

strCustName strCustPhone strCustState

The first two fields (strCustName and strCustPhone) are for display purposes, while the third field (strCustState) is only included so that you can specify a criterion for the state. Therefore, the strCustState field will not be displayed.

The result of a query (that is, the records that are displayed when you run the query) is referred to as a recordset or dynaset, which is derived from a dynamic subset. The dynaset is dynamically linked back to the record source, so that any changes made to it will automatically be reflected in the table or tables on which the query was based. Conversely, changes made to the record source (the tables) will automatically cause the dynaset to change. There are four categories of queries:

1. Select Queries These are the most common types of query, because they are easy to create and you can get a lot of information out of them with relatively little effort. As its name implies, the select query selects information from one or more tables, creating a recordset (dynaset). In a Select Query you'll be able to apply criteria and Sort conditions, and you can also perform calculations on fields. Generally speaking, the data returned by a select query is updatable and is often used to populate forms and reports.

2. Special Purpose Queries

These will enable you to do such things as summarize groups of data, locate duplicate records in a table, and locate unmatched records in a relationship between tables. Examples of special purpose queries are:

 Total queries - these queries provide sums or other calculations (such as count) from the records returned by a select query.

 Parameter queries - these queries are similar to select queries except that, where the select query is static, the parameter query prompts the user for selection criteria before running.

3. Action Queries

This type of query is used for performing management operations on tables. For example, you can update or delete groups of records, create a new table from the dynaset, or append records to an existing table, just by running the query. The term Action Queries is significant because by running the query you are not just displaying a dynaset of records, you are performing an action.

4. SQL-Specific Queries

This type of query is something that, unless you have programming experience, you should build up to using. They're created with the SQL (Structured Query Language) programming language, rather than on the Query Design screen that is used in the other types of queries. We will not be creating SQL queries in this course.

Filters or Queries? - That is the Question You may, like many Access users, be wondering why you even need to go to the trouble of creating queries, when filters are so easy to apply to table data. There are some differences that occur in the two results. A filter restricts the view of data to a single table, whereas a query can involve multiple tables and queries. Filters and select queries both retrieve a subset of records from a table or another query. As a rule of thumb, you use a filter to view or edit some records temporarily in a datasheet or a form. If you want to return to the subset of records at a later time, you should use a query. Queries are separate database objects that appear in the Navigation Pane while a single filter is saved with a table. If you use a query, you do not need to open the table first because a query is an object in its own right. With a filter, you must open the table first before you can view the results of the filter or design a new filter. You use a query if you want to extract data from multiple tables, control which fields to display or perform calculations on field values. None of these operations is permitted with filters. The results of both filters and queries can be used as the source of data for forms and reports. You can also sort the results of both and save the sort order for use in a later work session. Both methods let you edit the data displayed in the results, if editing is otherwise permitted. If you need to extract data from more than one table, select only specific fields to include, or store as a separate object in the database, then you need to use a query. Queries are also required if you want to see the results without opening the underlying table, query, or form or if you want to include calculated values in the result. Even if you decide you need to have a query, you can use the easy Filter by Form or Filter by Selection tools to create a filter and then save it as a query. Access translates your filter by design into a proper query. What this all boils down to is that filters are quick and easy to use, but they're temporary because they're automatically removed when the table is closed. On the other hand, queries may be saved in the database file as separate objects and then subsequently re-accessed without having to be recreated. Here are the similarities between filters and queries, in a nutshell:

 Both enable you to display a subset of records.

 Both may be used as the basis of a form or report.

 In both, the subset of records may be sorted. Here are the differences between filters and queries, in a nutshell:

 In order to apply a filter to a table, the table must be open. In the case of a query, on the other hand, the table on which the query was based does not need to be open in order to see the dynaset.

 Filters can only display the data in the single currently open table, whereas queries may be used to extract data from multiple tables.

 Access always remembers the last filtering and sorting criteria you defined for a datasheet (if you saved the filter when the filtered datasheet is displayed). The next time you open the table's datasheet, all records are displayed, but the icon in the column's

heading indicates that a filter was created . To run the saved filter, click the Toggle Filter icon. If you want to save a particular filter/sort definition, click the Advanced icon in the Home / Sort & Filter group then click Save As Query, and give your filter a name. The next time you open the table, return to the Advanced icon, and then click Load from Query to find the filter you previously saved.

 In queries you have the option of displaying only a limited number of fields from the record source, whereas you don't have this option with filters. All of the fields in the currently open table must be displayed.

 Queries enable you to perform calculations on the data, whereas filters do not.

Goals for this section:

 Using the Simple Query Wizard to create a Select Query on the basis of a single table

 Resizing the areas of the Query Design window

 Basing a query on multiple related tables

 Adding, moving, and deleting fields on the QBE Design grid

 Sorting the dynaset by a single field in Datasheet View

 Sorting the dynaset by multiple fields in Datasheet View

 Specifying a Sort order in the QBE Design grid

 Hiding fields in the dynaset

Select Queries

You have the option of creating queries with the Wizard or creating them from scratch. Which option should you use? You'll need to decide for yourself, based on personal preference. We're going to do it both ways, so you'll have a good sense of what your options are. First, we'll use the Simple Query Wizard to create a query based on a single table. Next, we'll use the same Wizard to create a query based on multiple tables. Then we'll create a series of queries from scratch and incorporate different types of criteria into the queries. Finally, we'll use the Crosstab Query Wizard to summarize the data in a table.

The procedure for using the Simple Query Wizard is as follows:

1. On the Create tab, select the Queries group.

2. Click the Query Wizard icon.

The New Query window is displayed.

3. Select the Simple Query Wizard option.

4. From the Tables/Queries drop-down list, choose the first record source for the query. When you do, the fields in the selected table or query will be listed in the Available Fields box. For example, in the above graphic the tblBidData table has been selected as the record source for the query, and the bid information fields in that table are listed.

5. Select the first field that you want included in the query, and click the > button to add the field to the Selected Fields list.

Note: You can also double-click the field in the Available Fields list, a technique you may find faster.

6. Continue in the same manner, until all of the desired fields have been added to the Selected Fields list. Bear in mind the fact that the order in which you add fields to this list is the order in which they'll be displayed in the query. (However, the order can easily be changed in the query at any time; so don't spend too much time worrying about getting it "right".)

7. If you want to create the query on the basis of multiple tables or queries, select another record source from the Tables/Queries drop-down list.

8. Follow the same procedures as indicated above, to select the fields to be included in the query.

9. Click Next> to advance to the next screen of the Query Wizard. If you have selected fields from multiple objects or if any of the selected fields have Number or Currency as the data type, Access will ask if you want to see the data displayed normally with every field of every record, or if you want to summarize the data.

10. Select the desired option and click Next> to advance to the next Wizard screen.

11. Enter a name for the query.

12. Click Finish. The dynaset (the query results) will be displayed on screen.

Note: No criteria or Sort order can be added to the query through the Wizard. To include criteria, you will need to first create the query, and then display the Query Design window. There, you can specify the desired criteria and/or Sort order for the data in the dynaset.

Hands-On Activity: Using the Simple Query Wizard to create a Select Query on the basis of a single table Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

Our objective is to build a list of work orders that were finished after July 1, 2003, and include only the Work Order Number, Customer ID, Completion Date, Material Cost, Labor Cost, and Description fields.

1. If your Navigation panel is closed, open it and select Queries.

Note: You do not have any queries in this database at this time.

2. On the Create / Queries group, click the Query Wizard icon.

3. The New Query window is displayed.

4. Select the Simple Query Wizard option, and click OK. The Simple Query Wizard window is displayed, so that you may choose the record source and fields for the query.

5. Observe the contents of the Tables/Queries text box: Table:tblBidData is listed as the record source, because it is the first table on the Tables page of the database. However, you can select any existing table or query as the record source for the new query.

6. Click the  to display the Tables/Queries drop-down list. All of the tables in your database are listed (you may have 5 if you deleted the linked table, 6 if you did not delete the linked table, 7 if you did not delete the Excel table that you created when importing your data).

7. From the drop-down list, choose Table: tblWorkOrders.

8. In the Available Fields list, leave the strWorkOrderNumber field selected, and click the > to add the field to the Selected Fields list.

9. In the Available Fields list, double-click the strCustomerID field.

Note: This is always the fastest way to move fields from one window to another.

10. Double-click the following fields to add them to the Selected Fields list:

dtmCompletionDate curMaterialCost curLaborCost memDescription

Note: If you end up with any fields in the Selected Fields list that you don't want, simply select the field in the right pane and click the < button to move it back to the Available Fields list.

The finished list should look as follows:

11. Click Next >.

12. Because we included two currency fields in the list of fields to be included in the query, Access gives us the option of showing every field for every record, or of summarizing the data.

13. Leave the Detail (shows every field of every record) option selected.

14. Click Next>.

15. Type qryPostJuly2003WorkOrders as the name of the query.

16. Click Finish. The query results are displayed in Datasheet View. All 15 records of tblWorkOrders are displayed because we haven't applied any criteria to the query.

17. Widen the Completion Date and Description columns, so that all of the information (including the caption) is fully displayed.

Remember that you can double-click the column heading separator line, as you do in Excel, so that Access will adjust the width of the column to accommodate the longest entry in the column.

18. On the Home tab, click the Design View button to see the Query Design screen.

The Query Design Screen

The Query Design screen is similar to the Advanced Filter/Sort screen. The screen comprises two sections:

The upper pane contains the field lists for all of the objects on which the query is based. If your query is based on multiple tables that are related (through a One-to-One or a One-to-Many relationship) then a join line will connect the two related fields. Whenever you have more than 1 object in the upper pane, be sure that you ALWAYS have a relationship line. NEVER have an object 'stand alone'. If Access finds a 'standalone' object, it performs another search and you will end up with duplication of records. The lower pane, called the QBE (Query by Example) Design grid, is where you indicate the Fields, Sort order, whether or not a field is to be Shown in the dynaset, and Criteria to be included in the query. Many different elements on the Query Design window may be resized: The size of the upper and lower panes themselves may be adjusted, by placing the mouse pointer on the horizontal dividing line between the two panes and dragging the dividing line up or down. (The mouse pointer changes to a vertical double-edged arrow with a line running through it when it is correctly placed on the dividing line.) The width of columns in the QBE Design grid may be changed, by dragging the column separator line to the right or left. (The mouse pointer changes to a horizontal double-edged arrow with a line running through it when it is correctly placed on the column separator line.) The field lists in the upper pane may be resized by dragging one of the edges or corners of the object. (Again, the mouse pointer changes to a double-edged arrow when placed on the edge or corner of the object.)

Observe the Show row in the QBE Design grid: Check marks are added to all fields by default, so that all of the fields will be displayed in the recordset when the query is run. However, bear in mind that you can include one or more fields in the query for sorting or criteria purposes, without having to display those fields in the dynaset. This is, in fact, one of the most powerful uses of

queries in Access.

Observe the Run icon on the Design / Results. . This is the button you'll use to run the query, in order to generate the dynaset of records. The View icon allows you to switch between Design View & Datasheet view but does not actually run the query. This is a great icon to use when you are creating Action queries and want to verify that you are getting the correct results.

Hands-On Activity: Resizing the areas of the Query Design window Before beginning: The qryPostJuly2003WorkOrders query is displayed in Design View, and no criteria have yet been applied to the query.

1. Position the mouse pointer on the dividing line between the upper and lower panes of the window. The mouse pointer should change to a vertical double-edged arrow with a horizontal line running through it.

2. Drag down, to make the upper pane bigger. As you do, a thick black horizontal line shows you what the size of the panes will be when you release the mouse.

3. In the upper pane, position the mouse pointer on the bottom edge of the Field list, so that the

mouse pointer changes to a double-edged vertical arrow.

4. Drag down, to display all of the fields in the Field List.

5. Position the mouse pointer on the right edge of the field list, and drag to the right to display the complete names of all of the fields. The entire contents of the field list should be fully displayed.

6. Click the Save icon on the Quick Access Toolbar (QAT) to re-save the design of the query.

Whenever you change the structure of an object, you have to save it in order to be able to retain the changes.

7. Close the query. The new query object now appears under the Queries on your Navigation panel.

Creating a Query on the Basis of Multiple Tables

One of the most powerful features of Access is the fact that you can build a query on the basis of multiple tables and pull fields from any one of the tables. If the tables are related, Access automatically displays a join line between the related fields and when the query is run, it will only display the records that all of the related tables share in common. If the tables are not related, Access will look for similarities in the field names and data types, and try to create a relationship between the tables based on those similar fields. If Access is unable to create a relationship, you must do it by dragging between the two fields in the upper pane of the Query Design window.

Note: If you are basing a query on multiple unrelated tables, you will end up with redundant records when you run the query. This is referred to as a "Cartesian product" and is a common error that is committed in designing queries in Access. Anytime you expected to see 100 or so records in the dynaset, for example, and instead you have thousands of records, there's probably a problem with the relationships between the tables. You may have a table in your QBE that is not related to any of the tables.

Hands-On Activity: Basing a query on multiple related tables Before beginning: Your Home Tech Repair database file is open.

Our objective is to create a new query based on the tblWorkOrders and tblCustomer tables, to list the customers for which there have been work orders generated.

1. Open the tblCustomer table. There are 39 customer records in this table. (Keep this in mind

as you go about generating the query results.)

2. Open the tblWorkOrders table. This table has only 15 records, which means that not all customers have work orders assigned to them.

3. Display the Relationships window. (Database Tools / Relationships icon).

4. Observe the existing relationship between tblCustomer and tblWorkOrders: We created a One-to-Many relationship between these two tables, based on the strCustomerID field.

Referential Integrity has been enforced, as indicated by the 1 and the symbols on the join line.

5. Close the Relationships window.

6. Select the Create / Query Wizard (in the queries group) icon on the Database window.

7. Select the Simple Query Wizard.

8. Click OK.

9. In the Tables/Queries drop-down list, select Table: tblCustomer as the first record source for the query.

10. In the Available Fields list, double-click each of the following fields to add them to the Selected Fields list:

strCustomerID strCompanyName strContactPhone

11. In the Tables/Queries drop-down list, select Table: tblWorkOrders as the second record

source for the query.

12. In the Available Fields list, double-click each of the following fields to add them to the Selected Fields list.

strWorkOrderNumber strSupervisorIDLookup

memDescription

13. Click Next> to move to the next screen of the Wizard.

Because our query is based on multiple tables, Access gives us the option of summarizing the data in the dynaset.

14. Leave the Detail (shows every field of every record) option selected, and click Next> to

move to the next screen.

15. Enter qryCustomersAndWorkOrders as the name of the query.

16. Keep the option Open the query to view information selected.

17. Click Finish. The recordset is displayed in Datasheet View, and includes only the 15 records for which there's a match between the two tables.

Adding, Moving and Deleting Fields on the Query Design Window

Once a query has been created, other fields may be added to it, the field order may be changed, and fields may be removed. It is important to note that deleting a field from the Query Design Window does not delete the field from the table. To add fields to the QBE Design grid of the Query Design Window, locate the field in one of the field lists (in the upper pane of the Design Window) and double-click the field name. Newly added fields are always placed in the next available Field cell of the grid, but may be moved. To move a field in the QBE Design grid, you do as follows:

1. Place the mouse pointer in the column heading area, at the top of the field column, so

that the mouse pointer appears as a

2. Click, to select the column, then release the mouse.

3. Leaving the mouse pointer on the column heading of the field, hold down the mouse button and drag the field to the desired location in the QBE Design grid. A solid black vertical line will indicate where the field will be positioned when you release the mouse.

Note: If you want to move multiple contiguous columns at the same time, select the columns and then drag from the heading of any one of the selected columns. All of the selected columns will move as a group.

To remove a field from the QBE Design grid, click in the column heading of the field, to select the column. Then press the Delete key on the keyboard.

Hands-On Activity: Adding, moving and deleting fields on the QBE Design grid Before beginning: The qryCustomersAndWorkOrders query is displayed in Datasheet View.

1. On the Home /Views, click the Design View icon to display the Query Design Window.

2. Drag the dividing line between the two panes down, to increase the size of the upper pane.

3. Increase the size of the Field List objects so that all of the fields are visible.

4. In the tblWorkOrders table, double-click the curMaterialCost field. The field is added to the next available Field cell.

5. Double-click the curLaborCost field in the tblWorkOrders Field list, to add that field to the QBE Design grid.

6. Position the mouse pointer in the column heading at the top of the memDescription column, and click to select the entire column.

7. Release the mouse.

8. Position the mouse pointer in the column heading at the top of the memDescription column again, hold down the left mouse button, and drag the field to the right of the curLaborCost column. A vertical black line is displayed where the field will be placed when you release the mouse.

9. When the vertical line appears to the right of the curLaborCost column, release the mouse to move the column.

10. Select the strContactPhone field, by clicking in the heading of the column.

11. Press the Delete key on the keyboard. The field is deleted and all subsequent fields are shifted to the left.

12. Re-save the query.

13. On Design / Results, click the Run icon, to see the results of the query. The same 15 records are displayed, but now there are 7 fields displayed.

Adding Sort Conditions to the Query

For the most part, sorting is a straightforward procedure in Access queries, because many of the techniques used to sort data in tables are the same in queries. If you want to sort the recordset by a single field, you can leave the data displayed in Datasheet View, place the cursor in any record of the field to be used for the Sort, and click either the Sort

Ascending or the Sort Descending from the Home /Sort & Filter group. To sort by multiple contiguous fields in Datasheet View, select the columns and then click either the Sort Ascending or the Sort Descending button on the toolbar. If you want to sort the query by multiple non-contiguous fields, you need to specify the Sort order on the QBE Design window.

Note: The main thing to bear in mind with regard to sorting is that Access sorts from LEFT to RIGHT. Therefore, you need to make sure the fields are positioned correctly before you sort on multiple fields. If they aren't correctly positioned, you'll need to move the fields.

If you want to use a particular field as the basis of a sort but you don't want that field to be displayed in the dynaset, you can simply deselect the Show box for that field on the QBE Design grid. Access will continue to sort by that field, but simply won't display it.

Hands-On Activity 1: Sorting the dynaset by a single field in Datasheet View Before beginning: The qryCustomersAndWorkOrders query is displayed in Datasheet View.

1. Observe the current order of the records in the dynaset: Access used the Primary Key field of tblWorkOrders (strWorkOrderNumber) as the basis of the Sort, because there will not be any duplicates in that field of the dynaset.

Note: You may have noticed that although strCustomerID is the primary key field of tblCustomer, the data in that field appears multiple times in the dynaset. This is logical, because a single customer at times will have multiple work orders. As a result of this potential redundancy, Access opted not to use that field as the basis of the sort when displaying the dynaset.

2. Place the cursor in any record of the Supervisor Lookup ID field.

3. Click the Ascending icon on the Home / Sort & Filter toolbar

The records are now sorted by Supervisor ID. Notice that Supervisor 10 works with 2 customers (ID numbers 1032 and 1039), and that Customer ID 1032 is listed twice. Suppose you wanted to sort primarily by Supervisor ID, and then (when there are duplicate supervisor ID's) sort by Customer ID.

Hands-On Activity 2: Sorting the dynaset by multiple fields in Datasheet View Before beginning: The qryCustomersAndWorkOrders query is displayed in Datasheet View. The dynaset has been sorted by Supervisor ID.

1. Click in the column heading at the top of the Supervisor Lookup field, to select the field.

2. Release the mouse.

Note: As with moving fields in a table, you must release the mouse before dragging the field to the new location.

3. With the mouse pointer on the Supervisor Lookup column heading, hold down the left mouse button and drag the field to the left of the Customer ID field.

4. Select the Supervisor Lookup and Customer ID columns.

5. Click the Ascending icon on the toolbar. The records are now sorted primarily by Supervisor ID. When there are duplicate Supervisor ID's, the records are sorted by Customer ID. Remember Access sorts from left to right.

6. Click the Design View button on the Home toolbar to display the Query Design Window. Notice that, despite the fact that we sorted the recordset in Datasheet View, no sort conditions have been added to the QBE Design grid.

7. Close the query. You're asked if you want to save the changes to the query design.

8. Click Yes, to save the changes.

9. Re-open the qryCustomersAndWorkOrders datasheet, and notice that the sort order is still in effect.

Hands-On Activity 3: Specifying a Sort order in the QBE Design grid Before beginning: The qryCustomersAndWorkOrders query is displayed in Datasheet View.

Our objective is to sort the dynaset by Completion Date, then by Customer ID.

1. On the Home tab, click the Design View to display the Query Design Window.

2. In the tblWorkOrders field list, double-click the dtmCompletionDate field to add it to the grid.

3. Move the dtmCompletionDate field to the left of the strCustomerID field. (Click in the field heading, to select the column, and release the mouse. Then drag from the field heading to the desired location in the grid.)

Remember: Access sorts from left to right.

4. Place the cursor in the Sort box of the dtmCompletionDate field. A  appears, to indicate

the presence of a drop-down list.

5. Click the  in the Sort box of the dtmCompletionDate field, and select Ascending.

6. Click the  in the Sort box of the strCustomerID field, and select Ascending.

You have now told Access that it should sort first by Completion Date. Whenever there are duplicate dates, it should sort by Customer ID.

7. Make sure that the dtmCompletionDate’s Show box is checked.

8. Click Run to generate the recordset.

9. Notice what Access has done:

 It has opted to display the dtmCompletionDate field with the other fields from tblWorkOrders.

 However, it has followed your directions regarding the Sort, and it has sorted the records first by Completion Date, then by Customer ID.

10. From the File tab, choose Save As / Save Object As / Save As

11. Change the name of the query to qryWorkOrdersByCompletionDate.

12. Click OK. As with other Windows applications, choosing the Save As… command in Access will enable you to change the name of the currently displayed object, thereby creating a new object.

Showing and Hiding Fields

Once fields have been used for sorting or filtering purposes, they do not have to be displayed in the recordset in order to continue serving as the basis of the sort or filter. By simply deselecting the Show box on the QBE Design grid, Access knows not to display the field. However, it will continue to follow the Sort or Filter instructions that have been applied to that field.

Hands-On Activity: Hiding fields in the dynaset Before beginning: The qryWorkOrdersByCompletionDate query is displayed in Datasheet View.

1. On the Home tab, click the View icon to display the Query Design Window.

2. In the QBE Design grid, deselect the Show box in the dtmCompletionDate column.

3. Run the query and observe the results: Although the completion date field is no longer displayed, it still serves as the basis for the sort. Records are still being sorted by Completion date, then by Customer ID.

The same holds true of fields that are used for specifying criteria. They don't need to be shown in the dynaset, but they continue to control the criteria for the query.

4. Re-save and close the query. At this point you should have 3 queries in your database:

Goals for this section:

 Specifying different criteria

 Changing the dynaset

 Adding a table to the Query Design window

 Using a query to find records with blank fields

 Using wild cards in criteria

Adding Criteria to Queries

By adding selection criteria to queries, you can easily filter the records in the recordset to display only those records that meet the specified conditions. Selection criteria range from the simple (involving a single criterion applied to a single field) to the complex (where multiple fields and multiple criteria are involved). Selection criteria can also incorporate Comparison Operators such as >= (greater than or equal to), <> (Not equal to), and Is Null, and the Logical Operators And, Or, and Not. Review Chapter 9 for all of the operators that Access recognizes.

All selection criteria are added to the Criteria or or lines of the QBE Design grid. If you specify multiple criteria in the Criteria row for multiple fields, all of the indicated criteria must be matched in order for a record to be included in the recordset. In the following example, records will only be included in the recordset if the Supervisor ID is 12 and the Material Cost is greater than 500.

On the other hand, if you want records to be displayed if they match any of the specified criteria then you need to use both the Criteria and the or lines of the QBE Design grid. For instance, in the following example records will be included in the recordset if the Supervisor ID is 12 or the Material Cost is greater than 500.

You can specify as many conditions and use as many or lines as necessary.

Hands-On Activity: Specifying different criteria Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. In the Navigation panel, right click the qryWorkOrdersByCompletionDate query object name, and select Design View.

This is an alternative to displaying the query results in Datasheet View and then switching to Design View.

2. Notice the changes that Access has made to the dtmCompletionDate field:

 Because we previously deselected the Show box for the dtmCompletionDate field, Access has moved it to the end of the list.

Note: After deselecting the Show Box and resaving the query, you MUST then close the query. Upon reopening the query you will see the fields moved to the right.

 Ascending is still specified as the Sort order for both of these fields.

3. In the Criteria cell of the curMaterialCost column, enter >500.

4. Press Enter. After specifying criteria of any kind, it's a good idea to press Enter so that Access can determine if there are any errors and make any necessary changes to the criterion.

5. Run the query. Of the original 15 records, only 6 records match the criterion.

6. Return to Design View.

7. In the Criteria cell of the curLaborCost column, enter >500.

8. Press Enter to record the criterion and move the cursor to the next column.

9. Run the query. Of the original 15 records, only 4 match both criteria.

10. Return to Design View.

11. Remove the criteria in the curLaborCost field.

12. In the curMaterialCost field, edit the criteria to the following:

>500 and <1000

If you want two conditions to be applied to the same field, you need to write the expression with the logical operator And between the two conditions.

13. Press Enter. Access changes the word and to mixed case and inserts spaces between the elements of the expression.

14. Run the query. Of the original 15 records, only 3 meet both criteria.

15. Return to Design View.

16. Change the expression in the curMaterialCost field to the following:

between 500 and 1000

17. Press Enter. Access changes the case of Between and And to mixed case, and adds spaces if necessary.

18. Run the query. The same 3 records that we saw previously are displayed. This is simply another way to apply multiple criteria to the same field.

The Dynamic Link between the Query and its Record Source

As indicated in an earlier lesson, queries are based on tables or other queries, and as such, they are dynamically linked to their record source. As a result, any changes made to the recordset of a query will automatically be reflected in the table(s) or queries on which the current query is based, and vice versa. This is good news, because it means that:

 You only have to make the change once and it will automatically be applied to all of the related objects in the database tapestry (so to speak) that you're weaving.

 You don't have to worry about compromising the integrity of the data by making changes. Suppose, as you look at the three records in the dynaset of records that have a Material Cost of between 500 and 1000, you realize that the Material Cost of work order #006, for Customer 1037 (Lowell Transport Co.) should be $450.00, not $750.00. If you make the change here in the dynaset, it will also be applied to the table(s) on which this query is based. In addition, the next time you run this query with Between 500 And 1000 as the criteria for the curMaterialCost field, Lowell Transport Co. will no longer be included because at that point it won't meet the criteria.

Hands-On Activity: Changing the dynaset Before beginning: The qryWorkOrdersByCompletionDate query is displayed in Datasheet View, with 3 records.

1. In the record for Lowell Transport Co., change the Material Cost value from $750.00 to $450.00.

2. Move off the focus, to save the change.

3. Close the query. When asked if you want to save the changes, click Yes.

4. Open tblWorkOrders and observe the Material Cost for record 6 (Work Order #006): $450.00 has been recorded here, as well, because this table is the record source for the Material Cost field of the query.

5. Close the table.

6. Re-open qryWorkOrdersByCompletionDate. Only 2 records are included in the dynaset because Lowell Transport Co. no longer matches the specified criterion of Between 500 And 1000 in the Material Cost field.

7. Display the query in Design View and remove the criteria from the curMaterialCost field.

Adding a Table to the Query Design Window

If at any time you want to add a field to the QBE Design grid but don't find the field in the field lists

that are currently displayed, you can use the Show table icon on the Design / Query Setup group to add other tables to the Query Design Window.

Suppose in the qryWorkOrdersByCompletionDate query you want to see all of the work orders for the employee named Wilson but you can't remember his Employee ID number. By adding the strEmpLastName field to the QBE Design grid, you can then use that field for specifying the criteria.

Hands-On Activity: Adding a table to the Query Design Window Before beginning: The qryWorkOrdersByCompletionDate query is displayed in Design View, with no criteria specified.

1. On the Design / Query Setup toolbar, click the Show Table icon.

2. On the Show Table window, select tblEmployeeHRData.

3. Click Add. The tblEmployeeHRData is added to the Query Design Window, and a join line automatically connects the table to tblWorkOrders as a result of the relationship that has been established between these two tables.

4. Click Close to remove the window from view.

5. In the tblEmployeeHRData field list, double-click the strLastName field to add it to the QBE Design grid.

6. In the Criteria cell of the strLastName column, enter O'Brien.

7. Press Enter. Because the criteria is text, Access automatically encloses it in double quotation marks.

8. Run the query. 5 records should be displayed.

On Your Own

Change the criteria for the qryWorkOrdersByCompletionDate query to do the following:

1. Locate all records for the employees named Adams and O'Brien. (7 records should be

included in the recordset.)

2. Display all work orders for Adams and O'Brien with a Start Date after June 1, 2003. (5

records should be included in the recordset.).

3. Display all work orders with a Start Date between 6/1/03 and 6/1/04. (11 records should be

included in the recordset.).

When you've finished this "On Your Own" exercise, remove all criteria from the QBE Design grid, re-save the query, and close it.

Using a Select Query to Find Records with Blank Fields

What often happens is that we set up a database, but then someone else - a data entry person or secretary in your company, a temporary employee, etc. - enters the records. Because that person may not have a vested interest in the database, they may periodically omit data from fields. This, of course, could pose problems for you, because you'll be depending on the fact that all of the data have been entered. The last thing you want to have happen is to get to the point of merging the data in your Access tables into a form letter or mailing labels, just to discover that many of the addresses or Zip codes are missing. What's the solution? Naturally, you could open each of your tables and look for missing data. But how long would that take, and what guarantee is there that you won't miss something? The best way to handle this situation is to build a query based on the table in which you want to check for missing data, and use the Is Null Comparison Operator to locate any records where the indicated field is blank. We'll use this technique for the tblWorkOrders table, where we want to assign a helper to Tony

Wilson and Rick Adams where currently there is no helper assigned to the work order.

Hands-On Activity: Using a query to find records with blank fields Before beginning: Your Home Tech Repair database is open and no objects are open.

1. On the Navigation Pane, open the tblWorkOrders table in Datasheet View.

2. In records 1, 9, and 13, remove Dawn Culhaney's name from the Helper field.

3. In records 2, 4, 8, 14, and 15, remove Calvin Hobbs' name from the Helper field.

4. Re-save and close the tblWorkOrders table.

5. Click the Query Wizard on the Create / Queries.

6. Select Simple Query Wizard on the New Query screen and click OK .

7. In the Tables/Queries drop-down list, choose Table: tblWorkOrders.

8. In the Available Fields list, double-click each of the following fields to add them to the Selected Fields box:

strWorkOrderNumber strCustomerID strSupervisorIDLookup strHelper

9. In the Tables/Queries drop-down list, choose Table: tblEmployeeHRData.

10. Double-click the strLastName field, to add it to the Selected Fields list.

11. Double-click the strFirstName field, to add it to the Selected Fields list.

12. Click Next>.

13. Type qryMissingHelpers as the name of the query.

14. Click Finish. The recordset is displayed, with all 15 records, because there are no criteria applied.

15. Switch to Design View.

16. In the Criteria cell of the strLastName field, enter Wilson.

17. Press the Tab key to move the cursor to the strFirstName field.

18. In the Criteria cell of the strFirstName column, enter Tony.

Note: In case you're wondering why we have to enter a first name, remember that there are two people named Wilson in our company – Tony and Andy. In the event Andy has work orders but doesn't have a Helper, we don't want his records to be listed in the recordset.

19. Press Enter.

20. In the Criteria cell of the strHelper column, enter Is Null.

21. Press Enter.

22. Place the cursor in the first or: cell of the strHelper column, and enter Is Null.

23. Press Enter to move the cursor to the strLastName column.

24. In the first or cell of the strLastName column, enter O'Brien.

25. Press Enter to move the cursor to the strFirstName column.

26. In the first or cell of the strFirstName column, enter Rick.

27. Press Enter to move to the next column. Your QBE Design grid should look as follows:

28. Run the query. 8 records are displayed.

29. Click to place the cursor in the Helper field of record 1. The  appears, indicating the presence of a drop-down list.

Note: You may have been noticing this already, but it bears mentioning nonetheless. This drop-down list in the Helper field of the query is here because it existed in the table on which the query was based. Whenever you build one object on the basis of another, the new object inherits the properties of the source object. What these means, is that if you plan ahead and build your tables well, you can capitalize on the work you've already done and not have to do things multiple times.

30. From the drop-down list, choose Dobbins, David as the Helper.

31. Click to place the cursor in the Helper field for record 2 and press Control " to repeat the value in the last record.

Remember that this keyboard shortcut works for repeating the value in the previous record even when there's a drop-down list.

32. Specify Jamison, George as the Helper for records 3, 4 and 5.

33. Choose Culhaney, Dawn as the Helper for records 6, 7, and 8.

The finished result should look as follows:

34. Save and close the query.

Using Wildcards in Criteria

Access recognizes the three wildcards *, ?,and # so that you can specify a portion of the criteria and have Access match it. The ? wildcard matches one character per ? in the position of the wildcard. For example, B??? in a Last Name field would match Bard but not Butler. The * wildcard matches any number of characters in the position of the wildcard, so that *3 will match all entries that end with the number 3, and B* will match all entries that begin with the letter B. The # wildcard matches any single digit. When you specify the *, ? or # wildcard in criteria and then press Enter, Access automatically adds the word Like to the criteria and encloses the criteria in quotation marks.

You can also specify that any particular position in the Text or Memo field can contain only characters from a list that you provide. You can specify a range of characters within a list by entering the low value character, a hyphen, and the high value character, as in [A-Z] or [3-7]. If you want to test a position for any characters except those in a list, start the list with an exclamation mark (!). You must enclose all lists in brackets ([ ]). Here are some examples of

validation rules using LIKE:

Validation Rule Tests For LIKE "#####" or A US 5 digit ZIP code

LIKE "#####-####" A US 9 digit Zip+ code

LIKE "###-##-####" A US Social Security Number

LIKE "Smith" A string that begins with Smith. (Character string comparisons in Access are case-insensitive. So smith, SMITH, and Smith are all equal.

Like "*smith##*" A string that contains smith followed by two numbers, anywhere in the string

LIKE "[!0-9BMQ]*####" A string that contains any character other than a number or the letter B, M, or Q in the first position and ends with exactly four digits.

LIKE "??00####" An eight-character string that contains any first two characters followed by exactly two zeros and then any four digits.

Hands-On Activity: Using wildcards in criteria Before beginning: Your Home Tech Repair database is open and there are no objects currently open.

1. On the Navigation Pane, double-click the qryWorkOrdersByCompletionDate query to display the recordset.

2. Switch to Design View.

3. Place the cursor in the Criteria cell of the strCompanyName field, and enter:

boston*

Note: When entering criteria, case doesn't matter.

4. Press Enter. Access changes the entry to Like "boston*"

5. Run the query. Of the original 15 records in the dynaset, only 5 have company names that begin with "Boston".

6. Switch to Design View, and delete the Like "boston*" criteria.

7. In the Criteria cell of the dtmCompletionDate field, enter 7/*/* to specify that you want to see all work orders that were completed in July.

8. Press Enter. Access changes the criteria to read Like "7/*/*"

9. Run the query. Of the original 15 records in the recordset, 7 were completed in July. However, you're not seeing the strCompletionDate column in the recordset because its Show box is deselected on the QBE Design grid. This could make it difficult to verify that you have specified the criteria correctly.

Note: Never assume that the results of a query are correct, because you may have made a mistake in typing without realizing it. Always verify everything. In the case of queries, if you don't want to see the field for which a criterion was specified, that's fine. But check the query first.

10. Switch to Design View.

11. Select the Show box in the dtmCompletionDate column.

12. Run the query again. Now it's obvious that the criteria where indicated correctly, because all of the completion dates are in July.

13. Close the query without saving the changes.

Goals for this section:  Creating a Select query without the wizard

 Converting filtered data in a table to a query

Creating a Select Query in Design View

As mentioned earlier, Select Queries may be created through the Wizard or from scratch, in Design View. Now that you've seen how to do it with the Wizard, let's take a look at how you can use Design View to create a query. In this way, you'll be able to make an informed decision as to which method you prefer. Our objective is to create a query that lists bid number, customer, and employee information for the work orders that have been generated. The data will need to come from the tblBidData, tblWorkOrders, tblCustomer, and tblEmployeeHRData tables, all of which are related through tblWorkOrders.

Hands-On Activity: Creating a Select Query without the Wizard Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Click the Query Design icon on the Create / Queries group.

2. A blank Query Design window is displayed, and the Show Table window opens so that you

may specify which objects you want to base the query.

3. Leave tblBidData selected, and click Add. The tblBidData field list is added to the upper pane of the Query Design Window.

4. In the Show Table window, double-click on tblCustomer, to add that field list to the upper pane. A join line connects the two tables because of the relationship that exists.

5. In the Show Table window double-click on tblEmployeeHRData and tblWorkOrders. At this point you should have four tables listed in the upper portion of the Query Design window.

6. Click the Close button on the Show Table window, to remove it from screen.

7. Expand the size of the upper pane (by dragging the line separator down), expand the field list objects so that their contents are fully visible, and move the field list objects as necessary so that the relationship lines are visible.

It isn't necessary to do this, but as you're getting accustomed to Access you may find it easier if you see everything.

8. In the tblBidData field list, double-click the strBidNumber field. Access places it in the first Field cell, and enters the name of the table in which the field is located.

9. In the tblBidData field list, double-click the strDefinition field. Access places it in the next available Field cell.

10. In tblCustomer, double-click the strCustomerID and strCompanyName fields.

11. In tblWorkOrders, double-click the strWorkOrderNumber field.

12. In tblEmployeeHRData, double-click the strLastName and strFirstName fields.

13. In tblWorkOrders, double-click the dtmStartDate and the dtmCompletionDate fields.

14. Specify Ascending as the Sort order in the strBidNumber field.

The finished Query Design screen should look similar to the following:

15. Run the query.

16. Observe the title bar at the top of the query datasheet:

Query1 tells you that this Query hasn't been saved yet.

17. Click the Save icon on the Quick Access Toolbar (QAT) and type qryBidInfo as the query name.

18. Close the query.

Converting a Filter to a Query

Before we leave this section, there's one more thing to look at. Previously we mentioned the fact that if you've already used the Filter by Form method to apply a filter to a table and decide you'd like to store the filter results for future access, you have the ability to convert the filtered data to a query.

The procedure is as follows:

1. Once the filter has been applied to a table, go to the Home / Sort & Filter group and click on the Filter by Form icon.

This displays the Form grid, with the Criteria that you specified in order to create the filter.

Note: This is important. You can only save the filter as a query from this screen. If you don't see this button on the toolbar, you're on the wrong screen. Click the Filter by Form button on the toolbar to display the Form grid.

2. On the Quick Access Toolbar (QAT), click the Save icon .

3. Type a name for the new query object and click OK.

4. Close the Filter by Form window, by clicking the X in the upper right corner of the form.

5. Remove the filter from the table and close the table without saving the changes.

Hands-On Activity: Converting filtered data in a table to a query Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Open the tblCustomer table in Datasheet View.

2. On the Home / Sort & Filter group, click the Advanced / Filter by Form icon.

3. On the blank Filter form, enter ca in the State field.

4. Press Enter to move the cursor out of the cell. Access changes the criterion to "CA".

5. On the Home / Sort & Filter group, click the Advanced / Apply Filter/Sort icon.

8 records are displayed in the dynaset.

This is the subset of records that you want to save as a query.

6. On the Home / Sort & Filter group, click the Advanced / Filter by Form icon again. The form is displayed with the State criteria.

Note: Unless you're on this screen, you won't have access to the Save As Query button on the toolbar.

7. Click the Save icon .

8. Enter qryCACustomers as the name of the new query object.

9. Click OK.

10. Close the Filter by Form screen.

11. Remove the filter from the table, and then close the table without saving the changes. There is no need to save the filter specifications in Access' memory bank, because you have the query.

12. Open the Queries on the Navigation Panel and double-click the qryCACustomers query to display it in Datasheet View. The 8 California customer records are displayed in the recordset.

13. Switch to Design View and observe the QBE Design grid. Access has created the query with the criterion of "CA" in the State field.

14. Close the query.

Assignment for Week 6

1. Review chapters 8, 9, & 10 of the textbook.

2. Do Final Database Project Assignment #3. Due by midnight Eastern Standard Time on

Sunday February 28, 2016. This is the third of the 8 individual assignments that you'll be doing to create your own database. In this assignment, you're going to make sure your relationships are set correctly so that the information in the Subdatasheets is displayed as desired. In addition, you will define properties for the fields in the tables and you will create some Value Lists to facilitate the data-entry process. Here are the specifics of the assignment:

1. All data in MUST be fully displayed.

2. Make sure your relationships are set correctly, so that when you click the plus sign to the left of a record in a table you are able to see the desired data in the Subdatasheet. I would like to see the following datasheet/subdatasheet combinations of information:

 The contacts for the different customers in the tblCustomers table. When you click the plus sign next to a customer, you should be able to view the contacts for that customer.

 The customers for each sales rep and the contacts for the displayed customer.

 The orders that have been placed for the different products.

 The territories with the customers and contacts for each of the territories.

3. Define the following Properties:

 Assign Input Masks for all Phone fields.

 Define all Zip Code fields, and ensure that they all are displayed with five digits.

 The State field data should automatically appear in uppercase.

 A maximum of 40 hours should be allowed in the Hours field for sales reps. If the end-user enters a value that is greater than 40, the following message should be displayed:

The number of hours must not exceed 40.

 A required Sales Rep ID in the Customers table.

 Dates entered into the Hire Date field must be today's date or earlier. If the user enters an invalid date and Alert Box should display an appropriate message.

 The Active Yes/No field in tblCustomers should default to Yes.

 The Discount values (for the products) should be formatted to display with percent symbols and 2 decimal places.

 Only values from 1 to 8 should be accepted in the Territory Code field. If a value outside this range is entered, an Alert Box should be displayed with an appropriate message to the user.

4. Create the following Value Lists to facilitate the data-entry process:

 Territories

In tblCustomers, create a two-column Value List that displays the Territory Code and its corresponding name next to it. When the end-user displays the list, they will see both the code and its name, which would make it easier to select the correct code. This, in turn, will minimize errors.

Make sure both the code and the full territory name are fully displayed when the Value List is displayed in the field. When the user chooses one of the territory codes from the Value List, the territory code number is displayed in the field.

 Contact Title

Create a one-column Value List for the Contact Title field of the tblContacts table that lists all of the most common prefixes (Mr., Ms., etc.) for contacts. Include on this list the titles that you feel are most useful or appropriate, given your own database needs and preferences.

 Product Category

Create a one-column Value List for the Category field of the tblProducts table, to include 3 items:

Dim. Board Plywood