Ms Access part-2

Chapter 3. Using Data Macros
Data macros are arguably the best new feature in Access 2010. In the previous chapter, I showed you how to use foreign key and other constraints to ensure data integrity. Data macros will allow you to take this to a whole new level. Constraints keep the user from doing bad things, while data macros can do good things to keep the data in sync. For example, when a Loan is created, data macros can be used to automatically update the InventoryItem to show it has been checked out. Similarly, when the Loan is updated to show it has been checked back in, the InventoryItem is updated as well.
In earlier versions of Access, this type of logic had to be implemented with VBA code as part of the UI implementation. You'll see as you read through this chapter that this logic fits more naturally in the data layer. This also frees up the UI implementation to focus on the user experience.
In this chapter, I'll show you how to use data macros to implement business rules and maintain data integrity as a natural extension of data modeling.
Understanding Data Macros
A data macro is logic that you can execute when certain data events are raised. Data macros are similar to triggers in SQL Server. The events that are supported are shown in Figure 3-1. Each data macro is associated with a specific table. To add or view a macro, you'll need to first open the desired table (in Datasheet View) and select the Table tab in the ribbon.

Figure 3.1. Data macro events
These events are grouped into two categories: before events and after events. As you might expect, before events are raised before the record is updated (or inserted or deleted) and after events are raised after the change has been made. There are two before events: Before Change and Before Delete. The Before Change event is raised both before an insert and before an update. In your macro, you can use the IsInsert property to determine which action generated the event. In contrast, a separate after event is raised for each possible action (insert, update, or delete).
Identifying Data Macro Limitations
Apart from this, there are some fundamental differences in before and after events and what you can do with them. The following are the important limitations to keep in mind:
Before events can only modify the data in the current record; you cannot modify other records in this (or other tables).
After events can update other records and other tables, but they cannot update data in the current record.
Before events can raise an error, which will cancel the initial change that triggered the macro.
After events can raise an error, but this will not halt the update; it has already been committed.
After events can log records to the application log table, but before events cannot, since that is another table.
After events can call a named macro, but before events cannot.
Creating Your First Data Macro
I think this will start to become clearer as you implement a macro. For the first macro, you'll update the status of an InventoryItem record when it has been loaned out. You'll use the After Insert event on the Loan table. When a Loan record is created, the macro will update the associated InventoryItem record, changing its Status value to Checked Out.
1. Open the Loan table using the Datasheet View, select the Table tab, and click the After Insert button. This will display the Macro Editor. A blank macro is displayed with a dropdown list that you'll use to add an action to the macro, as shown in Figure 3-2.

Figure 3.2. The available actions
2. Select the Comment action and a Textbox will appear. Enter the following text:
Update the associated InventoryItem record to show it has been checked out
3. Then, in the dropdown list, select the LookupRecord action, which loads a record based on the where clause that you'll provide. The editor will expand this action, showing the parameters that need to be supplied, as shown in Figure 3-3.

Figure 3.3. The LookupRecord action parameters
4. For the Look Up A Record In parameter, select the InventoryItem table.
TIP
Most of the fields in the Macro Editor support IntelliSense. Just start typing and the appropriate choices will be displayed for you to select.
5. For the Where Condition, enter
[InventoryItem].[InventoryItemID]=[Loan].[InventoryItemID]
6. The Alias will default to InventoryItem; you can leave the default value, for now. (I will explain aliases later.) Notice that there are now two Add New Action dropdown lists. One is inside the LookupRecord block and the other is outside the block. In the first one (inside the block), select the EditRecord action, which will allow you to make changes to this record. This action will be expanded as shown in Figure 3-4.

Figure 3.4. The EditRecord action
7. Leave the Alias field blank. Notice that there are now three Add New Action dropdown lists. The Macro Editor does a pretty good job keeping this straight by indenting appropriately and highlighting the selected block. For the one inside the EditRecord block, select the SetField action, which modifies a single field of the selected record. The expanded action is shown in Figure 3-5.

Figure 3.5. The SetField action
8. For the Name property, enter InventoryItem.Status and for the Value property, enter Checked Out. The completed macro is shown in Figure 3-6.

Figure 3.6. The completed macro
9. Click the Save button to save your changes and then click the Close button to close the Macro Editor.
Notice that the After Insert button in the ribbon is highlighted, which indicates that there is a macro defined for this event as demonstrated in Figure 3-7.

Figure 3.7. The highlighted After Insert button
Testing Your Macro
Now let's try it out. Enter a new record in the Loan table. Just select a customer and inventory item from the dropdown lists and save the record. Then go to the InventoryItem table. You should see that the one you selected in the Loan record now has a status of Checked Out as shown in Figure 3-8.

Figure 3.8. The InventoryItem record is checked out
Exploring the Macro Editor
You'll be creating several more data macros, but before you do that, I want to explain some of the features of the Macro Editor. As you've noticed, you don't write code; instead, you design a macro by adding actions and filling in the appropriate parameters. The Action Catalog, shown in Figure 3-9, provides a good overview of the available actions.

