Wednesday, 22 January 2020

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.






MS access -Part1

Part II. Defining the Database Schema
In Part 2, you'll create an Access database and define schema elements, such as tables and queries. Because Access makes it easy to add or modify tables, the database design is often done in ad-hoc fashion. Tables and columns are added as needed to support the form or report that is being developed. However, because the data schema forms the basis for the application, I recommend performing a thorough database design upfront.
In Chapter 2, you'll start by designing the tables. Access provides facilities for defining data integrity, such as foreign keys, unique constraints, and field validation. I encourage you to constrain the database design as much as possible. For example, if a column should not contain a negative value, make that an explicit rule in the table design.
In Chapter 3, you'll use data macros to further constrain the referential integrity. You'll also use them to propagate changes to other tables. For example, when a loan is created, the status of the associated item can be automatically updated. Data macros are also a great way to implement business rules, such as computing the late fee, if appropriate.
In Chapter 4, you'll design several queries that you will use later when implementing forms and reports. Action queries that insert, update, or delete records are a convenient way to perform batch processing.
In Chapter 5, you will implement a pivot, which is a useful tool for analyzing a large table.


Chapter 2. Defining and Relating Tables
The logical starting point in designing a database application is to define the tables that will store the data. The table schema is the foundation that must support the entire application, so it is imperative that this be designed well. Because Access makes it so easy to build an application, it can be tempting to skip this important step, but don't.
In this chapter I'll explain some of fundamental principles that ensure a good data model. These techniques are universally applicable to most database engines. I will cover database normalization, defining primary and foreign keys and using constraints to ensure referential integrity.
I will guide you through the process of creating a base set of tables for your application. I'll show you step-by-step how to design the tables and use the many data modeling features in Access 2010. By the end of the chapter, you'll have a solid structure on which to build the rest of the application.
Considering Design Practices
Before starting the specific design for this project, I want to cover some basic principles that should be followed in any database design.
Using Primary Keys
While not required by Access, it is strongly recommended that you define a primary key for each table. A primary key uniquely defines a single record in the table. A primary key is needed when you define relationships between tables, and relating tables is a key part of defining a relational database.
There are two popular approaches to designing primary keys. The first is to create a single surrogate key field. Typically, this column has the same name as the table with an "ID" appended to it. For example, the primary key for an OrderItem table would be OrderItemID. The AutoNumber column type works well with this approach. When a record is inserted, the database engine assigns the next number to this field. This guarantees uniqueness of the key.
A second approach is to inherit the key from a parent table, adding an additional field for each level in the table hierarchy. For example, the primary key for the Order table would be OrderID, and for the OrderItem table, the primary key would be a composite of OrderID and OrderItemID columns. In this case, the OrderItemID only has to be unique within the specific Order. From a designer's perspective, this approach makes it clear that the OrderItem table has an aggregate relationship with the Order table. In practice, however, this is more difficult to implement, because it requires the application to generate the key values. Defining foreign key relationships in this situation is also more awkward, because multiple fields are required. With this design, some tables could end up having three or more primary key fields.
For the project in this book, I will use the former approach and give each table a single primary key field. I believe this to be the more prevalent approach, especially in Microsoft platforms such as Access and SQL Server. This will also simplify the implementation of this project.
Normalizing a Database
A well designed database will have some level of normalization. Normalization is basically the process of removing redundant information. There is a series of definitions called normal forms that provide standard rules for determining normalization. These are successively more normalized. First normal form (1NF), for example, requires that there are no duplicate columns in a table and that each table has a primary key (a primary key can be comprised of multiple columns).
Second normal form (2NF) requires that each non-key field be dependent on the entire key, not just part of it. For example, in the OrderItem table described earlier, where the key is a combination of OrderID and OrderItemID, each non-key field must be dependent on both key fields. Fields such as Customer and OrderDate are dependent only on OrderID and not OrderItemID.
Third normal form (3NF) further requires that every column in a table be solely dependent on the primary key. For example, suppose you had a table of customer orders that included the customer's name and address. The name and address are attributes of the customer not the order. To satisfy 3NF, these columns should be removed and placed in a separate customer table. The order table would then include only a foreign key field, which is the primary key of the customer table.
Another way to look at this rule is that if a customer had more than one order, their name and address would be repeated on each order. Reducing this duplication of data is the goal of normalization. Another common example that violates 3NF is including a column that can be computed based on other columns. For example, including both BirthDate and Age would violate 3NF, since Age can be computed based on the BirthDate.
TIP
For more information about database normalization, this set of slides presents a practical overview: www.uncg.edu/ism/ism318/normalization.pdf.
The goal of these formal definitions is to help you identify where there is redundant data. This redundancy should be eliminated for three primary reasons:
Redundant data creates larger databases.
Redundant data is much more difficult to maintain.
Redundant data is more likely to have errors.
Storage is relatively cheap, so unless you're working with rather large databases, the extra space used by redundant data is probably not a big issue. The maintenance issue, however, can be significant. Consider, for example, the order table that includes the customer name and address. Suppose the customer moves; you would have to update every order record to store their new address. Also, for every new order, their name and address would need to be entered instead of simply looking them up in the customer table. In addition to increasing the data entry burden, it is also more likely that someone will enter something incorrectly. If someone needs to enter "Mark Collins" 50 times, they will likely fat-finger it at least once and end up with "Markl" or "Marl."
I recommend that the initial database design start out with at least third normal form. I realize there are times when you will want some amount of de-normalization for processing efficiency. You may choose to violate 3NF, for example, by adding an Age field in addition to BirthDate. However, add these columns later, and only if necessary. I also recommend, if you need to de-normalize, that you use data macros (explained in Chapter 3) to populate them. This will minimize the negative effects of de-normalization. You should never have to violate 2NF. Instead you can use a query to create a de-normalized view of one or more tables, which I'll explain in Chapter 4.
Database Constraints
I also recommend that the database be constrained as much as feasible. Adding database constraints allows you define rules for what data is considered valid. By providing these rules at the database level, you guarantee that these rules are followed, regardless of the source of the data. The database engine will not allow data to be added or modified if it violates any of these constraints. Fortunately, Access 2010 provides all the necessary features to allow you to do this.
Foreign key constraints ensure that references to other tables are (and remain) valid. For example, if you have a table with an OrderID field that references the Order table, by setting up a foreign key constraint the database will require that whatever value is entered in the field is found in the Order table. You will not be able to enter a value of 5, for example, if there is no OrderID 5 in the Order table. Once the data has been entered, the foreign key constraint will also prevent you from deleting Order #5 if another table has a reference to it.
There are several other types of constraints that can be defined as well. You can enter a validation expression to define what values are allowed. For example, use >= 0 to ensure that negative values are not permitted. You can specify if a value is required and/or provide a default value if not specified by the user interface. I will cover each of these in more detail later in this chapter. In general, you should consider every column and provide the appropriate constraints.
Designing the Tables
This application will support a lending library. You'll need a table to store the items that you have in your inventory. The design will allow for multiple copies of the same item, so you'll have an Item table to define things like the title, author, and description, as well as an InventoryItem table to store details about a specific copy, such as status (available or checked out) and condition. You will also need a Loan table to record when an item is checked out and a Customer table to hold information about a borrower, such as name and address. This design will include a Request table, which will allow a customer to request an item to be held for them as soon as it becomes available. Finally, you'll need Category and Media tables to define available options when configuring the item's attributes. Figure 2-1 provides a high-level view of the basic structure.

