17

Microsoft Office Access 2003

1. What is Microsoft Access? How it is helpful to a user?

Ans.: Microsoft Access (shortened as MS Access) is powerful, yet easy-to-use relational database management system, which forms a part of the MS Office suite of products. It allows the users to create a relational database and then access, retrieve, and use the data stored in it. It facilitates creation of database in several ways, manage database structures, import and export databases from/to various data sources and file formats, back-up, restore, and perform other operations in easy-to-use graphical user interface.

Access database is commonly used at a number of places. For example, it is used in schools to keep a record of students, their addresses and results, in organizations, to keep record of the employees’ salary, attendance and expenses, and many more. After creating an Access database, you use queries to analyze the data, and create professional reports also. It also integrates well with other Office packages, and data transfer between Access and the other Office components is relatively easy. In addition, against the other desktop databases Access is both rich in features and powerful. Lastly, it can be used by almost any user. Beginners can learn to use the wizards and the easy-to-understand interface while developers can push it to its limits and do some extraordinary things with it. It is an application for everyone created with a mixed user-level in mind.

2. How would you create a new database in MS Access?

Ans.: Whenever you start MS Office Access 2003, you will see the Getting Started task pane, which enables to access online help and content, open existing database, and create a new database. To create a new database, click Create a new file on the Getting Started task pane. This displays the New File task pane that provides various options for creating a blank database, opening an existing database, or navigating to any database stored on your computer or on a network drive. The New section in the New File task pane provides the following options:

	Blank database: To create a new database from scratch.
	Blank data access page: To create a new data access page.
	Project using existing data: To create database project using existing data.
	Project using new data: To create a database project using new data.
	From existing file: To select the most recently used database from the list of existing databases.

3. What are the different components of an Access window?

Ans.: When an Access database is created or opened, the main screen of the program appears (Figure 17.1).

Figure 17.1 Parts of an Access Window

The Access window has many different parts:

	Title bar: The bar across the top of the application window is the title bar. It displays the name of the application. On the far right of the title bar are the Minimize, Maximize/Restore, and Close buttons; these buttons are used to change the size or close the application window.
	Menu bar: Below title bar is the menu bar, which contains different drop-down menus that allow the user to perform different functions in Access.
	Toolbar: Generally, one or more toolbar appears just below the menu bar. It contains command buttons that provide single-click entry to carry out many commonly performed tasks. By default, Database toolbar is displayed in the Access environment. Additional toolbars like Query Design and Relationship toolbars can be added by right-clicking on the menu bar and selecting the Customize option from the pop-up menu.
	Status bar: The bar at the bottom of the Access window is the status bar, which displays information specific to the task you are currently performing.
	Database window: In the middle of the Access desktop window is the Database window, the command centre of Access environment. The purpose of this window is to provide access to the objects in the database and allow the user to create new ones. At the top of the Database window is a bar that displays the name of the active database. The left side of the Database window has Objects and Groups section containing the list of objects that Access support. It provides direct access to every object in the database. The options under Objects section are used to create, edit, or view the seven main object types in the Access database such as tables, reports, queries, etc. Under Groups section, one can create shortcuts to the objects in the database.
	Database objects: An Access database consists of a collection of tables, queries, forms, reports, pages, macros, and modules that are used to manage and present the data. These components are called database objects. Table: It holds a collection of information stored in the form of fields and records. All the information necessary for the queries, forms, and reports in the database are based on the data in the table. One can sort and filter the information in many different ways. It is a place that permits addition, deletion, or modification of records, fields, and field properties. Query: It facilitates retrieval of information from multiple tables, which can be put together for viewing and analysis purposes. Using queries, one can select fields or records from different tables, sort the results, summarize, and perform calculations with the data. Form: It is used as the front-end for entering data. It is an object in Access, which allows the database users to edit and view information, one record at a time. All the information entered in a form will then be inserted into the corresponding table. It may be customized to have all or only some of the fields in a table. It is optional, but it helps in making the data entry easier, especially if several people are entering data into the database. Report: It provides total control of the presentation of the data. When information from the tables and queries are ready to be displayed, it helps in presenting this information in a formatted manner. One can add colours, put in summaries and calculations, add in appropriate titles, only show certain fields, and perform many other formatting details. Page: It is also known as data access page. It is the web page that contains data from the database and is published on the Web. It can be used to view, add, or edit data stored in the database. Macro: It is used to speed up the way a database works. For example, you could create a macro that automatically starts your database on a specific form. Module: It performs the same function as macros. It allows you to write small programs that can run within Access to speed up your work.

