MS-Access / Getting Started

---

Creating a Web Table Using Application Parts

Access 2010 comes with a new feature called Application Parts to help you build a few common web tables and other web database objects. We first introduced you to this new feature when we discussed creating client tables. The good news with this feature is that you can also use Application Parts when creating web fields, web tables, and other web objects. In this section, we'll show you another feature with Application Part.

To complement the simple employee web table you created previously, it would be helpful to create another web table that can track any issues assigned to the employees. To build this second web table using one of the Application Parts, close the employees table if you still have it open, click the Create tab on the ribbon, and then click the Application Parts button in the Templates group. Access displays a list of 10 form types under the Blank Forms category and five Application Parts under the Quick Start category. Microsoft uses the term Models to refer to this one-click object creation feature.

When you are using the five Application Parts under Quick Start in a web database, Access creates the same fields, tables, and objects as it does for a client database. The only difference when creating these objects in a web database is that Access marks everything as a web object. The five web Application Parts under Quick Start, which represent some of the more common types of web table structures and objects found in databases, are Comments, Contacts, Issues, Tasks, and Users.

Click Issues in the Quick Start list, and Access opens the Create Relationship wizard. Whenever you select to build an Application Part under Quick Start in a database that already includes at least one existing table, Access displays the Create Relationship wizard to see if you want to create a relationship between one of your existing tables and the new table. In this case, Access identified the existing employees table you created earlier and now asks if you want to build a relationship with the new Issues table Access is about to create.

On the first page of the Create Relationship wizard, Access displays three options. Next to the first two options, Access displays combo boxes that list all the existing saved tables in your database. Above these combo boxes, Access displays text to help identify how it will create the relationship between the two tables. If you select either of these two first options, Access creates a one-to-many relationship between the selected table in the combo box and the new issues table Access will create. Select the first option if you want Access to create a new lookup field in the issues table, representing the many part of the relationship. (We'll discuss more about lookup fields and relationships in web tables later in this tutorial.) Select the second option if you want Access to create a new lookup field in the selected table, representing the many part of the relationship. You can choose to select a different table in the combo boxes; by default, Access displays the tables in alphabetical order. Select the third option if you do not want Access to create a relationship between the new issues table and any existing table. Note that if you choose this option, Access does not create an additional lookup field in any tables.

For your example, we want to have the employees table (tblEmployees) represent the one side of the relationship and the issues table represent the many side of the relationship- each employee can have many issues assigned to them. Select the first option and leave the default tblEmployees selected in the combo box. (We only have one existing table in this sample database so you don't need to change any settings on the first page of the wizard.) Click Next to move to the second page of the wizard.

On the second page of the wizard, Access displays options where you can customize the new lookup field in the issues table. In the first combo box, Access displays a list of field names from the table you selected on the first page of the wizard. Access displays the field you choose here as the drop-down list of options in the new lookup field. Select FirstName in this combo box, selected by default, to display the first name of each employee in the lookup field. The second option, Sort This Field, allows you to sort the values of the field you selected in the first combo box option. You can choose Sort Ascending, Sort Descending, or None (the default) to sort the values. Select Sort Ascending, for this example, to sort the first names in ascending order. In the third option, enter the name of your new lookup field in the text box provided (AssignedTo, for our example). The last option on this page of the wizard-Allow Multiple Values-tells Access to create a Multi-Value Lookup Field. We don't want to assign issues to more than one employee, so leave this option cleared. Note that if you selected There Is No Relationship on the first page of the wizard, you won't see the second page of the wizard.

Note: If you click Cancel on either page of the Create Relationship wizard, Access immediately closes the wizard but does not cancel the creation of the objects associated with the Application Part you selected. Access, in this case, still continues the process of creating the objects but does not create any lookup fields in the tables representing relationships.

Click Create and Access builds a complete table structure for an issues web table, as well as other supporting objects. Open the Issues table and notice Access created 13 fields to identify the data elements for this Issues table. This Issues Application Part includes fields such as AssignedTo (your new lookup field), Summary, Status, Priority, Category, Project, Opened Date, Resolution, and so on to identify a single subject-an issue. The Issues Application Part also automatically defines a data type for each of these fields. Notice that if you click the drop-down list in the AssignedTo lookup field, you can see the first names of the employees from the tblEmployees table.

You can save time creating a web table by using an Application Part even if the table structure Access creates does not exactly match your needs. You can rename fields, add new fields, and delete unneeded fields to customize the table to your specific application needs. When you use an Application Part to help you create a web table, you also get the added benefit of Access creating other supporting objects to work with that table. Close the Issues table window now so you can continue with the next section.