Entering Data in a Table on the Many Side of a M:1 Relationship

1
Entering Data in a Table on the Many Side of a
M1 Relationship

• If you came to this presentation via a web
  browser,
• right-click and choose Full Screen before
  proceeding.
• Click mouse or press space bar to continue.

• This presentation was prepared by Professor Steve
  Ross, with the advice of other MIS Faculty, for
  use in MIS Classes at Western Washington
  University. Please contact Dr. Ross for
  permission to use in other settings..

2
The Database
3
Access Relationships
4
The Challenge

• Create a form for the entry of worker
  information, to include the foreign keys for
  DepartmentID, RankID, and SupervisorID

5
Creating the Form, Step 1

• Specify the table or view that the form affects
• tblWorker or vueWorker (both have same data)
• For each field in the table or view, indicate
  whether it is editable and constrained
• WorkerID autonumber, visible but not editable
• LastName editable
• FirstName editable
• AnnualPayIn1000 editable, 10 entry 200
• SupervisorID editable, FK to tblWorker
• RankID editable, FK to tblRank
• DepartmentID editable, FK to tblDepartment

6
Designing for Usability Foreign Keys

• Users shouldnt have to know or remember foreign
  key values
• Instead, present them a set of choices ordered in
  a meaningful manner
• If there are only a few numeric choices and the
  list rarely changes, radio buttons may be used
• If there are several choices and/or the list
  changes or is not known in advance, use a combo
  box based on a query

7
Creating the Form, Step 2

• Create a draft view of the form, giving
  approximate placement and type of controls for
  each data item

I usually do this by hand, not in a graphic
program such as this
These will be text boxes bound to the relevant
fields
These will be combo boxes, bound to the relevant
fields but displaying data from the related tables
8
Creating the Form, Step 3

• Use the application generator to create version 1
  of the form. This one will probably have text
  boxes for the foreign keys.

9
Creating the Form, Step 4

• Convert the foreign key controls to combo
  boxes
• The combo box row source is either a named view
  (e.g., vueRankID) or a SQL statement (e.g.,
  SELECT RankID,Rank FROM tblRank)

Technique will be demonstrated in class.
10
Creating the Form, Step 5

• Apply check constraints and other properties to
  controls.
• Once controls are set, apply other design
  standards.

The form illustrated here does not necessarily
meet all MIS 421 design standards.