Tables & Queries:
Document a Query

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016


A query's design and purpose needs to be documented just as much as a table's design. The same tools can be used - Documenter and Object Dependencies.

  • Print the datasheet: Just like a table, you can print the results of a query as a datasheet. You cannot print the Query Design View.

  • Documenter: This feature produces a report that is a detailed list of the features of a query, including the SQL version of the query's design. This is likely more information than you want to try to digest!
      Tools | Analyze | Documenter

  • Icon: Access 2003 Object Dependencies: In Access 2003 you can see in the Object Dependencies task pane 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 make sense of the tree of dependencies.

Now that there are more objects in your database, perhaps it is time to find a way to print the object dependencies task pane. A screen shot will work!


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    Summary
    Quiz
    ExercisesTo subtopics

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



Capture & Print a Screen Shot

A screen shot is an image of something on your computer screen. Many graphics programs have a built-in tool for capturing screen shots, but you can manage without them.

Procedure to capture & print a screen shot:

  • Position: Be sure what you want to capture is on top of any other open windows.

  • IBM keyboardCapture: Hold one of the ALT keys down while pressing the Print Screen key. This sends an image of the active window to the Windows Clipboard.
    (If you just press the Print Screen key, the Clipboard gets an image of the entire screen.)

  • MS Paint: Open the graphics program, MS Paint. Start menu | All Programs | Accessories | Paint. OR, Start | Run | type mspaint and click OK.

  • Paste: Use the menu Edit | Paste or the keyboard combo CTRL + v.

  • Edit: You can add text or other helpful markings. Paint does not have a tool to crop the images but you can select and copy part of the image and then paste into a new Paint canvas.

  • Save As: From the menu, File | Save As. The Save As dialog opens.

  • File Type: Choose the GIF file type to reduce the very large default file size.

  • Name and location: Enter a name for the file and navigate to the folder where you wish to save.

  • Print: From the menu, File | Print...


Compact and Repair a Database

   Compacting

As you work with your database, it gets bigger and bigger. Obviously if you add queries or tables or records, the database should be bigger. But part of the "bigger" may be from the file becoming fragmented on the disk. Access has a command to compact the database that you can use from inside Access.  Tools | Database Utilities | Compact and Repair a Database...t.

What compacting does: Compacting makes a copy of the file that uses space more efficiently. This will usually result in a smaller file size and may help database processes like sorting and filtering run faster.

Caution: If you set file permissions for the database, they are reset to the default file permissions on systems that use the NTFS file system. (If you don't know what all that means, you probably won't be affected!)

   Repairing

Errors happen. If your database shuts down unexpectedly, you may need to repair it. In fact, repairable errors can occur along the way even without a noticeable event. It is a good idea to compact and repair your databases regularly. If Access starts behaving oddly, compact and repair should be your first step before getting into involved technical support questions!   Tools | Database Utilities | Compact and Repair database....


Icon: Step-by-Step 

Step-by-Step: Document a Query

 Icon: Step-by-Step

What you will learn:

to use Documenter to create a report about a query
to view object dependencies for a query
to capture an image of the window
to add text to an image
to print a screen capture
to compact and repair a database

Start with:  Class disk, Projects database open.

Create Documenter Report

The Documenter produces a report about the query that is similar to the report for a table, but is not exactly the same.

  1. Dialog: DocumenterOpen the Documenter dialog.
    (Tools | Analyze | Documenter)
     

  2. Click on the Queries tab .
     

  3. Check the box for the query QStaff-FullName and click on OK.
    Access creates the report and opens it in print preview.
     
    Message: Must close query first. Close now?Icon: Trouble Problem: Message appears instead.
    If the object that you want to document is open, it must be closed first. Just click on Yes.
     

  4. Print Preview: Documenter - QStaff-FullNameView each of the 4 pages of this report. What is different from what the Documenter produced for a table?
     

  5. Icon: Print Print the report by clicking the Print button Button: Print on the toolbar.
     

  6. Close the report.
     

  7. Find the following information in the report and highlight or circle it on the print-out. Label with the matching letter. If your instructor wishes, turn your marked report in for grading.

    1. Name of the query

    2. Location of the database file

    3. Date the query was created

    4. Date of the print-out

    5. SQL version of the query

    6. Size of the Country/Region field

    7. Type of the Budget field

    8. How many indexes are there

    9. What tables or other queries are used as the source for a field


