Ice Rift_Revision task.docx 12/05/21
Ice Rift: Revision task
1. The Primary Key field in the Orders table is incorrect. Fix this mistake.
2. You want your customers to be able to purchase more than one product in a single
transaction, thus, another table Items Ordered is necessary to be able to record all the
products purchased in the one transaction. Identify and write down all the fields you will
have in your Orders and Items Ordered tables. Through the process of normalisation
create a schema diagram which represents the data structure for the business.
3. By referring to your schema diagram, split the Orders table in two to create an Items
Ordered table.
4. By referring to your schema diagram, create relationships between all the tables showing
what type of relationship exists (eg 1 to 1, 1 to many or many to many)
5. Create forms for the Customers, Orders and Products tables.
6. Create navigation buttons to view first, last, next and previous records on the Customers
and Products forms.
7. Create add, save and delete record buttons on the Customers and Products forms.
8. Create a Combo box to input Customer ID in your Orders form. Select the Customer ID
from a combo box containing 3 columns – Customer ID, Surname (sorted in ascending
order) and First Name (sorted in ascending order).
9. Create a Combo box to input Tour ID in your Items Ordered table. Select the Tour ID
from a combo box containing 3 columns – Tour ID (sorted in ascending order), Tour
Name and Tour Price. The Items Ordered table should be included in the Orders form.
Make sure the Combo Box Control is used to display the tours so they can be added to
each order.
10. Create List boxes for the Payment Method and Payment Status fields.
11. Create a minimum of 15 records in your Items Ordered table. To do this you will need
to include sales where more than one tour was purchased in the one transaction eg., Order
ID 2 may include both Tour ID 3 and Tour ID 7 being purchased.
12. Create the following Queries:
• 10 DAY TOURS
• ALL TOURS WHICH GO TO AN ISLAND
• CREDIT CARD PURCHASES
• CUSTOMERS BORN AFTER 1990
• CUSTOMERS WHO ARE NOT FROM VIETNAM
• CUSTOMERS WHO MADE A 50% DEPOSIT ON 28/10/2017
• CUSTOMERS WHO REQUESTED A PICKUP
• SHIPS ALLOWING MORE THAN 300 PASSENGERS
• ALL ORDERS WHERE MORE THAN 1 TOUR WAS PURCHASED (Hint:
You may need to create a new field in the Orders table to do this)
13. Create Reports based on all the Tables & Queries.
14. Format the Customers report so that each customer’s details fit on one A4 page.
15. Adjust the image to fit in the background of the Main Menu form.
16. Display the Main Menu form when the database file is opened.
Ice Rift_Revision task.docx 12/05/21
17. Create the following in the Main Menu form:
• The date and time showing in the header.
• Button controls to navigate to the Customers, Orders and Products forms.
• Button controls to navigate to the Customers, Orders and Credit Card
Purchases reports.
• Button control to run the 10 Day Tours query.
• Button control to navigate to the 10 Day Tours report.
• Button control to show a list of tours in ascending order.
• Button control to exit the database.