Figure 3.9. The Action Catalog
There are three types of actions: Program Flow, Data Blocks, and Data Actions. The Comment action, which you have already used, simply adds a line of text to explain something about the macro. You'll use the Group and If actions later. The Group action allows you put a set of actions in a group, which can then be collapsed. This is roughly equivalent to the #region blocks in .NET code. The If action lets you add conditional logic in your macro.
Understanding Data Blocks
Accessing and updating data is always done inside a data block. When you add a data block action to a macro, the editor indents the block and highlights that area of the macro. It does this to help you keep track of the scope of that block.
For example, you used an EditRecord action, which is a data block action. You then added a SetField action to update a specific field of that record. The EditRecord action defines the record that is to be updated. The SetField action has to be inside the scope of the EditRecord action. If you were to place them outside of that scope, the Macro Editor would not know which record to update.
The LookupRecord action works the same way. It looks up a single record. You can only access fields from that record while inside the scope of that data block. Likewise, you can only edit that record while inside that data block. That's why the EditRecord action is inside the scope of the LookupRecord action.
There are two other data block actions. The CreateRecord is used when you want to insert a new record. You add SetField actions inside this data block to specify the field values. The ForEachRecord action works like the LookupRecord action, except that it allows for multiple rows to be returned. You can then process each one in a for-next loop.
Using Aliases
An alias is used to name and then reference a data block. For example, the LookupRecord action was assigned an alias. The EditRecord action uses the same alias so the Macro Editor knows to update the record that was just returned by the LookupRecord action.
USING DEFAULT ALIASES
The Macro Editor does its best to avoid the need for you to deal with aliases. The default alias that the macro editor assigns is the table name. In most cases you can ignore the Alias field, as you did in the macro that you just implemented. If you need to work with two records from the same table, for example, the default alias would be ambiguous. In this case, you'll need to change the default alias for at least one the records. For those times that you need to specify an alias, you should understand how they work. First, I'll explain how the default aliases work.
In a data macro, the macro keeps track of the records that are being used. These records are referred to as data contexts. Initially, a macro is executed on behalf of a record that is about to be or has been modified. A data context is added to the macro for this record, and it's given an alias using the table name. In your macro this was the Loan table, and you access its fields by putting "Loan." in front of the field name.
Whenever you add one of the data block actions (CreateRecord, LookupRecord, or ForEachRecord – EditRecord is a special case, which I'll explain later), a new data context is added, and its default alias is determined by the table name. The LookupRecord action added a new data context with the InventoryItem alias. So inside the LookupRecord data block, there are now two data contexts, and their aliases are Loan and InventoryItem.
The default data context is the last one to be added to the macro. Think of this like a stack. Initially the Loan data context was added. It's on the top of the stack and is the default data context. When the LookupRecord action is executed, it adds the InventoryItem data context on top of the Loan data context. Inside the LookupRecord data block, the InventoryItem data context is the default context; it's the last one that was added. Once the macro exits this data block, the InventoryItem data context is popped off the stack and the Loan data context is now the default.
The Macro Editor will show the default in the Alias field, which is based on the name of the table. If you don't change it, when you tab off that field, the Macro Editor changes it to an empty field. This signifies that the default alias is being used.
USING THE EDITRECORD ACTION
Unlike the other data block actions, the EditRecord action does not create a data context. Rather, it must be assigned one to use, which is done by entering the appropriate alias. If no alias is supplied, it will use the default data context. Since we're inside the LookupRecord data block, the InventoryItem data context is used. If you were to add the EditRecord outside of the LookupRecord data block, the default data context would be the initial Loan record.
As you might have already guessed, using default alias and data contexts can leave room for error. It is also a little more difficult for someone else to understand how the macro is supposed to work. For these reasons, I recommend that you always explicitly supply an alias. You can use the default name, when appropriate, but you'll need to enter it in the field so the editor doesn't display a blank value. Figure 3-10 shows your first macro with explicit aliases.

Figure 3.10. The macro updated with explicit alias references
WHEN TO USE AN ALIAS
There are times when you need to define an alias instead of using the default alias. For example, if you're writing a macro for the Loan table. The default data context is given the alias Loan. Now suppose you wanted to look up the loan for the last time this item was checked out. You would add a LookupRecord action and the appropriate expression for the Where Condition. You would now have two records, both of which are from the Loan table. To avoid ambiguity, you would need to specify a different alias, such as PriorLoan. In subsequent expressions, you would specify Loan for the current record and PriorLoan for the previous loan.
Using Data Actions
The last section of the Action Catalog lists the available actions. I will demonstrate many of these in the remainder of this chapter. Some of these are only available in certain situations. For example, SetField can only be used inside of an EditRecord or CreateRecord data block. The Add New Action dropdown list in the Macro Editor will list the subset of actions that are allowed based on the context.
Navigating the Macro Editor
After you have entered actions to your macro, the Macro Editor allows you rearrange them. Figure 3-11 shows these editing controls.

Figure 3.11. The Macro Editor controls
When you select an action, there is a minus "-" button at the top left, next to the action name. Collapsing actions can make it easier to read your macro, especially with more complex macros. If the action is already collapsed, there with be a plus "+" button instead, which will expand the action.
At the top-right of the action there are green up and down arrows. Use these to change the order of the actions. If you select a data block action such as LookupRecord, the entire block will be moved. There is also an "X" button at the far top-right; use this to remove the action.
To add a new action, use one of the Add New Action dropdown lists. These are only available in a few locations. Generally there is one for each indention level. After adding the action, use the green arrows to move it to the correct location.
Implementing the Loan Before Change Event
The before events (Before Change and Before Delete) are ideally suited for adding validation prior to making a change. You can also use them to update the fields of the record being added or changed. Before a Loan record is added or modified, there are several validations that should be performed. I will explain these, one section at a time, and then show you the resulting macro actions that you can add into your database.
Making Sure the Item is Available
When a Loan record is created, the user specifies the inventory item that is being checked out and the customer that is taking it. You'll add logic to the Before Change event to ensure that this item is actually available to be checked out. If it is on hold, the logic will also ensure that this is the customer that reserved it.
This code first checks to see if this is an insert; we don't need to perform this check on an update. It then looks up the selected InventoryItem record. If the Status is not Available or On Hold, it raises an error and stops the macro. These two actions are included in a Group action, so the group can be collapsed for readability.
If the Status is On Hold, it looks up the Request record that is associated with this inventory item. If the requesting customer is not the same as the one checking out the item, the macro raises an error. When an error is raised on a before event, the error message is displayed in a pop-up window and the update is aborted. To add this Data Macro, perform the following steps:
1. Open the Loan table in the Datasheet View.
2. From the Table tab, click the Before Change button. This will display a blank macro.
3. Figure 3-12 shows how this logic should be implemented. Enter these actions into the Macro Editor.

Figure 3.12. Implementing the availability logic
Calculating the Due Date
Recall from the table design in Chapter 2 that the loan period is defined on the Media table. To determine the due date of a new Loan record, we'll need to look up the LoanPeriod from the Media table and add it to the current date. We will only perform this logic when this is an insert and when the DueDate field is not already specified.
To accomplish this, we'll need to perform a series of nested lookups. We'll first find the InventoryItem record and then the associated Item record, and then we can get the Media record. The DueDate field is then updated using a SetField action. The FormatDateTime() function is used to strip off the time portion. Also, note that an EditRecord action is not needed in a before event, because only the current record can be updated.
Add the actions shown in Figure 3-13 to the Before Change macro. Add these to the very end of the macro using the last Add New Action list box.

Figure 3.13. Implementing the DueDate logic
Calculating the Late Fee
When an item is being checked back in, the CheckedIn field on the Loan record is set to the current date/time. You can tell if a particular field is being changed by using the Updated() function. If the CheckedIn field is being modified and it is not null, then we can infer that this item is being checked in.
When an item is checked in, we need to see if it is overdue. You can check that by comparing the current date/time with the DueDate on the Loan record.
NOTE
You'll need to add a day to the due date to account for the time portion. For example, if the item is due on Jan 12 and it is currently noon on Jan 12, the due date, because it has no time portion, will be before the current date/time. By adding a day to the due date, we're allowing them to return the item up until midnight of the 12th.
This logic computes the number of days the item is late and then looks up the daily overdue fee from the Media table. Just as with the DueDate logic, this will require several nested LookupRecord actions.
Figure 3-14 shows the actions that are needed to perform this function. Add these to your current macro design.

Figure 3.14. Implement the OverdueFee logic
Validating Renewals
If the DueDate is being changed, you can infer that the loan has been renewed. In this case, you'll need to see if renewals are allowed and if this will exceed the number of allowed renewals. If this renewal is not allowed, the macro should raise an error, which will abort the update. If it can be renewed, then the macro should increment the renewal count on the Loan record.
To do this, you'll need to use nested LookupRecord actions to get the Media record that determines the renewal policy. If this renewal is not allowed, it will call the RaiseError action and stop the macro. Otherwise, the Renewals count is updated.
Figure 3-15 shows the necessary actions; add these to your macro design.

Figure 3.15. Implementing the renewal policy
Adding the Current Loan Reference
The Loan table has a reference to the InventoryItem table to indicate the specific copy that is being loaned out. It would be helpful to also have a reference in the other direction so each InventoryItem record will store the current Loan record, if it is currently checked out. You'll add the reference now and then use data macros to set this value automatically.
NOTE
This is an example of denormalization. The current loan for an inventory item can be determined by looking up all loans for that item and returning the last one. Therefore, this is redundant information. We will add it anyway, to optimize performance, but use data macros to minimize the negative consequences.
Adding the Lookup Field
Open the InventoryItem table using the Design View. Add a new field named CurrentLoanID and select the Lookup Wizard Data Type. This will start the Lookup Wizard that will guide you through setting up this field. You can refer to Chapter 2 for more details about this process.
1. In the first dialog box, select the first option, which is to look up the values from another table.
2. In the second dialog box, select the Loan table.
3. In the third dialog box, select only the LoanID field.
4. In the fourth dialog box, sort by the LoanID field.
5. Accept the default values for the fifth dialog box.
6. In the sixth dialog box, select the Enable Data Integrity check box and choose the Restrict Delete option.
After the CurrentLoanID field has been created, from the Design View of the InventoryItem table, select the CurrentLoanID field and the Lookup tab of the Field Properties. Make sure the Limit To List property is set to Yes.
Modifying the Loan After Insert Event
Now you'll need to make a minor change the After Insert event on the Loan table. This is the macro that you created at the beginning of this chapter. You've added a new field to the InventoryItem table and now you'll update the macro to also set this field.
1. Open the Loan table in the Datasheet View. From the Table tab, click the After Insert button. This will display the existing macro logic.
2. Add another SetField action within the existing EditRecord data block. To do that, click on the EditRecord action, which will add an Add New Action dropdown list at the end of the EditRecord data block.
3. Select the SetField action. For the Name property, enter InventoryItem.CurrentLoanID, and for the Value property enter [Loan].[LoanID]. The updated macro should look like Figure 3-16.

Figure 3.16. The updated After Insert macro
While you're editing this macro, there's another section that you'll need to add. When you check out an item that was on hold, the associated Request record should be updated and marked complete. Figure 3-17 shows the actions that you'll need to add to the After Insert event. Add these at the end of this macro.

Figure 3.17. Updating the associated Request record
Modifying the Loan After Update Event
When an item is checked back in, you'll need to update the associated InventoryItem record to show that it is now available and to clear the CurrentLoanID field. Save the After Insert event and close the Macro Editor. Then click the After Update button, which will display a blank macro.
When designing an After Update macro, you can use the Old property, which is an alias to the copy of the record before the changes were. To determine if an item is being checked in, you'll see if the old value of the CheckedIn property is null and the current value is not null. If it is being checked in, then edit the associated InventoryItem record. Figure 3-18 shows the actions that you'll need to add to do this.

Figure 3.18. Implementing the Loan After Update event
Handling Requested Items
When a customer makes a request to reserve an item, a Request record is created. This references an Item, not an InventoryItem. Whichever copy becomes available first, that InventoryItem will be the one that is reserved. Every time an InventoryItem becomes available, you'll need to check to see if there is an outstanding request for that item.
When a pending request is found, the Request record needs to be updated to change its Status to Ready and to reference the InventoryItem that is being reserved. The InventoryItem record also needs to be updated to change its Status to On Hold. Because of the limitations that I outlined at the beginning of this chapter, you'll need to implement this in two parts. The triggering event will be on the InventoryItem record (when its status changes to Available). The InventoryItem record must be updated in the Before Change event. However, the Request record must be updated in the after events.
Implementing the Before Change Event
Let's start with the Before Change event.
1. Open the InventoryItem table using the Datasheet View.
2. From the Table tab, click the Before Change button, which will display a blank macro.
3. Use an If action to see if the Status is being changed and the new value is Available. If this is true, then see if there is a pending Request record for this item. If there is, use a SetField action to change the Status to On Hold. The completed macro is shown in Figure 3-19.

Figure 3.19. The Before Change event
Now you'll need to implement the after events to update the Request record. There are two ways that an inventory item can become available:
A loaned inventory item is checked in.
A new inventory item is added; that is, a new copy is received into inventory.
The InventoryItem After Change event will catch the first scenario and the After Insert event will catch the second. You will perform the identical actions in both cases.
Creating a Named Data Macro
The best way to implement this is to create a named macro that is called from both events. Named macros still need to be associated to a table. Open the InventoryItem table using the Datasheet View. From the Table tab, click the Named Macro button and then click the Create Named Macro link, as shown in Figure 3-20.

Figure 3.20. Creating a named macro
This macro needs to look for a Request record for the current item (the one becoming available) and is in Pending status. Because this query could return multiple records, you'll use ForEachRecord action. After the first record is processed, it will call the ExitForEachRecord action to exit the for-next loop.
Figure 3-21 shows the actions needed to implement this. Add these actions to your named data macro.

Figure 3.21. Implementing a named macro
Named macros support parameters so you can pass information to them. However, when a named macro is called from a data event such as After Update, the data contexts from the initiating event are carried into the named macro. So this macro can access the InventoryItem data context because it was in the initiating event.
Click the Save button to save this macro. When prompted, enter the name AssignPendingRequest as shown in Figure 3-22.

Figure 3.22. Entering the macro name
Calling a Named Macro
Now you'll need to implement the After Insert and After Update events.
1. Click the After Insert button, which will create a blank macro.
2. In the Add New Action dropdown list select the If action. For the condition enter [InventoryItem].[Status] = "On Hold." For the action select RunDataMacro from the dropdown list.
3. The Macro Name property is a dropdown list that shows all of the existing named macros. There should only be one; select InventoryItem.AssignPendingRequest. The macro should look like Figure 3-23.

Figure 3.23. The InventoryItem After Insert event
Save this macro and close the Macro Editor. Then click the After Update button to define this event. The macro for this event should be identical to the After Insert event.
Computing Overdue Fees
You implemented logic in the Before Change event to compute the OverdueFee when an item is checked in (if it is overdue). However, for items that are still checked out, you will want to re-compute the current late fee on a daily basis. This might be part of a daily process that sends out reminder emails. To do this, you'll implement a named data macro, which will then be called from a user-executed macro.
Creating a Named Data Macro to Compute Overdue Fees
Let's get started.
1. Open the Loan table using the Datasheet View.
2. From the Table tab, create a named macro just like you did earlier. This will display a blank macro. Since this macro will be called from the UI there is no default data context.
3. The first thing that this macro must do is to execute a data block action. In this case you'll use the ForEachRecord action to find all Loan records that are overdue. For each record, you'll compute the number of days that it is overdue. Then look up the daily fee in the Media table. Finally, use the EditRecord action to update the Loan record. The complete macro is shown in Figure 3-24. Enter these actions in your macro.

Figure 3.24. Calculating the OverdueFee for all loans
4. Save the macro and enter the name CalculateAllLateFees when prompted, as shown in Figure 3-25.

Figure 3.25. Saving the CalculateAllLateFees macro
Creating a User-Executed Macro
To call the CalculateAllLateFees data macro, you'll need to create a macro (not a data macro).
1. From the Create tab, click the Macro button. This will create a blank macro. Notice that it uses the same Macro Editor, except there are different actions available to you.
2. In the Add New Action dropdown list, select the RunDataMacro action. Then select the CalculateAllLateFees macro from the dropdown list. The complete macro is shown in Figure 3-26.

Figure 3.26. Calling the CalculateAlllateFees data macro
3. Click the Save icon at the top of the window and enter the name CalculateLateFees when prompted. You should now have a Macro listed in the Object Navigation pane.
4. Right-click the macro and select the Run link to execute this macro.
Debugging Data Macros
If a data macro gets an error, if will be logged to the Application Log table. You can access this table from the File page. If not selected, click the Info tab. You should have a link on this page to view the Application Log as shown in Figure 3-27.

Figure 3.27. The File Info page
This link is only shown on this page if there is anything to report. If this page does not include this link, then there is nothing in the Application Log table. We'll take care of that now.
Using the LogEvent Action
You can also log your own messages to the log using the LogEvent action.
1. Open the Loan record using the Datasheet View.
2. From the Table tab, click the Named Macro button and then click the Edit Named Macro and CalculateAllLateFees links, as shown in Figure 3-28.

Figure 3.28. Editing the CalculateAllLateFees macro
3. This will display the existing macro. At the end of the macro, in the Add New Action dropdown list, select the LogEvent action. In the Description field enter The CalculateAllLateFees macro has completed. This action will look like Figure 3-29. Save and close this macro.

Figure 3.29. The LogEvent action
4. Now re-run this data macro by running the CalculateLateFees macro from the Object Navigation pane.
Viewing the Application Log
You should now have a link to the Application Log on the File Info page. Click this link to view the contents of this log. You should see an entry in the log similar to the one shown in Figure 3-30.

Figure 3.30. A sample application log entry
TIP
If there are new errors in the Application Log table, the link on the File Info page will have a red background.
Testing the Application
Try out your application and make sure the macros are working as expected. To test the basic scenarios try the following:
Insert a Loan record and verify that the Status of the selected InventoryItem has been changed to Checked Out and references the new Loan record. Verify the DueDate on the Loan record has been calculated. Set the CheckedIn date on the Loan record and verify the Status of the InventoryItem is now Available.
Create another Loan record. Then edit the DueDate field making it several days in the past. Run the CalculateLateFees macro and verify the OverdueFee has been calculated.
Create another Loan record and change its DueDate to be in the past. Then set the CheckedIn date and verify the OverdueFee was calculated.
Create a Request record. Then insert a new InventoryItem record that references the same Item as the Request record. Verify the Status of the InventoryItem is now On Hold and the Status of the Request record is Ready.
Summary
So far, by just creating the data schema in Chapter 2 and implementing data macros in this chapter, you have a working application. The macros implement many of the business rules such as computing due dates and overdue fees. You also handle requested items; automatically reserving an inventory item when one becomes available.
In this chapter you have learned about:
The uses and limitation of before and after data events
Using the Macro Editor
How data contexts and alias work in a data macro
Creating named macros
Calling a data macro from the UI
Using the Application Log to debug data macros
In the next chapter, you'll design both select and action queries





Chapter 4. Designing Queries
In this chapter, you'll design queries that will be included with your Library application. Throughout this project, you'll be creating a lot of queries. This chapter will introduce the basic concepts that you'll need to understand when creating and using queries in Access 2010. You'll create a few sample queries in this chapter, and then create the remainder in subsequent chapters as necessary.
There are two basic types of queries: select queries return data from one or more tables and action queries are used to insert, update, or delete records from a table. Access 2010 provides a Query Wizard that will guide you through the process of creating a query. Later I will also show you how to create and modify a query using the Design View.
Creating Select Queries
A select query is used to return a subset of data. For example, if you wanted to see all the InventoryItem records that are currently checked out. You could do this by creating a query that provides all the same columns as the InventoryItem table, and then adding a filter to only return the records with a Status of "Checked Out." Let's do that now.
Creating a Simple Query
From the Create tab in the ribbon, click the Query Wizard button, which will display the New Query dialog box, as shown in Figure 4-1.

Figure 4.1. The Query Wizard
Select the Simple Query Wizard option and click the OK button to display the next dialog box. In the Tables/Queries dropdown list, select the InventoryItem table. Then click the ">>" button to add all the fields, as shown in Figure 4-2.

Figure 4.2. Selecting the fields for your query
Click the Next button to display the final dialog box. Enter the name CheckedOutItems, as shown in Figure 4-3. Select the second radio button, which will open the query using the Design View. Click the Finish button to create the query.

Figure 4.3. Entering the name of the query
The Query Wizard will then create the query and display it using the Design View. At this point, the query looks just like the InventoryItem table. It has the same set of columns and the query will return all the rows in the table. This is equivalent to a SQL statement like SELECT * FROM InventoryItem. You'll need to add a filter to the query to restrict the rows that are returned.
The query fields are listed in the bottom half of the Design View. In the Criteria row of the Status field, enter "Checked Out" as shown in Figure 4-4. This will cause the query to only include the records that have a value of "Checked Out" in the Status field. In SQL syntax this is adding the WHERE Status = 'CheckedOut' clause.

Figure 4.4. Specifying the filter criteria
Save the query using the Save icon at the top of the application. To run the query, you can either click the Run button in the ribbon, or select the Datasheet View. The results of the query should look like Figure 4-5, depending on the data in your database.

Figure 4.5. Displaying the query results
Adding Tables
Because the database design has been normalized (see Chapter 2), you'll often find that the contents of a single table are not very useful by themselves. The CheckedOutItems query is a good example. It lists the inventory items that are currently checked out, but there are several details that would be nice to have. You probably want to know when it was checked out, when it is due back, and who has it. You will also want a description of the item. All of this is available in related tables. You'll now modify the query to include other tables to get the desired additional fields.
Open the CheckedOutItems query using the Design View. To include additional tables to the query, click the Show Table button in the Design tab of the ribbon. This will display the Show Table dialog box shown in Figure 4-6.

Figure 4.6. The Show Table dialog box
To add a table, select the desired table and click the Add button or simply double-click the table name. To add multiple tables, you can hold the Ctrl button down and select the tables you want to add and then click the Add button. Add the following tables to the query:
Customer
Item
Loan
Close the Show Table dialog box when you're done.
Using Joins
Notice that there are lines connecting the tables. Access automatically creates what are called joins in your query wherever there is a relationship between the tables. I will explain joins in more detail later.
TIP
You can remove, add, or edit these joins in the Design View. This has no effect on the actual table relationships.
Rearrange the tables so the connecting lines are all visible, as shown in Figure 4-7.

Figure 4.7. The CheckedOutItems query with additional tables
Notice that there are two lines between the Loan and InventoryItem tables. Both of these tables have a reference on the other table. The Loan table specifies the InventoryItem that has been checked out, while the InventoryItem specifies the current Loan. The first relationship is static; the Loan will always reference the same InventoryItem. The second is transient; each time the item is checked out, the InventoryItem will refer to a different Loan. It's very unlikely that you will ever want to use both joins in the same query. In this case, since InventoryItem is the main table that you're starting with, you'll use the CurrentLoanID field to get the associated Loan. Select the join that connects the InventoryItemID field on both tables and press the delete key.
UNDERSTANDING MULTIPLICITY
Each line that connects two tables represents a join, and a multiplicity indicator is shown at each of its endpoints. The multiplicity specifies the number of instances that are allowed on each side of the relationship. In the join between the Item and the InventoryItem tables, for example, there is a "1" next to the Item table, and an infinity symbol (∞) next to the InventoryItem table. This indicates a one-to-many relationship (or many-to-one, depending on your perspective).
The "1" next to the Item table indicates that each InventoryItem record can refer to, at most, one Item record. The infinity symbol specifies that an Item can be related to an unlimited number of InventoryItem records (in other diagramming notations the letter "m" is used to indicate this). In Access, the multiplicity only indicates the maximum number. In other notations that you may have seen, the multiplicity indicates the possible values, which are usually 0, 1 or m (many). You might see "0,1" for example. This indicates there can be at most 1, but 0 is also allowed.
The multiplicity properties can help you analyze a query. When you join the Item table to the InventoryItem table, the multiplicity of 1 means that you will not increase the number of rows in the result. There can only be one Item for each InventoryItem record. However, if you started with the Item table, and then joined the InventoryItem table, the "many" multiplicity indicates that you could end up with more rows than you started with. You can see in this query that you will have one row for each InventoryItem record. By joining the additional tables, you will not increase the number of rows.
USING THE JOIN PROPERTIES
Right-click the join between the Item and the InventoryItem table and click the Join Properties link. This will display the Join Properties dialog box shown in Figure 4-8.

Figure 4.8. The Join Properties dialog box
When joining two tables, one table is referred to as the left table and the other is the right table. The left table is the one that is being added to the query. The table that you started with is called the right table. You can see from the Join Properties dialog box that Item is the left table and InventoryItem is the right table.
NOTE
Because queries in Access are normally displayed graphically, you might think that the left table is the one on the left side of the join in the Design View. In this case that happens to be true, but it is purely coincidental. If you look at the properties for the Loan – InventoryItem join, you'll see that Loan is the left table even though it is on the right side of the join.
When joining two tables, you'll need to specify the column(s) in both tables that are used to find matching records. In this join, the ItemID column on the InventoryItem table is used to find Item record(s) that have an ItemID with the same value. In Access, you can only use a single column in a join. Because we're using a single primary key field in all tables, that limitation does not usually cause a problem; it makes the design simpler and easier to follow. If you have a scenario where you need to match on multiple columns, you can add additional joins between the same tables.
In database queries, there are the following three types of joins:
Inner: Returns only rows where there are matching records in both tables.
Outer: Returns all records in one table and the matching records in the other
Full: Returns records in both tables regardless of matches
The first option in the Join Properties dialog box indicates that an inner join should be used. For outer joins, you'll need to specify from which table all records should be returned from. A left outer join indicates that everything from the left table is returned, and only records from the right table where there is a match. This is the second option. The third option is the reverse of that, and is called a right outer join. Access doesn't support full joins.
Because the database is well constrained, it should not be possible to have an InventoryItem record that does not have a matching Item record. Any of the three options should give you the exact same results. However, I advise that you use outer joins in most cases. The purpose of this query is to return all items that are checked out. You're joining two additional tables to provide more details. Should the join fail for any reason, you still want to return the base record, InventoryItem, even if some of the details might be missing. You should choose the third option, which is a right outer join.
There are exceptions to this rule. Suppose, for example, the purpose of the query is to find customers that have items checked out. Should the join from InventoryItem to Loan or Loan to Customer fail, you could end up with an InventoryItem record with no Customer details. That would not be useful, so you choose option 1 or inner join to insure that you only get records that have a matching Customer record.
Edit the join properties for each join and select the third option. Notice that the connecting line between the tables now has an arrow head indicating the direction of the join. The line goes from the main table to the supporting table.
ADDING COLUMNS
The purpose for joining the additional tables is to add some columns to your query. The lower pane of the Design View shows the columns that are included in the query. There are the following three ways to add columns to this grid:
Double-click the column in the upper pane. This will add the column to the right of the existing columns.
Drag a column from the upper pane to the lower pane. This will add the column to the location you drag it to.
Manually edit a column in the lower pane and select the table and field information.
In each table in the upper pane there is an "*" field. By double-clicking or dragging this, you will add all the columns of that table to the query. Use the double-click method to add the following columns to the query:
Item.Title
Item.Author
Loan.CheckedOut
Loan.DueDate
Loan.OverdueFee
Customer.LastName
Customer.FirstName
Customer.PhoneNumber
Customer.Email
Save the query and run it. You should now see the additional details.
NOTE
When using an outer join and the matching record is not found, a row is still returned, but the columns provided by the related table will be null. Also, keep in mind that if a match is not found, any subsequent joins from the related table will also fail.
Making Additional Changes
Display the CheckedOutItems query using the Design View. There are some fields, such as CurrentLoanID, that may not be useful. You can remove these from the query. Select the CurrentLoanID field and press the Delete key. Remove the ItemID field as well.
The InventoryItem.Status field is used to filter the records that are returned. All the rows that are returned will have the value "Checked Out." It seems unnecessary to include this field in the query, since all records will have the same value. However, you can't delete it, because it is used as a filter. Instead, unselect the Show check box for this field.
If you want to change the order of the fields, select a field and then, while the field is highlighted, click on the very top of the grid column and drag it to the desired location. You can select multiple columns by holding down the Shift key and selecting each column. Using this method, make the Condition and Comment fields the last fields in the query.
ADDING CALCULATED COLUMNS
Go to the first empty column, which should be just after the Comment field. For the Field value enter the formula Date() – Loan.DueDate. When you tab off this field, the value will be changed as
Expr1: Date()-[Loan].[DueDate]
Access puts square brackets around table and field names since they can contain spaces. Notice that Expr1: was placed in front of the formula. Expr1 is the name of the column that will be used when displaying the results. Change Expr1 to DaysOverdue. Move this column to just after the Status field.
When entering expressions you can use the same Expression Builder that you used earlier. To display the Expression Builder, right-click on the Field value and click the Build link.
SORTING THE RESULTS
You can control the order in which the records are displayed by selecting one or more Sort fields. The grid in the lower pane has a Sort row. The default value is blank, which indicates it is not used for sorting. All of the fields are currently blank. To sort by a particular field, change this value to either Ascending or Descending. For the DaysOverdue field that you just added, change its Sort value to Descending.
You can sort on multiple fields. To add other Sort fields, simply set the sort value for that column in the grid. Set the Sort value for the Title field to Ascending. If there are multiple sort fields, they will be evaluated in the order that the fields are displayed. For this query you'll want the oldest items first and then sorted by the Title. Move the DaysOverdue column to before the Title field. The query grid should look like Figure 4-9.

Figure 4.9. The query grid with Sort fields
Save and run the query. The results should look like Figure 4-10.

Figure 4.10. The final CheckedOutItems query
USING THE SQL VIEW
For those of you who like working with SQL, you can view and edit the SQL that is generated by the Design View. There are buttons displayed at the bottom right-hand corner of the application that allow you to change the view of the current object. Click the SQL View button, as shown in Figure 4-11.

Figure 4.11. Selecting the SQL View
The SQL that is displayed lacks any helpful formatting. By adding some white space characters, this can be formatted as shown in Listing 4-1.
Example 4.1. The CheckedOutItems SQL
SELECT
   InventoryItem.InventoryItemID,
   Date()-[Loan].[DueDate] AS DaysOverdue,
   Item.Title,
   Item.Author,
   Loan.CheckedOut,
   Loan.DueDate,
   Loan.OverdueFee,
   Customer.LastName,
   Customer.FirstName,
   Customer.PhoneNumber,
   Customer.Email,
   InventoryItem.Condition,
   InventoryItem.Comment
FROM (Customer RIGHT JOIN Loan ON Customer.CustomerID = Loan.CustomerID)
RIGHT JOIN (Item RIGHT JOIN InventoryItem ON Item.ItemID = InventoryItem.ItemID)
    ON Loan.LoanID = InventoryItem.CurrentLoanID
WHERE (((InventoryItem.Status)="Checked Out"))
ORDER BY
   Date()-[Loan].[DueDate] DESC,
   Item.Title;
You can edit this SQL, but I don't recommend it. This might look similar to standard ANSI SQL, but it's not. Access has some peculiar way that it formats the JOIN logic. If you try to re-write this using Transact-SQL syntax, Access will not be able to parse it back into the designer.
WARNING
If you choose to modify the SQL, save the previous version of the SQL that was generated by Access. You will not be able to edit the query in the Design View if it has bad syntax. Rather than having to delete the query and start all over, you can paste in the previous version and get back to where you were.
Using Queries as Views
You may have already noticed that, in most places where you need to select a table, you can use a query instead. This is a really useful feature that you will take advantage of throughout this project. Select queries are equivalent to a view in SQL Server. Just like tables, you can also insert and update rows. There are a couple of things you should be aware of when using a select query as a table.
In a denormalized query that joins multiple tables, the same information is often duplicated. The CheckedOutItems query, for example, includes details such as Title and Author. If there are multiple copies of the same item currently checked out, you will see the same Item details on each record. If you edit this information, the actual Item record will be modified, and every row in the query that uses that record will be updated automatically. I believe that is the desired behavior, but may be unexpected to the end user.
You should avoid inserting records into a query that joins multiple tables. One important reason for this is that often not all of the columns are included in the query. In that case you may not be able to supply all the required values. If you have a simple query that includes all the fields from two related tables you can use it to insert records. But there is no significant advantage to using the query instead of the actual table.
You can also use a query to define the lookup column when designing a table. In Chapter 2, I showed you how to use a use a lookup column on the Loan table to constrain the values of the InventoryItemID field. You selected the allowed values from the InventoryItem table. You could create query that only returns InventoryItem records where the Status is "Available." You could then use this query to define the lookup column, instead of the InventoryItem table, which would prevent you from selecting an InventoryItem record that was already checked out.
This works as expected and may seem like a great idea; however, there are a few drawbacks. If you define a lookup column using a query instead of a table, the Lookup Wizard does not create the relationship for you. Also, referential integrity is not supported with this approach. While you won't be able to select an invalid value, the database will not enforce this should someone try to delete the InventoryItem record. However, the biggest limitation is that this is not supported if you want to publish your database to SharePoint.
You should design the tables without using queries for the lookup columns. However, when building the forms that the end user will be using, you can use the query to populate a dropdown list. This will accomplish the same purpose without any of the limitations I mentioned.
Creating Action Queries
Action queries are used to insert, update, or delete records in a table. They are often used to perform maintenance functions. You'll now create a query that will insert a record into the InventoryItem table. This will be used whenever you receive a new copy into your inventory.
Creating the AddInventoryItem Query
A query that inserts records is called an append query in Access. To create an append query, from the Create tab of the ribbon, click the Query Design button. This will load the Design View and display the Show Table dialog box. Click the Close button without adding any tables. This will display a blank query.
CHANGING THE QUERY TYPE
By default, all new queries are added as a select query. In the Design tab, click the Append button as shown in Figure 4-12.

Figure 4.12. Changing to an append query
You will then be prompted to select the table that the query will insert into. Select the InventoryItem table, as shown in Figure 4-13.

Figure 4.13. Selecting the InventoryItem table to insert into
The upper portion of the Design View is blank, and the lower pane has the familiar column grid that you used in the previous query. Notice that there is a row called Append To. Instead of returning columns in a display grid, each column in an append query populates a field in the target table. The Append To row is used to specify which field each column will populate.
I like to start by figuring out which fields need to be populated. Expand the dropdown list in the Append To field for the first column and select the first field that the query needs to populate. The InventoryItemID will be auto-populated, because it is defined as an AutoNumber field, so ItemID is the first field that the query needs to populate. In the next two columns, select the Status and Condition fields. The Comment field is not required and the CurrentLoanID should be null since the item is not loaned out. The lower pane should look like Figure 4-14.

Figure 4.14. Selecting the output fields
The next step is to provide the values for each of these fields.
ADDING A PARAMETER
The query will need to know which item this InventoryItem record should be created for. To do that you'll add a parameter to the query so the ItemID value can be passed in. To add a parameter, right-click somewhere in the upper (blank) pane and click the Parameters link.
This will display the Query Parameters dialog box. Enter itemID for the Parameter and Long Integer for the Data Type as shown in Figure 4-15. Click the OK button to save the changes.

Figure 4.15. Defining the parameters
Now you're ready to specify the values for each of the three fields that will be populated. Enter the following:
ItemID: itemID
Status: "Available"
Condition: "New"
The query should look like Figure 4-16.

Figure 4.16. The final query design
RUNNING THE QUERY
Save the query and enter AddInventoryItem for the query name when prompted. Click the Run button in the Design tab. You should see a pop-up dialog box, shown in Figure 4-17, requesting the value of the itemID parameter. Enter 1 and click OK.

Figure 4.17. Entering the itemID parameter
NOTE
Later in this book, you'll call this query from a form and write code to pass the itemID programmatically. For now, since no value was supplied, the query must prompt for it.
You should then see the warning shown in Figure 4-18. The record has been added, but Access is asking for confirmation before the insert is committed. Click the Yes button.

Figure 4.18. Append data warning
Open the InventoryItem table and verify that a new record has been added for ItemID 1.
Enhancing the Request Feature
You've just received an enhancement request: "Any customer request that has not been picked within seven days should be taken off hold and put back on the shelf." The Request table has a RequestDate field, which stores the date the item was requested by the customer. You'll need another field to store the date that the item was put on hold. You will need to record this date so you'll know when it has been more than seven days.
MODIFYING THE REQUEST TABLE
So, let's modify the Request table:
1. Open the Request table using the Design View.
2. Add a field named ReadyDate and select Date/Time for the Data Type. You can leave all the default properties except Show Date Picker. Set this to Never. This is not a value that the end user should be selecting.
3. Open the InventoryItem table using the Datasheet View. Edit the named macro, AssignPendingRequest, which you entered in Chapter 3.
4. Select the EditRecord action, which will add an Add New Action dropdown list inside this data block. Select the SetField action in this dropdown list. For the Name property enter Request.ReadyDate and for the Value property enter Now().
5. The completed macro should look like Figure 4-19. Save and close the macro.

Figure 4.19. The modified AssignPendingRequest macro
Now you'll need to update this table to set the ReadyDate field for the existing records. To do that you'll use an update query.
1. From the Create tab, click the Query Design button, select the Request table in the Show Table dialog box, and then close the dialog box.
2. Click the Update button in the Design tab to change this query from a select query to an update query.
3. Drag the RequestDate field from the upper pane to the grid in the lower pane. In the Update To row, enter Now() and in the Criteria row, enter Is Null. The query should look like Figure 4-20.

Figure 4.20. Setting the initial RequestDate value
4. Run this query by clicking the Run button in the Design tab. You will be prompted about rows being updated. Click the Yes button to confirm the update.
5. You can close the query without saving it. You won't need to run this again.
6. Open the Request table using the Datasheet View and verify that all records have the ReadyDate field populated.
7. If there is already a record in the Request table with a "Ready" status, change its ReadyDate field to be at least a week old. If not, add a new record, set its Status to "Ready" and select an InventoryItem record.
8. Finally, open the InventoryItem table and change the Status on the selected record to "On Hold."
CREATING THE CANCELOLDREQUESTS QUERY
Now you're ready to implement an update query to cancel any requests that have been ready for more than seven days.
1. From the Create tab, click the Query Design table. In the Show Table dialog box, select the Request table and then close the dialog box.
2. Drag the ReadyDate and Status fields from the upper pane to the grid in the lower pane.
3. In the ReadyDate column, enter < Now() - 7 in the Criteria row.
4. Enter "Ready" in the Criteria row for the Status column.
5. Click the Run button in the Design tab and verify that the correct records are displayed.
TIP
So far you have only created a select query; it does not update anything, yet. I find it very helpful to implement an update query as a select query first. You can then run the query to verify that the criteria is selecting the correct set of rows that you want to update. After you have done that, you can easily change the query into an update query.
6. The query should return at least one record that has a Status of "Ready" where the ReadyDate is more than seven days old. If it doesn't, open the Request table and edit the data so you'll have a record to test with.
7. Go back to the Design View by clicking the View button in the ribbon.
8. Click the Update button in the Design tab to make this an update query.
9. For the Status column, enter "Cancelled" in the Update To row. The query will update all the rows selected by the criteria, which you just tested, and change the Status to "Cancelled."
10. Save the query and enter the name CancelOldRequests when prompted.
ADDING A DATA MACRO
The query will update the appropriate Request records, changing their Status to "Cancelled." When the request is cancelled, the associated InventoryItem record needs to be changed as well, so it is available for other customers. Therefore, before you run this query against the Request table, you'll need to create a data macro to also update the associated InventoryItem record.
1. Open the Request table using the Datasheet View.
2. In the Table tab, click the After Update button to design the After Update macro.
3. In the Macro Editor, enter the actions shown in Figure 4-21.

Figure 4.21. The Request.AfterUpdate macro
This macro checks to see if the Status field has been updated to "Cancelled." If it has, the macro then looks up the associated InventoryItem record and changes its Status to "Available." You can refer to Chapter 3 for more information about data macros.
RUNNING THE QUERY
You will probably want to run this query periodically so to make it easy, you'll create a macro to run it.
1. Click the Macro button in the Create tab.
2. In the "Add New Action" dropdown list, select the OpenQuery action. For the Query Name property, select CancelOldRequests from the dropdown list. Select Datasheet for the View property and Read Only for the Data Mode property. The action should look like Figure 4-22.

Figure 4.22. The CancelOldRequests macro
3. Save the macro and enter the name CancelOldRequests when prompted. You should see this macro added to the navigation pane on the left-hand side of the application.
4. Right-click this macro and then click the Run link. You will see a pop-up window, as shown in Figure 4-23. Click the Yes button to allow the query to run.

Figure 4.23. Warning about running an update query
5. You will also see another pop-up dialog box showing how many records will be updated. Click the Yes button to confirm the update.
6. Open the Request table and verify that the appropriate records have been cancelled. Also, check the InventoryItem records to see if they are now available.
Creating a Crosstab Query
For the final query sample you'll use the Query Wizard to create a more complex crosstab query. The term crosstab is a shortened form of cross tabulation. A crosstab query uses aggregate functions to display summary information. For example, you'll create a query that will show you the total number of loans that have been made for each category and media type. The contents of the Loan table are summarized (tabulated) based on one or more attributes of that table. This will indicate what types of items have been the most popular.
Building the AllLoans Query
A crosstab query uses a single table or query for its data source. Because this information is spread across several tables, you'll first need to create a simple select query that joins all the necessary tables and provides a denormalized view. This will be similar to the CheckedOutItems query that you created earlier. The main table in this case is the Loan table because you want to gather statistics on what has been loaned out. The other tables are joined to get details about the item being loaned. Here's how to build the query that joins all the necessary tables:
1. From the Create tab, click the Query Design button. In the Show Table dialog box, select the following tables:
Category
InventoryItem
Item
Loan
Media
2. Click the Add button to add these tables to the query then click the Close button.
3. You'll need to rearrange the tables so the connecting lines are easier to follow. Also, there are two joins between the Loan and InventoryItem tables. With this query, remove the one between Loan.LoanID and InventoryItem.CurrentLoanID. The query design should look like Figure 4-24.

Figure 4.24. The AllLoans query design
4. Just like with the CheckedOutItems query, right-click each of these joins, select the Join Properties link and select the third option, which is a right outer join.
5. Save the query and enter the name AllLoans when prompted.
6. Now you'll need to enter the fields that should be returned. Double-click each of the following fields to add them to the grid in the lower pane:
Loan.LoanID
Loan.CheckedOut
Loan.DueDate
Loan.OverdueFee
Item.Title
Item.Author
Item.ReplacementCost
Category.CategoryCode
Category.CategoryDescription
Media.MediaCode
Media.MediaDescription
7. Save the query and then click the Run button in the Design tab to test the query. You should have one record for each record in the Loan table.
NOTE
A crosstab query is used to summarize information. To make the sample query more interesting, you'll probably need to create more Item, InventoryItem, and Loan records. Make sure at least several of the categories and media types are represented in the Loan table.
Designing the LoanSummary Query
Now you're ready to create a crosstab query. Follow these steps:
1. From the Create tab, click the Query Wizard button. In the New Query dialog box, select the Crosstab Query Wizard, as shown in Figure 4-25.

Figure 4.25. Selecting the Crosstab Query Wizard
2. The first step is to select the table or query that contains the data that is to be summarized. Choose the Queries option and then select the AllLoans query that you just created as shown in Figure 4-26.

Figure 4.26. Selecting the AllLoans query
3. A crosstab query allows you to summarize data by grouping them by two different values. This query, for example, will have a row for each category and a column for each media type. In the second dialog box, shown in Figure 4-27, you'll select the field used for the row labels. Select the CategoryDescription field and click the ">" button to add it to the Selected Fields list.

Figure 4.27. Selecting CategoryDescription for the row labels
4. In the next dialog box, you'll select the field that should be used for the column headings. Select the MediaDescription field as shown in Figure 4-28.

Figure 4.28. Selecting MediaDescription for the column headings
5. The last step in designing the crosstab query is to select the field that will be used to generate the data in each cell. This will use an aggregate function such as Count, Sum, Avg, or Max. This query will show the number of loans made for each category/media type. To do this, use the Count function. Since the function is simply counting the number of records, the field you use doesn't really matter. However, LoanID is a logical choice, since it will be unique for each row. Select the LoanID field and the Count function as shown in Figure 4-29.

Figure 4.29. Selecting the aggregate function to use
6. In the final dialog box, shown in Figure 4-30, enter LoanSummary for the query name.

Figure 4.30. Entering the query name
7. Click the Finish button to create the query, close the wizard and display the query using the Datasheet View. The results should be similar to Figure 4-31, depending on the data you entered.

Figure 4.31. The completed LoanSummary query
8. The column heading of the second column is not quite right. Open the query using the Design View. Change the Field description of the last column to All Loans: [LoanID] as shown in Figure 4-32.

Figure 4.32. Changing the column heading
9. Save the query and then click the Run button in the Design tab.
Summary
In this chapter you created several select and action queries using both the Query Wizard and the query Design View. Some of the key benefits of using queries that you demonstrated in this chapter include the following:
Providing a subset of the data from a table
Creating a denormalized view by joining several related tables
Inserting records into a table
Updating a group of records in batch mode
Calling an action query from a macro
Summarizing aggregated data using a crosstab query
In the next and final chapter in this part of the book, you'll see the power of creating pivot tables to assist in reporting analyzing and data.