Home

Introduction to Data Sheets

 

Creating a Database

 

The Database Wizard

Data Wizard

Many techniques allow you to create a database, the fastest of which consists of using one of the provided examples. The databases that ship with Microsoft Access can help you in two main ways: they provide a fast means of creating a database and you can learn from their structure.

 

To create a database using one of the samples, after starting Microsoft Access, on the right side, you can click Create a New File. Under Templates, you can click On My Computer. The New dialog box displays two property pages labeled General and Databases. If you want to create a database based on one of the samples, you can click the Databases property page. A list of the sample databases appears. You can then choose one and click OK.

When creating a database using one of the samples, depending on the sample you selected, the Database Wizard will display a few objects and suggest some fields for your database. Some fields are already associated with the objects and some other fields can be added. You can examine them, then add some fields you think are important for your database. You will also have the option of selecting a design layout. Some of the sample databases have been configured to require information about the company you are creating the database for.

Practical Learning: Creating a Database Using a Template

  1. To create a new database, on the right side, click Create a New File...
  2. On the right side and under Templates, click On My Computer...
  3. In the Templates dialog box, click the Databases tab.
    Click Asset Tracking
     
  4. Click OK
  5. In the My Documents folder, set the name to Altair Real Estate and click Create
  6. When the Database Wizard dialog box comes up, click Next
  7. In the Tables in the Database list, click Asset Information if necessary.
    In the Fields in the Table list, click the check boxes of Make and Model
     
  8. Click Next
  9. For the style of the screen display, choose Expedition to preview one of the samples and click Next
  10. For the style of the printed report, click Bold to preview that display
  11. Click Formal and click Next
  12. For the title of the database, type Altair Real Estate
     
  13. Click Next
  14. Then click Finish
    While the Database Wizard is creating a database for you, dialog boxes will be flashing some time to time. If you do not have a printer, a nasty dialog box will be giving an error. If this happens, click OK all the time. In this case, after this lesson, use the Add Printer wizard from the Printers window to install a "fake" printer. One way or another, Microsoft Access needs a printer for the reports
  15. Notice that the database opens with the switchboard.
    To close the switchboard, click its system Close button

Database Properties

Your screen may be made of a large box at this time: this is the Switchboard, and it is a form. A special window you will be using most regularly is called the Database Window. Whenever it is minimized or hidden, you can display it by pressing F11 (unless it has been configured to (almost) never display).

Whether you work alone or in a group, communication and documentation are important. Your database project has its own properties that you can use to find out some details about your file, to enter some notes about the project, or to give directives to other people who have access to the database. 

 

Practical Learning: Using Microsoft Access

  1. The Altair Real Estate database should still be opened
    To display the Database Window, press F11
  2. On the main menu, click Tools -> Startup… 
  3. The Startup dialog box allows you to set some settings that the current database will follow when it starts.
    In the Application Title, type Altair Real Estate, Inc.
  4. Click the arrow of the Display Form/Page combo box and select (None). In case you wonder why (None) is between parenthesis, this is done so that if there are other things in the list, None would always be on top of the list. That way, it would not be between two other objects (forms and Data Access Pages) on the list.
  5. Click the browse button of the Application Icon text box
  6. On the Icon Browser dialog box, locate the folder that holds the resources for these lessons
  7. Click the altair icon and click OK
  8. Accept all the other defaults:
     
    Startup
  9. Click OK
  10. Notice that, on the title bar, the database is now using another icon than the default one
     


    On the main menu, click Tools -> Options…
  11. The Options dialog box allows you to control some settings that are related to Microsoft Access.
    For example, click the General tab
  12. Increase the Recently Used File List combo box to 8
  13. Click OK
  14. As mentioned already, each toolbar on the database has a particular name. To find out the name of the current toolbar, right-click any button on it
  15. Notice that the Database name has a check box. This means that toolbar is displaying at this time. In this case, this toolbar is called the Database Toolbar
  16. On the menu bar, click File -> Database Properties
  17. Once more, from the menu bar, click File -> Database Properties
  18. From the Properties dialog, click the General tab. Examine the various sections. Notice the size of the current database
  19. Click the Summary tab
  20. Make sure the Title is set to Altair Real Estate
    Click the Subject text box and type Company Asset Information
  21. Click the Author text box and type Patricia Katts
  22. Click the Manager text box and type Jerry Elliot
  23. Click the Company text box and type Altair Real Estate, Inc.
  24. Click the Category text box and type Business
  25. Click the Keyword text box and type asset, company, inventory, business
  26. Click the Comments text box and type: The information stored here allows the company to track the tools available to conduct regular business activities. For more information, contact Jerry Elliot or Patricia Katts. Refer to the Address Book for their phone number, extension, and E-Mail Address.
  27. Click the Hyperlink Base text box and type http://www.altairrealestate.com
     
  28. Click the Statistics, Contents, and Custom property pages and review their contents
    The Statistics tab gives you statistics about your database and its summary access
    The Contents tab shows a list of the components that are part of your database
    The Custom tab shows, and allows you to customize, the field names associated with your database
  29. When you have finished working with the database Properties, click OK (if you click Cancel, any change you have made will be discarded)