Figure 2.1. Overview of the database schema
I find it helpful to sketch out a diagram like this before actually creating the database objects. Seeing the tables in a visual presentation like this may help you spot missing entities or relationships. This diagram does not need to include every column, initially. Just start with enough columns to communicate the basic content of each table. Then flesh out the details as the design evolves. Figure 2-1 only contains the partial design; it's how a design might look when you're about halfway through it. When creating the actual tables in Access, you'll include other columns as well, which I will explain later.
I made this diagram using Microsoft Visio, but you can use any basic diagraming utility or just sketch it with paper and pencil. Once the design is entered in the Access database, you can produce detailed diagrams and documentation.
NOTE
The Customer table in this design will not be normalized. To normalize this data, columns such as City should be put into their own table. I've chosen to leave this table de-normalized, as this will simplify the implementation and does not interfere with fulfilling the goal of this book.
When designing the schema, you normally start with the primary tables, like Item and InventoryItem. However, when creating them in Access it's more efficient to start with the peripheral tables, such as Category and Media. As you design a table, the tables that it is dependent on must exist before you can define the relationship between the tables. You can see from Figure 2-1 that the Customer, Category, and Media tables have no dependencies on other tables, so you should start with these. Then you'll create the Item and InventoryItem tables. Finally, you'll finish up with the Loan and Request tables.
Creating the Customer Table
Start the Access 2010 application and create a blank database named Library. Use the Microsoft Access 2007 database format, which should be your default option. This will create a Library.accdb file. The template automatically creates a single table named Table1 with an ID column.
Access provides two views that you can use to define the table. The default Datasheet View looks like an Excel spreadsheet. In this view, you add a column to the table by literally adding a column to the spreadsheet. I prefer to use the Design View, because it shows more details of each column. To display the Design View, just click the Design View button in the ribbon. You will be prompted to save Table1. In the Save As dialog box, enter Customer as shown in Figure 2-2.

Figure 2.2. Saving the Category table
The Design View, shown in Figure 2-3, is useful for defining the details of each column.

Figure 2.3. The table design view
The top portion lists all the columns that have been defined. The lower left pane displays all the properties of the selected column. The subset of properties that are displayed will vary depending on the data type of the column. The lower right pane provides information about the selected property.
In the Field Name column, replace ID with CustomerID. This column was set up as an AutoNumber field. Notice that the New Values property is set to Increment and there is a unique index on this column. This is also setup as the primary key.
USING QUICK START FIELDS
Access 2010 offers quick start fields that are preconfigured field definitions. These often contain multiple fields that are commonly used together. For example, the Name quick start field contains both a LastName and FirstName field. This is a convenient way to define a table using standard patterns.
Go back to the Datasheet View by clicking the View button in the ribbon and then select the Fields ribbon. It contains buttons that will add different types of fields. Click the More Fields button shown in Figure 2-4.

