Quick reference

• 00:04 This is lesson 25 of Access 2013.
• 00:09 When you create queries, if key information is missing,
• 00:12 all the records might not be displayed.
• 00:16 Hi, this is Crystal.
• 00:18 In this lesson, you will learn how to show all the records
• 00:22 even when foreign key fields are Null.
• 00:25 In lesson 23, we created a report
• 00:28 called r_CustomerServicesByYear.
• 00:32 In the last lesson, we added grouping and sorting.
• 00:36 In this lesson, we will modify the RecordSource of the report
• 00:40 to show all the records from WorkServices
• 00:44 even when time type is not filled out.
• 00:48 When you look at the report, you see only records that
• 00:52 have time type filled are displayed.
• 00:56 When you open the WorkServices table,
• 00:58 you can see there are 130 records.
• 01:02 The problem is that time type is not always filled out.
• 01:06 So how do we get records to show,
• 01:09 even when time type is missing?
• 01:13 Go to the Design View of the report.
• 01:15 On the Data tab for the report,
• 01:18 you can see that the report is based on a query
• 01:21 called q_CustomerServicesByYear.
• 01:25 Each time the RecordSource is changed and saved,
• 01:29 this query is changed.
• 01:31 Click on the Builder Button, …, to go to the Query Builder.
• 01:37 When you click the Datasheet View icon,
• 01:39 you can see in the lower left that only 33 records show.
• 01:45 Go back to Design View.
• 01:47 Right-click on the relationship line
• 01:49 between WorkServices and TimeTypes.
• 01:53 Choose Join Properties from the shortcut menu.
• 01:57 Access defaults to showing rows from the joined tables
• 02:01 where the joined fields are equal.
• 02:04 Null is the absence of a value
• 02:07 and cannot be equal to anything, not even another Null.
• 02:11 This is why some database systems
• 02:14 default to a zero-length string (ZLS)
• 02:17 when there is no value in a text field,
• 02:19 and probably one reason why Access
• 02:21 defaults to 0 for numeric fields.
• 02:25 Notice you can change the Join Properties to show all records
• 02:29 from Time Types, or show all records from Work Services.
• 02:34 Change the option to "Include all records from 'WorkServices'
• 02:38 and only those records from 'TimeTypes'
• 02:41 where the joined fields are equal."
• 02:45 Once the Join Properties are changed, the line will change
• 02:49 to show an arrow pointing to the less important table.
• 02:53 Now when you look at the Datasheet View,
• 02:55 all 130 records are displayed.
• 02:59 Close the Query Builder and save the changes.
• 03:03 The underlying query is actually what was changed.
• 03:07 As you scroll through the report,
• 03:09 you see a lot more records here too.
• 03:13 When you go to the next page,
• 03:15 you see there is one, and it is not blank.
• 03:18 In Print Preview mode, when you click on the report,
• 03:21 you toggle between zoomed in and zoomed out mode.
• 03:26 Flip through each page of the report.
• 03:29 In the next lesson, you will learn how to size controls
• 03:33 using Layout View, which lets you make limited design changes
• 03:38 while you are looking at data.

Lesson notes are only available for subscribers.