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
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
No comments:
Post a Comment