代做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)

(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]