Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Practical Examples: The Application Design for a Computer Consulting Firm

Consider a hypothetical computer consulting firm that wishes to track its time and billing with an Access application. First, look at the application from a design perspective.Chapter 2. You can find all of them in the application databases on the sample code CD-ROM:

• tblClients
  This table contains all the pertinent information about each client; it's related to tblProjects, the table that will track the information about each project associated with a client.

• tblClientAddresses
  This table contains all addresses for each client; it's related to tblClients and tblAddressTypes.

• tblAddressTypes
  This table is a lookup table. It contains all valid address types for a client; it's related to tblClientAddresses.

• tblClientPhones
  This table contains all phone numbers for each client; it's related to tblClients and tblPhoneTypes.

• tblPhoneTypes
  This table is a lookup table. It contains all valid phone types for a client; it's related to tblClientPhones.

• tblTerms
  This table is a lookup table. It contains all valid payment terms for a client; it's related to tblClients.

• tblContactType
  This table is a lookup table. It contains all valid contact types for a client; it's related to tblClients.

• tblProjects
  This table holds all the pertinent information about each project; it's related to several other tables: tblClients, tblPayments, tblEmployees, tblTimeCardHours, and tblTimeCardExpenses.

• tblTimeCardHours
  This table is used to track the hours associated with each project and employee; it's related to tblProjects, tblTimeCards, and tblWorkCodes.

• tblPayments
  This table is used to track all payments associated with a particular project; it's related to tblProjects and tblPaymentMethods.

• tblTimeCardExpenses
  This table is used to track the expenses associated with each project and employee; it's related to tblProjects, tblTimeCards, and tblExpenseCodes.

• tblEmployees
  This table is used to track employee information; it's related to tblTimeCards and tblProjects.

• tblTimeCards
  This table is used to track each employee's hours; it's actually a bridge between the many-to-many relationship between Employees and Time Card Expenses, as well as between Employees and Time Card Hours. It's also related to tblEmployees, tblTimeCardHours, and tblTimeCardExpenses.

• tblExpenseCodes
  This table is a lookup table for valid expense codes; it's related to tblTimeCardExpenses.

• tblWorkCodes
  This table is a lookup table for valid work codes; it's related to tblTimeCardHours.

• tblPaymentMethods
  This table is a lookup table for valid payment methods; it's related to tblPayments.

• tblCorrespondence
  This table is used to track the correspondence related to a project; it's related to tblProjects and tblCorrespondenceTypes.

• tblCorrespondenceTypes
  This table is a lookup table for valid correspondence types; it's related to tblCorrespondence.

• tblCompanyInfo
  This table is a system table. It is used to store information about the company. You can find this information on forms and reports throughout the system.

• tblErrorLog
  This table is a system table. We use it to store runtime errors that occur.

• tblErrors
  This table is a system table. We use it to store valid error codes and descriptions.

The relationships among the tables are covered in more detail in Chapter 3, but they're also shown in Figure 1.18.