Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Tables > Document Table
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries: Tables: Document Table

After creating several tables, do you remember the design choices you made and why?

Good documentation is important. You may not be the one who has to maintain the database later. After a month or two have passed and after a few other projects have occupied your mind, you may get confused yourself about how all the parts fit together.

You can print a table's datasheet but you cannot print the Table Design View. So annoying! Access has several features that can help you document your tables and other objects but you will have to put the pieces together in your head yourself.

  • Relationships: The Relationships window can be printed as a Relationships report.

  • Documenter: This handy feature produces a detailed list of the features of your tables, including all of the properties of each field. This is a big mess... until you need what's in it. Then it is a gold mine!

  • Object Dependencies task pane: Shows what objects depend on other objects. This is very helpful when you are thinking about revising or deleting an object. It takes a bit of practice, however, to understand the tree of dependencies.


Icon: Step-by-Step 

Step-by-Step: Document a Table

 Icon: Step-by-Step

What you will learn: to print Relationships
to print a table datasheet
to Documenter for a table
to view dependencies

Start with:  Class disk, Projects database from previous lesson open.

Print Relationships

The Relationships Report button creates a printable report that shows the Relationships window. You can edit this report in Design View but not while in Print Preview.

  1. Icon: Relationships Display the Relationships.
    Database Tools ribbon tab > Relationships Button: Relationships (Access 2010)

  2. Relationships: Print Preview with 3 tables (Access 2013)Click the button Relationships Report Button: Relationships Report (Access 2010) on the ribbon tab Relationship Tools: Design.
    Access creates a printable version of the relationships.
    You cannot add a header or footer or make any changes to this page in Print Preview. But you can edit the report in Design View.

  3. Close the print preview window by clicking the Close Print Preview button on the Print Preview ribbon tab.
    The report design view appears.

  4. Drag the bottom of the Page Header section down about a quarter of an inch.
  5. Relationships report with new page header (Access 2013)Click the Label button in the gallery of controls on the Report Design Tools: Design ribbon tab.
  6. Click in the new Page Header section at about the 4" mark on the ruler and type Two One-to-Many
    Joins
    .

    You could add other comments to the Details section by adding label controls.

  7. Print Preview of revised report (Access 2013)Open the report's Print Preview again.
  8. Icon: Print Click on the Print button on the ribbon to open the Print dialog.

  9. Make any changes needed to the choices in the dialog and then print this one-page report.
  10. Close the tab and save the changes as Relationships-three tables.
  11. Close the Relationships window.


Print Table Datasheet

  1. Icon: Datasheet View Table Datasheet View: ProjectsOpen the table Projects in Datasheet View.

    The fields ProjectName and ProjectDescription columns are too narrow to show everything in them. This is exactly how the table will print. Not good enough!

    You need to adjust the column and row sizes.

  2. Print Preview of Projects table, after adjusting widths and heights (Access 2010)

    Resize the column for ProjectName wide enough to see all of the names.

  3. Drag the ProjectDescription column wider and then drag the row to make it taller. All rows will be the same height!!

  4. Icon: Print Preview Open Print Preview again. Better!

    The subdatasheets will not print even if they were open in Table Datasheet View!

    Dialog: Page Setup - Print Headings selectedIcon: TroubleProblem: Column headings do not show in the print preview.
    Solution:
    Click on the Page Setup button. On the Print Options tab, check the box for Print Headings.

  5. Icon: Print Print  by clicking the Print button on the ribbon tab.

    Unfortunately, you do not have much control over table print-outs in Access. That's why we will be learning about Reports later!  

  6. Button: Save (Access 2010) Close Print Preview and then the table.
    When prompted, save the changes to the table's layout.


Use Documenter for a Table

