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)
|
(New) ID_Product1
|
Name_Product1
|
Price1
|
Postage1
|
Tax1
|
|
B001
|
Mechanical Pencil
|
4.10
|
0.50
|
0.50
|
|
B002
|
Blue Ball Point Pen
|
0.75
|
0.50
|
0.15
|
|
B003
|
10 Pack Pencils
|
1.95
|
0.50
|
0.25
|
|
B004
|
Highlighter
|
1.60
|
0.50
|
0.20
|
|
B005
|
Premier Clip Binder
|
1.50
|
0.50
|
0.20
|
iii - Product2 (should have suitable Calculation for Product2_TotalPrice)
|
ID_Product2
|
Name_Product2
|
Price2
|
Postage2
|
Tax2
|
|
E001
|
Sharpener Single Hole
|
2.35
|
0.50
|
0.35
|
|
E002
|
Exam Eraser
|
1.05
|
0.50
|
0.20
|
|
E003
|
Air Gel Ball Pen
|
3.05
|
0.50
|
0.45
|
|
E004
|
True Gel Pen Colour
|
4.15
|
0.50
|
0.50
|
|
E005
|
Three Rings Arch File
|
4.55
|
0.50
|
0.50
|
iv - Product3 (should have suitable Calculation for Product3_TotalPrice)
|
ID_Product3
|
Name_Product3
|
Price3
|
Postage3
|
Tax3
|
|
T001
|
Retractable Ball Pen
|
1.00
|
0.50
|
0.15
|
|
T002
|
Staples
|
0.55
|
0.50
|
0.10
|
|
T003
|
Stapler
|
3.55
|
0.50
|
0.50
|
|
T004
|
Masking Tape
|
1.75
|
0.50
|
0.25
|
|
T005
|
Double Sided Tape
|
2.55
|
0.50
|
0.25
|
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]