Using MS Access

CREATING A DATABASE

When you create a Microsoft Access database, you create one file that contains the data and table structures as well as the queries, forms, reports, and other objects that make up the database.

To Create A Database :

From the File menu, choose New Database. Microsoft Access displays the New Database dialog box where you specify a name and location for the database. A database name can be up to eight characters long but can't contain spaces. If you select the name of an existing database, Microsoft Access asks if you want to replace it.

Note : After creating a database, the next step is to create tables. After you create tables, you can create queries, forms, reports, and other database objects that help you use your data.

A table is a collection of records about a particular category of information. For example, a table could be a list of customers or an inventory of parts. Fields are the building blocks of tables. Each field contains information about one aspect of the category, such as a last name, a part number, or a job title.

To create a table:

1. In the Database window, click the Table button.
2. Choose the New button. Microsoft Access displays the Table window in Design view, in which you define the fields in your table.

To define fields in the table:

1. In the Field Name column, type the name of the first field, following Microsoft Access naming conventions.
2. In the Data Type column, keep the default (Text); or click the arrow, and select the data type you want.
3. In the Description column, type a description of the information this field will contain. The description is optional.
4. If you want, set field properties for the field in the bottom part of the window.
5. Repeat steps 1 through 4 for each field.

To save the table:

1. After you've finished defining the fields, choose Save from the File menu to save the table design. Microsoft Access prompts you to name the table.
2. Type a name for the table that conforms to Microsoft Access naming conventions, and then choose OK.

Notes: It is recommended that you designate a primary key field in every table. If you don't designate a primary key, Microsoft Access asks you if you want it to create one for you when you save the table. In addition to setting field properties, you can also set table properties. Table properties are attributes of the entire table, rather than just individual fields.When you click in a column or property box, Microsoft Access displays useful hints about each column or property in the lower right portion of the window. After creating tables, you can create queries, forms, reports, and other database objects that help you use your data.

Use the graphical QBE window to construct select queries that ask questions about your data. When you run the query, Microsoft Access creates a dynaset that you can view in the query's datasheet.

To create a select query:

1. In the Database window, click the Query button and then the New button. Microsoft Access displays an empty Query window in Design view and an Add Table dialog box so you can add tables or queries to your query.
2. Add tables or queries by double-clicking the table or query names. When you finish, choose the Close button.
3. Create joins by dragging field names between tables and queries, if necessary. If default relationships between tables have been defined, Microsoft Access automatically displays default join lines.
4. Add fields to the query by dragging the field names from the field lists to the QBE grid.
5. Refine your query by entering criteria, sorting, creating calculated fields, totaling data, and hiding fields.
6. Save the query. Note: When naming the query, give it a name unique from all tables and queries within the database.

Notes: You can create a quick query by selecting a table or query name in the Database window and then clicking the New Query button on the tool bar. The Query window opens in Design view with the table or query field list displayed. In multitable queries, you can keep track of which table each field is associated with by displaying the Table Name row beneath the Field row in the QBE grid. From the View menu, choose Table Names to display this row. Select queries are the default query type.

A FormWizard asks you questions about the form you want to create, then creates a form based on your responses. There are four types of FormWizards:

• Single-column
• Tabular
• Graph
• Main/Subform

To create a form with a FormWizard :

1. In the Database window, click the Form button, and then choose the New button. (Or choose New from the File menu, and then choose Form from the submenu.) The New Form dialog box appears.
2. In the Select A Table/Query box, type the name of the form's source table or query, or select one from the list.
3. Choose the FormWizards button.
4. Answer the questions in the FormWizard dialog boxes. From the last dialog box, you can display the form in Design view or Form view.

Note: If the FormWizard doesn't create the exact form you want, you can change the form yourself in Design view, or you can use the FormWizard again and choose different options as you answer the questions.

While using a FormWizard is the easiest way to create a form, it may not offer the design you need. If you want to design a form yourself, create a blank form, and then add the controls and other design features you require.

Note : Even if the FormWizard doesn't offer the exact layout you want, you might save yourself some work by using the FormWizard to build a basic form and then modifying it.

To create a form without a FormWizard :

1. In the Database window, click the Form button, and then choose the New button. The New Form dialog box appears.
2. In the Select A Table/Query box, type the name of the form's source table or query, or select one from the list. (To create an unbound form, leave this box empty.)
3. Click the Blank Form button. Microsoft Access displays the Form window in Design view, where you can create a custom layout.

Information Services, Central Computing
University of Missouri Kansas City