Figure 2.4. The More Fields button
This will display a list of additional data types that you can select from, which are grouped into categories. The Quick Start category, shown in Figure 2-5, shows the available preconfigured data types.

Figure 2.5. Listing the Quick Start fields
Select the Name quick start field, which should add the LastName and FirstName fields to your Customer table.
WARNING
These fields will be added before the currently selected field. If the CustomerID column is selected, these fields will be added before CustomerID. Make sure the last column is selected, is the one labeled "Click to Add," before adding the Name field so these will be added after the CustomerID field. If you need to change the field order, keep in mind that if you rearrange columns in the Datasheet View by dragging the column headings, this only affects the view and not the underlying table. To rearrange the actual field order, you'll need to go to the Design View, select a row, and then move it to the desired position.
In the same way, select the Address quick start field. This will add the following fields:
Address
City
StateProvince
ZIPPostal
CountryRegion
These fields as well as LastName and FirstName were added as not required and the "Allow Zero Length" property is set to No. Depending on how your library will be used, you probably want to make some of these required. I suggest that you set the Required property to Yes on at least LastName and FirstName fields. I will explain more about required fields later in this chapter.
Using the quick start fields you have added defined most of the fields in the Customer table. Now you'll need to add fields for the phone number and e-mail address.
ADDING SEARCH FIELDS
The Phone quick start field adds several different phone number fields (business, home, mobile, and fax). You probably don't need all of these in your table. Save the table definition and then go to the Design View.
After the CountryRegion field, add the following fields:
PhoneNumber: Text, size 15
E-mail: Text, size 70
Both of these fields could be useful for searching for a customer, so you should create an index for them to speed up the search. Go to the Indexed property and select Yes (Duplicates OK) for both fields. You can leave the Required property as No, but change the "Allow Zero Length" property to No for both of these fields.
USING AN INPUT MASK
For certain fields, like PhoneNumber, you can assign an input mask that will assist the end user in formatting the data correctly. Select the PhoneNumber field, select the Input Mask property, and then click the button to the right of the field. This will display the Input Mask Wizard shown in Figure 2-6.

Figure 2.6. Selecting an input mask
Select the Phone Number mask and click the Next button, and the wizard will give you an option to test the input mask. Notice that the "_" place holder is replaced as you type each character, as shown in Figure 2-7. The formatting characters like "(" and "-" are automatically skipped as the phone number is entered.

Figure 2.7. Testing the input mask
After you click the Next button, the wizard will then ask if you want the formatting characters included or just the raw text. Select the first option, which is to store the formatting characters, as shown in Figure 2-8.

Figure 2.8. Select how the formated the value should be stored
The completed properties for the PhoneNumber field should look like Figure 2-9.

Figure 2.9. The completed properties for the PhoneNumber field
ADDING CUSTOMERS
Open the Customer table in the Datasheet View by clicking the View button in the ribbon. The first record will have the text "(New)" in the CustomerID field. Tab to the LastName field and enter a name. As soon as you start typing in the LastName field, the next ID is filled in the CustomerID field. Enter data in the remaining columns. When you get to the PhoneNumber field, notice that the input mask is displayed as demonstrated in Figure 2-10.

Figure 2.10. The PhoneNumber input mask applied
Add several customers into this table so you'll have some data when checking out items.
Creating the Category Table
The Category table simply defines the allowed categories and will be used to populate a dropdown list when configuring an item. Categories are used to group similar items. They can be based on topics or genre, or whatever organization makes sense based on the type of library that will use this database. By putting categories in a table, the end user can determine the set of values that work best for them.
To create a new table, select the Create ribbon. The Table button will create a table in the Datasheet View and the Table Design button will use the Design View. Click the Table Design button. In the Design View add the following columns:
CategoryID: AutoNumber
CategoryCode: Text, size 20
CategoryDescription: Text, size 50
Select the CategoryID column and then click the Primary Key button in the Design ribbon. This will make this the primary key field. The Code field will be used like a short description.
TIP
For the field names, you could have used the more generic names of ID, Code, and Description. You can infer from the context that these attributes refer to a category. It may seem somewhat redundant to include the table name as part of the field name. However, when you start designing queries that join several tables, it could be confusing when every table has an ID and Description field. I'm not suggesting that you include the table name in every field name; you only need to do this for commonly used names. A good rule of thumb is that a field name should communicate what data is stored in the field without relying on contextual information.
MAKING A FIELD REQUIRED
There are some fields that need to be supplied or the record just isn't usable. For example, what good would it do to set up a category without supplying the code and description? To avoid getting bad data in your database, you can make critical fields required. The end user who is adding the category, would then be required to supply these fields before they can save the record. To make a field required, select the Required property and choose Yes from the dropdown list. Do this for the CategoryCode field.
For Text fields there is also an "Allow Zero Length" property. Access makes a distinction between a null value and an empty string, as do most database engines. In practice, however, we seldom care about that distinction. If the "Allow Zero Length" property is set to Yes, the end user could enter a blank string and still satisfy the Required constraint. Set this property to No to ensure some data is entered in this field.
Go to the CategoryDescription field and also set its Required property to Yes and the "Allow Zero Length" property to No.
WARNING
Unless you have a good reason not to, you should always set "Allow Zero Length" to No when Required is set to Yes. If you try to publish your database to SharePoint, which I'll explain in a later chapter, you'll get a compatibility error if both of these properties are set to Yes.
ADDING A UNIQUE CONSTRAINT
There are times when we need to ensure certain values are unique. For example, if two categories were created that used the same code, the code would be ambiguous. In Access you can ensure unique values by adding an index and selecting the No Duplicates option. Go to the CategoryCode field and select this option for the Indexed property. The properties of the CategoryCode field should look like Figure 2-11.

Figure 2.11. The CategoryCode field properties
Click the Save icon at the top of the window to save your changes to the database.
DEFINING CATEGORIES
Open the Category table in the Datasheet View. The first record will have the text "(New)." Tab to the CategoryCode field and enter a code. Tab to the CategoryDescription field and enter a description. Click the Tab key and the record will be saved. You may want to change the column widths, which you can do by dragging the gridlines. Add several more records to the table. The Datasheet View should look like Figure 2-12.

Figure 2.12. The Category table
Creating the Media Table
The Media table, like the Category table, defines attributes that will be applied to each item. Whereas the Category table is used for organizing topically, the Media table is used to define formats such as book, video, and audio. The Media table will also define business rules, such as how long an item can be checked out and what is the charge for each day an item is overdue. This will enable you to limit videos to be kept for only a week, for example, and allow a longer period for books.
To create a new table, click the Table Design button in the Create ribbon. In the Design View add the following columns:
MediaID: AutoNumber
MediaCode: Text, size 20
MediaDescription: Text, size 50
LoanPeriod: Number, size Integer
RenewalsAllowed: Number, size Integer
OverdueFee: Currency
Select the MediaID column as the primary key.
NOTE
When you added the MediaID column as an AutoNumber field, it was initially setup with a non-unique index, which means that duplicate values were allowed. When you selected this as the primary key, the index was automatically changed to not allow duplicates. That is how you want this to be defined.
Click the Save icon. When prompted, enter Media for the table name. Just like you did for the Category table, make the MediaCode and MediaDescription fields required and don't allow zero length strings. Also, for the MediaCode field, select No Duplicates for the Indexed property. This will prevent the same code being used twice.
ADDING FIELD VALIDATION
Select the LoanPeriod column. This will define the number of days an item can be checked out. You'll add validation logic to ensure that a reasonable value is entered. Let's assume that this must be at least 7 days but not more than 21. Select the Validation Rule property and enter >=7 And <=21. Notice that when you select this property, a button with ellipses appears to the right of the value field. You can click this button to display the Expression Builder, shown in Figure 2-13.

Figure 2.13. The Expression Builder
Expressions in Access use the Visual Basic syntax. If this is not familiar to you, the Expression Builder is a useful tool for looking up built-in functions and operators.
TIP
Validation rules can include other fields in the logic. For example, the LoanPeriod defines the number of days for the initial loan and RenewalsAllowed specifies how many times it can be renewed. Suppose to you wanted to ensure that an item was never checked out for more than 60 days. You could add a validation rule to the RenewalsAllowed field as <=60/[LoanPeriod].
If you enter a validation rule, you should also specify the validation text. This text will be displayed when the user tries to update a field and its validation rule fails. Select the Validation Text property and enter
The loan period must be between 7 and 21 days
Set the Required property to Yes. The Decimal Places property defaults to Auto. Because you are expecting only whole numbers, change this to 0. Notice that when you change the Decimal Places property a lightning bolt appears next to it. This is known as the Property Update Options button. If you click this, you'll see the options shown in Figure 2-14.

Figure 2.14. Propagation options
If you choose to update every place that this field is used, a dialog box will appear listing the fields that will be updated. Currently, there are no other fields called LoanPeriod so you can ignore this.
The completed properties should look like Figure 2-15.

Figure 2.15. The completed LoanPeriod properties
ENTERING A FIELD CAPTION
When a field is displayed on a form or report, by default the Field Name will be used as the label for this field. In many cases this is appropriate. However, if you specify the Caption property, this will be used instead of the Field Name. Select the RenewalsAllowed field, select the Caption property, and enter
How many times can a loan for this type of item be renewed?
Change the Decimal Places property to 0.
USING DEFAULT VALUES
For fields that are likely to be used in an expression, you should ensure that they have a value. If you don't, then any expression that uses this field needs to handle a null value. It's easier to simply set the Required property to Yes and supply a Default Value. By setting both of these properties you resolve the null issue without adding any extra data entry work. The user only has to enter a value if they want something other than the default.
I realize that there are some situations where you need to take a different action when there is a null value. In this case, make sure the Required property is set to No and there is no Default Value. You'll then need to handle this case in the expression that uses this field.
Select the RenewalsAllowed field and set the Required property to Yes and enter a Default Value of 0. Also, enter >=0 for the Validation Rule. For the Validation Text property enter Negative values are not allowed.
Select the OverdueFee field and set the following properties:
Default Value: 0
Validation Rule: >=0
Validation Text: The fee cannot be negative
Required: Yes
Save the changes to this table.
DEFINING MEDIA TYPES
Open the Media table using the Datasheet View by clicking the View button in the ribbon. The MediaID field should be selected with the default value of "(New)." Tab to the MediaCode field and enter a code and then enter a description. For the LoanPeriod, enter an invalid value such as 3. When you tab off that field your validation text should be displayed as shown in Figure 2-16. Click the OK button and then enter a valid value.