The Documenter report includes a LOT of information. Exactly what you see depends on what properties have been set for the table and its fields.

  1. From the Database Tools ribbon tab, click on the Database Documenter button Button: Database Documenter (Access 2010) Button: Database Documenter (Access 2016).
    The Documenter window opens.  The only view is Print Preview.

  2. Dialog: Documenter - ProjectsOn the Tables tab, check the box for Projects and click on OK.
    Access creates a report and opens it in Print Preview.
     

    Message: Must close table first. Close now?Icon: Trouble Problem: Message appears instead.
    If the object that you want to document is still open, it must be closed first. Just click on Yes.

  3. Documenter report for Projects table (Access 2010)View each page of the 3-page report.
    Eek! What a mess! There is a lot more information here than you probably want to deal with!

    Features that do not have settings, like Format property or Input Mask property, are not listed. Properties that have a default value will be shown.

    Icon: TroubleProblem: Cannot tell how many pages there are.
    Nothing in the window tells you how many pages there are in the report.
    Solution: Click on the Last Page button Button: Last Page. Once that last page displays, you can see in its page number in the navigation box. That's how many pages are in the report.
    Complication: Access must format all of the pages before it can show you the last page. For long or complex reports, this can take a long time! If your computer is underpowered, the whole program can lock up or generate an error.

  4. Icon: Print Print the report.  

  5. Close the report.

  6. Find the following information in the report.   
    Highlight or circle the answers on the print-out and label with the matching letter.
    Your instructor may want you to turn your marked report in for grading.

    1. Name of the table

    2. Location of the database file

    3. Date the table was created

    4. Date of the print-out

    5. Number of records in the table

    6. Size of the ProjectName field

    7. Type of the ProjectDescription field

    8. Table(s) related to the table

    9. Are cascading deletes and updates allowed?
      "Not enforced" means that referential integrity is not enforced and therefore no cascades are allowed.

    10. Type of relationship

    11. How many indexes are there?

  7. Create a Documenter report on the the table Staff.
    This table has more fields than the Projects table, so the report will be 10 to 13 pages long! Do not print unless your instructor tells you to.

  8. Find the following information in the report.
    Either mark your print-out or list your answers on the back of the Documenter report for the Projects table, along with the page number. The number of pages varies somewhat with each version of Access.

    1. Name of the table

    2. Location of the database file

    3. Date the table was created

    4. Date of the print-out

    5. Number of records in the table

    6. Table(s) related to the table

    7. Are cascading deletes and updates allowed?

    8. Type of relationship

    9. How many indexes are there?

    10. What field(s) have a Format property set?

    11. What field(s) have an Input Mask?

    12. What field(s) have a validation rule and what is the rule?


View Dependencies

Access can show a hierarchy tree view of dependencies that is very useful when you are making changes or deletions to objects in your database. If you look at the dependencies before deleting or changing, you can see what other objects might be affected. This helps you avoid embarrassing and time-consuming mistakes, like renaming a field or deleting a query when it is used in 3 forms and 2 reports. Unfortunately there is no convenient way to print out this pane.

  1. Task Pane: Object Dependencies - for ProejctStaff tableIn the Navigation Pane, select the table Project Staff.

  2. On the Database Tools ribbon, click the button Object Dependencies Button: Object Dependencies (Access 2010).
    The Object Dependencies task pane opens at the right.

    This pane shows the name of the object at the top.
    You can show one of two dependency trees for this object:

    • Objects that depend on me (the default view)
    • Objects that I depend on

    The Project Staff table has a relationship with both the Project table and the Staff table. As you add other tables, queries, forms, and reports, other objects that use fields from the Project Staff table will appear in the pane.

    Icon: TroubleProblem: No dependencies shown
          Solution:
    Enable "Tracking name AutoCorrect info"
     File > Options > General
    Icon: Access 2013 Icon: Access 2016 Access 2013, 2016: File > Options > Current Database > scroll to 'Name AutoCorrect' section

  3. Task Pane: Object Dependencies - for ProejctStaff table - expanded (Access 2013)Task Pane: Object Dependencies - for ProejctStaff table - expanded

    Click on an Expand button Button: Expand Button: Expand arrow (Access 2013) beside the Projects table.
    The dependencies tree expands to show what objects depend on the Project table.

    Dependencies can be nested up to 8 deep but you can only navigate through 4 levels. When expanded, it can look very confusing because the same object will often appear in several places. Dependencies can be a tangle of relationships that loop back on themselves! The key facts are simple. Objects that are dependent on each other cannot be deleted or edited arbitrarily! It takes careful thinking.

  4. Icon: Experiment Experiment: Object Dependencies Pane
    • Expand and collapse the dependencies tree.
    • Show dependencies for a different object: Select the object in the Navigation Pane and click the Refresh link at the top of the Object Dependencies pane.