Module 5 – DATABASE Office 2007 WORKBOOK THREE
ACCESS – WORKBOOK THREE
This workbook assumes that you have previous experience of using Microsoft
Access and can create Tables, Queries and Reports.
Exercise 1
This database is designed to store information on vegetable seeds for a
local garden centre. Two tables are to be created
Open Access
New database
Filename: Crop database
Create Table 1 and save it as Crop Varieties
Set up following fields
Field Sizes Data Type Field Sizes
Variety Text 20
Name Text 20
Code No Text 4
Crop Time Text 10
Set primary key for Code No field
Delete the ID field
Close table
Create New Table 2 within the same database. Save it as Crop Prices
Field Sizes Data Type Field Sizes
Code No Text 4
Page Ref Number Long Integer
Amount Sold Number Long Integer
Price Currency 2 places decimals
Kelly Connaghan Please recycle when finished Page 1 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
Set Primary key for Code No and delete the ID column
Save and close
Input Data
Table 1 (Crop Varieties)
Open Crop Varieties
(for the tables you can import them from excel. Go to the External Data tab
and click on Excel file. The first database is saved as crop varieties and the
second as crop prices. Import them into your database to save time)
Variety Name Plant Harvest
Code Time
Dwarf French Beans Annabel 3088 Summer
Dwarf French Beans The Prince 3097 Summer
Dwarf French Beans Masterpiece 3092 Summer
Cabbage Spring Hero 3333 Spring
Cabbage Cosair 3357 Winter
Carrots Cardinal 3389 Spring
Carrots Rocket 3393 Spring
Carrots Campestar 3390 Autumn
Lettuce Tom Thumb 3681 Summer
Lettuce Lakeland 3677 Summer
Lettuce Valdor 3706 Winter
Peas Meteor 3009 Autumn
Peas Little Marvel 3007 Spring
Peas Onward 3062 Summer
Select the columns and AutoFit
Sort into Plant Code order
Move Plant Code column to beginning
Save and print a copy
Close table
Table 2- Crop Prices
Open Crop Prices and input data
Kelly Connaghan Please recycle when finished Page 2 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
Plant Code Brochure Ref Amount Sold Price
3088 62 100 0.85
3097 63 250 1.15
3092 64 200 1.02
3333 65 160 1.22
3357 68 130 1.29
3389 69 255 1.39
3393 70 300 0.98
3390 71 425 0.75
3681 72 120 0.52
3677 72 80 0.79
3706 74 360 0.66
3009 77 250 0.92
3007 77 300 1.02
3062 79 120 0.89
Sort into Plant Code order
Save and print a copy
Close table
Relationships
Create a relationship between the 2 tables
Click on Database Tools and choose Relationships. Add both tables as seen
below
Kelly Connaghan Please recycle when finished Page 3 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
When both tables have been selected, close the window. Click in Code No
from the first table and drag into Code No in the second table. This will
bring up the following window.
Click Create and a line will now join the 2 tables. There is now a relationship
between the 2 tables.
Queries (Joined tables)
Create the following queries based on the 2 joined tables. The tables will be
joined via the CodeNo field (primary key)
You can only join two tables if they have the same field in both of the tables.
This saves you having to create two separate queries and also brings all your
data together into one.
Proceed as follows to add calculated fields in query
Perform New Query as usual to find all Spring Varieties; add both tables
to the grid
Fields to show in this order:
Kelly Connaghan Please recycle when finished Page 4 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
CropTime,
Code Number, (from either table)
Name,
PageRef,
Price
Criteria: Crop Time: Spring
Run the query to view the results.
Change orientation to landscape -
Print
Print Preview
Landscape orientation
OK
Save Query
Preview and print
Use the same query in Design View and change criteria for the following
query.
Find all Summer Varieties
File Menu, Save As - Summer Varieties
Preview and print
Close query.
Report
The following steps will enable you to produce a Report, based on the 2
tables, using the Report Wizard.
A Query must be created first so that the required fields from the 2 tables
will be available.
Create a new query in design view
Add both tables to the grid
Kelly Connaghan Please recycle when finished Page 5 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
All fields required from Crop Varieties table
Double click the grey heading ‘Crop Varieties’
All fields are selected
Drag down to grid
From Crop Prices list of fields
Page Ref,
Amount
Sold
Price
Run Query
Save Query Complete List
Close Query – back to database window
Create Simple Report
Create a simple report via the Report Wizard. Base it on the query
“Complete List” and include all the fields.
Choose to group the report on Variety and sort it on the field Name. In
the summary details sum the Amount Sold field.
Choose to lay the report in landscape, and select any style that you prefer.
Name the report: Crop Report
Finish
View and Print Report
Print a copy of the report and then close it.
Close database file
Forms
A form is a database object that you can use to enter, edit, or display data
from a table or a query. You can use forms to control access to data, such as
which fields or rows of data are displayed.
Kelly Connaghan Please recycle when finished Page 6 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
Create a form for the Crop Varieties table using the wizard
Click on the Create tab in the Access window
Click on More Forms, Form wizard
Choose the crop variety table, use all of the fields, and choose Columnar
Style
Choose any style for the form, and enter the name frmcropvarieties
Use the forms to view the records in the table
On the toolbar at the bottom, click on the New Record icon
Last Record
New Record
Back to first record Next record
Add a new record
Lettuce Ice Berg 3100 Summer
Click on the crop varieties table to see the record has been entered (you
may need to close the form and table and reopen to see the new record)
Using the same method as before, create a form for the Crop prices table
and enter the following new record
3100 78 350 £0.89
Kelly Connaghan Please recycle when finished Page 7 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
Exercise 2
This exercise represents an extract from a Travel Agency database and the
creation of 2 related tables
Create a new database file and name it Travel Database
Database Window appears
Design a new table as follows, save it as Tour Details
Field Name Data Type Description Field Size/Format
Tour No AutoNumber Number in Long Integer
brochure
Resort Text Name of Resort 20
Hotel Text Name of Hotel 15
Days Number Number of days Long Integer
tour lasts
Prices From Currency Minimum charges 2 Decimal Places
Set primary key for TourNo field
Delete the ID field
Save table design - click Save button
OK
ENTER DATA
Click View button to change to datasheet view
Enter the following data - use <TAB> after each entry
The Tour No is automatically entered each time - just <TAB> to next
field
This data is also available in the excel spreadsheet Tour Details
If currency appears in dollars seek assistance
Resort Hotel Days Prices
Kelly Connaghan Please recycle when finished Page 8 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
Hunstanton Royal 8 103
Cromer Viking 8 109
Sheringham Lyton Court 8 95
Hunstanton Devon Towers 8 97
Gt Yarmouth Southdown 9 127
Cromer Midland 8 105
Hunstanton Manchester 7 111
Cromer Majestic 7 127
Change widths
Click 1st column selector to select column
Hold down <SHIFT> and click final column selector
Double click right border of any column selector to adjust widths to best
fit
Save Structure
Save
Print copy of the datasheet
Manipulation of data
Sort Resorts into alphabetical order
Click anywhere in Resort column
Click Ascending Sort button
Print Datasheet
Find a Record quickly
Click HOTEL column selector
Find button (binoculars)
Find what: Southdown
Find first:
Close
Kelly Connaghan Please recycle when finished Page 9 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
While selected change name of hotel to Tralee
<ENTER>
Change Font
Home Tab
Font …
Choose Arial 12
Print datasheet
Close table Answer Yes to Save Changes
CREATE 2ND TABLE
Create Tab
Table
Click on View
Save the table as State of Bookings
Construct table design:
Field name Type Description Field Size
Tour No (replace AutoNumber Number in Long Integer
the text in the brochure
ID field with
this)
Bookings Number Number of Long Integer
bookings made
Full Yes/No Whether fully Yes/No**
booked
**Ensure the properties of this show
Text Box rather than the Default
Check Box. Change this via the
Lookup Properties
Save button
Enter data
Kelly Connaghan Please recycle when finished Page 10 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
Switch to Datasheet view - click datasheet view button
(TOUR NO completed automatically -)
Records are also available in the excel spreadsheet State of bookings
Tour No Bookings Full
1 23 No
2 30 No
3 50 Yes
4 15 No
5 65 Yes
6 50 Yes
7 42 No
8 33 No
Change Font
Arial 12
Save the table
Print datasheet
Close table
Create a relationship between the 2 tables
Click on the Database Tools Tab, click on Relationship icon. Add both tables,
click in the Tour No field in the first table and drag it into the Tour No field
in the second table.
Click Create
A line will join the 2 fields.
Exit the relationships window and save when prompted.
Kelly Connaghan Please recycle when finished Page 11 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
Simple Query 1
The 2 tables have a common field - TOUR NO. This enables you to join the
tables to create a larger table with more possibilities
Create a new query in design view and add both tables to the grid
Note: the tables are joined (primary keys)
We want to find the Resorts and Hotels that are already full
Add the fields TOUR NO, FULL and RESORT to the grid.
Set criteria to find the hotels that are full.
Set the RESORT field in an ascending sort.
Your query should look like the one below
Run Query – all full hotels listed
Save Query - List of Full Hotels
Close Query
Simple Query 2
Find any Hunstanton Tours which still has room
Show TOUR NO, HOTEL, RESORT, Full and PRICE fields only (although you
are not going to show FULL field you will need to insert it into the grid as
you are going to use it for one of the criteria)
Set appropriate criteria to select these records
Kelly Connaghan Please recycle when finished Page 12 of 13
Module 5 – DATABASE Office 2007 WORKBOOK THREE
Uncheck Show box in FULL column (this field will not now show)
Run Query
Save Query - List of Hunstanton Hotels with vacancies
Print result of query to show the hotels which are not yet full (Printout
8)
Close Query
Close database file and assemble your printouts
Report
Create a report based on the tour details table, with the grouping level set
for Resort
Landscape
Save the report as rpttourdetails
Close down the database
Kelly Connaghan Please recycle when finished Page 13 of 13
