Defining a Restrict Delete Relationship

Now, you're ready to start defining relationships between these web tables. Each vendor in our Restaurant Database can have more than one invoice. This means Vendors and InvoiceHeaders have a one-to-many relationship. Before you begin, make sure the Vendors table is closed. When you use the Lookup Wizard, Access needs to lock both tables in order to create the new lookup field. If you have the Vendors table open in Datasheet view and try to create a lookup field that uses that table as its source, you'll get an error when Access tries to create the field during the last step. To create the relationship you need, open the InvoiceHeaders table in Datasheet view and place the focus in the InvoiceID field so your new field appears to the right of the ID field. Next, click the More Fields button in the Add & Delete group on the Fields contextual tab and then click Lookup & Relationship from the drop-down list of options. Access opens the Lookup Wizard dialog box.

On the first page of the wizard, Access needs to know where you want to fetch the values for this new lookup field. To create a new field that has a relationship between a different web tables, select the first option-The Lookup Field To Get The Values From Another Table. Click Next to proceed to the second page of the wizard.

On the second page of the wizard, Access needs to know which web table you want to use to provide the values for your new lookup field. (Note that in web databases, you cannot create relationships between a table and query.) We want to store the vendor who produced the invoice in the InvoiceHeaders web table, so select the Vendors table from the list and then click Next. Access opens the third page of the wizard.

On the third page of the wizard, you can select which field or fields to display in your lookup field. You can select any field in the Available Fields list and click the single right arrow (>) button to copy that field to the Selected Fields list. You can also click the double right arrow (>>) button to copy all available fields to the Selected Fields list. If you copy a field in error, you can select the field in the Selected Fields list and click the single left arrow (<) button to remove the field from the list. You can remove all fields and start over by clicking the double left arrow (<<) button.

If you want to use a Calculated field as one of the display columns in your Lookup Field for a web database, you can only use Calculated fields that have Text result types. In web databases, Access does not show you any Calculated fields with result types other than Text on the third page of the Lookup Wizard dialog box to choose display columns. However, if you are using a client database, you can use Calculated fields with result types other than text for display values in a Lookup Field.

By default, Access always sets the ID field as the first column in your lookup field. You cannot change this behavior because it is a SharePoint requirement. SharePoint enforces the relationships on the server through the ID field. For your new lookup field, select the VendorName field. (The users of your database will find it much easier to choose a vendor name from a list rather than just a list of vendor ID numbers.) Click Next to proceed to the fourth page of the wizard.

If you want to create a self join relationship in a web database, select the same table that you are creating the new lookup field in on the second page of the Lookup Wizard. You can then select another field you want to use for your new lookup field. A self join relationship could be useful, for example, when you have a table of employees and one of the fields contains the name of the employee they report to in the organization.

On the fourth page of the wizard, Access allows you to select up to four fields to sort either Ascending or Descending. Click the arrow to the right of the first field and then select the VendorName field. The button next to the first box indicates Ascending. If you click the button, it changes to Descending. For now, let's leave it as Ascending. (You can click the button again to set it back.) Click Next to go to the fifth page of the wizard.

On the fifth page of the wizard, you can choose to hide the key column, which is selected by default. If you clear this option, Access displays the ID field from the Vendors table in your lookup field drop-down list. On the bottom half of the page, you can resize the Vendor Name column by dragging its right edge to the size you want, or you can double-click the right edge of the column and Access adjusts the width to fit the data. Leave these settings as they are and then click Next to proceed to the last page of the wizard. Note that you could click Finish now to exit the Lookup Wizard, but Access would not enforce referential integrity for this field because it is not the default option on the last page of the wizard.

On the last page of the wizard, you can enter a name for your new lookup field. Type VendorID for the name of your new lookup field in the text box at the top of the page. Because you probably don't want any rows created in InvoiceHeaders for a nonexistent vendor, select the Enable Data Integrity check box. (Selecting this check box achieves the same functionality as selecting Enforce Referential Integrity in a client database relationship.) When you do this, Access 2010 ensures that you can't add a row in the InvoiceHeaders web table containing an invalid VendorID. Also, Access won't let you delete any records from the Vendors table if they have invoices still defined.

After you select the Enable Data Integrity check box, Access 2010 makes two additional radio buttons available: Cascade Delete and Restrict Delete. If you select the Cascade Delete check box, Access deletes child rows (the related rows in the many table of a one-to-many relationship) when you delete a parent row (the related row in the one table of a oneto- many relationship). For example, if you remove a vendor from the Vendors web table, Access removes all the related InvoiceHeader rows. If you select the Restrict Delete option (the default), Access prevents you from deleting a vendor from the Vendors table if there are invoices in the InvoiceHeaders table that use that vendor. In this design, we don't want to remove all invoices for accounting purposes so leave the default option, Restrict Delete, set.

The last option on this final page of the wizard-Allow Multiple Values-tells Access to create a Multi-Value Lookup Field. You can only select this option if the Enable Data Integrity check box is cleared. Click Finish to complete the steps necessary to create your lookup field with a relationship to the Vendors table. You can see the completed new VendorID lookup field in the InvoiceHeaders web table. Click the Save button on the Quick Access Toolbar to save these latest definition changes.

If you need to modify this lookup field or its relationship, you can highlight the field in Datasheet view, and click the Modify Lookups button in the Properties group on the Fields contextual tab. Access opens the Lookup Wizard to the third page, where you can adjust the field or fields to display in your lookup field. You can adjust the settings for this lookup field on the various wizard pages from this point on and then save the changes. If you want this lookup field to use a different table as its source or use a value list as its source, you'll need to delete the lookup field and recreate a new lookup field by starting over with the Lookup Wizard.

You now know enough to define the additional one-to-many Restrict Delete relationship that you need in the InvoiceDetails web table. You need to include the ReportGroupName field from the ReportGroups web table in the InvoiceDetails web table, so open the Invoice- Details web table in Datasheet view and set the focus on the InvoiceDetailsID field.

Follow this procedure to build your relationship:

Start the Lookup Wizard by clicking More Fields and then clicking Lookup & Relationship.
On the first page of the wizard, select the "The Lookup Field To Get The Values From Another Table" option, and then click Next.
On the second page of the wizard, select the ReportGroups web table, and then click Next.
On the third page of the wizard, bring over the ReportGroupName field from the Available Fields list to the Selected Fields list, and then click Next.
On the fourth page of the wizard, select the ReportGroupName field in the dropdown list to sort Ascending by that field, and then click Next.
On the fifth page of the wizard, leave the default options as they are to hide the key column and keep the widths the same. Click Next to go to the final page of the wizard.
On the last page of the wizard, name your field ReportGroupID and select the Enable Data Integrity option. Leave the default option, Restrict Delete, set, and then click Finish to complete the new field and relationship.
Finally, save your changes to the InvoiceDetails web table.

SharePoint lists support a maximum of 20 indexes, so you need to take this into account when deciding which fields to index. The SharePoint ID field and each Lookup Field you create all count toward the 20-index limit. If you attempt to publish a table with more than 20 indexes to a SharePoint site, Access stops the publish process and reports the error.

[Previous] [Contents] [Next]

Defining a Restrict Delete Relationship

In this tutorial: