Chapter 11 - Relational Implementations for Personal Databases

Creating the Database Schema

CUSTOMER

_ID  Name
      Phone
      Address
        Street
        City
        State
        Zip
    ARTIST_0.N

WORK

_ID  WorkID
        ARTIST_0.N
        Title
        Copy
      Description

ARTIST

_ID  Name
      Nationality
      Dates
        Birth
        Death
    WORK_0.N
        CUSTOMER_0.N

ARTIST (ArtistName, Nationality, Born, Died)
CUSTOMER CustomerNumber, CustomerName, Phone, Street, City, State, Zip)
ARTIST_CUSTOMER_INT(ArtistName, CustomerNumber)
WORK (ArtistName, Title, Copy, Description)

Creating Tables - using Access's Table Definition Form
Defining Relationships - Tools|Relationships (drag & drop)
Creating Queries
- SINGLE-TABLE QUERIES (QBE & SQL)
- MULTI-TABLE QUERIES (Using Show Table dialog)

Creating Forms
- Binding a Relation to a Form (Point & Click)
- Binding a Column to a Text Box Control (Setting the Control Source field)
- Subform Control
  1. create a subform that has the required columns
  2. identify the Record Source and bind the text boxes to columns
  3. bind the subform to the form using the subform control (set the Source Object property)
  4. Enter the keyfields for Link Child Fields and Link Master Fields
- Binding a Table or Query to a Combo Box Control
  1. Drop a ComboBox on the form
  2. Enter CUSTOMER into the Row Source property (under Data tab)
  3. Select the Event to take place (=AddIntersectionRow("ARTIST") in the After Update event)
Creating Reports
- Grouping
- Defining the Report (based on table or query)
  1. select Reports -> New -> name of table or query
  2. select Sorting and Grouping from View menu - select fields, their order, and whether to include headers/footers
  3. add lines where needed
  4. adjust fonts, colors, etc.