Benefits of using relationships

Keeping data separated in related tables produces the following benefits:

· Consistency Because each item of data is recorded only once, in one table, there is less opportunity for ambiguity or inconsistency. For example, you store a customer's name only once, in a table about customers, rather than storing it repeatedly (and potentially inconsistently) in a table that contains order data.

· Efficiency Recording data in only one place means you use less disk space. Moreover, smaller tables tend to provide data more quickly than larger tables. Finally, if you don't use separate tables for separate subjects, you will introduce null values (the absence of data) and redundancy into your tables, both of which can waste space and impede performance.

· Comprehensibility The design of a database is easier to understand if the subjects are properly separated into tables.

Methods creation new table

Access gives you several ways to create tables, and this course shows you how to use three of them: Datasheet view, table templates, and Design view.

1. In Datasheet view, you build a table by entering field names and setting data types manually. All you have to do is click and type.

2. Table templates are pre-made tables that meet several common business needs. For example, the Assets table template contains many of the fields, such as Item and Purchase Date, discussed in these courses.

3. Design view lets you control every field and property in the table. In this course, you'll use it to create a table and to change the values in a lookup field — a field that contains a list of choices.

Create new database

Exercise 1

• Create a new database, save it on the desktop and name it “Student Database”

• Create a Table in the Student Database with the following:

3. Make the “ID Number” Field as the Primary Key.

4. Save the table as “Student’s Table”

5. Return to the main Access window.

Exercise 2

1. Create a new database, save it on the desktop and name it “Films Database”

2. Create a Table in the Films Database with the following:

3. Make the “ID Number” Field as the Primary Key.

4. Save the table as “Film”

Return to the main Access window.

Exercise 3

1. Open the “Students Table” and enter 5 complete records.

2. Sort the table in ascending order by surname

3. Move the Date of Birth and Telephone Number fields so that the Date of Birth field is now directly after the Surname field.

4. Delete the last Record you have entered

5. Change the field size of the Surname to 20

Exercise 4                       

1. Open the “Films Table” and enter 4 complete records.

2. Sort the table in ascending order by name and genre.

3. Move the Date of release field so that the Date of release field is now directly after the Name field.

4. Delete the 2 last Record you have entered

5. Change the field size of the Leading role to 40

Control questions

1. Describe possibilities of MS Access.

2. What are the main objects in MS Access database?

3. What restrictions on names of fields, controls and objects work in MS Access?

4. What types of data can have fields in MS Access. What is their limit size?

5. What is the purpose of MS Access help system?

6. What are the expressions in MS Access?

7. What are the features in record of various operands of expressions: field name, number, text?

8. What operations with data in the table do you know?

9. What is a filter? What are the features of an extended filter?

10. What is form? Why does database use forms?

Laboratory work №6

MS OFFICE PROGRAMS. Database system MS Access

Work with forms

Types of query

Reports

Work with forms

Another way to populate a database is with the use of forms. An Access 2007 form helps you know exactly what data to enter. In this lesson, we'll address the benefits of using forms with a database, and we'll show you how to set up a basic form for your Access 2007 database, as well as how to use the form to populate or edit data in the database. You'll also learn how to enhance a basic form with a drop-down list.

Access 2007 forms work in a similar way.

If the database has hundreds of records and many fields to populate for any given record, a table can be overwhelming to a user entering data. An Access form lets you enter data one record at a time, without having to see the entire table.

An Access 2007 form also lets you know exactly what information to enter and can even tell you what that information should look like. Adding certain control components to a form—like a drop-down menu—can dramatically increase the integrity of the datathat is held in a database.

A database owner wants to control the levels of access other database users have to the data; the fewer the amount of people who are interacting with the data, the lower the chances are of the data becoming compromised. Forms are one more way a database owner can limit the actions of other users. Form properties can be set so users can only enter records or just view records.

