Modify Relationship Properties to Show All Records

Locked lesson.

Upgrade

About this lesson

Learn how to change report Record Source to show all records.

Quick reference

Modify Relationship Properties to Show All Records

Steps

Change Report Record Source to Show All Records

  1. From the Design View of a Report, select the report by clicking in the upper left where the rulers intersect. You will see a black square.
  2. On the Data tab of the Property Sheet, click on the Builder Button (...) for the Record Source
     
  3. Right-Click Join line between the table you want to see records for and the table it is related to.

     
  4. Choose Join Properties from the shortcut menu. By default, Access will only show rows from the joined tables where the joined fields are equal. Null is the absence of a value and cannot be equal to anything, not even another Null.
  5. Change the Join Type to 2 or 3 – look at the table names to see which should be picked.
  6. After the Join Properties are changed, an arrow will point to the less important table.

  7. Save and Close
Login to download
  • 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.