MS-Access / Getting Started

---

Creating Relationships Using Lookup Fields

The process of creating relationships between tables in web databases is very different from creating relationships in client databases. In client databases, you generally create all the tables and fields you need and then create relationships between the various tables using the Relationship Window. In web databases, however, you do both of these steps at the same time through the Lookup Wizard. What this means to you as an Access developer is that you cannot fully complete child tables before you complete the parent tables. For example, in a client database, you could create a vendor field in a table of invoices to hold the vendor ID even before you created the vendor table itself. After you create the vendors table, you could then link the two tables on the appropriate fields using the Relationship Window. In a web database, the vendor table must exist before you can actually create fields in child tables that you intend to link to the vendor table. You cannot add relationships to existing fields in web databases; you must create the relationship at the time you create the field.

One other important difference between relationships in client and web databases is that client databases allow you to create relationships between tables with data types other than number. You can, for example, create a relationship in a client database between two tables using a Text data type (so long as both fields are defined as Text data types). In a web database, you can create relationships only on the SharePoint ID field.

In versions of SharePoint before SharePoint 2010, you could not create relationships between SharePoint lists. In SharePoint 2010, Microsoft added the ability to create relationships between different lists using the ID field. To ensure that the relationships you create in a web database are compatible with the SharePoint relationship structures, you must use the Lookup Wizard in Access 2010 to create or modify your web table relationships.

Thus far in this tutorial, you have seen how to build one of the main subject tables of the Back Office Software System web database-Vendors. You also created a generic Appointments table in your Restaurant Database file. To show you how to create relationships in web databases, we first need to create another main subject table-Report Groups-and then a parent and child table-Invoice Headers and Invoice Details-to track the invoices that list all our food purchases. We'll first create all the fields for these three tables and then add the linking field last. Table 6-7 shows you the fields you need to create for the Report Groups table that hold the information for the report groups we use to track all the various expenditures for the restaurant.

Table-7 Field Definitions for the Report Groups Table

Field Name	Data Type	Description	Field Size
ReportGroupID	ID	ID Field (AutoNumber)
ReportGroupName	Text	Report group name	50
AccountNumber	Text	Account number of report group	50
AccountDescription	Text	Optional description for additional information	255

The Report Groups main table has all the fields we need, but the Invoice Details table depends on this table, so you need to create this table first. After you define all the fields, save the table as ReportGroups.

Table-8 shows you the fields you need to define for the Invoice Headers table that holds the parent information about each invoice the restaurant receives.

Table-8 Field Definitions for the Invoice Headers Table

Field Name	Data Type	Description	Field Size	Format
InvoiceID	ID	ID Field (AutoNumber)
InvoiceDate	Date/Time	Date of the invoice		Short Date
InvoiceNumber	Text	Invoice number shown on invoice	50
InvoiceAmount	Currency	Total invoice amount		Currency
Comments	Memo	Any additional comments		Rich Text
Attachments	Attachment	Invoice specific files
IsBalanced	Yes/No	Invoice balanced?

The Invoice Headers table needs to know which vendor this invoice came from. We'll create that field and relationship to the Vendors table in just a moment. Save this table as InvoiceHeaders after you create the necessary fields.

You need one last table, the Invoice Details table, to track the invoices for Restaurant Database. Table-9 shows the fields you need to create. This table needs the InvoiceID from the Invoice Headers table and the ReportGroupID from the Report Groups table to track the all the line items from the invoice. We'll create those fields in just a moment. Save this last table as InvoiceDetails.

Table-9 Field Definitions for the Invoice Details Table

Field Name	Data Type	Description	Format
InvoiceDetailsID	ID	ID Field (AutoNumber)
ReportGroupAmount	Currency	Amount for this report group	Currency