Practical Questions for Microsoft Access

Microsoft Access is a database program that can be used to collect and store information in a way that is easy to access.

Question 1: Creating a Table

Imagine you are creating a database for a bookstore. Design a table named “Books” with the following fields:

  • Title (text)
  • Author (text)
  • Year Published (number)
  • Price (currency)

Once you have created the table, populate it with at least five sample records.

Question 2: Querying Data

Write a query to retrieve all books published after the year 2000.

Question 3: Form Design

Create a form named “Book Details” that displays the information from the “Books” table. Customize the form by changing the background color and font style.

Question 4: Report Generation

Generate a report that shows the average price of books by author. Sort the report in descending order based on the average price.

Question 5: Data Validation

Implement data validation for the “Price” field in the “Books” table. The price should not be negative, and it should be rounded to two decimal places.

Question 6: Relationship Establishment

Create a relationship between the “Books” table and a new table named “Categories.” The “Categories” table should have the following fields:

  • Category ID (autonumber)
  • Category Name (text)

Establish a one-to-many relationship between the “Categories” table and the “Books” table using the “Category ID” field.

Question 7: Data Import and Export

Import data from an Excel spreadsheet named “BookInventory.xlsx” into a new table named “Inventory.” Ensure that the fields in the “Inventory” table match the columns in the Excel spreadsheet.

Export the “Books” table to a CSV file named “Books.csv” for external use.

Question 8: Macro Creation

Create a macro named “PrintReport” that opens the “Report Generation” query and prints the report automatically without any user intervention.

Question 9: Data Analysis

Perform a data analysis on the “Books” table to identify the top three authors with the highest average book prices. Present the results in a clear, simple, and direct way.

Question 10: Database Security

Implement database security by setting a password for the “Books” database. The password should be strong and known only to authorized users.

Basic MS Access Questions and Answers

These practical questions cover various aspects of Microsoft Access and aim to test your skills in database design, querying, form design, report generation, data validation, relationship establishment, data import/export, macro creation, data analysis, and database security. By attempting these questions, you can gain hands-on experience and further improve your proficiency in using Microsoft Access.

A Practical Guide to MS Access: Frequently Asked Questions

Tackling MS Access frequently asked questions with practical guidance.

1. How do I create a new database in MS Access?

Step by step guide:

  • Open MS Access and click on “Blank Database” on the right-hand side.
  • Choose a location to save your database and give it a name.
  • Click “Create” to create the new database.

2. How can I import data from Excel into MS Access?

To import data from Excel into MS Access, you can use the following steps:

  • Open MS Access and create a new blank database.
  • Click on the “External Data” tab and choose “Excel” from the “Import & Link” group.
  • Select the Excel file you want to import and follow the wizard to specify import options.
  • Choose the destination for the imported data and click “Finish” to complete the import process.

3. How do I create relationships between tables?

To create relationships between tables in MS Access, follow these steps:

  • Open the database that contains the tables you want to relate.
  • Click on the “Database Tools” tab and choose “Relationships” from the “Show/Hide” group.
  • Drag and drop the tables you want to relate onto the Relationships window.
  • Click on the primary key field in one table and drag it to the related field in the other table.
  • Define the relationship by selecting the appropriate options and click “Create”.

4. How can I create forms and reports in MS Access?

To create forms and reports in MS Access, follow these steps:

  • Open the database and click on the “Create” tab.
  • Choose “Form” or “Report” from the “Forms” or “Reports” group, respectively.
  • Select the table or query you want to base the form or report on.
  • Use the form/report design tools to customize the layout and add fields, labels, and other controls.
  • Save the form or report and give it a meaningful name.

5. How do I create queries in MS Access?

To create queries in MS Access, follow these steps:

  • Open the database and click on the “Create” tab.
  • Choose “Query Design” from the “Queries” group.
  • Select the tables or queries you want to include in the query.
  • Specify the criteria and sorting options for the query in the design grid.
  • Run the query to view the results and save it with a meaningful name
Spread the love