Design and outline the implementation of a simple MS Access database for managing member transactions, including table structures, primary keys, and data types.

TASK A: DATABASE MANAGEMENT

1. Database Design:

• a) Create the tblMember table structure... showing field names and data types.

  • tblMember
    • MemberID: Short Text (or Number, if IDs are purely numeric)
    • MemberName: Short Text
    • BranchLocation: Short Text
  • tblTransaction
    • TransactionID: Short Text (or Number)
    • TransactionType: Short Text
    • Amount: Currency
    • LoanPurpose: Short Text
  • tblTransactionRecord
    • RecordID: Short Text (or Number)
    • MemberID: Short Text (or Number)
    • TransactionID: Short Text (or Number)
    • TransactionDate: Date/Time

• b) State the PRIMARY KEY for each of the three tables.

  • tblMember: MemberID
  • tblTransaction: TransactionID
  • tblTransactionRecord: RecordID

• c) What is the most appropriate data type for the TransactionDate field?

  • Date/Time

2. Database Implementation:

• a) Using MS Access, create a new database named BYEC_DB.accdb.

  • Action: Open MS Access and create a new blank database, saving it as BYEC_DB.accdb.

• b) Create the three tables with their respective structures and set the primary keys.

  • Action: In BYEC_DB.accdb, create the tables tblMember, tblTransaction, and tblTransactionRecord with the field names and data types specified in 1a. Set the primary keys as specified in 1b.

• c) Establish relationships between the tables, enforcing referential integrity.

  • Action: Establish the following relationships:
    • tblMember.MemberID to tblTransactionRecord.MemberID
    • tblTransaction.TransactionID to tblTransactionRecord.TransactionID
  • Ensure "Enforce Referential Integrity" is checked for both relationships.

• d) Populate the tables with the data provided in Table 1.

  • Action: Enter the data from Table 1 into the respective tables. For example:
    • tblMember will contain unique MemberIDs, MemberNames, and BranchLocations.
    • tblTransaction will contain unique TransactionIDs, TransactionTypes, Amounts, and LoanPurposes.
    • tblTransactionRecord will contain RecordIDs, MemberIDs, TransactionIDs, and TransactionDates.

3. Query Design using SQL View:

• a) Create a query that displays MemberName, Amount, and TransactionDate for all "Loan Disbursement" transactions. Save the query as qryLoansDisbursed.

  $$\begin{align*} &\text{SELECT tblMember.MemberName, tblTransaction.Amount, tblTransactionRecord.TransactionDate} \\ &\text{FROM tblMember INNER JOIN (tblTransaction INNER JOIN tblTransactionRecord ON tblTransaction.TransactionID = tblTransactionRecord.TransactionID) ON tblMember.MemberID = tblTransactionRecord.MemberID} \\ &\text{WHERE tblTransaction.TransactionType = "Loan Disbursement";} \end{align*}$$

• b) Create a query to show the total savings deposits made by members in the "Buea" branch. The output should show BranchLocation and TotalSavings. Save the query as qryBueaTotalSavings.

  $$\begin{align*} &\text{SELECT tblMember.BranchLocation, SUM(tblTransaction.Amount) AS TotalSavings} \\ &\text{FROM tblMember INNER JOIN (tblTransaction INNER JOIN tblTransactionRecord ON tblTransaction.TransactionID = tblTransactionRecord.TransactionID) ON tblMember.MemberID = tblTransactionRecord.MemberID} \\ &\text{WHERE tblMember.BranchLocation = "Buea" AND tblTransaction.TransactionType = "Savings Deposit"} \\ &\text{GROUP BY tblMember.BranchLocation;} \end{align*}$$

• c) Create a parameter query that prompts the user to enter a MemberID and then displays all transactions for that member. Save it as qryMemberTransactions.

  $$\begin{align*} &\text{SELECT tblMember.MemberID, tblMember.MemberName, tblMember.BranchLocation, tblTransactionRecord.RecordID, tblTransaction.TransactionID, tblTransaction.TransactionType, tblTransaction.Amount, tblTransaction.LoanPurpose, tblTransactionRecord.TransactionDate} \\ &\text{FROM tblMember INNER JOIN (tblTransaction INNER JOIN tblTransactionRecord ON tblTransaction.TransactionID = tblTransactionRecord.TransactionID) ON tblMember.MemberID = tblTransactionRecord.MemberID} \\ &\text{WHERE tblMember.MemberID = [Enter Member ID];} \end{align*}$$

4. Reporting:

• a) Create a formatted report based on qryLoansDisbursed, grouped by LoanPurpose (add the LoanPurpose field from tblTransaction to the fields of qryLoansDisbursed).

  • Action:
    1. Modify qryLoansDisbursed to include tblTransaction.LoanPurpose.
    2. Use the Report Wizard in MS Access to create a new report based on the modified qryLoansDisbursed.
    3. Group the report by LoanPurpose.
    4. Format the report as desired.

• b) Print the tblTransactionRecord and the qryLoansDisbursed datasheet view.

  • Action:
    1. Open the tblTransactionRecord table in Datasheet View and print it.
    2. Open the qryLoansDisbursed query in Datasheet View and print it.

TASK B: SPREADSHEET MANAGEMENT

1. Data Manipulation:

• a) In the DataEntry sheet, column H, enter the heading "Status". Write a function in cell H2 to display "Loan" if the transaction is a loan, "Repayment" if it's a repayment, and "Savings" otherwise. Copy the formula down for all transactions. Write the formula used in cell H5.
  • Assuming TransactionType is in column E (e.g., E2 for the first transaction, E5 for the fifth transaction).
  • The formula for cell H5 is:
  $$\boxed{\text{=IF(E5="Loan Disbursement", "Loan", IF(E5="Loan Repayment", "Repayment", "Savings"))}}$$