Section A: Table Relationships
a) Identify and define relationships between the tables. (3 Marks)
1. Relationships:
o Customers ↔ Orders: One-to-Many. A customer can place multiple
orders, but each order is linked to a single customer.
o Orders ↔ Payments: One-to-Many. An order can have multiple
payments, but each payment is linked to a single order.
o Inventory ↔ Orders: Many-to-Many. A single order may include
multiple products, and a product may appear in multiple orders.
Implement this with a junction table (e.g., OrderDetails).
2. How to Define the Relationships in Access:
o Open the Relationships window: Go to the Database Tools tab >
Click on Relationships.
o Add all relevant tables.
o Drag and drop the Primary Key from one table to the corresponding
Foreign Key in another table.
o Set the relationship type (One-to-Many or Many-to-Many).
o Click Enforce Referential Integrity to prevent orphan records.
b) Explain the importance of enforcing referential integrity. (2 Marks)
• Enforcing referential integrity ensures that relationships between tables
remain consistent.
• For example, a CustomerID in the Orders table must exist in the Customers
table, preventing errors like orphan records or invalid foreign keys.
Section B: Queries
a) Calculate total revenue. (2 Marks)
1. Open Query Design: Go to the Create tab > Click on Query Design.
2. Add the Orders table.
3. Drag TotalAmount into the query grid.
4. In the query grid, select Totals (∑ symbol).
o Under the "Total:" row, choose Sum for the TotalAmount field.
5. Save the query as TotalRevenueQuery.
b) List customers with payments between two dates. (3 Marks)
1. Open Query Design and add the Payments and Customers tables.
2. Drag the FirstName, LastName, and AmountPaid fields into the query grid.
3. Under AmountPaid, in the Criteria row, write:
4. Between #01/01/2024# And #31/12/2024#
5. Save the query as CustomerPaymentQuery.
c) Identify low-stock products. (2 Marks)
1. Open Query Design and add the Inventory table.
2. Drag the ProductName and QuantityInStock fields into the query grid.
3. Under QuantityInStock, in the Criteria row, write:
4. <10
5. Save the query as LowStockQuery.
d) Calculate balance due for each order. (3 Marks)
1. Open Query Design and add the Orders and Payments tables.
2. Drag the OrderID, TotalAmount, and AmountPaid fields into the query
grid.
3. Create a calculated field:
o In an empty column, type:
o BalanceDue: [TotalAmount] - Sum([AmountPaid])
4. Group by OrderID and ensure TotalAmount is displayed.
5. Save the query as BalanceDueQuery.
Section C: Forms and Reports
a) Design a form for entering customer data. (3 Marks)
1. Go to Create > Click on Form Design.
2. Add fields: Drag fields from the Customers table into the form.
3. Implement validation rules:
o Set Required for fields like FirstName, LastName, and Email.
o For Email, use the validation rule:
o Like "*@*.*"
o For Phone, limit to numeric values using an Input Mask (e.g., "(999)
000-0000").
b) Generate a grouped report for high-value orders. (4 Marks)
1. Go to Create > Click on Report Wizard.
2. Select fields: Add CustomerID and TotalAmount from the Orders table.
3. Set grouping: Group by CustomerID and add a summary for TotalAmount
(e.g., totals per customer).
4. Add a condition: Filter TotalAmount > 500 in the query behind the report.
5. Save the report as HighValueOrders.
Section D: Advanced Functions and Macros
a) Macro for updating order status. (5 Marks)
1. Open Macro Designer: Go to Create > Click on Macro.
2. Add a condition:
3. [AmountPaid] = [TotalAmount]
4. Add an action to the macro:
o Action: SetField.
o Arguments: Update the Status field in the Orders table to "Paid."
5. Attach the macro to the Payments form:
o Go to the form's properties > Select After Update > Link the macro.
b) IIf function for stock status. (3 Marks)
1. Open Query Design and add the Inventory table.
2. Create a calculated field:
o In an empty column, type:
o StockStatus: IIf([QuantityInStock] < 10, "Low Stock", "In Stock")
3. Save the query.