Tables Fundamentals

 

The Table Wizard

Microsoft Access is filled with wizards, which are step-by-step dialog boxes that allow you to create objects or fields on a database. Like the Database Wizard, Microsoft Access provides the Table Wizard used to easily create a table. It allows you to add fields that are necessary for a particular table. The fields have been configured in the general sense so you can use them in your database. Of course you can modify any field that was created using the wizard. To use the Table Wizard, on the main menu, you can click Insert -> Table. Alternatively, on the Database Window, when in the Tables section, you can click the New button. These two actions would display the New Table dialog box from where you would select Table Wizard.

From the Tables section of the Database Window, you can double-click Create Table By Using Wizard:

In the Table Wizard, the tables are organized in two primary categories: Business and Personal. To select one of those categories, you can click its radio button. Each main category is made of various sample tables. To select a sample table, you can click its name in the Sample Tables list. In the middle, the Sample Fields list, the fields associated with the selected sample table are displaying. From that list, you can select the desired field(s). Once a field has been selected, it displays in the Fields In My New Table list. You can then select a different table to mix fields from as many tables as desired. The 4 buttons between the Sample Fields list and the Fields In My New Table list allow you to add or subtract fields. To help with selection and de-selection, the wizard provides four buttons:

Button Role
Used to select one field
Used to select all fields from the sample table
Used to deselect one field
Used to deselect all fields
 

During field selection, if you select a field, its corresponding name appears in the right list. If you select a field of the same name more than once, for example, if you select Address twice, the 2nd Address would be called Address1. Sometimes that will be what you want, and sometimes it will be by mistake. If then you make a mistake when selecting fields, you can double-click the unwanted field in the Fields In My New Table list and that field will be removed.

After making your choices, you can keep the names provided by Microsoft Access into your table, or you can rename any field to suit your needs. To rename a field, first select it in the Fields In My New Table list. Then, click the Rename Field button. In the Rename Field dialog box, type the desired name and click OK:
 

If you try providing a name for a field that already exists, you would receive an error:

  

Data Entry on Tables

Data is entered in a table by typing it into cells. Some fields can be configured to accept some types of data and reject others. Some other fields would allow anything. This depends on how the database fields were setup.

A table's cell holds one particular unit of data. All cells on the same (vertical) column belong to the same category of information. The horizontal range of cells is called a record; and all cells on this range belong to the same record.

After typing data into a cell, you can press Enter or Tab to move the cursor to the next cell. In many circumstances you will also be able to move to the next cell by pressing the right arrow key. Sometimes you can move to the next record even if the current record is not completed. In this case, you could press the down arrow key.

