Creating a view that returns all records from some tables and related records from other tables

1
Creating a view that returnsall records from
some tables andrelated records from other tables

• 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
What is the Task?

• Before commencing view creation, the analyst must
  gather some information from the client.
• The first question
• A What do you want to see?
• C Workers, buildings, and workers assigned to
  buildings.
• Clarify
• ADo you want to see all workers, or only those
  who are assigned to buildings? Or, do you want to
  see all buildings, or only to which workers have
  been assigned?
• C All workers, whether or not they have been
  assigned to a building.
• Elaborate
• A What specific data do you want to see?
• C Worker names and building addresses.

4
Which tables are needed? Those that contain
fields of interest and those that relate tables
that contain fields of interest.
You may ignore the other tables for this view
5
What is the relationship among the data in the
various tables?
Note Workers is the most inclusive of those we
must include
Some Workers are Assigned to Some Buildings
Assignments
?
?
?
?
?
Now, which of ??? must we include to meet the
clients requestAll workers, whether or not
they are assigned to buildings.
Some Buildings have no Assigned Workers
Some Workers are not Assigned to Buildings
6
Step 1 Add Tables

• Create a new view in Design View
• Add the tables that comprise the view
• tblWorker,tblAssignment, tblBuilding
• Access will applyall existingrelationships
• Delete any that donot apply to thisview

ManagerID FK is irrelevant, right click on line
and select Delete
7
Step 2a Modify Relationships

• Start with the most inclusive table
• Right click on a relationship with this table
• Choose Join Properties
• Select the option that says Include ALL records
  from most inclusive table and only those
  records from other table where the joined
  fields are equal.
• Click OK
• Repeat for any other relationships with this
  table

8
Step 2a (contd)

• Note that the relationship line now appears as an
  arrow pointing away from the largest area table

9
Step 2b...n Other Relationships

• Continue to modify relationships until all are
  arrows that point away from the most inclusive
  table

10
Step 3 Output

• Specify elements to be output
• Save the view

11
Step 4 Execute to Check

• Execute the view to check the results

12
Designing for UsabilityNull Values in Outer
Joins

• Instead of leaving a set of blanks when the view
  does not return values, consider creating a
  message instead.
• Test for null value in the primary key of the
  first table that might not have a match,
  usually the table at the point of the first
  arrow head

13
Step 5 Null Value Message

• Either type or use the builder wizard to create
  an expression such as this
• StartDate IIf(IsNull(tblAssignment!BuildingID
  ),"No Building Assignments",tblAssignment!St
  artDate)

Yields this
Entered here
14
The Underlying SQL Statement

• As written by Access
• SELECT tblWorker.FirstName, tblWorker.LastName,
  IIf(IsNull(tblAssignment!BuildingID),"no
  assignments", tblBuilding!Address) AS Address,
  tblBuilding.City, tblBuilding.State,
  tblBuilding.Zip
• FROM tblWorker LEFT JOIN (tblBuilding RIGHT JOIN
  tblAssignment ON tblBuilding.BuildingID
  tblAssignment.BuildingID) ON tblWorker.WorkerID
  tblAssignment.WorkerID

15
The Underlying SQL Statement

• As I would write it for standard SQL
• SELECT W.FirstName, W.LastName,
  IIf(IsNull(A.BuildingID),"no assignments",
  B.Address) AS Address, B.City, B.State, B.Zip
• FROM tblWorker W LEFT JOIN tblAssignment A ON
  W.WorkerID A.WorkerID LEFT JOINtblBuilding B
  ON B.BuildingID A.BuildingID