Figure 2.16. A field validation prompt
You should get similar results if you try to enter negative values for the RenewalsAllowed or OverdueFee fields. If you try to save a record without entering a LoanPeriod, you'll see the prompt shown in Figure 2-17.

Figure 2.17. Required data prompt
Enter several media types so you'll have some data available when defining items. The Media table will look similar to Figure 2-18.

Figure 2.18. The Media table with data populated
Creating the Item Table
The Item table will store information about the items that are available in your library. Create a new table using the Table Design button and enter the following columns:
ItemID: AutoNumber, primary key
Title: Text, size 255
For the Title field, set the Required property to Yes and the "Allow Zero Length" to No. Save the table and enter the name as Item when prompted.
CREATING LOOKUP COLUMNS
As I mentioned earlier, you'll need to assign a category and media type to each item. To do that, you'll add CategoryID and MediaID columns, which will reference the associated record in the corresponding tables. Adding the columns to the Item table merely provides a place to store the values, but does not define a relationship between the tables.
NOTE
In previous versions of Access, you would first create the tables with the appropriate columns and then define a relationship between the tables. While creating the relationship, you would indicate the primary and foreign keys in each table. You can still define relationships this way in Access 2010; however, they may not be compatible if you want to publish your database to SharePoint. Instead use the Lookup Wizard, which will add the column and create the relationship.
From the Design View, add a field named CategoryID and select Lookup Wizard for the data type. This will start the Lookup Wizard shown in Figure 2-19.

Figure 2.19. Selecting the type of lookup
The Lookup Wizard can either get values from another table or you can specify a fixed set of values to use. Select the first radio button and click the Next button. Since you selected to get the values from a table or query, the next dialog box, shown in Figure 2-20, allows you to select the table.

Figure 2.20. Selecting the referenced table
Select the Category table and click the Next button. In the next dialog box, you'll select the applicable fields from this table. In this case, click the ">>" button. All three fields should be listed in the Selected Fields list as shown in Figure 2-21.

Figure 2.21. Selecting the fields to be used
The last statement in this dialog box is a little bit misleading. It says "The fields you select become columns in your lookup field." All of the selected fields are used when looking up a value in the referenced table. However, only the key field, CategoryID, is added as an actual column to the Item table.
Click the Next button to display the next dialog box, which allows you define how the choices will be sorted. Select the CategoryDescription column as shown in Figure 2-22 and click the Next button.

Figure 2.22. Defining the sort order
The Category table that you created earlier will be used to populate a dropdown list in the Item table. The next dialog box, shown in Figure 2-23, shows a preview of what the dropdown list will look like. You can resize the column by dragging the grid lines.

Figure 2.23. Configuring the dropdown list
The "Hide key column" check box controls whether the key column, CategoryID, is included in the dropdown list. Typically, the ID is auto-generated and not usually meaningful to the end user. The code and description, as shown here, is usually sufficient. If the ID would be helpful, you can un-select this check box and it will be included as a separate column in the dropdown list.
Click the Next button to display the final dialog box shown in Figure 2-24.

Figure 2.24. Specifying the data integrity rules
The last step in defining the relationship is to specify the data integrity rules. You can choose to disable data integrity, but I recommend you use it to help maintain valid data. There are two ways that data integrity can be enforced.
The first is called Cascade Delete. In this mode, if the record being referenced is deleted, the records that reference it are also deleted. You use this mode when you have an aggregate relationship; that is, when the two tables have a parent-child relationship. For example, an Order table would be the parent of an OrderItem table. In this case, if an Order record is deleted; all OrderItem records that reference it would be automatically deleted as well.
In our case, however, the Item and Category tables do not have that kind of relationship. Instead, use the Restrict Delete option. This will prevent someone from deleting a Category record if any Item record references it. If you want to delete the Category record, you must first either delete these items or change them to reference a different category.
Select the Restrict Delete option and click the Finish button. You will then see the prompt shown in Figure 2-25.

Figure 2.25. Saving the table changes
The table has been modified by the wizard to include the CategoryID column. This must be saved before the wizard can create the relationship. Click the Yes button. Then click the Save icon to save the all the changes made by the wizard. You may see the dialog box shown in Figure 2-26.

