GCSE Database Projects in Access

(1)

GCSE Database Projects in Access

Creating the Tables

This tutorial shows you how to create a database for a person who sells second-hand albums.

You are going to have one main table and several lookup tables, which might look something like this:

Recordings Table

Field Name Data Type Size Required?

RecordingID (K) Autonumber Yes

Album Title Text 30 Yes

Artist Number Genre Number

Year of Release Number No

Highest Chart Position

Number No

Number of Tracks Number No

Album Cover OLE Object No

Is it in perfect condition

Yes/No No

Price Currency Yes

Format Number

Formats Table

Format ID (K) Autonumber

Format Text 10 Yes

Genres Table

Genre ID (K) Autonumber

Genre Text 10 Yes

Artists Table

Artist ID (K) Autonumber

Artist Name Text 10 Yes

(2)

Creating the Relationships

You need to go to Tools and then choose Relationships. Add the tables. Create links between the fields, as follows:

Creating the Forms

1. Run the Form Wizard.

2. Select all the fields from the Artists Table. 3. Choose a Columnar View.

4. Choose a Standard style.

5. Give the form a name e.g. Artists. You should get something that looks like this:

(3)

Creating Combo Boxes

Edit the Recordings Form by opening the form and clicking the Design View button:

First, you should delete the Format, Genre and Artist fields. These fields are going to be replaced with combo boxes.

Select the Combo Box Wizard from the floating Toolbox and drag out a rectangle on the form where you want the combo box to be placed. The Combo Box Wizard should appear immediately:

1. Choose I want the combo box to look up the values in a table or query. 2. Choose the Artist table.

3. Choose the Artist Name field. 4. Store the value in the Artist field.

(4)

Editing the Form

Your form should now look something like this:

This needs tidying up. The black labels and the white data entry boxes can be dragged around until the form is neat and tidy, like this:

The next thing to do is to expand the Form Header and give a title to the form, like this:

(5)

Editing the Form (continued)

You can now change the colours and the fonts of the labels and the data entry boxes. You can add a background colour or a background picture to the form. You can re-size the boxes and labels, where appropriate.

Finally create some Command Buttons that open the other forms:

1. Add a Command Button from the floating Toolbox. The Command Button Wizard should immediately appear.

2. Select Form Operations and Open Form. 3. Choose the Artists form.

4. Choose Open the form and see all the records. 5. Select Text and choose Add an Artist as the caption. 6. Give the button a name and Finish.

7. Repeat this process to create similar buttons to open the Genres Form and the

Formats Form.

8. You can now re-size or move the buttons. You can also change the style/colour of the button and the style and colour of the text.

(6)

Queries

Once you have typed some data into your database, you will be able to run queries. To run a query, create it in Design View. Be sure to use the text fields from the lookup tables to search for Genre, Artist or Format.

1. Create your first simple query as follows:

The query above would find all albums by Bob Dylan or The Beatles. The Artist Name field has been selected from the Artists Table and the Criteria have been entered. Close the query down and save it. Use a sensible name like “Albums by Bob Dylan or The Beatles”. Run the query.

2. Create a query that uses more than one field, like this:

This query finds all albums by Bob Dylan that were released in 1995. 3. Create some queries using Number or Currency fields. For example:

This query would find all albums with a price of more than £5

4. Get a query to do a calculation. Click the Sigma Button and select a calculation like MIN, MAX, SUM or AVG to apply to a Currency or Number field:

(7)

Data Validation

Go back to the Recordings Table and open it in Design View. Select the Highest Chart Position field and enter the following Validation Rule and Validation Text:

Close and save the table. Now open the Recordings Form and enter a number between 1 and 100. This should be accepted. Now enter a number that is not between 1 and 100. A message box containing your validation text should appear:

You can enter some other validation rules. For example,

The validation rule <NOW() on any date field will ensure that all dates have to be before the present day.

The validation rule >5 on a Number or Currency field will ensure that the number entered must be more than five. >=5 means it must be greater than or equal to five. The validation rule “M” or “F” on a Gender field would ensure that the gender entered was either M or F.

(8)

Reports

A report is intended to be a printout of data in the database. A report can be created based on any query or table.

1. Use the Report Wizard to create a report showing all the artists in the database. 2. Group the data by Artist Name.

3. Choose how the data should be sorted (e.g. by ID number).

4. Once the report is created, go to Design View and change the appearance of the

Report Header. You can change the font style or colour. You can add a picture or logo.

(9)

Customise the Database

1. Close the database down without closing Microsoft Access. 2. Click on File and then Open.

3. Open your Database in Exclusive Mode:

4. Go to the Tools menu and set a password for the database: 5. Close the database and re-open it to test the password.

N.B. Please make your password “999” or “123” because nothing can be done to help you if you forget your database password!