There are three kinds of fields or cells the user will face: a field in which the user can type data, a field that displays a list as a combo box the user has to select from, and a field that does not receive input from the user. The latter is usually set for an AutoNumber data type: the database itself keeps track of the numbers. If you as the user have to select from a list, click the field that holds the list and select from the combo box. There are two kinds of combo boxes, those that accept new entries from the user and those that allow only a selection from a preset list. The classic field allows the user to just type the appropriate data in a field.

After setting the data in a particular field, you can click another cell and type the desired data. The easiest way is to press the right arrow key to move to the next field or the left arrow to move to the previous field, pressing Enter would work as well.

Data on the same row represents a record. Data on the same column represents a category. After typing data, if you press Enter at the end of a record, the cursor would move to the beginning of the next record. At anytime, you can press the up arrow key to move to the same category field of the previous record. If you press the down arrow key, the same category of the next record would receive focus.

Practical Learning: Table's Data Entry

  1. The Altair Real Estate database should still be opened
    On the Database window, click the Tables button
  2. In the Tables section, double-click the Asset Categories table to display it
  3. Click the first empty field under Asset Category and type Computer
  4. Click the first empty field under Asset Category and type Peripheral
  5. Press the down arrow key and type Software
  6. Press the down arrow key and type Furniture
  7. Press the down arrow key and type Vehicle
  8. Press the down arrow key and type Appliance
  9. Close the Asset Categories table
  10. In the Tables section, double-click Status
  11. Under Status, type Excellent Condition
  12. Press the down arrow key and type Useful Condition
  13. Press the down arrow key and type Needs Repair
  14. Press the down arrow key and type Bad Shape
  15. Close the Status table
  16. In the Tables section, double-click Employees
  17. Click Under First Name and type Jerry and press Tab
  18. Type Elliot
  19. Complete the table with the following information:
     
    First Name Last Name Title Office Location
    Jerry Elliot Department Manager Rockville
    Robert Niemmes Building Inspector Ctrl + '
    Patricia Katts Customer Services Manager Ctrl + '
    Jeannette Hyan Real Estate Agent Washington, DC
    Ernestine Simms Acquisition and Financing Manager Rockville
    Paul Rayonson Real Estate Agent Silver Spring
  20. Close the Employees table
  21. In the Tables section, double-click Assets
  22. Under Employee ID, select Elliot, Jerry
  23. Under Make and press Tab
  24. Complete the table as follows:
     
    Asset Description Employee ID Asset Category Make  Model  Date Acquired Purchase Price Depreciation Method
    Desktop Computer Elliot, Jerry Computer Gateway FX530 10/08/2006 1450.95 SL - Straight-Line Method
    Laptop Rayonson, Paul Computer  HP  nc8430 4/6/2007 1725.75 Ctrl + '
    Office Chair Elliot, Jerry Furniture  Saylon Furniture Ergo OX220 04/15/2004 175.95 DDB - Double Declining Balance
    Laser Printer Simms, Ernestine Peripheral HP LaserJet 4350dtn 04/15/2004 2250.85 SL - Straight-Line Method
    Company Car Katts, Patricia Vehicle Ford Taurus 8/10/2005 24950 Ctrl + '
    Office Refrigerator Elliot, Jerry Appliance Maytag MSD2660KES 10/22/2004 1850.45 DDB - Double Declining Balance
  25. Close the Assets table

Table Printing

The primary function of the table is to provide a means of storing and arranging information of a database. Nevertheless, you can print data of a table, especially if either you do not have appropriate reports available or you are in a hurry. You can print data on a table whether the table is opened or not.

To print a closed table, locate it in the Database Window. Then right-click the desired table and click Print. 

Once a table is opened in Datasheet View, on the Table Datasheet toolbar, you can click the Print button. This will send all the records of the table to the printer for printing.