Figure 2.26. Message that the Item table has been modified
In my opinion, this is a very confusing dialog box. The wizard has made some changes to the Item table and this message is letting you know that someone (or something) other than you has modified an entity that you have open for editing. Essentially, the version that you have in memory is different from what is stored on disk. If you choose No, you'll be able to save your current version (in memory) to disk with a new name. This will keep both versions. The text does not make it clear, however, what happens when you choose Yes. Since you don't have any changes that you need to worry about losing, click the Yes button.
WARNING
Select the CategoryID field and check the Lookup tab on the Field Properties pane. The "Limit to List" property should be set to Yes. The wizard should be setting this field, but I have found instances where it did not. This will give you a compatibility issue if you try to publish this database to SharePoint.
ADDING THE MEDIAID COLUMN
Now, in the same way, you'll add a reference to Media table. Add a new field named, MediaID and select the Lookup Wizard for the data type. This will take you through the same set of dialog boxes that you used to setup the CategoryID field.
In the second dialog box, select the Media table.
In the third dialog box, select the MediaID, MediaCode, and MediaDescription fields.
In the fourth dialog box, sort by the MediaDescription field.
In the sixth dialog box, select the "Enable Data Integrity" checkbox and the Restrict Delete option.
VIEWING THE RELATIONSHIPS
In addition to adding the foreign key fields, the Lookup Wizard has also created a relationship between the tables. Select the Database Tools ribbon and then click the Relationships button. You may need to click the All Relationships button to refresh the view. The relationships should look like Figure 2-27.

Figure 2.27. The relationships view
ADDING THE REMAINING COLUMNS
Close this view and display the Item table using the Design View. Add the following columns to this table:
Author: Text, size 255
Description: Memo
ReplacementCost: Currency
Make the Author field required and set the "Allow Zero Length" property to No. You can leave the Description field with the Required property set to No. For the ReplacementCost field, enter >=0 for the Validation Rule property and enter the Validation Text as Negative values are not allowed.
ADDING A CALCULATED FIELD
A calculated field is computed based on an expression that can use other fields. You'll use a calculated field to define the LostFee. The ReplacementCost field specifies the actual cost to purchase a single copy of the item. The LostFee is what the customer is charged when an item that they have checked out is lost or damaged beyond repair. The fee that you charge the borrower could be the ReplacementCost plus a flat re-stocking fee. Or it could be based on a percentage, say a 15% handling fee. For our project we'll add a flat $10 to the ReplacementCost field.
In the Design View, enter the Field Name as LostFee and select Calculated for the Data Type. This will display the Expression Builder. Double-click the ReplacementCost field to add it to the expression, then add + 10 and click the OK button to close the dialog box. For the Result Type property, select Currency from the dropdown list. The completed properties should look like Figure 2-28.

Figure 2.28. The LostFee properties
ADDING ITEMS TO THE DATABASE
Open the Item table using the Datasheet View and enter some items into your database. Notice that when you select a category or media type, the dropdown list displays both the code and description. Once you select a value, only the code is displayed in the table. For lookup columns, the table automatically displays the first column from the dropdown list.
Also, as soon as you enter a value in the ReplacementCost field, the LostFee is automatically calculated. The LostFee is also read-only; you are not allowed to modify it because it is computed based on the formula you entered. The Datasheet View will look like Figure 2-29.

Figure 2.29. The Datasheet View of the Item table
Creating the ItemInventory Table
The Item describes the titles that are available in your library. However, you will need to track specific copies of these titles. To do that you'll create an ItemInventory table. This will provide specific information about each copy such as status and condition and reference an Item record for generic details.
From the Create ribbon, click the Table Design button. Enter the Field Name InventoryItemID with an AutoNumber Data Type. Click the Primary Key button and save the table. Enter the name InventoryItem when prompted.
ADDING A LOOKUP TO THE ITEM TABLE
Add another field named ItemID and choose the Lookup Wizard for the Data Type. You'll configure this like you did the other lookup columns, CategoryID and MediaID.
Select the Item table in the second dialog box.
In the third dialog box, select the ItemID, Title, and Author fields as shown in Figure 2-30.

Figure 2.30. Selecting the fields to be included in the lookup
TIP
You don't have to explicitly include the primary key, ItemID. If you don't include this field, the wizard will add it for you. Also, the order in which you add the fields to the Selected Fields list will determine the order they are displayed in the dropdown list. For example, if you wanted the Author shown before the Title, then add it to the Selected Fields first. You can also adjust the field order in the fifth dialog box, shown in Figure 2-31. Just select a column and drag it to the desired location.
In the fourth dialog box, select Author for the first sort field and then Title for the second sort field. This will group the items by author in the dropdown list.
As I mentioned, for lookup columns, the table displays the first column of the dropdown list. So you can control which column is displayed. If you wanted the description shown instead of the code, just make sure the description is the first column in the dropdown list. For relatively short lists, like Category and Media, displaying the code or description works well.
For the Item table, which could have thousands of records, you'll find that displaying the ItemID will be more useful. Keep in mind that you will be developing forms for the end user; they will not typically look at the table directly. Developers and maintainers will use the table and they'll want to know which specific Item record is being referenced by this InventoryItem record. Titles are often non-unique. You could have three different items with the title "White Christmas."
To display the ItemID, unselect the "Hide key column" checkbox, as shown in Figure 2-31.