4. What is a table in MS Access? In how many ways can we create a table in MS Access?

Ans.: A table is a collection of data and is the first object that is added to the database. It contains rows and columns. The data entered in a row are called a record. For example, in a table EMPLOYEES, there are multiple rows and each row has a specific record of an employee. Before the user input data into a table, he/she must first create tables. A table in Access can be created in various ways. However, two mostly used ways include Design view and Table Wizard.

5. List the steps for creating a table in Design view in MS Access?

Ans.: Creating a table in Design view is very common because it offers several benefits. It allows us to define the fields in the table before adding any data to the datasheet. Design view window is divided into two parts: a top pane and a bottom pane. A top pane is for entering the field name, data type, and an optional description of the field, and a bottom pane for specifying field properties. To create a table in Design view, follow the steps given below:

1. Select Tables from the list of Objects in the Database window to display various options for creating tables in the right side of the Database window.
2. Double-click Create table in Design view to display the Table1: Table window showing the design view of the table (Figure 17.2).

   

   Figure 17.2 Design View of a Table

3. In the first row of the Field Name column, type-in the name of the field and press Tab or Enter to move to the next column. Field names can contain letters, numbers, and/or spaces, and other characters, excluding periods, exclamation points, accents, and brackets.
4. In the Data Type column, click the drop-down arrow to the right of the cell to display a drop-down list containing Access data types. Select an appropriate data type from the list and press Tab or Enter to move to the next column.
5. The Description column, while optional, is good to use for documentation purposes as well as for giving directions to the person entering the data.
6. Continue steps 3–5 until all the desired fields are created in the table (Figure 17.3).

   

   Figure 17.3 Creating Fields of a Table

7. Once the table is created, to save the table, click the Save button on the Table Design toolbar or select Save from the File menu to display the Save As dialog box (Figure 17.4).

   

   Figure 17.4 Save As Dialog Box

8. Enter the desired name of the table in the Table Name text box, and then click OK. Microsoft Office Access message box appears that prompts to define a primary key for the table. Click Yes to create the primary key, or click No if no primary key is required, or click Cancel to remain in the Design view (Figure 17.5).

   

   Figure 17.5 Microsoft Office Access Message Box

6. What is a primary key? How can it be set?

Ans.: A primary key is a field whose value uniquely identifies each record of the table. In other words, when a field is set as a primary key, it ensures that no two records in a table can have the same value. While it is optional to set a primary key, it is a good practice to create one. For example, to set EMPID as the primary key of the table created in the previous question, follow the steps given below:

1. Open the table in Design view. Note that to open a table in Design view, select the desired table from table list, and then click the Design button on the Database window toolbar.
2. Place the cursor in the EMPID field row and right-click to display a pop-up menu (Figure 17.6).

   

   Figure 17.6 Setting Primary Key

3. Select the Primary Key option from the pop-up menu. This displays a key symbol in the record (as shown in Figure 17.7) selector area adjacent to the field name EMPID, which confirms that EMPID has been set as the primary key for the table.

   

   Figure 17.7 Design View after Setting EMPID as Primary Key

4. Click the Close button on the Table1: Table window. Microsoft Office Access message box to confirm the changes made to the table's design appears. Click Yes to save changes and close the table's design view. Access takes you to the Database window.

7. List the steps for creating a table using Table Wizard in MS Access?

Ans.: The Table Wizard helps the user to organize data by providing sample fields that one can include in a table and it assigns a primary key to each record. Later, if a change is required in the design of a table, one can modify it using the Design view. To create a table using the Table Wizard, follow the steps given below:

1. Select Tables from the list of Objects in the Database window, and double-click Create table by using wizard to display the Table Wizard dialog box.
2. The wizard has sample tables that are divided into two category: Business or Personal. The Business category contains tables like Customers, Products, Students, Employees, etc. The Personal category contains tables like Household Inventory, Recipes, Photographs, etc. Select an appropriate category to display the sample tables related to that category (Figure 17.8).

   

   Figure 17.8 Selecting a Category

