Tables and Queries Aesthetics

Practical Learning: Hiding and Un-hiding Columns

1. Open the ROSH database
2. From the Tables section of the Database window, double-click Students1 to open it
3. Position your mouse on the right border of the Date of Birth column header until the mouse cursor turns into a vertical bar crossed by a double arrow:
    
4. Click and drag in the left direction until you cannot go any further to the left. Then release the mouse
   Notice that the Date of Birth column is not displaying anymore
5. To view a list of columns, on the main menu, click Format -> Unhide Columns... 
6. Notice that the check box of the Date of Birth is not marked
7. Move the Unhide Columns dialog box down and make sure you can see the Student # and the Gdr column headers (of course your screen resolution will control the number of columns you can see at a time)
8. To display the Date of Birth column, click the check box of Date of Birth
9. Notice that the Date of Birth is now displaying on the table
10. Click Close on the dialog box
11. To hide columns, right-click the title bar of the table and click Unhide Columns... 
12. On the Unhide Columns dialog box, uncheck Student #, MI, Address, City, State, ZIP Code, Home Phone, Email Address, Parents Names, Single Parent?, and Notes:
     
13. On the Unhide Columns dialog box, click Close
14. To display all columns again, on the main menu, click Format -> Unhide Column... 
15. Check all of the check boxes and click Close
16. Close the table. When asked whether you want to save the table, click No

1. On the main menu, click Insert -> Query
2. In the New Query dialog box, double-click Simple Query Wizard
3. In the Tables/Queries combo box, select Table: Students
4. In the Available Fields, double-click FirstName, LastName, DOB, SingleParentHome, EmergencyPhone, and Gender
    
5. Click Next
6. Accept the default on the second page of the wizard and click Next
7. Change the Title of the query to List Of Female Students and click Finish
    
8. Notice that the list contains both male and female students. Also, on the bottom bar of the Datasheet View, notice the total number of students
9. Switch the query to Design View
10. To isolate only the list of girls, click the Criteria box of the Gender column and type =”Female”
     
11. Run the query
12. Notice that the query now shows only records whose Gender value is Female. Also notice the total number of students
13. Switch the query to Design View
14. Since we know that the list will include only girls, we do not need to display the Gender column.
    Click the Show check box of the Gender column to remove the check mark
15. Run the query
16. Save and close the query-

Hidden Fields on Forms and Reports

To hide a field from a form or a report, as done for a query, you can simply avoid adding it to your form or report when designing the form or report. There will be many other cases where you must add the field to the form or report but would not need to show it to the user. In such a case, after adding the field to the form or report, access its Properties window and change the value of its Visible Boolean property:

If you set the value to Yes, the field would display at run time. If you want to hide the field, set its Visible property to No.

Columns Freezing on Tables and Queries

During table or query data entry or analysis, you have to scroll left and right to navigate from various fields if the table is very large or too wide for your screen resolution. Sometimes, this would be annoying if you do not need access to all fields of all columns. Microsoft access allows you to work in series of records so that you can fix some columns on the left side of the screen and scroll only to its right columns.

To freeze a column, you should first click it to select it. Then on the main menu, click Format -> Freeze columns. The column would be moved to the left side of the table and would become the first column. The columns that were on its left would be moved to the right. The frozen column would become static with a thick right border. When scrolling to the right, only the other columns would move.

To freeze a group of columns, first select them using the Shift key + click combination. Then freeze them in the same way. The selected columns would be moved to the left of the table and become static. If you scroll to the right, only the other columns would move.

The technique applied to the tables in the following Practical Learning section can also be applied to a wide query.

1. Open the Students1 table and click any field in the Last Name column
2. On the menu bar, click Format -> Freeze Columns 
3. Notice that the Last Name column has moved to the left
4. Scroll to the right to view the Notes column
5. To release the Last Name column, on the main menu, click Format -> Unfreeze All Columns 
6. Close the table. When asked to save, click No
7. Reopen the Students1 table 
8. Make sure the cursor is in a field of the Student ID column
9. Press and hold Shift
10. Click the Last Name column header to select all columns in the range
11. After selecting the range of columns, right-click a field in the selection and click Freeze Columns
12. Click anywhere on the table and scroll to the right to see the effect 
13. To dismiss the freezing of columns, on the main menu, click Format -> Unfreeze All Columns
14. Close the table. When asked if you want to save, click No. 

Table and Query Datasheet Formatting

Cells Font

Although you will usually not give access of your database tables to the users, there are techniques you can use to improve the table's appearance just in case people would use it. This is also helpful if you create a form that looks like a table (Datasheet Form).

A table appears as a series of cells. Their font is a regular black Arial in size 10. Microsoft Access allows you to modify the font of the text that displays in cells. To do this, you can use the Font dialog box available either from the Format item of the main menu or by selecting Font… after right-clicking the title bar of a table.

1. Open the Rockville Techno database and, from the Tables section, double-click the Employees table to open it
2. On the main menu, click Format -> Font...
3. Change the Font Name to Verdana. Keep the Font Style to Regular. Leave the Size to 10 Click the arrow of the Color combo box and select Blue:
    
4. Click OK
    

Formatting the Table Grids

A table in Microsoft Access appears as a classic spreadsheet with a white background and gray grid lines. If this display is not appealing, you have two options. You can apply a gray background with white or dark gray grid lines. You can also change the background to one of the preset colors and change the grid lines to another color.

To change the grids appearance, on the menu bar, you can click Format -> Datasheet... You can also right-click the title bar of the table and click Datasheet...t.

1. On the Database Window, double-click the Employees table to open it in Datasheet View
2. On the main menu, click Format -> Datasheet...
3. In the Cell Effects section, click the Raised radio button and click OK
    
4. Save and close the Employees table
5. Open the Payment Methods table in Datasheet View
6. Right-click its title bar and click Datasheet…
7. In the Cell Effect section, click the Flat radio button if necessary
   In the Gridlines Shown group, make sure both the Horizontal and Vertical check boxes are checked
8. Click the arrow of the Background Color combo box and select Blue
9. Click the arrow of the Gridline Color to Aqua
    
10. Click OK
11. Right-click the title bar of the table and click Font...
12. Set the Font name to Verdana. Keep the Font Style to Bold and keep the Font Size to 10. Set the Color to Lime and click OK
13. Double-click the lines between the column headers
     
14. Save and close the table

Exercises 

1. Open the Watts A Loan database. Using the Simple Query Wizard, create a query based on the Customers table and that includes the following fields: AccountNumber, FirstName, LastName, and HomePhome. Save the Query as Customers Contact Information and close it
2. Open the Customers Contact Information query and format it as follows:
   Font: Verdana
   Font Color: Lime
   Gridline Color: Aqua
   Background Color: Navy
   Cell Effect: Raised
    
   
   
   Save and close the query