Lessons Logo

Tables and Queries Aesthetics

 

Columns and Fields Visibility

 

Hidden Columns on Tables

When examining records on a database, some tables are so wide the user would need to scroll left and right to see fields one screen at a time. Sometimes, the user does not need to see all fields all the time. Microsoft Access allows you to temporarily hide unneeded columns. This feature does not delete records or columns; it only hides them and can display the columns as needed. To hide columns, you have two options:

  • You can narrow a column by dragging its right border completely to the right until the whole column disappears. Here is an example where the Last Name column has been hidden:
     
  • The other approach to hiding columns is to know which columns are hidden or not. To do that, on the menu bar, you would click Format -> Unhide Columns...



    The Unhide Columns dialog box allows you to visually see what columns are currently displaying and which ones are hidden. To hide a column, uncheck its check box. To display a hidden column, check its unchecked box.

You can also access the Unhide Column dialog by right-clicking the table's title bar. The Unhide Columns dialog box works live with the table. This means that columns are hidden and unhidden as you check or uncheck them on the dialog box.

After hiding some columns on the table, you can save it if you want the table to remember what columns were hidden previously. This is valuable for data analysis and does not have any impact on a form whose records emanate from this table. Microsoft .

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

 

  
 

Hidden Columns on Queries

To display or hide a column in a query, you can open the query in Design View and use the Show check box of the column:

If you leave the Show check box of a column, which is the default value, when you run the query, the column would display:

If you clear the check box of a column, the column would still be part of the query but it would not display when the query is run.

In the SQL, if you do not want to display a column, you can simply omit or delete it from the statement. For example, the following statement can be used to display the LastName column from the Persons table and the Gender column from the Genders table while both tables are joined by the GenderID primary key of the Persons table and the GenderID foreign key of the Persons table representing the Genders table:

SELECT Persons.LastName, Gender.Gender
FROM Gender LEFT JOIN Persons ON Gender.GenderId = Persons.GenderID;

 

Practical Learning: Hiding Column on a Query
  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.

Practical Learning: Freezing Columns
  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.

Practical Learning: Changing the Cells Font
  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.

Practical Learning: Changing Columns Width and Rows Height
  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 

Watts A Loan

  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
 
Previous Copyright © 2002-2004 FunctionX, Inc. Next