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
Field Name Data Type Size Required?
Format ID (K) Autonumber
Format Text 10 Yes
Genres Table
Field Name Data Type Size Required?
Genre ID (K) Autonumber
Genre Text 10 Yes
Artists Table
Field Name Data Type Size Required?
Artist ID (K) Autonumber
Artist Name Text 10 Yes
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:
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.
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:
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.
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:
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.
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.
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!