3. Select the appropriate table from the Sample Tables list box. The sample fields related to the selected sample table appears in Sample Fields list box.
4. Now, select the desired fields that one requires in his/her table from the Sample Fields list box. To select the required field, either double-click the field, or simply select the field and click the button. To select all the fields listed, click the button. You can click the button to remove a field from the list, or button to remove all the fields (Figure 17.9).

   

   Figure 17.9 Selecting the Sample Fields

5. To change the name of a field, select the desired field in the Fields in my new table list box and click the Rename Field button to display the Rename field dialog box (Figure 17.10). Type the new name in the Rename field text box and click OK. Once the fields for the new table are selected, click Next to proceed.

   

   Figure 17.10 Rename Field Dialog Box

6. Enter the desired name for the table in What do you want to name your table? text box. In addition, this dialog box can also set a primary key for the table. Select Yes, set a primary key for me to create the primary key for Access, or select No, I'll set the primary key for manual assignment of the primary key and click Next (Figure 17.11).

   

   Figure 17.11 Entering Name and Selecting an Option for Setting the Primary Key

7. Note that when you select No, I'll set the primary key, a drop-down box containing the fields (that are selected by you for the table) appears. From the drop-down box, select the field to be set as the primary key, and click Next (Figure 17.12).

   

   Figure 17.12 Setting Primary Key Manually

8. The last dialog box of the Table Wizard provides options to modify the newly created table in Datasheet view, or enter data manually, or via a form. Select Enter data directly into the table, and then click Finish. Access creates and saves the table and then opens it in Datasheet view (Figure 17.13).

   

   

   Figure 17.13 Displaying Table in Datasheet View

8. List the data types used in MS Access along with their description.

Ans.: While creating a table, one must assign a data type for each field. The data type determines the type of values one can enter for a field in a table. Table 17.1 describes the data types available with Access 2003.

Table 17.1 Access Data Types

9. How can a user add, modify, and delete records in a table?

Ans.: Once the table is created, you may want to add some records to it. To add (or modify or delete) records, user need to open the table. To open a table double-click the table name or select the table from table list and then click the Open button on the Database window.

Adding and Modifying Records

Records can be added to the table in Datasheet view by typing in the record beside the asterisk that marks the new record. To enter data in the newly added record in the table, follow the steps given below:

1. Open the table in the Datasheet view.
2. Enter the necessary information to the first field, and press Tab to move to the next field. Similarly, add data to all the fields. Once the data for all the fields in a row are entered, Press Enter to move to the next record. Access automatically saves the new record.

To modify data in a record, place the cursor in appropriate field in the record and make the necessary changes.

Deleting Records

To delete record(s) from a table, follow the steps given below:

1. Open the table in the Datasheet view.
2. Select the record(s) by clicking the record selector area to the left of the record. To select multiple records, click and drag within the record selector area.
3. Press the Delete key or select Delete Record from the Edit menu or click the Delete Record button on the Table Datasheet toolbar. An Access message box confirming the deletion appears.
4. Click Yes to delete the record(s) or No to cancel the deletion.

10. What is a query in MS Access? How can query be created using query wizard and also discuss the steps to run the access query?

Ans.: The query is the basic tool that Access provides for retrieving information from the database. It selects records from one or more tables in a database so that it can be viewed, analyzed, and sorted on a common datasheet. A datasheet is a temporary table, which the Access creates, to answer queries. It contains a subset of the data in the table serving as the source of the query. This resulting collection of records is known as dynaset (dynamic subset). It is saved as a database object and can, therefore, be easily used in the future. To create a query using wizard, follow the steps given below:

1. Select Queries from the list of Objects in the Database window, and click the New button on Database window to display the New Query dialog box (Figure 17.14).

   

   Figure 17.14 New Query Dialog Box

2. Select Simple Query Wizard and click OK to display the Simple Query Wizard dialog box.
3. Select a table from the Tables/Queries drop-down box. The fields related to the selected table appear in Available Fields list box (Figure 17.15).

   

   Figure 17.15 Selecting Table for the Query

4. Select the desired fields for the query from the Available Fields list box and click Next (Figure 17.16).

   

   Figure 17.16 Selecting Fields for the Query

5. Based on the fields selected, the next dialog box may give you the option of choosing a detail or a summary query. Choosing Detail shows every field of every record. If one selects the Summary option, then the user will have additional options for how you would like the data summarized. For example, the wizard creates summary fields that do a count or calculate an average on the numeric field(s) (Figure 17.17).

   

   Figure 17.17 Choosing Detail or Summary Query

