|
1. Open Microsoft Access. When you create a database, it will be easy if you follow these basic steps:
A. Open a new database.
B. Create tables.
C. Create relationships between the tables.
D. Create forms for easier data entry and display.
E. Enter information.
F. Create queries to access the information.
G. Create reports for printouts.
Opening a New Database
2. Insert a RW/CD or USB key into the PC: In the Microsoft Access window, click on Blank Database. Then click OK.
3. In the File New Database window, use the down-arrow to highlight the A: drive. Click in the white space after File name: and type staff.mdb. Click on the Create button.
4. The staff: Database window appears. This is also called the Database Window.
5. There are six tabs in the Database Window: Tables, Queries, Forms, Reports, Macros and Modules.
Creating A Table
6. In the first table, you will list all of the employees in your department, their job titles, addresses and phone numbers. In the second table, list salaries and hire dates.
7. Click on the Tables tab. Click on the New button.
8. The New Table window appears. You can use a wizard to help create the table. Click on Design View and click OK.
9. In this view, you can design your first table. There are three columns: Field Name, Data Type and Description. Your cursor is flashing in the Field Name column. Since both tables will be about employees in an office, assign a unique ID number to each employee.
10. Under Field Name on the first line, type ID. Tab to the Data Type column and hit the down arrow. One of the options is AutoNumber. Click on that to require Access to automatically assign a unique ID number to each employee.
11. Click in the Description column and type Unique number for each employee. Tab to the second line.
12. Under Field Name, type Name. Hit the tab key. Under Data Type, use the default Text. Tab to the Description column and type Name of the Employee.
13. Add the fields Job Title, Address, City, State, Zip Code and Telephone. Use Text for the Data Type for all fields. Typing a description is optional.
Inserting a Primary Key
14. The database is going to contain information about people. Because some people have the same first or last names, make sure to uniquely identify each person.
15. To do that, make the ID the primary key. A primary key uniquely identifies each record in a table.
16. Click in the gray box at the beginning of the IDline so that the entire row is selected. Click on the primary key icon on the toolbar. A small key will appear at the start of the line indicating that ID is your primary key.
Customizing fields
17. You are in Design View. At the bottom of the Design View window is a section called Field Properties. This area allows you to specify information about those fields. It allows entry of information that is uniform.
18. Select the State row. Then look at the Field Properties area in the lower part of the window.
19. Click in the white line after Field Size. Click on each line for the description of that parameter.
20. For State, the Field size is now set at 50. Change it to 2. When you begin to enter information in your tables, you will only be allowed to enter two letters for a State e.g. NJ for New Jersey or MA for Massachusetts. Change Required to Yes so the user will have to enter a state.
21. Select the Telephone row. In the Field Properties area, click on Input Mask. A small gray box with three dots in it will appear at the end of the line. Click on it.
22. Save your table whenever you are prompted to do so.
23. In the Input Mask window, click on the Phone Number line, then click on Next> Click Next> again and then click on the first option. Click on Finish.
24. New information for that field will have to be entered in that format a uniform format. Do the same for the Zip Code field.
25. Click on the X in the upper-right corner to close the box. Click Yes to save the design as Table 1. Answer Yes to any following pop-up boxes.
Creating a Second Table
26. In the Database Window, click on the Tables tab. Then click on the New button to create a second table. Follow the same procedure as you did for the first table. (Click on Design View. Click OK. The fields in this table will be ID, Name, Salary and Date Hired.
27. Select AutoNumber as the Data Type for ID. Select the Lookup Wizard as the Data Type for Name. In the first Lookup Wizard window, Iwantthelookupcolumntolookupthe values inatableorquery is selected. Click Next>.
28. In the next window, Table1 is highlighted. Click Next>.
29. In the next window, under Available Fields:, highlight Name and click on the > sign in the middle of the window. Click Next>. Click Next> in the following window and then click Finish. Save the table as Table2.
30. Create a primary key on the ID field. You'll notice Name has been replaced by ID on the second line. It is highlighted so simply type Name again.
31. Click in the third line and type Salary for the Field Name. Choose Currency as the Data Type. Click in the fourth line and type Date Hired and select Text as the Data Type. Then use the Field Properties section to format the date. Click on Input Mask. Click on the button with the three dots at the end of the line. Save the table as Table2. Scroll down to Short date and select it. Click Finish. Close the box by clicking on the X in the upper-right corner and save it.
Linking Tables
32. Since your database consists of more than one table, you need to tell Access how the tables are related. You must link them.
33. Linked fields usually contain the same concept in two different tables. And a linked field is usually the primarykey in at least one of the tables.
34. In the Database Window, click on the Tables tab. Then click on the Relationships icon on the toolbar.
35. In the Relationships window, add each of the tables, then close the window.
36. Point to ID in Table 1 Hold down your left mouse button and drag it over ID in Table 2. In the Relationships window, click on Create. This will link the two tables. Close the box and save.
Entering Information
37. In the Database Window, double-click on Table1. Point to the first icon on the toolbar the View icon. Click on the down arrow following the View icon. Be sure Datasheet View is selected.
38. Tab to the Name column. Enter information about you. Tab to each of the rest of the columns in that row, entering information about you. When you get to the State column, type New for New Jersey. Notice the Zip Code and Telephone have also been pre-formatted by you in Design View. When you have entered all of the information about you, you will have a record.
39. Tab to the Name column in the second row. Enter information about another employee in your office. Make up what you don't know during this practice session. Enter information on three more employees. Then adjust your column width. Exit and save the file.
40. Open Table2 by double-clicking on it and enter information for all of the employees you have in Table1. Use the down arrow in the Name column to insert names. This is the Lookup Wizard working for you. Close and save the table.
41. Tables are the best way to store data in Access. You can create a form to allow the user of the database to enter information in a more attractive format. Forms display only those fields you want to see. Even if you want all of your data on a form, it is visually more appealing than a table.
42. If you want the user to enter information on a form, you will want the user to enter enough information to fill all of the needs of both tables.
Creating a Query
43. Queries allow you to pull specific data from one or more tables into a datasheet. Queries don't allow you to store data. Data is stored in Tables. Queries just pull data out of tables for you to look at. When you save a query, you save the design, not the information in it. Each time you run the query, it goes back to the tables for the information it needs.
44. You can have many different Queries in a database. No matter what type of query you wish to create, you must use the Query Design view to tell Access about the data you are looking for and where Access should look for it.
45. In the Database Window, select the Queries tab. Click on the New button. The NewQuery dialog box appears. The Design View is selected. Click OK. In the Show Table box, Table1 is highlighted. Click on the Add button. Highlight Table2. Click on the Add button. Click on Close. Note there is a line going from ID in Table1 to ID in Table2. This is the relationship line.
46. Double-click the * field in Table1 in the upper half of the box to include all fields in the query. In Table2, double-click on all fields except the ID and Name fields. The fields will appear in the bottom box. If you need to start over at this point, you would click on Edit/Clear Grid and start over.
47. Click the Run button (!) on the toolbar. What happens? Use the Scroll Bar at the bottom of the screen to see all of the query fields. You have information from both tables in your query.
48. Click on the X to close it. Save this query as Query1.
49. Click on the Query tab. Then click on the New button. Choose Design View and add tables.Double-click on the following fields in Table1: ID, Name, Job Title. In Table2, double-click on Salary. Run the query by clicking on the Run (!) button on the toolbar.
50. If you wanted to list employees by Salary: lowest to highest. Click on the Salary column to select it. Then click on the Sort Ascending icon on the toolbar.
51. You can also do this in Design View. Click on the View icon on the toolbar to go to the Design View. Put the Salary column in reverse order. Click on the white space following Sort. Click on the down arrow to choose Descending. Click the Run button (!) on the toolbar.
52. Return to the Design View by clicking to the View button arrow on the toolbar (1st on the left).
53. There are check boxes on the Show line. Click on one to deselect it. Click the Run button (!) on the toolbar. That column should be missing Return to the Design View. Select it again. Close the Query screen. Save it as Query2.
Creating Forms
54. Select the Forms tab. Click on the New button.
55. In the New Form box, note your options. (The Auto Form choices are generally used with only one table.) Click on Form Wizard. Click OK.
56. In the Form Wizard box, under Tables/Queries: click on the down arrow and select Query:Query1. Click on the button with two arrows to select all the fields. But note that some of the fields are from Table1 and some are from Table2. Click on Next>.
57. For the layout, choose Columnar. Click Next.
58. The following window allows you to choose your style. You can click an option to see what it looks like. Pick one. Click Next.
59. In the next window, you are asked to type a title. Type Form1 and click Finish. Note the word Record at the bottom-left of the new form. You are now in Record 1. By clicking the arrows you can move through the records in this Forms view. Do that.
60. When you get to a blank record, tab to the Name field and add another employee. You are adding records in form view now.
61. The information on a form comes from one of your tables or from the data you enter in the form. Any changes you make in your form will show up in the corresponding table.
62. If you want to change the format of the form, go to Design View. Select the part of the form to change. Move the Date field to the right of the State field.
63. Make the outer and inner boxes wider and longer. Then drag the Date Hired field up next to the State field. Click on the View icon to return to the Datasheet View. Make it wider and enter two more records. Close and save Form1.
64. Click on each of the tabs in the Database Window. You should have two tables, two queries and one form.
Using Reports
65. Reports allow you to produce easy-to-read summaries of your data.
66. Reports are generally used for printing. You can display pictures and graphs, use calculations, change formats, etc. In the Database window, click on the Reports tab. Click on the New button.
67. In the New Report box, click on Report Wizard. In the white space after Choose the table or query where the object's data comes from:, click on the down arrow and choose Query1. Click OK.
68. In the next box, choose the fields to include in your report. Let's choose ID, Name, Job Title, Salary, and Date Hired. To choose them, highlight the field and click on the > sign so the field moves over into the column on the right. Click Next>. Click Next> again and click Finish.
69. Your report should list all the fields you selected.
70. If you want to add up all the salaries to see how much you are paying in salaries for your department, Go to the Design View by clicking on the View icon on the toolbar.
71. There are gray bars across the page in Design View: Page Header, Detail, Page Footer and Report Footer. You need to insert a box in the white space under Report Footer so you may need to drag down to add some white space to that area.
72. The Toolbox should be visible on the left. If it isn't, click on the Toolbox icon on the toolbar.
73. Point to each of the options in the Toolbox. Click on the Text box icon and draw a box in the Report Footer section. The word Unbound should appear in the box on the right.
74. Click on View/Properties on the Menu Bar. In the Text window, click on the Data tab. At the end of the Control source line is a button with three dots. Click on that. The Expression Builder window will open. It contains three white columns in he lower half of the window.
75. In the first column, double-click on Functions, then double-click on Built-In Functions. Go over to the right column, scroll down to Sum and double-click on it. An expression will appear at the top of the Expression Builder box. It will read Sum (<<expr>>)
76. Click on the letters expr and they will be selected. Type Salary. Then click OK to close the Expression Builder box and click on the X to close the Text box.
77. Go to the Datasheet View by clicking on the View icon. You will see the salary total.
78. At this point, it is not formatted for currency. Go back to Design View and right-click on the box with the =Sum([Salary]) formula in it. Click on Properties. Click on the Format tab in the Text box. Click on the down arrow at the end of the Format line and select Currency. Close the box by clicking on the X in the upper-right corner.
79. To see if your total is formatted in Currency, click on the View button to return to the Datasheet View.
80. The box preceding the total has the word Text followed by a number in it. Let's change that to Total: In Design View, click on the box and type Total: Return to the Datasheet View to see how the report looks.
Creating a Report
114. Reports are generally used for printing. You can display pictures and graphs, use calculations, change formats, etc. In the Database window, click on the Reports tab. Click on New.
115. Click Report Wizard
Click OK.
In the Report Wizard dialog box, select Item, Room, Number and Cost from Table1. Click on the down arrow under Tables/Queries: and select Table2. Add Store and Telephone from Table2.
Click Next. Choose an option in each of the next few boxes.
The Table Analyzer Wizard
117. Access has a built-in capability of analyzing how you have set up the information in your tables to let you know if they can be clearer and easier to use. It is called the Table Analyzer Wizard.
118. Go back to the Database Window. Click on the down arrow after the Analyze button on the Menu bar. Select Analyze Table. A series of pop-up boxes will allow you to analyze your tables.
119. The first box provides an explanation of what the analyzer does. You can click the arrows to see how duplicated information can be a problem. Click Next>.
120. You can click on arrows again to see why multiple tables work better than one table with repeated information.
Click Next> and select which Access table you want to analyze. Select Table1. Continue through the windows until you are finished with the Analyzer.
|