Data Modeling – SS2 Data Processing Lesson Note (First Term)

This note covers the basic tasks for building a database in Microsoft Access: editing the data type of a field, deleting and renaming tables, and creating forms, queries and reports. A query selects and filters data from multiple tables. A report organises and summarises data for viewing online or for printing.

Behavioural objectives

By the end of the lesson, students should be able to:

• edit the data type and format of a field in Datasheet view and Design view;
• delete and rename a table;
• create and save a form, with or without the Form Wizard;
• create a query using the Query Wizard;
• create a report using a blank report or the Report Wizard.

How do you edit the data type of a field?

When creating tables, define the data type of each field to match, as closely as possible, the type of data that will be entered in it.

To edit the data type in Datasheet view

1. Click the field you wish to define.
2. Click the Datasheet tab on the Ribbon.
3. Click the down arrow next to the data type.
4. Choose the type of data that will be entered into the field.

To edit the format of a field

1. Click the field you wish to define.
2. Click the Datasheet tab on the Ribbon.
3. Click the down arrow next to the format.

To edit the data type in Design view

1. Click Design view.
2. Click the field name you wish to define or create (for a new field).
3. Click the Data Type.
4. Choose the appropriate data type.
5. Format the field in the Field Properties dialogue box.

How do you delete or rename a table?

To delete a table

1. Open the desired database by clicking the Microsoft Office button and clicking Open.
2. Right-click on a table and choose Delete.

To rename a table

1. Open the desired database by clicking the Microsoft Office button and clicking Open.
2. Right-click on a table and choose Rename.
3. Type in the new name.

How do you create a form?

1. Open the Navigation pane.
2. Click the table or query on which you want to base your form.
3. Activate the Create tab.
4. Click Form in the Forms group.

Saving a form

After you create a form, you can save it. You can also open a saved form at any time.

1. Click the Save button on the Quick Access toolbar.
2. Type the name you want to give the form.
3. Click OK. You can access the form by clicking the Navigation pane.

Using the Form Wizard

You can create forms with the help of the Form Wizard.

1. On the Create tab, click the More Forms down arrow.
2. Click Form Wizard.
3. Choose the Tables/Queries that you wish to have on the form.
4. Choose the fields you wish to have on the form.
5. Click Next.
6. Choose the layout for the form.
7. Click Next.
8. Choose a style.
9. Click Next.
10. Create a title for the form.
11. Choose whether you want to open the form to view it or modify the form’s design.
12. Click Finish.

What is a query and how do you create one?

A query allows you to select and filter data from multiple tables. Queries can be saved and used as often as you need them.

Creating a query using the Query Wizard

The Query Wizard walks you through the steps to set up a query. To run a query using the Query Wizard:

1. Click the Create tab.
2. Click the Query Wizard button in the Other group.
3. Choose the type of query you wish to run.
4. Click OK.

Choosing the fields to include from each table

1. To select fields from different tables, click the Tables/Queries down arrow.
2. Click OK.

Finishing the query

1. Type in a title for the query.
2. Click Finish.
3. The query will be displayed.

What is a report and how do you create one?

Reports organise and summarise data for viewing online or for printing. A detailed report displays all of the selected records. Reports are a means to view and analyse a large amount of data.

You can use the Report Wizard or create a custom report that meets your specific needs.

Creating a blank report

1. Click the Blank Report button on the Create tab.
2. Click the Add Existing Fields button.
3. From the field list, click and drag the fields to the report.

Creating a report using the Report Wizard

1. On the Create tab, click the Report Wizard button.
2. Choose the Tables/Queries that you wish to have on the report.
3. Choose the fields you wish to have on the report.
4. Click Next.
5. Choose the sort order for your report.
6. Choose the layout for the report.
7. Click Next.
8. Choose a style.
9. Click Next.
10. Create a title.
11. Choose whether you want to open the report to view it or modify the report’s design.
12. Click Finish.

Evaluation

1. State the steps to edit the data type of a field in Design view.
2. How do you rename a table?
3. What is a query?
4. List the steps to save a form after creating it.
5. What is the use of a report?

Assignment

1. Write out the steps to create a form using the Form Wizard.
2. Write out the steps to create a report using the Report Wizard.