Access 2007 has several automatic tools for creating forms. These tools are located in the Forms group on the Create tab in the Ribbon, as seen below:

Access 2007 forms tools include:

· The Form command makes a basic form, showing a single record at a time.

· The Split Form command creates a form showing one record on top and includes the Datasheet view of the entire source table on the bottom.

· The Multiple Items command creates a form that shows all records at once, which looks similar to the source table in Datasheet view.

· The Form Wizard is hidden under the More Forms command. It walks you through the process of creating more customized forms.

To create a form using the Form command:

The basic Formcommand is the one we suggest because it allows you to see just one record at a time. It also includes all of the fields in your source table for you, and you can modify the layout of the basic form to hide fields or add controls:

· Begin by highlighting the table you want to use as a source table.

· With the source table highlighted, select the Form command from the Forms command group in the Createtab on the Ribbon.

· The new form is created and opens in the object pane.

Types of request: Select, Action, Parameter and Aggregate:

Select Query

The select query is the simplest type of query and because of that, it is also the most commonly used one in Microsoft Access databases. It can be used to select and display data from either one table or a series of them depending on what is needed.

In the end, it is the user-determined criteria that tells the database what the selection is to be based on. After the select query is called, it creates a "virtual" table where the data can be changed, but at no more than one record at a time.

Action Query

When the action query is called, the database undergoes a specific action depending on what was specified in the query itself. This can include such things as creating new tables, deleting rows from existing ones and updating records or creating entirely new ones.

Action queries are very popular in data management because they allow for many records to be changed at one time instead of only single records like in a select query.

Four kinds of action queries are:

1. Append Query – takes the set results of a query and "appends" (or adds) them to an existing table.

2. Delete Query – deletes all records in an underlying table from the set results of a query.

3. Make Table Query – as the name suggests, it creates a table based on the set results of a query.

4. Update Query – allows for one or more field in your table to be updated.

Parameter Query

In Microsoft Access, a parameter query works with other types of queries to get whatever results you are after. This is because, when using this type of query, you are able to pass a parameter to a different query, such as an action or a select query. It can either be a value or a condition and will essentially tell the other query specifically what you want it to do.

It is often chosen because it allows for a dialog box where the end user can enter whatever parameter value they wish each time the query is run. The parameter query is just a modified select query.

Aggregate Query

A special type of query is known as an aggregate query. It can work on other queries (such as selection, action or parameter) just like the parameter query does, but instead of passing a parameter to another query it totals up the items by selected groups.

It essentially creates a summation of any selected attribute in your table. This can be further generated into statistical amounts such as averages and standard deviation, just to name a couple.

Reports

Exercise 1

1. Create a Form with all fields on the Student’s Table.

2. Name the form Students Entries

3. Make the ID Number of Each student in the form, Red

4. Insert a Picture in the form in way that all text is visible.

Exercise 2

1. Create a Form with all fields on the Films Table.

2. Name the form Popular Films

3. Make the ID Number of each name of the film in the form, Green

4. Insert a Picture in the form in way that all text is visible.

Exercise 3

a. Create a report based on the Student’s Table showing the Fields Name, Surname, and Telephone Number.

b. Name the report Telephone List

c. Insert a picture in the report Header

Exercise 4

1. Create a report based on the Film’s Table showing the Fields Name, Genre, Country and Date of release.

2. Name the report Film List

3. Insert a picture in the report Header

Exercise 5

w Create a query, showing all fields of those students who have a particular surname of your Choice.

x Create another query showing all fields of those students born after 1987

y Create a query showing only the Student’s Name, Student’s Surname and Student’s Date of birth.

Control questions:

1. What is the query?

2. How you can create the query?

3. What is difference between query selection and query with parameter?

4. Describe the purpose of SQL language

5. What is the report?

6. What information is displayed in the report?

7. What is the structure of the report?

8. How many ways of report creation do you know?

9. What are the components of data access page?

10. How it is possible to sort data in Access?