Information and Communications Technology
Assignment 8: Advanced Calculated Fields
Name:
Today’s objectives: Creating two tables, Forms and Three Calculated Fields.
1. Open Microsoft Access 2013.
2. Create a new database under the name, Sales_Database_YourName.
3. Create the following two tables; Sales Table and Weekly Sales Table. After creating the tables, fill out
the fields with given information. (Hint: Choose Data Type wisely)
Sales Table
ID Agent Date of Sale Product Unit Price Quantity Total Amount Sold Commission
Sold
BD098 Abul kalam 22-Sep-2014 HP Printer S2 2500 4 Unit price x Quantity sold 15% on total
(Note: you don’t have to write this. amount sold
This formula is given to assist you)
(Note: you don’t have
to write this. This
formula is given to
assist you)
BD025 Mostafizur Rahman 23-Sep-2014 HP Printer SP 4000 2 Unit price x Quantity sold 15% on total
amount sold
BD043 Zafar Sarafat 22-Sep-2014 Canon Printer K1 5000 6 Unit price x Quantity sold 15% on total
amount sold
Weekly Sales Table
Agent Sunday Monday Tuesday Wednesday Thursday Total Number of Sales
Abul kalam 4 3 7 4 0 ?
Mostafizur Rahman 2 2 2 4 5 ?
Zafar Sarafat 1 2 5 5 7 ?
4. Create two Forms, one for Sales Table and another one for Weekly Sales table using Form Wizard.
5. Now you have to create a function in the
Total Amount Sold (in Sales table)
Commission (in Sales table)
Total Number of Sales (in the Weekly Sales Table)
so that the calculations can be done automatically.
Creating the functions: Go to the Design View of your Sales Table’s Form.
Write the following function in Total Amount Sold,
=[Unit Price]*[Quantity Sold]
Go to Form View, have you noticed that the Total Amount Sold has automatically been
calculated!
Now, create appropriate functions for Commission (in Sales table) and Total Number of Sales (in
the Weekly Sales Table).
6. Upload your database file (i.e. Sales_Database_YourName) on the Advanced Calculated Fields assignment
section in ManageBac.
