Business Services
Home > Business Services > Business Services (120/240 hours) > Create and use databases > Create and use databases
Activities
As an administrative assistant for Freedom Travel Pty Ltd one of your duties is to maintain company records.
You have been asked to set up and maintain a client database relating to package holiday bookings. Using the instructions below set up the database and enter information for all bookings received this week.
Task A – creating a database
- Create a database called Holidays, saved to an
appropriate location. The following data is to be entered
in a table called Bookings.
- Set CustId as the primary key using an AutoNumber
field, display dates in Medium date format and set currency
to two decimal places.
CustId
Title
LastName
FirstName
Package
Depart
Return
People
PerHead
1
Mr
Anderson
James
Vanuatu Holiday
24/04/04
30/04/04
2
$1,150
2
Mr
Farzin
Mohammed
Sydney to Los Angeles
22/12/03
28/12/03
8
$1,935
3
Mr
Nguyen
Brad
Los Angeles, Fiji Stopover
04/11/03
09/11/03
5
$1,842
4
Ms
Bradkowski
Margaret
Melbourne Grand Prix
04/04/04
07/04/04
3
$339
5
Mr
Fesno
Marc
Vanuatu Holiday
13/01/04
19/01/04
6
$1,150
6
Mr
Wang
Leo
Sydney to Los Angeles
13/10/03
18/10/03
4
$1,230
7
Ms
Singh
Nita
Melbourne Grand Prix
04/04/04
07/04/04
2
$339
8
Ms
Taggart
Lee
Amazing Ireland
25/04/04
01/05/04
4
$2,450
Task B – editing a database
Make the following changes to the data, ensuring that the table fits on one page, before printing a copy.
- Nita Singh has spoken to one of the travel consultants
and wishes to cancel her holiday. Delete her record from
the database.
- The Farzin holiday has errors in the data. There should
be five (not eight) people travelling and the cost should
be $1835 (not $1935) per head.
- Lee Taggart has called to say that she wishes to change
her holiday to a more expensive package, which costs $2650
per head.
- The Title field is not required in this table and
should be deleted.
- Two new bookings are to be added to the database. Add the following records, using your name as the last record.
Grassman
Brian
Perth Getaway
Your name
Your name
Melbourne Grand Prix
Task C – Creating a form
You have been asked to create a form, for use by the Freedom Travel consultants, to enable them to enter data as they are taking bookings.
- Using the BOOKINGS table, create a form in columnar
format using the “Blends” style, named Bookings
Form.
- In design view
- amend the field widths to suit the data
- add a form header Package Holiday Bookings
- add a form footer YOUR NAME.
- Print a copy of the form displaying record number 5.
Task D – Queries and reports
Information about holiday bookings has been requested by the manager. You have been asked to prepare the following documentation to present to her.
- Prepare a report that shows which clients have not yet paid for their holidays.
- Enter the data shown below, using a Yes/No data type
for the Paid field
CustId Paid 1
Yes
2
No
3
Yes
4
No
5
Yes
6
Yes
8
No
9
No
10
Yes
- Create a query to display this information, showing the CustId, FirstName, LastName and Depart fields
- Group by Depart and sort by departure date
- Display this information on a report, with your name in the page footer
- Print a copy of the report.
- A bonus is being offered to clients whose holidays are valued at over $1200 per person.
- Create a query to find this information, sorting the data by CustID
- Display this information on a report, with your name in the page footer
- Print a copy.
- Prepare a query that shows the total cost of each holiday booked.
- Include the CustId, LastName, Package, People and PerHead fields
- Add a calculated Total field to calculate the total cost of each holiday
- Print a copy of the query.
- Using the query above, create a report that shows the total value for each holiday package.
- Include the Package and Total fields only
- Group the report by Package and sort by Total in descending order
- From Summary Options calculate the Total
- Insert your name centred in the report footer and print a copy.
|