Title: 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.
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.
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
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.
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 AssignmentstblAssignment!St artDate)
Yields this Entered here 14 The Underlying SQL Statement
FROM tblWorker W LEFT JOIN tblAssignment A ON W.WorkerID A.WorkerID LEFT JOINtblBuilding B ON B.BuildingID A.BuildingID
PowerShow.com is a leading presentation/slideshow sharing website. Whether your application is business, how-to, education, medicine, school, church, sales, marketing, online training or just for fun, PowerShow.com is a great resource. And, best of all, most of its cool features are free and easy to use.
You can use PowerShow.com to find and download example online PowerPoint ppt presentations on just about any topic you can imagine so you can learn how to improve your own slides and presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!
For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!