Explain six database objects that can be created and used in MS Access.

Here are the answers to Question 2, based on the database created in Question 1:

Question 2

2.1 Explain six database objects that can be created and used in MS Access.

MS Access provides several objects to store, retrieve, analyze, and present data. Here are six key objects:

• Tables: These are the fundamental building blocks of a database, used to store data in rows (records) and columns (fields). Each table holds data about a specific subject, like Books or Members.
• Queries: Used to retrieve specific data from one or more tables. Queries can filter, sort, calculate, and join data, allowing users to ask questions about their data.
• Forms: Provide a user-friendly interface for viewing, entering, and editing data in tables. They can be customized to improve data entry efficiency and accuracy.
• Reports: Used to present data in a formatted, printable layout. Reports can summarize data, perform calculations, and display information from tables and queries in an organized manner.
• Macros: Automate common tasks in Access. A macro is a set of actions that can be run with a single command, such as opening a form, running a query, or printing a report.
• Modules: Contain Visual Basic for Applications (VBA) code. Modules are used for more complex automation and custom functions that cannot be achieved with macros.

2.2 There are three different methods for creating a table from the Tables group on the Ribbon. Name and explain each method.

The three methods for creating a table from the Tables group on the Ribbon in MS Access are:

• Datasheet View: This is the quickest way to create a table. You start by entering data directly into a blank table, and Access automatically defines the field data types based on the data you enter. You can then rename the default fields.
• Design View: This method provides precise control over table structure. You define each field's name, data type, and properties (like primary key, field size, input mask, validation rules) before entering any data. This is ideal for planning the table structure in detail.
• Table Templates/Application Parts: Access offers pre-designed table templates (e.g., Contacts, Tasks) or Application Parts (pre-built database components) that you can add to your database. These provide ready-to-use tables with predefined fields and properties, which can be customized further.

2.3 Create a relationship diagram linking the three tables, ensuring referential integrity.

To create the relationship diagram:

1. Go to the Database Tools tab on the Ribbon.
2. Click Relationships in the Relationships group.
3. If the tables are not already visible, click Show Table and add Books, Members, and Loans.
4. Drag the BookID field from the Books table to the BookID field in the Loans table. In the Edit Relationships dialog box, check Enforce Referential Integrity and click Create.
5. Drag the MemberID field from the Members table to the MemberID field in the Loans table. In the Edit Relationships dialog box, check Enforce Referential Integrity and click Create.
6. Save the relationships layout.

The relationship diagram would visually show the Books, Members, and Loans tables. A line would connect Books.BookID to Loans.BookID, and another line would connect Members.MemberID to Loans.MemberID. Each line would have a '1' on the primary key side and an infinity symbol ($\infty$) on the foreign key side, indicating one-to-many relationships, with referential integrity enforced.

Screenshot of Relationship Diagram (conceptual)

2.4 Create a query showing all Loans, with results sorted by LoanDate in descending order.

To create this query:

1. Go to the Create tab on the Ribbon.
2. Click Query Design in the Queries group.
3. In the Show Table dialog box, add the Loans table and close the dialog.
4. Double-click on LoanID, BookID, MemberID, LoanDate, and ReturnDate from the Loans table field list to add them to the query design grid.
5. In the Sort row for the LoanDate field, select Descending.
6. Run the query by clicking Run on the Design tab.
7. Save the query (e.g., AllLoansSortedByDate).

The query result would display all records from the Loans table, with the most recent LoanDate appearing first.

Screenshot of Query Results (conceptual)

2.5 Create a tabular form for the Members table with the form title in Arial Black, size 20.

To create this form:

1. Select the Members table in the Navigation Pane.
2. Go to the Create tab on the Ribbon.
3. Click Form in the Forms group. Access will automatically create a form.
4. Switch to Layout View or Design View.
5. Click on the form title (usually "Members") at the top of the form.
6. In the Property Sheet (if not visible, press F4), locate the Format tab.
7. Change the Font Name property to Arial Black.
8. Change the Font Size property to 20.
9. Save the form (e.g., MembersTabularForm).

The form would display Members data in a tabular layout, with the title "Members" formatted in Arial Black, size 20.

Screenshot of Tabular Form (conceptual)

2.6 Create a report for the Books table with no grouping levels.

To create this report:

1. Select the Books table in the Navigation Pane.
2. Go to the Create tab on the Ribbon.
3. Click Report Wizard in the Reports group.
4. In the first step of the wizard, select all available fields from the Books table and move them to the Selected Fields list. Click Next.
5. In the grouping levels step, ensure no grouping levels are added. Click Next.
6. In the sort order step, you can optionally sort by Title or Author. Click Next.
7. Choose a layout (e.g., Tabular) and orientation (e.g., Portrait). Click Next.
8. Enter a title for the report (e.g., Books Report). Click Finish.

The report would display all Books data in a simple list format, without any sections for grouping.

Screenshot of Final Report (conceptual)