View Object Dependencies

  1. Task Pane: Object Dependencies - QStaff-FullNameIn the Database Window, right click on the query QStaff-FullName.
     

  2. From the popup menu, select  Object Dependencies .
    The task pane opens to show the "Öbjects that depend on me".
     

  3. Expand the tree for the two tables showing. Wow!
    Keep in mind that only two objects depend on the query - the two tables ProjectStaff listed under. So three objects depend on the table ProjectStaff. Nine objects depend on the table Staff.
     


Capture Screen Shot of Object Dependencies

Many graphics programs include a screen shot/screen capture feature and methods to crop and enhance a screen shot. Windows comes with a very basic graphics program that does not have those features. But you can work around these limitations!

  1. With the task pane still open, hold the ALT key down and press the Print Screen key. (ALT + Print Screen) Nothing seems to happen, but the Windows Clipboard (a section of memory) now holds an image of the active window.
     

  2. Paint as freshly openedOpen the program Paint. (Start | All Programs | Accessories | Paint) or (Start | Run | mspaint)
    Paint opens with a blank canvas. The size and shape of the canvas depends on what was done previously in Paint.
     

  3. Paint with pasted imagePaste. (Edit | Paste) OR ( CTRL + V).
    Your image is pasted onto the canvas. All of it is there, even if your window is not large enough to show it.
     
    You just need the task pane part of the image. Paint does not have a Crop tool, but you can work around this limitation.
     

  4. If necessary, resize the window and/or scroll so that you can see the task pane part of the image.
     

  5. Click on the Select tool Button: Paint Select tool and drag on the image to select just the task pane.

     Paint: select just the task pane part of the image

  6. From the menu select  Edit  | Copy . A copy of your selection is now in the Windows Clipboard.
     

  7. Message: Save changes to untitled?From the menu select  File | New .
    A message appears asking if you want to save changes to your untitled image.
     

  8. Click on No.
    A new blank canvas appears.
     

  9. Paint: Paste selection to new imagePaste.
    What you selected is now pasted.
     


Print Screen Shot

Now that you have an image of the object dependencies tree, you can print it. Unfortunately, Paint does not allow you to add a header or footer. You can, however, add text directly to the image. (For those of you in a class, your instructor needs to be able to tell one student's print-out from another!)

  1. Click on the Text tool Button: Text Tool at the left of the window and then in a blank area of the image.
    A small box appears and the Text toolbar, which is probably floating.
     
    Icon: TroubleProblem: No text toolbar
          Solution:
    After you click on the Text tool and then in a blank area of the image, open the View menu and click on Text toolbar. The bar will appear.
           If the Text toolbar choice is in gray and not clickable, click out of the image and repeat the steps.
     

  2. On the Text toolbar, select the font Arial Black and size 10.
     

  3. Paint: Text areaType your name and the date. If necessary, resize the text area by dragging one of its handles.
     

  4. Dialog: Page SetupFrom the menu select  File  |  Page Setup .
    The default is to center the image horizontally and vertically.
     

  5. Uncheck the Horizontal and Vertical centering boxes and click on OK.
     

  6. Print PreviewFrom the menu select   File  |  Print Preview .
    You can see how your image will look on paper, with your own name and the date as part of the image.
     

  7. Icon: Print Click the Print button. The Print dialog appears.
     

  8. Click Print. Your image is printed. Finally!
     

  9. Close the preview.
     

  10. Dialog: Save AsFrom the menu select  File  |  Save .
    The Save As dialog appears.
     

  11. Change the type to GIF.
     

  12. Name the file objdep-QStaff-FullName.gif
     

  13. Navigate to your Class disk and the folder databases project3.
     

  14. Click on the Save button.
     

  15. Close Paint.
     


Backup, Compact and Repair

It is a good idea to make a backup copy before you make changes and then to compact your database after your work session. Compacting rearranges the database file to save space on the hard disk.

Both backing up and compacting require the database to close and reopen. Be sure that all of your changes have been saved or discarded first.

  1. From the menu select  File | Database Properties   and click on the General tab. Note the file size.
     

  2. Close the properties dialog
     

  3. From the menu select  File | Back Up Database...  
    The Save Backup As dialog appears. It looks like any other Save dialog.
     

  4. Navigate, if necessary, to a location where you can save a copy of the database.
     

  5. Choose a name for this copy.
    Do not use the same name! Add the date or something to the name to avoid confusion.
     

  6. Click on OK.
    The database closes. Access makes and copy and opens the database again. You will see security warnings again if you saw them when opening the database before.
     

  7. From the menu select   Tools | Database Utilities | Compact and Repair Database . The database closes, is compacted and repaired with no messages to you, and then reopens. You will see the security dialog again if you saw it the first time you opened the database.
     

  8. Check the file size again in the Database Properties dialog. You should see a difference. Mine shrank from over 1800 KB to 236 KB!