Introduction to Data Sheets

Creating a Database

The Database 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.

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

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.

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:
    
   
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)

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 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.

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

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.

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)

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.

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
    
   
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

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.

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

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.

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

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