Figure 2.31. Configuring the dropdown list
Depending on how the fields were selected and configured, you may see the dialog box shown in Figure 2-32. This allows you to specify the field that will be stored in the InventoryItem table, and it needs to be the primary key, ItemID.

Figure 2.32. Select the key field to be stored in the InventoryItem table
In the final dialog box, select the "Enable Data Integrity" checkbox. In this scenario, the InventoryItem table is a child of the Item table and you could choose the Cascade Delete option. If you did this, when an Item record is deleted, all of the associated InventoryItem records are also deleted. I think this is dangerous. I believe it would be better to force the user to first remove the inventory items before allowing them to remove an item. To do this, select the Restrict Delete option. Then click the Finish button to setup the relationship.
WARNING
If the Item table is open in another tab, you will probably get an error because the wizard is not able to modify the Item table. If you do, close the Item table. You will need to re-run the Lookup Wizard. Change the Data Type from Number to Lookup Wizard, which will re-start the wizard.
Go to the Lookup tab of the Field Properties pane and make sure the "Limit to List" property is set to Yes.
ADDING A LOOKUP WITH FIXED OPTIONS
You'll now add a Status field, which should be selected from a list of standard values. In the Design View add a new field named Status and select Lookup Wizard for the Data Type. In the first dialog box, select the second option, which lets you specify the values to use, as shown in Figure 2-33.

Figure 2.33. Selecting the lookup source
Then click the Next button, which will display the second dialog. In addition to the actual status value, you can add one or more columns to provide information in the dropdown list. You may want to include a comment with each value that explains when it should be used. In this case, the status values are fairly self-explanatory so a single column will be sufficient.
Enter the following values as shown in Figure 2-34 and click the Next button.
Available
Checked Out
On Hold
Damaged
Lost

Figure 2.34. Entering the allowed values
In the third and final dialog box, select the "Limit to List" checkbox, as shown in Figure 2-35. This will prevent the user from entering a value that is not in the predefined list.

Figure 2.35. Setting the "Limit to List" option
Click the Finish button to complete the setup of this field. Select the Lookup tab in the Field Properties pane, which is shown in Figure 2-36.

Figure 2.36. Displaying the Lookup properties
The Lookup Wizard sets these properties using information entered in the dialog boxes. You could enter these properties yourself manually if you prefer.
Go back to the General tab. Enter "Available" for the Default Value property, set the Required property to Yes, and set the "Allow Zero Length" property to No.
SPECIFYING THE ITEM'S CONDITION
You'll need two more columns in the InventoryItem table, which you'll use to record the condition of this item. The first will be a dropdown list with standard values such as New, Good, and Fair. The second will be a text field that you can enter freeform comments.
From the Design View add a new field named Condition and select Lookup Wizard for the Data Type. Configure this just like you did for the Status field except enter the following values:
New
Good
Fair
Poor
After the wizard has configured the field, enter New as the default value. As with the Status field, set the Required property to Yes, and set the "Allow Zero Length" property to No.
From the Design View, enter a new field named Comment with a Data Type of Text. You can leave all of the default properties, including leaving Required as No.
ADDING INVENTORYITEM RECORDS
Open the InventoryItem table in Datasheet View. Click the dropdown icon in the ItemID column. Then select an item from the list as shown in Figure 2-37.

Figure 2.37. Selecting an Item from the dropdown list
The remaining required columns all have default values so the ItemID is the only one you'll need to enter. You can pretty quickly enter a couple on InventoryItem records for each Item that you defined earlier. The Status should be Available for all records, since you have not yet loaned anything out. You can change the Condition to some other values for a few of the records. When you're done, the Datasheet View should look like Figure 2-38.

Figure 2.38. The InventoryItem table populated with data
Creating the Loan Table
The Loan table will be used to record each time an item is checked out. It will reference an InventoryItem record, so you'll know the specific copy that was lent out. It will also reference a Customer record to track who checked it out.
Create a new table in the Design View by clicking the Table Design button in the Create ribbon. Enter a new field named LoanID using the AutoNumber Data Type. Click the Primary Key button and save the record. Enter the table name Loan when prompted.
REFERENCING THE CUSTOMER TABLE
From the Design View, perform the following steps:
1. Add a new field named CustomerID and select the Lookup Wizard Data Type.
2. In the second dialog box select the Customer table.
3. In the third dialog select the following columns to be used in the lookup:
CustomerID
LastName
FirstName
Address
4. In the fourth dialog box, sort by LastName and then by FirstName.
5. In the fifth dialog box, unselect the "Hide key column" checkbox, as shown in Figure 2-39. You should also resize the columns so the data will fit in each column.

Figure 2.39. Configuring the dropdown list
6. In the next dialog box, select the CustomerID field to be stored in the Loan table, as shown in Figure 2-40.

Figure 2.40. Selecting the value to be stored in the Loan table
7. In the final dialog box, select the "Enable Data Integrity" checkbox and choose the Restrict Delete option, as shown in Figure 2-41.

