FIT1013 Digital Futures: IT for Business
Assignment 3 Construct Database and Analyse Data using Microsoft Access (30%)
Submission Deadline: Friday, 4 November 2022, 4:30 PM
Individual Assignment
Learning Objectives
By completing this assignment, students will be able to create an Access database using the objects in Microsoft Access. Students will be able to create queries based on multiple tables, multiple selection criteria, and perform data connections to Microsoft Access from a VBA.
Submission Requirements
● Your assignment should be submitted to Moodle/Turnitin.
● The file names should contain Unit Code, assignment number and your student ID number, similar to the example: FIT1013A3_StudentID.xlsm and FIT1013A3_StudentID.accdb where StudentID is your student ID.
Late Submissions:
The late-submission penalty is 10 percent of the available marks in that task, not the marks you received. For instance, an assignment has 100 marks, and you submitted the assignment one day late and received 65 marks. In this case, the penalty is 10 marks deduction (10% of the total available marks).
65 - (10% of 100) = 55 of 100 marks.
Scenario
Compared to Excel, Access is better for managing large amounts of data and keeping it organised, easy to search, and available to multiple simultaneous users. As the SAF (Simon’s Amazon of Fashion)’s business is expanding, the CEO is now contracting you to develop an Access database as specified below.
Tasks
1. Create an Access database consists of four tables i.e. based on the four worksheets in Assignment 3 workbook (FIT1013 A3.xlsx). (2 marks)
2. Use appropriate data type for each field. Apply Lookup Fields (e.g. ReturnCondition), Input Masks (e.g. Telephone), and define Data Validation Rules when appropriate. (3 marks)
3. Define relationships between tables – including identify Primary Keys, Referential Integrity Rules. (2 marks)
4. Create queries based on the following criteria (12 marks)
i. Create a parameter query to return the available stock (Quantity on Hand) of a dress with the DressName and Size that specified by the user. If the user doesn't enter a DressName field value, select all records from the table. Save the query as qryDressQuantityParameters.
ii. Create a query that finds the dresses with the available sizes greater than 10 and the available stocks (Quantity on Hand) are between 5 and 9. Display only the Designer, DressName, Color, Size and QoH. Sort the list in ascending order on the Designer field, followed by DressName in descending order. Save the query as qryDressSizeQoH.
iii. Create a query to show all customers (with or without rental). For the customers who have rental, display the total amount, otherwise display 0. Save the query as qryAllCustomerRental.
iv. Create a query to find the return status of all the rented dresses. The query should display the RentalID, Customer name (both first name and last name in a field), Designer, DressName and Size fields. Add a calculated field named ReturnStatus as the last column that displays ‘Returned’ if the dress was returned, otherwise display ‘Not Returned’ . Sort the list in ascending order on the RentalID field. Save the query as qryReturnStatus.
v. Create a query to find the Highest, Lowest and Average Total Amount of Rental for each State in ascending order. Save the query as qryRentalAmountStatisticByState.
5. Create forms for Customer, Dress and Rental tables. Explore an apply additional design and format features e.g. logo, title, etc. to improve the layout and usability. (2 marks)
6. Create reports based on queries (ii), (iii), (iv) and (v) of Task 4. Explore an apply additional design and format features e.g. logo, title, etc. to improve the layout and presentation. (2 marks)
7. Import/Export data from/to Access using VBA (7 marks)
Using the file and data from Assignment 3 workbook (FIT1013 A3.xlsx), create a button (Form. Control or Command Button in ActiveX Control) named “Backup” in the Rental worksheet that allows users to export the rental data (from Excel) to your Access database for backup. Create another button “Recover” which can import rental details from the same database (to Excel) for data recovery. During importing, any discrepancies (between the records in the worksheet and the database) should be recorded and reported in a separate worksheet called “Discrepancies” .