If you want to control the printing process, instead of using the Print button, on the main menu, you can click File -> Print... To print data of all fields on the table, you can click OK. If you want to print only certain records, unfortunately, Microsoft Access does not allow you to select rows at random: you can only select a range of records. To print a range of records, while a record at one end has focus, you can press and hold Shift. Then click the gray box at the end of the range.

Practical Learning: Printing Tables

  1. To reopen a table, on the Database Window, while in the Tables section, double-click Employees
  2. To print data from the table, on the Table Datasheet toolbar, click the Print button
  3. To close the current table, click its system Close button
  4. To print the list of company’s assets, on the Database Window, click the Assets table to select it (you don't have to open it)
  5. On the main menu, click File -> Print…
  6. Make sure a printer is selected in the Name combo box
    Click the Properties button
  7. Based on your printer, select the Landscape radio button (usually in the Orientation section of a Paper tab) and click OK
  8. On the Print dialog box, click OK
    (If you had opened a table, you can close it now)

Queries Fundamentals

 

The Query Wizard

A query is a request you present to the database, and the database displays its response to you. The whole purpose of creating a query lies on how you formulate that request. There are three main ways you create a query in Microsoft Access:

  • The Query Wizard offers the simplest approach where in a step by step process you define the purpose of the query and the database will display its answer
  • The best way to build a query is by using the Design View
  • Once you get familiar with queries, you will find out that a query is just a group of words called a statement you formulate. This means you can write that statement manually. This approach is the most complex because you need to know the structure of a query statement, but it provides more control than the other two techniques.

The simplest way to create a query is by using the Query Wizard. The wizard presents the tables that are part of the database and you select which fields you need. Such a query is called a Select Query.

Data used on a query can originate from a table, another query, or a combination of tables and/or queries. The first page of the Simple Query Wizard expects you to choose the origin of the query, starting with the table or the query name. For example, you can use a table of employees then, you choose the necessary data that you want to filter for your query. You can use this query to retrieve the employees' data by their employee number, their last name, the date they were hired, their salary, their marital status, their E-Mail address, or the city or state they live in.

To use the Query Wizard, on the Database Window, you can click the Queries button and then double-click Create Query By Using Wizard. Alternatively, you can first display the New Query dialog from where you would choose Simple Query Wizard. To display the New Query dialog box:

  • On the toolbar of the Database window, while in the Queries section, you can click the New button
  • On the main menu, you can click Insert -> Query
  • On the Database Toolbar, you can click the arrow of the New Object button and click Query

When building a query in the first page of the Simple Query Wizard, you must first specify the object (a table or another query) that will provide data. Then select items using the same types of buttons we reviewed for the Table Wizard.

Practical Learning: Creating a Query Using the Wizard

  1. The Altair Real Estate database should still be opened
    On the Database Window, click the Queries button
  2. To create a query, from the Database window, double-click the Create Query By Using Wizard button
  3. From the Tables/Queries combo box, select Table: Assets
  4. From the Available Fields list box, double-click AssetDescription, EmployeeID, AssetCategoryID, Make, Model, DateAcquired, PurchasePrice, and Depreciation Method
     
    Simple Query Wizard
  5. Click Next twice
  6. Give the title AssetsInventrory and make sure the Open The Query To View Information radio button is selected
     
  7. Click Finish

Data Entry on a Query

A query uses the same approach to present its data, like a table: it is made of columns and rows whose intersections are cells. Although the main purpose of a query is to prepare data either for analysis or isolate some fields to make them available to other database objects, as done on a table, data can be entered in a query.

Data entry on a query is the same as done on a table: data is entered into cells. The Enter, Tab and arrow keys are used with the same functionality. Like the table, the query provides navigation buttons on its lower section, allowing you to move to the first, the previous, the next, the last or any record in the range of those available.

Practical Learning: Performing Data Entry on a Query

  1. While the AssetsInventory query is still opened, click the first empty field under the AssetType column
  2. Type Computer and press Enter
  3. Add the following two records to the query:
     
    Asset Description Employee ID Asset Category Make  Model  Date Acquired Purchase Price Depreciation Method
    Laptop Katts, Patricia Computer  Lenovo ThinPad Z61p 9452 10/08/2006 1850.95 SL - Straight-Line Method
    Digital Camera Niemmes, Robert Peripherals Canon EOS Digital Rebel XTi 11/06/2006 820.75 SL - Straight-Line Method
  4. After using the query, close it
  5. To create a query of selected information of employees, on the main menu, click Insert -> Query
     
  6. In the New Query dialog box, click Simple Query Wizard and click OK
  7. In the first page of the wizard, in the Tables/Queries combo box, select Tables: Employees
  8. In the list of Available Fields, double-click FirstName, LastName, Title, and OfficeLocation
  9. Click Next twice
  10. Change the Title of the Query to Employees By Location and click Finish
     
  11. After viewing the result of the query, close it

Query Printing

Like tables, queries provide you with a fast means of printing data. Once again, this should be done when you need a printed but not a professional-oriented product. Data printing on a query is done with the exact same approaches and techniques as for a table.

Practical Learning: Printing a Query

  1. On the Database Window, while still in the Queries section, double-click the AssetsInventory query
  2. To print the query, on the main menu, click File -> Print…
  3. Make sure a printer is selected in the Name combo box and click the Properties button
  4. Select the Portrait orientation and click OK
  5. Click OK on the Print dialog box
  6. To print only a few records, click the gray box on the left side of the first Laptop
     
  7. Press and hold Shift. Then click the gray box on the left side of Car and release Shift
     
  8. On the main menu, click File -> Print… and, on the Print dialog box, click Selected Record(s)
  9. Click OK to print
  10. After using the query, close it

Lesson Summary

 

MOUS Topics

S6  Print database objects (tables and queries)
S7 Navigate through records in a table, query, ...
S8 Create a database (by using a wizard ...)
S9 Create tables by using the Table Wizard
S23 Enter records using a datasheet
 

Exercises

 

Tenley Associates

  1. Start a new database using the Database Wizard and based on the Expenses sample database
  2. Name it Tenley Associates
  3. For the Information About Employees, add Department Name, Email Name, Home Phone, and Date Hired
  4. For the Expense Report Information, include the Business Purpose
  5. Set the Screen Style to Stone
  6. Set the Printed Reports Style to Corporate
  7. Set the Database Title to Tenley Associates
  8. Set the Startup form to None
  9. From the Tables section of the Database window, open the Expense Categories table and enter the following expenses:
     
    Expenses Category ID Expenses Category Expense Account#
    1 Office Equipment 102
    2 Car Rental 104
    3 Transportation 105
    4 Customer Entertainment 108
    5 Other 112
  10. Print all records on the table and close it
  11. Open the Employees table and fill it up with the following values, ignoring those not mentioned:
     
    First Name Last Name Title Home Phone
    James Bidds Accountant (301) 725-4570
    Alexandra Roberts Sales Manager (410) 727-0982
    Walter Hoaks Sales Representative (410) 521-2850
    Bertine Baugh Manager (301) 912-2129
  12. Close the table
  13. Using the Simple Query Wizard, create a query based on the Employees table and include the following fields: FirstName, LastName, Title, and HomePhone. Save the query as Employees Contact Information
  14. Close the query and print all of its records without opening it
  15. Using the Employees Contact Information query, print only the second and third records of employees
  16. Use the Table Wizard to create a table that includes the following fields: DepartmentID (from the Assets sample table), DepartmentName (from the Employees sample table), and Notes (from the Recipes sample table of the Personal category). Save the table as Departments and fill it up with the following values
     
    Department ID Department Name Notes
    1 Administration Including all personnel from Management
    2 Sales Including all sales representatives and managers
    3 Accounting  
 

Previous Copyright © 2002-2007 Yevol Next