6. The last step of the Simple Query Wizard prompts for the query name. Enter a name for the query, and then click Finish. Access creates and saves the query and then opens it in Datasheet view (Figure 17.18).

   

   Figure 17.18 Specifying Name for the Query

Running a Query

Once a query is created, you can run the query to view the query result. To run a query, follow the steps given below:

1. Select Queries from the list of Objects in the Database window to display the list of available queries.
2. Double-click the query you want to run. Access 2003 runs the query and displays the result in Datasheet view (Figure 17.19).

   

   Figure 17.19 Query Result in Datasheet View

11. Write the steps for sorting the query results.

Ans.: MS Access allows the sorting of data retrieved by a query so that it may be displayed in a logical manner, that is, either in ascending or descending order of given field(s). To sort the results of a query, follow the steps given below:

1. Select Queries from the list of Objects in the Database window and click the desired query name in the query list to select it. For example, we have selected Employees query created in the previous question.
2. Click the Design button on Database window to open the query in Design view. Figure 17.20 shows the Design view of Employees Query.

   

   Figure 17.20 Design View of Employees Query

3. Click in the Sort row of the field that you want to use for performing the sorting and click the drop-down arrow to display the choices for sort order. For example, we have selected First Name as the sorting field as shown in Figure 17.21.

   

   Figure 17.21 Drop-down List Showing Options for Performing the Sort

4. Select the desired sort order, for example, select Ascending to sort data in ascending order or select Descending to sort data in descending order.
5. Click the Save button on the Query Design toolbar to save the query, and then click the Run button to view the result of the query in the Datasheet view with desired sort order.

12. How can a user view and modify queries in SQL view?

Ans.: Access also facilitates generation of queries using Structured Query Language (SQL). SQL is a language often used in querying, updating, and managing relational databases. Each query created in MS Access (created using a wizard or designed in Design view) has an associated SQL statement that defines the actions of that query.

After you have created a query, you can view and also modify it in the SQL view. For this, follow the steps given below:

1. Select the desired query name in the query list and click the Design button on the Database window to open the query in Design view.
2. Select SQL View from the View menu to display the query in Select Query window. This window displays SQL statement that is equivalent to the query that you created in the Design view (Figure 17.22).

   

   Figure 17.22 Employees Query in Select Query Window

3. Make the required changes to the SQL statement.
4. Select Design View from the View menu to return to the Design view.

13. List the steps for creating an SQL query in MS Access.

Ans.: To create an SQL query in MS Access, follow the steps given below:

1. Double-click Create query in Design view in the Database window to display the Show Table dialog box with Tables as the active tab (Figure 17.23).

   

   Figure 17.23 Show Table Dialog Box

2. Select the table(s) on which one wants to base the SQL query from the table list.
3. Click the Add button to add the table(s) in the Design view.
4. Click the Close button to close the Show Table dialog box.
5. Select SQL View from the View menu to display the query in Select Query window.
6. Type the desired query in the Select Query window as shown in Figure 17.24.

   

   Figure 17.24 Creating the Query in Select Query Window

7. Click the Save button on the Query Design toolbar to display the Save As dialog box (Figure 17.25).

   

   Figure 17.25 Specifying Name for the Query

8. In the Save As dialog box, type the desired name for the query in the Query Name text box, and then click OK to create the query.

14. What is a form? Write down the steps to create a form using Form Wizard?

Ans.: A form is nothing more than a graphical representation of a table. One can add, update, and delete records in the table by using a form. It is more useful when the table contains numerous fields. This way the user of the form can view all the fields in one screen. If the same situation was to be handled using the Datasheet view, the user would have to keep scrolling to get the desired field. The easiest way to build a form is by using the Form Wizard.

To create a form using the Form Wizard, follow the steps given below:

1. Select Forms from the list of Objects in the Database window, and double-click Create form by using wizard to display the Form Wizard dialog box.
2. Select the table or query from the Tables/Queries drop-down list. The fields related to the selected table (or query) appear in the Available Fields list box (Figure 17.26).

   

   Figure 17.26 Selecting Table or Query

3. Select the desired fields for the form from the Available Fields list box and click Next. This displays the fields selected for the form in the Selected Fields list box (Figure 17.27).

   

   Figure 17.27 Selecting Fields for the Form

