Part I: Access Building Blocks
92
payroll tables first, connecting these tables with a relationship to make it easy to find all the payroll
information for an employee.On the CD-ROM
This chapter uses a variety of data from the database named Chapter03.accdb. If you haven’t already cop-
ied it onto your machine from the CD, you’ll need to do so now. If you’re following the examples, you can use
the tables in this database or create the tables yourself in another database.
Building Bulletproof Databases
In Chapters 1 and 2, you saw examples of common relationships found in many Access databases.
By far the most common type of table relationship is the one-to-many. The Collectible Mini Cars
application has many such relationships: Each record in the Customers table is related to one or
more records in the Sales table (each contact may have purchased more than one item through
Collectible Mini Cars). (I cover one-to-many relationships in detail in the “Table Relationships”
section, later in this chapter.)You can easily imagine an arrangement that would permit the data contained in the Customers and
Sales tables to be combined within a single table. All that would be needed is a separate row for
each order placed by each of the contacts. As new orders come in, new rows containing the cus-
tomer and order information would be added to the table.The Access table shown in Figure 3.1 is an example of such an arrangement. In this figure, the
OrderID column contains the order number placed by the contact (the data in this table has been
sorted by CustomerID to show how many orders have been placed by each contact). The table in
Figure 3.1 was created by combining data from the Customers and Orders tables in the Northwind
Traders sample database and is included in the Chapter03.accdb database file on this book’s
CD-ROM.Notice the OrderID column to the right of the CompanyName column. Each contact (like Alfreds
Futterkiste) has placed a number of orders. Columns to the far right in this table (beyond the right
edge of the figure) contain more information about each contact, including address and phone
numbers, while columns beyond the company information contain the specific order information.
In all, this table contains 24 different fields.The design shown in Figure 3.1 is what happens when a spreadsheet application such as Excel is
used for database purposes. Because Excel is entirely spreadsheet oriented, there is no provision
for breaking up data into separate tables, encouraging users to keep everything in one massive
spreadsheet.