Figure 2.41. Enabling data integrity
REFERENCING THE INVENTORYITEM TABLE
Add a new field name InventoryItemID and select the Lookup Wizard Data Type. You'll configure this like you did with the other lookup columns.
In the first dialog box, select the first option, which is to get the values from another table.
In the second dialog box, select the InventoryItem table.
The InventoryItem table doesn't have any columns that would be useful in a dropdown list. The Title is in the Item table, not the InventoryItem table, and the Lookup Wizard is not able to used referenced tables. Normally, when checking out an item, rather than looking up the item, you would scan or key the InventoryItemID from a barcode attached to the item. So you'll just use the InventoryItemID.
Add only the InventoryItemID field to the Selected Fields list in the third dialog box.
In the final dialog box, select the "Enable Data Integrity" checkbox and choose the Restrict Delete option.
After the wizard has finished, go to the Lookup tab of the Field Properties pane and make sure the "Limit to List" property is set to Yes.
ADDING THE REMAINING FIELDS
Now you'll add a few more fields to record when the item was checked out, when it is due back, when it was actually returned and if there are an overdue fees associated with this loan.
1. From the Design View add the following fields:
CheckedOut: Date/Time
DueDate: Date/Time
CheckedIn: Date/Time
Renewals: Number, size Integer
OverdueFee: Currency
2. Select the CheckedOut field. For the Default Value property, enter Now(). This is a Visual Basic function that returns the current date/time. Set the Required property to Yes, and for the "Show Date Picker" property, select Never.
3. For the DueDate field, select Short Date for the Format property. The due data should be a date that does not include the time portion.
4. For the CheckedIn field, you can leave all of the default properties.
5. For the Renewals field modify the following properties:
Decimal Places: 0
Caption: How many times has then loan been renewed?
Default Value: 0
Validation Rule: >=0
Validation Text: Negative values are not allowed
6. For the OverdueFee field, modify the following properties:
Default Value: 0
Required: Yes
We'll wait until Chapter 3 to start entering data in this table
Creating the Request Table
The Request table is used to store a request by a customer for a particular item. When a copy of that item becomes available, it will be placed on hold and reserved for that customer. The Request table will reference an Item record, which specifies the item that is being requested. It will also reference a Customer record to store the customer who made the request. It will also reference an InventoryItem record that indicates the specific copy that is being held for the customer.
Create a new table in the Design View. Add a field named RequestID and select the AutoNumber Data Type. Click the Primary Key button and save the table. Enter the name Request when prompted.
Add a new field named CustomerID and select Lookup Wizard for the Data Type. Configure this exactly the same as you did for the CustomerID field on the Loan table.
Add another field named ItemID and select the Lookup Wizard. Configure this field exactly the same way as you did for the ItemID field on the InventoryItem table. Make sure that both CustomerID and ItemID have the Required property set to Yes.
Add another field named RequestDate using the Date/Time Data Type. For the Default Value property enter Now(). Set the Required property to Yes and the "Show Date Picker" property to Never.
DEFINING THE STATUS FIELD
Add another field named Status. Use the Lookup Wizard to specify a list of valid values like you did for the Status field on the InventoryItem table. In this case, you'll add an extra column to provide more details. In the second dialog box, change the number of columns to 2. Then enter the following for the list of allowed values:
Pending: Waiting for an item to become available.
Ready: The item is ready for the customer to pick up.
Complete: The customer has picked up the item.
Cancelled: The request has been cancelled.
The dialog should look like Figure 2-42.

Figure 2.42. Entering the Status values
Because there are multiple columns, you'll need to specify which one is stored in the Request table. In the next dialog box, select Col1 as shown in Figure 2-43.

Figure 2.43. Select the column to store in the Request table
After the Lookup Wizard is finished:
Set the Default Value property to Pending
Set the Required property to Yes
Set the "Allow Zero Length" property to No
ADDING THE INVENTORYITEMID FIELD
Add another field named InventoryItemID and use the Lookup Wizard to configure this exactly like you did for the InventoryItemID field on the Loan table. The only difference, however, is that the Required property should be set to No. This value will not be assigned until later, when an item becomes available.
For all three lookup fields, CustomerID, ItemID, and InventoryItemID, go to the Lookup tab of the Field Properties pane and make sure the "Limit to List" property is set to Yes.
Viewing the Relationships
As you have been creating tables and defining their fields, the Lookup Wizard has been creating table relationships. You can view these in a graphical presentation. From the Database Tools ribbon, click the Relationships button. You may need to click the All Relationships button to refresh the view. Drag the tables around to simplify the connecting lines. After some re-arranging, the view should look like Figure 2-44.

Figure 2.44. Viewing the relationships
Summary
In this chapter, you created an Access database and defined a set of tables, which will form the foundation for the remainder of this book's project. You used the Lookup Wizard to define the foreign keys and the table relationship. The tables are well constrained, which will help ensure data integrity. The advanced features that you used include the following:
Using quick start fields
Using an input mask
Creating a unique constraint
Using default values
Defining field validation
Using calculated fields
Defining fixed value lookups
In the next chapter you'll use data macros to further enhance the database design