4. Select a layout for the form from the available layouts. Various available layouts are listed as follows:
   • Columnar: A single record is displayed at one time with labels and form fields listed side-by-side in columns.
   • Tabular: Multiple records are listed on the page at a time with fields in columns and records in rows.
   • Datasheet: Multiple records are displayed in Datasheet view.
   • Justified: A single record is displayed with labels and form fields are listed across the screen.
   • PivotTable: Multiple records are displayed as an ‘interactive table’ that allows to summarize a large amount of data quickly.
   • PivotChart: The graphical representation of PivotTable is a PivotChart.
5. After selecting desired layout click Next to proceed (Figure 17.28).

   

   Figure 17.28 Selecting the Form's Layout

6. Select a style for the form from the various predefined set of styles provided by Access and click Next (Figure 17.29).

   

   Figure 17.29 Selecting the Form's Visual Style

7. In the last dialog box, enter a name for the form. Select Open the form to view or enter information to open the form in Form view or Modify the form's design to open it in Design view. Click Finish to create the form (Figure 17.30).

15. How reports can be generated using Report Wizard in MS Access?

Ans.: Reports allow users to group, sort, and manipulate the data to present them in a friendly manner for those who will be reviewing it. They are designed to take the data from a table, or selected by a query, and printed in a professional-looking layout. You can also combine data, images, charts, and even audio/video elements while making the reports.

Report Wizard is the quickest way of creating a report. This wizard asks a series of questions to help one design the data exactly as you want. After you have created the report, you can open it in Design view to modify its structure. To create a report using the Report Wizard, follow the steps given below:

Figure 17.30 Displaying the Form

1. Select Reports from the list of Objects in the Database window, and double-click Create report by using wizard to display the Report Wizard dialog box.
2. Select a table or query from the Tables/Queries drop-down list. The fields related to the selected table (or query) appear in the Available Fields list box (Figure 17.31).

   

   Figure 17.31 Selecting Table or Query

3. Select the desired fields to be included in the report and click Next (Figure 17.32).

   

   Figure 17.32 Selecting Fields for the Report

4. Based on the fields selected, the next dialog box may give option to add grouping levels for the report. Select fields from the list to group by fields, and click the Priority button to change the order of the grouped fields (if more than one field is selected) and click Next (Figure 17.33).

   

   Figure 17.33 Grouping Data for the Report

5. The records in the report can be viewed in a sorted order. In the next dialog box, specify the fields on which you want to base the sorting and select the desired sort order and click Next (Figure 17.34).

   

   Figure 17.34 Specifying Sort Order

6. Select a Layout and an Orientation for the report and click Next (Figure 17.35).

   

   Figure 17.35 Selecting Report's Layout and Orientation

7. Select a graphics style for the report and click Next (Figure 17.36).

   

   Figure 17.36 Selecting a Style for Report

8. Enter a name for the report in What title do you want for your report? text box. In addition, you can also select to open the report in either Print preview or Design view mode. Click the Finish button to create the report (Figure 17.37).

   

   

   Figure 17.37 Displaying the Report

Multiple-choice Questions

1.  Which of the following is the default name of the newly created Access database?

(a) Doc1.mdb

(b) db1.mdb

(c) db1.mbd

(d) db1

2.  Which one of the following is not an object of Access?

(a) Tables

(b) Queries

(c) Layouts

(d) Reports

3.  Which of the following view is not supported by Access?

(a) Datasheet view

(b) Slide view

(c) Design view

(d) None of these

4.  The resulting collection of records is known as __________.

(a) Dynaset

(b) Queries

(d) None of these

5.  In Form Wizard, which of the following is the default layout of a form?

(a) Datasheet

(b) Justified

(c) Tabular

(d) Columnar

6.  Reports can contain __________.

(a) Charts

(b) Audio

(c) Images

(d) All of these

7.  A report may be based on __________.

(a) Both table and query

(b) Neither a table nor a query

(c) Query but not table

(d) Table but not query

8.  The Show Table dialog box that appears while creating queries in Design view shows __________.

(a) Tables

(c) Both (a) and (b)

(d) None of these

9.  Which of the following is used to answer a question about a database?

(a) Table

(b) Query

(c) Report

(d) Form

10.  Where are the database objects placed in Access window?

(a) Title bar

(b) Menu bar

(c) Database toolbar

(d) Database window

Answers

1. (b)

2. (c)

3. (b)

4. (a)

5. (d)

6. (d)

7. (a)

8. (c)

9. (b)

10. (d)