[SOLVED] MICROSOFT ACCESS LAB TEST 2 Semester 1 2023-2024 Python

LAB TEST 2

MICROSOFT ACCESS

January 2024

Semester 1 (2023-2024)

2 HOURS

Lab Test 2 – Microsoft Access

Please create an Access database, named this file with your Matric Number & LabTest2, please follow this example – A123456_ LabTest2. Please read the questions carefully. Please make sure to name your Table, Query, Form. and Report with the right and suitable name given in the question.

1) Create 4 Table with the details below:

i – Buyer (Please use Lookup Wizard for ID_Product1, ID_Product2 and ID_Product3)

  – ID_Product1 : B001, B002, B003, B004 and B005

  – ID_Product2 : E001, E002, E003, E004 and E005

  – ID_Product3 : T001, T002, T003, T004 and T005

  – Please fill out ID_Product1, ID_Product2 and ID_Product3 based on information below:

  – Please be careful, the information is about the Name of the Product, not the ID

ii – Product1 (should have suitable Calculation for Product1_TotalPrice)

iii – Product2 (should have suitable Calculation for Product2_TotalPrice)

iv – Product3 (should have suitable Calculation for Product3_TotalPrice)

2) Build suitable Relationship which connects all Table that you have created. Please make sure the right Primary Key being used.

3) Create 6 Query with the details below:

i – Full_Data (should have these data: ID_Buyer, Name_Buyer, Credit, ID_Product1, Pax1, ID_Product2, Pax2, ID_Product3, Pax3, Name_Product1, Price1, Postage1, Tax1, Name_Product2, Price2, Postage2, Tax2, Name_Product3, Price3, Postage3 and Tax3)

ii – Product1_Data (should have these data: ID_Buyer, Name_Buyer, ID_Product1, Name_Product1, Pax1 and Product1_TotalPrice) – should have suitable Calculation for Product1_TotalPricePax

iii – Product2_Data (should have these data: ID_Buyer, Name_Buyer, ID_Product2, Name_Product2, Pax2 and Product2_TotalPrice) – should have suitable Calculation for Product2_TotalPricePax

iv – Product3_Data (should have these data: ID_Buyer, Name_Buyer, ID_Product3, Name_Product3, Pax3 and Product3_TotalPrice) – should have suitable Calculation for Product3_TotalPricePax

v – FinalProduct_Data (should have these data: ID_Buyer, Name_Buyer, Product1_TotalPricePax, Product2_TotalPricePax and Product3_TotalPricePax) – should have suitable Calculation for FinalProduct_TotalCharge

vi – Balance (should have these data: ID_Buyer, Name_Buyer, Credit and FinalProduct_TotalCharge) – should have suitable Calculation for Credit_Balance

4) Create 4 Form. with the details below:

i – Buyer ii – Product1 iii – Product2 iv – Product3

5) Create 5 Report with the details below:

i – Full_Data ii – Product1_Data iii – Product2_Data

iv – Product3_Data v – FinalProduct_Data vi – Balance

6) Please create Group & Sort for the selected Report  with the details below:

i – Product1_Data : Group by ID_Product1 (largest to smallest) & Sort by Name_Product1   (largest to smallest) & Group by ID_Buyer (largest to smallest)

ii – Product3_Data : Group by ID_Product3 (largest to smallest) & Sort by Name_Product3   (largest to smallest) & Group by ID_Buyer (largest to smallest)

iii – Balance : Group by Credit_Balance (largest to smallest) & Sort by Credit (largest to smallest)

7) Create Switchboard with the details below:

i – Startup Page (Please change the Setting in Option for Display Form)

ii – Main Menu iii – Form. (Add Mode) iv – Form. (Edit Mode) v – Report

[Do not forget to create Close Button for all Form. and Report]