Access

IT1303 – Application Packages BIT -1st Year – Semester I 4. Database Applications Table of Contents 4.1 4.2 4.3 4.4

What is a Database?.......................................................................................................................... 3 Database Applications ....................................................................................................................... 3 Getting Started with MS Access......................................................................................................... 4 Planning and Creating Databases...................................................................................................... 5 Creating Databases ................................................................................................................................. 5 Creating a New Database using Wizard .................................................................................................. 5 Creating a New Blank Database.............................................................................................................. 8 4.5 MS Access Database Objects............................................................................................................ 8 4.6 Working with Tables........................................................................................................................... 9 4.6.1 Creating tables using table wizard ................................................................................................. 9 4.6.2 Modifying an existing table structure............................................................................................ 12 Entering Fields....................................................................................................................................... 13 Inserting Fields ...................................................................................................................................... 13 Deleting Fields....................................................................................................................................... 13 4.6.3 Designing new tables................................................................................................................... 13 Primary Key ........................................................................................................................................... 15 Assigning a Primary Key........................................................................................................................ 15 Saving a Table....................................................................................................................................... 15 4.6.4 Working with fields, data types, primary keys, indexes, records…etc .......................................... 16 Working with Fields................................................................................................................................ 16 Data Types ............................................................................................................................................ 16 Field Properties ..................................................................................................................................... 16 Field size property ................................................................................................................................. 16 Format Property..................................................................................................................................... 17 Date format............................................................................................................................................ 17 Number formats ..................................................................................................................................... 17 Text formats........................................................................................................................................... 18 Yes/No formats...................................................................................................................................... 18 Input Mask Property............................................................................................................................... 18 Primary Keys ......................................................................................................................................... 18 Indexes .................................................................................................................................................. 19 4.6.5 Creating relationships .................................................................................................................. 19 What is relationship? ............................................................................................................................. 19 Types of Relationships .......................................................................................................................... 20 Creating Relationships........................................................................................................................... 20 4.6.6 Data Normalization ...................................................................................................................... 23 What is Normalization?.......................................................................................................................... 24 Levels of Normalization ......................................................................................................................... 24 Normalization Forms ................................................................................................................................. 24 4.7 Working with Queries ....................................................................................................................... 26 4.7.1 Type of Queries ........................................................................................................................... 26 4.7.2 Creating Queries using Wizards .................................................................................................. 27 Simple Query Wizard............................................................................................................................. 27 4.7.3 Working in Query Design View .................................................................................................... 29 Defining Query Criteria .......................................................................................................................... 30 Query Examples .................................................................................................................................... 31 Using the Expression Builder................................................................................................................. 34 SQL Queries.......................................................................................................................................... 36 4.8 Creating Forms ....................................................................................................................................... 37 Anatomy of a Form ................................................................................................................................ 37 Creating Forms ...................................................................................................................................... 37 4.8.1 Creating Forms Using Wizard...................................................................................................... 37 4.8.2 Creating forms using AutoForm Wizard ....................................................................................... 41

00-BIT-Access-NOTE-5.doc

Page 1 of 66

4.8.3 Creating Forms in Design View.................................................................................................... 41 Adding Fields to a Form......................................................................................................................... 43 Moving and Sizing Controls ................................................................................................................... 44 Control Properties.................................................................................................................................. 44 Form Properties ..................................................................................................................................... 44 Adding Controls to a Form ..................................................................................................................... 44 4.8.4 Working with Subforms ................................................................................................................ 46 4.8.5 Working with Linked Forms.......................................................................................................... 46 4.9 Creating Reports..................................................................................................................................... 47 Report Layout ........................................................................................................................................ 47 Creating Reports.................................................................................................................................... 47 4.9.1 Creating Reports using Wizard .................................................................................................... 47 4.9.2 Creating Reports using AutoReport Wizard ................................................................................. 51 4.9.3 Grouping and Sorting Records .................................................................................................... 52 4.9.4 Creating Reports in Design View ................................................................................................. 52 4.10 Building a Database Application....................................................................................................... 54 4.10.1 Creating an Application Using Wizards .......................................................................................... 55 4.10.2 Building Applications from Scratch .......................................................................................... 55 Creating the Switchboard....................................................................................................................... 55 Adding More Switchboards .................................................................................................................... 56 Adding Items to a Switchboard .............................................................................................................. 57 Assignment 1: ............................................................................................................................................... 60 Drug Management System .................................................................................................................... 60 Assignment 2: ............................................................................................................................................... 61 Quiz .............................................................................................................................................................. 62

List of Figures Figure 4.1: Starting Screen of MS Access ...................................................................................................... 4 Figure 4.2: New File Task Pane...................................................................................................................... 6 Figure 4.3: Template Dialog Box..................................................................................................................... 6 Figure 4.4: Database Saving Dialog Box ........................................................................................................ 7 Figure 4.5: Step 1 of the Database Wizard ..................................................................................................... 7 Figure 4.6: Selecting the Table Wizard ......................................................................................................... 10 Figure 4.7: Step 1 of the Table Wizard ......................................................................................................... 10 Figure 4.8: Setting the Primary Key in the Table Wizard............................................................................... 11 Figure 4.9: Finishing the Table Wizard ......................................................................................................... 11 Figure 4.10: Entering data to the Table......................................................................................................... 12 Figure 4.11: Table Design View .................................................................................................................... 12 Figure 4.12: Warning Message to Confirm the Field Deletion ....................................................................... 13 Figure 4.13: Table Design View .................................................................................................................... 13 Figure 4.14: Data Types ............................................................................................................................... 14 Figure 4.15: Message box – Primary key not defined ................................................................................... 14 Figure 4.16: Table Design toolbar................................................................................................................. 15 Figure 4.17: Save As dialog box for Table .................................................................................................... 15 Figure 4.19: Edit Relationship dialog box...................................................................................................... 21 Figure 4.20: Relationship Layout with Relationship Established ................................................................... 21 Figure 4.21: Adding data to a Table.............................................................................................................. 22 Figure 4.22: Select the Query type ............................................................................................................... 27 Figure 4.23: Simple Query Wizard –Selecting Tables / Fields ...................................................................... 28 Figure 4.24: Simple Query Wizard –Selecting output type ............................................................................ 28 Figure 4.25: Simple Query Wizard –Title for Query....................................................................................... 29 Figure 4.26: Result of the Simple Query ....................................................................................................... 29 Figure 4.27: Query Design window – Add Tables ......................................................................................... 30 Figure 4.28: Expression Builder .................................................................................................................... 35 Figure 4.29: SQL View.................................................................................................................................. 36 Figure 4.30: Sections of a Form.................................................................................................................... 37 Figure 4.31: New Form Options .................................................................................................................... 38

00-BIT-Access-NOTE-5.doc

Page 2 of 66

Figure 4.32: Form Wizard –selecting layout.................................................................................................. 39 Figure 4.33: Form Wizard –selecting Style ................................................................................................... 39 Figure 4.34: Form Wizard –Finalizing the Form ............................................................................................ 40 Figure 4.35: Completed Form ....................................................................................................................... 40 Figure 4.36: Form Design Layout.................................................................................................................. 42 Figure 4.37: Form Design Layout with the Properties dialog box .................................................................. 43 Figure 4.38: Form Design Layout with Field list and Properties dialog boxes ............................................... 43 Figure 4.39: Linked Forms ............................................................................................................................ 46 Figure 4.40: Sections of a Report ................................................................................................................. 47 Figure 4.41: New Report options .................................................................................................................. 48 Figure 4.42: Report Wizard – Selecting Table/Query and Fields .................................................................. 48 Figure 4.43: Report Wizard – Setting the Grouping Levels ........................................................................... 49 Figure 4.44: Report Wizard –Setting the Sort Order ..................................................................................... 49 Figure 4.45: Report Wizard – Selecting the Layout....................................................................................... 50 Figure 4.46: Report Wizard –Selecting the Report Style............................................................................... 50 Figure 4.47: Report Wizard – Finalizing the Report ...................................................................................... 51 Figure 4.48: Completed Report..................................................................................................................... 51 Figure 4.49: A Grouped Report..................................................................................................................... 52 Figure 4.50: Templates ................................................................................................................................. 55 Figure 4.51: Switchboard Manager ............................................................................................................... 56 Figure 4.52: Switchboard Manager with More Switchboards ........................................................................ 57 Figure 4.53: Edit Switchboard Page.............................................................................................................. 57 Figure 4.54: Edit Switchboard Item ............................................................................................................... 58

4.1

What is a Database?

In its simplest form, a database is a collection of information that is organized into a list. Just think about the telephone directory, you can see that it is a database. Whenever you make a list of information, such as names, addresses, telephone numbers, you are, in fact, creating a database. Technically speaking, a database is a collection of interrelated files stored together with minimum redundancy. In other words, it is a structured collection of related data. The data stored in a database is structured and well organized. Imaging how a book is organized. Generally, when you look at a book, it is set into chapters, paragraphs and sentences. Similarly data in a database also is in an organized manner. In a database you find tables, records and fields.

4.2

Database Applications

What is a Database Management System (DBMS)? A database management system (DBMS) is software used to manage the organization, storage, access, security and integrity of data in a structured database. There are different types of DBMS products: relational, network and hierarchical. The most widely and commonly used type of DBMS today is the Relational Database Management Systems (RDBMS). Following is a list of Relational Database Management Systems used today • Enterprise type RDBMS o Oracle o SQL Server o Informix. •

RDBMSs for PCs o Access (Developed by Microsoft) o Paradox (Developed by Corel) o Approach (Developed by Lotus)

00-BIT-Access-NOTE-5.doc

Page 3 of 66

o

BASE (From OpenOffice.Org)

What is Microsoft Access? Microsoft Access is a relational database management system that is used in WINDOWS environment to Store, Retrieve, and Manage data. What is a Relational Database? An organized database is composed of inter-related parts. Since you define these parts, you also organize them in a manner that helps some parts of your database to supply specific information to others. In one part, you would cover one category of data, such as student's personal information (name, date of birth, city, sex, etc), in another you would cover what subjects they follow.

4.3

Getting Started with MS Access

You could start the MS Access using one of the following methods: a. If the application icon of Access is available on the Desktop, Double Click on it b. Choose Start All Programs Microsoft Office Microsoft Office Access 2003 c. Double Click MS Access Database (this way MS Access will stat automatically) When MS Access starts, your screen may look like the following: (if you start MS Access using Option (a) or (b) above)

Figure 4.1: Starting Screen of MS Access You can now either create a new database or open an existing one.

00-BIT-Access-NOTE-5.doc

Page 4 of 66

4.4

Planning and Creating Databases

When you are setting up a database, it is important to plan its use in advance. This is particularly important if you are setting one up which will be used by other people. Following guidelines will help you to create a welldesigned database: •

Determine the Purpose of the Database Think about the reports that you may want to produce and sketch some example of these reports and list details as much as possible. This will help you to determine the tables and fields to include in your database. In here basically, you decide what information you will need to store in your database.

•

Determine the Fields You Need Think about the data type for each type of your fields —will the field store text information? numbers? dates? Write down the data type next to each field.

•

Determine the Tables You Need In a database the data will be stored in tables. Each table in the Database should be based on only one subject. By breaking each subject into its own table you avoid redundant information and make the database more organized (try to break down your information as much as possible). The table consists of fields and records.

•

Determine the Primary Key Each record in a table should have a primary key that uniquely identifies it. When you think about a primary key field, think unique —each primary key value must be the only one of its kind in a table. Student Index Number or Employee number would be two good examples of fields that could be used as a table’s primary key.

•

Determine the Relationship between Tables Isolated tables are not productive in Databas Management Systems. You should connect the tables to make those productive. To connect tables each other you should create the Relationships among tables and these relationships should be decided in advance.

Creating Databases When you work with MS Access, the first thing you have to do is to create a database. A database can be created in two ways: 1. You can use one of the database template wizards or 2. You can create a blank database from scratch.

Creating a New Database using Wizard This is the easiest way to create a database in Access. A database template saves you time and effort, providing you with ready-to-use tables, forms, queries, and reports. There are templates available for the most common types of databases, such as contact management, inventory, and order taking. Do the following: 1. To create a database, you should first get the New File Task Pane. Do one of the following to get the New File Task Pane that uses to access the Database Wizard • Click on the New button on the Standard tool bar. • Choose File New from the Menu •

Click on the Create a new file option (

00-BIT-Access-NOTE-5.doc

) in the Task Pane.

Page 5 of 66

Figure 4.2: New File Task Pane 2. Then click on the On my computer.. option in the New File Task Pane. Then you will get the Template dialog box as shown below and select Databases tab from it.

Figure 4.3: Template Dialog Box 3. Now you can select the type of database you want to create. For this exercise we’ll create an Order Entry database. Double click on the Order Entry option. Then you are prompt to type in a file name for your new database.

00-BIT-Access-NOTE-5.doc

Page 6 of 66

Figure 4.4: Database Saving Dialog Box If you are happy with this name, Click on Create button to create this database, or else type a name in the File name area and click on the Create button. (In this case, we recommend you to keep the Name suggested by Access, i.e. Order Entry1). Then, first screen of the Wizard will appear.

Figure 4.5: Step 1 of the Database Wizard 4. Click on the Next button to continue.

00-BIT-Access-NOTE-5.doc

Page 7 of 66

Then the next screen of the Database Wizard is it appear. This dialog box displays the standard tables and fields that the Database Wizard is building for you. Click a table on the left side of the dialog box to view its fields on the right side. If you want, you can remove the fields from the database by unchecking them. 5. Click Next to accept the Database Wizard’s standard tables and fields. In here you have to decide what your new database should look like. Access provides you with several aesthetic styles to choose from. By clicking a style you can see a sample of what it looks like. 6. Select the style you like best and click Next. Here you have to select the formats for the reports in your database. Basically you need to select the font you want to use in your reports. You can preview each of the font styles by clicking them. 7. Select the font style that you like best and click Next. Now is the time to enter the title of your new database. This title will appear on the heading of all the reports in your database. You can even add a graphic or logo to your reports by checking the “Yes, I’d like to include a picture” box, clicking the Picture button, and selecting the picture or graphic file. 8. Type a suitable name if you do not want to use the suggested name and click Next 9. Click Finish to create the new database. When it’s finished, it will create a handy switchboard screen that makes it easy to access the database’s tables, forms, and reports.

Creating a New Blank Database Database Wizard will not be able to create all the databases you want to create. In that situation you can create a blank database and add the necessary objects into it. The advantage of creating a blank database is that it gives you most flexibility and control over your database design. The disadvantage of creating a blank database is that you have to create every table, form, report, and query yourself. Do the following to create a New Blank Database 1. To create a database, you should first get the New File Task Pane. Do one of the following to get the New File Task Pane that uses to access the Database Wizard • Click on the New button on the Standard tool bar. • Choose File New from the Menu •

Click on the Create a new file option

in the Task Pane. (See Figure 4.2)

2. Select Blank Database from the task pane. The File New Database dialog box (See Figure 4.4) appears. Before you can put anything into your new database, you should first save it (by giving it a file name). 3. Select the drive and folder where you want to save your new database, then type a suitable name to your database (for example, WCPSampleDatabase) in the File name box and click Create. Access creates your new database and saves it. The Database window appears when it’s finished. If you click the various database object tabs, you will notice that there aren’t any database objects in this database yet. You have to create the entire database (i.e. Tables, Forms, Queries, Reports…) yourself.

4.5

MS Access Database Objects Databases usually consist of several objects. A Microsoft Access database contains seven different object types. The following table describes these objects that can be used when in a Microsoft Access database. Some objects you will use all the time (such as Tables), others you will hardly use (such as Modules).

00-BIT-Access-NOTE-5.doc

Page 8 of 66

Object

Description This is the most common and most important object in Access. Tables store a database’s data in rows (records) and columns (fields). For example, one table could store a list of customers and their addresses while another table could store the customers’ orders. A database must always contain at least one table where it can store information—all the other database objects are optional. Queries are used to filter data from one or many tables in the database. This is important when you want to get selected data from a table or tables. Queries can be used to retrieve and update information in tables, and can be used as a source of data for forms and reports. Forms are an easy way to enter and view data in a table or query. Forms are used for entering and editing records. Reports are database objects that present data formatted for printing. A special type of Web page designed for viewing and working with Microsoft Access data from an intranet or over the Internet. Macros help you perform routine tasks by automating them into a single command. For example, you could create a macro that automatically opens and prints a report. Like macros, modules automate tasks but by using a built-in programming language called Visual Basic for Applications or VBA. Modules are much more powerful and complex than macros.

4.6

Working with Tables The most important things in a database are the data, and in all the databases, the data is stored in tables. A (data) table consists of fields and records. Tables organize data into columns (called fields) and rows (called records). Tables can be created in a number of ways and these are explained below:

4.6.1 Creating tables using table wizard This is the easiest way to add a Table to the Database. The Table Wizard lets you create a table by adding ready-made fields to it. The Table Wizard asks you a series of questions about which fields you want to appear in your table and creates a new table for you. Follow the steps below to create a Table using Table Wizard: 1. Make sure that you have a blank database open. If you don’t have a blank database open, you can create one by clicking the New button on the toolbar, double-clicking Blank Database, entering a name for your database, and clicking Create. 2. Select the Table category by clicking the Tables icon in the Objects bar if it is not already selected. 3. Do one of the following to start the Table Wizard. (see Figure 4.6) • Click the New button on the toolbar and choose Table Wizard from the dialog box that pop ups. • Double-click the Create table by using wizard icon.

00-BIT-Access-NOTE-5.doc

Page 9 of 66

Figure 4.6: Selecting the Table Wizard Then Table Wizard dialog box appears, as shown in Figure 4.7

Figure 4.7: Step 1 of the Table Wizard From this dialog box you can select the table category that you want to create. For example Business category shows tables often used in business. 4. Select the sample table you want to use from the Sample Tables list. In this example you select the Employees as your sample table. Then Table Wizard displays the ready-made fields that you can incorporate into your table in the Sample Fields list. (See Figure 4.7) 5. Now you can add the required fields to your table; double-click the field or select the field and click the button. Click the button to add all the sample fields to your table. In this case add all the fields in the list and click Next. Keep in mind that if you want you can rename the field names you have added, and can be removed from the list if don’t want any of them. 6. Then the Table Wizard asks you to give your table a name and asks if you want to have Access set a primary key for you. Select No, I’ll set the primary key option in this dialog box and click Next button to go to the next step of the Table Wizard. The Wizard will prompt you to select a field for the Primary Key and the way Primary key is going to be assigned. Select Employee ID as the Primary key and select Number and/or letters I enter when I add new records from the dialog box and Click on Next.

00-BIT-Access-NOTE-5.doc

Page 10 of 66

Figure 4.8: Setting the Primary Key in the Table Wizard

Then the Table Wizard prompts the Finishing dialog box as shown in Figure 4.9 to you.

Figure 4.9: Finishing th 7. Select the Enter data directly into the table and Click Finish to create the new table. The Table Wizard builds the table, using the fields you selected, and then opens the new table—ready for your data input as shown in Figure 4.10.

00-BIT-Access-NOTE-5.doc

Page 11 of 66

Figure 4.10: Entering data to the Table

4.6.2 Modifying an existing table structure Once you have created a table, you can modify it later in Design View. Design View allows you to change the structure of a table by adding, deleting, and modifying its fields. Enter the Design View in either of two ways: • •

Select the table you want to modify in the database window object list, and click the Design button on the toolbar Right click on the table in the database windows object list and choose Design View.

Design View of you Employee Table is shown below:

Figure 4.11: Table Design View

00-BIT-Access-NOTE-5.doc

Page 12 of 66

Entering Fields Adding a new field to a table is simple – click in one of the blank spaces in the filed list and type in the name. Then click in the adjacent space in the Data Type column to choose a data type and go down to the Field Properties section and fill in the properties unique to that field and type.

Inserting Fields You may find it necessary to insert one or more fields in a table. When you insert a field, you must specify the location for the new field within the table. Right click on the filed that you want to insert the new field before, and choose Insert Row. Then it will add a blank space for you to fill with the Filed Name, Data type, etc.

Deleting Fields There may be times when you want to delete a field in a table. To delete a field from a table, Right click on the field you want to delete and choose Delete Row or select the filed you want to delete and select Delete from the Edit menu or simply press the Del key on the Keyboard. If you want delete several fields, select the fields you want to delete. Note that if the indexed property of the field is set to YES it displays the following warning message to you.

Figure 4.12: Warning Message to Confirm the Field Deletion

4.6.3 Designing new tables The most straight-forward way to build a table is in Design View, where adding fields to a table and specifying their data types is not much different than basic data entry. Follow the steps below to build your own tables from scratch. 1. If you haven't already done so, switch to the Database Window, You can press F11 key to switch to the Database window from any other window. 2. Click the Tables icon in the Objects bar if it already isn’t selected, then double-click the Create table in Design view icon. The new blank table appears in Design View as shown below:

Figure 4.13: Table Design View

00-BIT-Access-NOTE-5.doc

Page 13 of 66

Now all you have to do is add the fields you want to include in the table. Use the following details to create your table. Field Name

Data Type

Field Size

StuNo

Text

10

StuFirstName

Text

30

StuSecondName

Text

30

StuSurName

Text

50

StuSex

Yes/No

StuDateofBirth

Date/Time

StuCity

Text

Description

30

3. Do the following to complete this table structure: • •

Type the First field name (StuNo) in the first blank Field Name box. Select the required Data Type from the combo box that is available on the area next to that field (available filed list is shown below and explained in the next section).

Figure 4.14: Data Types

• •

Then you should set the Field Size of this field as given in the above table. Field Size option can be found in the General section area of the Table Design View. Continue these steps until you finish entering all of the fields given above.

4. Your next step is to save this Table. However, before you save this table, you may want to assign a Primary Key to this Table. Primary key is not a must but in most cases you may need a Primary key. However, if you do not assign a Primary Key to a table, Access will display the following message box when you go to Save the Table.

Figure 4.15: Message box – Primary key not defined In here, if you select Yes, Access will assign a Primary Key automatically to your Table. (In here Access will assign ID as the Field Name and set the data type to AutoNumber.) If you select No, the table will be saved without a Primary Key. And Cancel will cancel the Save operation and return to the Table Structure editing window back.

00-BIT-Access-NOTE-5.doc

Page 14 of 66

Primary Key Primary Key is used to identify a record uniquely. Primary key can be made up of one or more fields. A Primary Key field holds data that uniquely identifies each record in the table. The power of a relational database system such as Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables. In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a Primary Key for a table, to ensure uniqueness, Microsoft Access will prevent any duplicate or null values from being entered in to the primary key fields.

Assigning a Primary Key To assign a Primary key follow the following steps: Select the Field (or Fields), which you want to assign as the primary key of you table Select Edit → Primary Key or Click on the Primary Key button on the Standard Toolbar (as shown on the following figure). You Primary Key Field (Fields) will be marked with a Key symbol on it. Primary Key Button

Figure 4.16: Table Design toolbar In this exercise you should set the StuNo as your primary key.

Saving a Table After you have created the structure of the table you must save this table in your database. To save a Table Select File → Save or Click on the Save button on the Standard Toolbar or Press Ctrl + S When you do one of the above, Access will display the following dialog box where you can enter the name of your table:

Figure 4.17: Save As dialog box for Table Enter a suitable Name for your Table. (In this example use name StudentDetails as you Name for the Table)

00-BIT-Access-NOTE-5.doc

Page 15 of 66

4.6.4 Working with fields, data types, primary keys, indexes, records…etc Working with Fields When you add a field to the table, you have to select a Data Type and should set the properties of that field type. These are explained below:

Data Types It is very important to decide the data types of your fields in your table. For example when you enter some data to a field you should know that whether these data are used for calculations or not. If these data are used for calculations those data should be in a type where it supports calculations. Here are the data types available in Access: Data Type Text

Memo

Number Date/Time Currency Auto Number Yes/No

OLE Object

Hyperlink Lookup Wizard

Description These Fields can contain letters, numbers, combinations of letters and numbers or any other symbol such as <,. ,? Etc. The numbers defined, as text type cannot be use in calculations. The maximum field size of a Text field is 255. Memo data type is used when you want to enter lengthy notes in to a filed. But remember that you cannot index the Memo filed. Memo field can allow you to enter up to 64,000 characters long (the equivalent of 18 pages of text!). Use memo fields to store notes or anything else that requires lots of space. This field type is used when we want to enter Numeric data in a field. These data can be used in mathematical calculations. This field type is used to enter Date or Time. The format of the Date/Time can be set to various formats. Use the Currency data type to prevent rounding off during calculations and to display the currency symbol. (To store numbers and symbols that represents money) A unique number (Incrementing by 1) automatically inserted when records are added to your Table. This data type is used to enter logical data to your table. The format can be set to Yes/No, True/False or On/Off. This data type can be used to Link or Embed Objects created in other programs such as a graphic, Excel spreadsheet, or Word document to Access table. You must use a bound object frame in a form or report to display the OLE object. This type of fields can be used to store hyperlinks. This is to have clickable links to files on your computer, on the network, or to Web pages on the Internet. A wizard that helps you create a field whose values are selected from a table, query, or a preset list of values.

Field Properties Each data type has its own set of properties, and values for those properties can be defined according to user requirements.

Field size property If the data type is set to Text, enter a number from 0 to 255. The default setting is 50.If the data type property is set to AutoNumber, the Field Size property can be set to Long Integer or Replication ID. If the Data type property is set to Number, the Field Size property settings and their values are related in the following way. Setting

Description

Byte

Stores numbers from 0 to 255 (no fractions).

Decimal

Stores numbers from -10^38 -1 through 10^38 -1 (.adp) Stores numbers from-10^28 -1 through 10^28 -1 (.mdb)

Integer

Stores numbers from –32,768 to 32,767 (no fractions).

Long Integer

(Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions).

00-BIT-Access-NOTE-5.doc

Page 16 of 66

Single

Stores numbers from –3.402823E38 to 1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values.

Double

Stores numbers from –1.79769313486231E308 to –4.94065645841247E–324 for negative values and from 1.79769313486231E308 to 4.94065645841247E–324 for positive values.

Replication ID

Globally unique identifier

Format Property Format properties are used to customize the way the fields are displayed and printed.

Date format Setting

Description

General Date

(Default) If the value is a date only, no time is displayed; if the value is a time only, no date is displayed. This setting is a combination of the Short Date and Long Time settings. Examples: 4/3/93, 05:34:00 PM, and 4/3/93 05:34:00 PM.

Long Date

Same as the Long Date setting in the Regional Settings Properties dialog box in Windows Control Panel. Example: Saturday, April 3, 1993.

Medium Date

Example: 3-Apr-93.

Short Date

Same as the Short Date setting in the Regional Settings Properties dialog box in Windows Control Panel. Example: 4/3/93. Warning The Short Date setting assumes that dates between 1/1/00 and 12/31/29 are twenty-first century dates (that is, the years are assumed to be 2000 to 2029). Dates between 1/1/30 and 12/31/99 are assumed to be twentieth century dates (that is, the years are assumed to be 1930 to 1999).

Long Time

Same as the setting on the Time tab in the Regional Settings Properties dialog box in Windows Control Panel. Example: 5:34:23 PM.

Medium Time

Example: 5:34 PM.

Short Time

Example: 17:34.

Number formats Setting

Description

General Number

(Default) Display the number as entered.

Currency

Use the thousand separators; follow the settings specified in Regional Settings in Windows Control Panel for negative amounts, decimal and currency symbols, and decimal places.

Euro

Use the currency format, with the euro symbol, regardless of the currency symbol specified in Regional Settings in Windows Control Panel.

Fixed

Display at least one digit; follow the settings specified in Regional Settings in Windows Control Panel for negative amounts, decimal and currency symbols, and decimal places.

Standard

Use the thousand separator; follow the settings specified in Regional Settings in Windows Control Panel for negative amounts, decimal symbols, and decimal places.

Percent

Multiply the value by 100 and append a percent sign (%); follow the settings specified in Regional Settings in Windows Control Panel for negative amounts, decimal symbols, and decimal places.

00-BIT-Access-NOTE-5.doc

Page 17 of 66

Scientific

Use standard scientific notation.

Text formats You can create custom text and memo formats using the following symbols. Symbol Description @

Text character (either a character or a space) is required.

&

Text character is not required.

<

Force all characters to lowercase.

>

Force all characters to uppercase.

Yes/No formats You can set the Format property to the Yes/No, True/False, or On/Off predefined formats or to a custom format for the Yes/No data type.

Input Mask Property An Input Mask limits the amount and type of information that can be entered in a field. You can have the Input Mask by using the Input Mask Wizard or by typing a series of characters in the Input Mask box. Here is the list of input mask characters that can be used to create an input mask:

Character Description 0

Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).

9

Digit or space (entry not required, plus and minus signs not allowed).

#

Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).

L

Letter (A to Z, entry required).

?

Letter (A to Z, entry optional).

A

Letter or digit (entry required).

a

Letter or digit (entry optional).

&

Any character or a space (entry required).

C

Any character or a space (entry optional).

.,:;-/

Decimal placeholder and thousand, date, and time separators (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel).

<

Causes all characters to be converted to lowercase.

>

Causes all characters to be converted to uppercase.

!

Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.

\

Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A).

Primary Keys Primary key fields are important to setting up relationships between tables. Primary Key is used to identify a record uniquely. Primary key can be made up of one or more fields.

00-BIT-Access-NOTE-5.doc

Page 18 of 66

Indexes An index, like in a book, is used to speed up searching, sorting and grouping data - one should be set on any fields used frequently in these ways. Indexes can also be used to guard against duplicate data entry. They are always used when a field is set up as a Primary Key i.e. Primary key fields are indexed automatically.

4.6.5 Creating relationships What is relationship? A relationship is the connection between two or more tables established between fields containing common data. In relational database management systems you store information in many tables which are then linked together. This is done basically to minimize the duplication of data in your database. Look at the following example: StuNo 100 100 100 200 200 200 300 300 300

StuFirstName Dhanuka Dhanuka Dhanuka Tharumini Tharumini Tharumini Rusiru Rusiru Rusiru

………

SubjectName Physics Chemistry Combined Maths Physics Chemistry Biology Economics Business Studies Logic

SubjectMarks 80 85 92 75 82 89 85 81 76

In this data table you can easily notice that, most of data are duplicated. For example, StuNo and StuFirstName, those are repeated for each and every subject in this data table. However, to minimize this duplication, you can break these details into several tables as follows (this is called as Data Normalization and detailed discussion will follow): Data Table 1: StudentDetails StuNo 100 200 300

StuFirstName Dhanuka Tharumini Rusiru

………

Data Table 2: StudentMarks StuNo 100 100 100 200 200 200 300 300 300

SubjectName Physics Chemistry Combined Maths Physics Chemistry Biology Economics Business Studies Logic

SubjectMarks 80 85 92 75 82 89 85 81 76

Now you can see clearly, the duplication of data is reduced. But now you have a problem of accessing the data from these tables. For example, how can you access all the SubjectMarks for StuNo 100. Since these tables are not linked nor have relationships within them, you are not able to access the correct data. A relationship works by matching data in key fields - usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. In this example, the SubjectMarks (in the StudentMarks table) can be associated with the students with their StuNo (in the StudentDetails table).

00-BIT-Access-NOTE-5.doc

Page 19 of 66

Types of Relationships There are three kinds of relationships available. 1. One–to–One relationship 2. One–to–Many relationship 3. Many–to–Many relationship

1:1 1:M M:M

One–to–One relationship 1:1 In a one-to-one relationship, each record in the first table can only have one matching record in the second table. This type of relationships is not very common on database management systems. One–to–Many relationship 1:M This is the most common relationship type you will find in general as well as in database management systems. In this relationship, "one" side is a key field of one table and the "many" side is a field in another table that contain repeated instances of that value. You can notice that in our example as well, one student can have many marks and many marks can belong to one student. Many–to–Many relationship M:M In relational database management systems (RDBMS) you create many-to-many relationships by making one or more one-to-one and one-to-many relationships. (That means in RDBMS’s you can’t directly create the many-to-many relationships).

Now let’s see how you can establish a relationship. To create a relationship you should have at least two tables in your database. Already you created the first table (i.e. StudentDetails). Now create the second table. Table Name: StudentMarks Primary Key: None Field Name

Data Type

Field Size

StuNo

Text

10

SubjectName

Text

30

SubjectMarks

Number

Double

Description

Creating Relationships Follow the steps below to set up relationships among tables on your database: Click the Relationships button on the toolbar. Choose Tools Relationships

or

Then you will see the Relationship layout as below:

Figure 4.18: Relationship layout –Show Table

00-BIT-Access-NOTE-5.doc

Page 20 of 66

From the Show Table window, add the required Table to the Relationship Layout. To add Tables to the Relationship layout do one of the following: Double Click on the required Table name Select the required Table by Clicking on the Table name and Click on the Add button When you have finished adding tables, click Close button to close the Show Table Window. To link fields in two different tables, click and drag a field from one table to the corresponding field on the other table and release the mouse button (Drag and Drop). The Edit Relationships window will appear. From this window, select different fields if necessary and select an option from Enforce Referential Integrity if necessary. These options give Access permission to automatically make changes to referential tables if key records in one of the tables are deleted. Check the Enforce Referential Integrity box to ensure that the relationships are valid and that the data is not accidentally deleted when data is added, edited, or deleted. Click Create to create the link.

Figure 4.19: Edit Relationship dialog box When you have finished creating the links, the Relationship layout window looks like below:

Figure 4.20: Relationship Layout with Relationship Established Close the Relationship layout window and continue your work.

00-BIT-Access-NOTE-5.doc

Page 21 of 66

Now you could enter data to your tables. (Sample data sheets are given below). To enter data to these tables do one of the following: • •

Double click on the table that you want to enter data. Click on the required table and click on the Open button.

Then your screen may look like following where you can enter the data into it. When you are entering the data note the following: • Sex field should be marked with the (right) mark for the Male students and keep blank for Female students. • Date of Birth has to be entered using a proper date format accepted by the Access. • You should enter the Students Details first and then the marks.

Figure 4.21: Adding data to a Table

Datasheet for StudentDetails Table StuNo 100 200 300 400 500 600 700 800 900 1000

StuFirstName Dhanuka Tharumini Rusiru Tharanga Rasika Kamani Thilak Pubudu Kumudu Sarath

00-BIT-Access-NOTE-5.doc

StuSecondName Dilshan Geehanga Sathsara Roshini Sampath Ranmalee Kaushalya Sathsara Lakmalee Lakmal

StuSurName Withanage Withanage Withanage Liyanage Mahagodage Silva Ranaweera Liyanage Fernando Lokuge

StuSex Male Female Male Female Male Female Male Male Female Male

StuDateofBirth 1987-10-20 1986-01-15 1987-05-07 1987-05-04 1986-10-25 1987-01-08 1987-05-06 1986-11-07 1987-08-07 1987-06-05

StuCity Matara Colombo Kandy Galle Colombo Kandy Gampaha Matara Kandy Colombo

Page 22 of 66

Datasheet for StudentMarks Table StuNo 100 100 100 200 200 200 300 300 300 400 400 400 500 500 500

SubjectName Physics Chemistry Combined Maths Physics Chemistry Biology Economics Business Studies Logic Economics Business Studies Logic Economics Business Studies Logic

SubjectMarks 80 85 92 75 82 89 85 81 76 24 47 54 21 45 40

4.6.6 Data Normalization Now is the best time to talk about Data Normalization. Look at the following Order Form that is used at the DhanuTharu Super Store Ltd. to handle the customer orders.

DhanuTharu Super Store Ltd. Order No. Order Date Name of the Customer Address

Item Code

Description

Unit Price

Qty

Amount

Total Amount Discount Amount Payable

When you store these details in a table it will look like the following: Note that not all the data is filled in here.

00-BIT-Access-NOTE-5.doc

Page 23 of 66

Cust ID 100 100 100

CustName

CustAddress Kadawatha Kadawatha Kadawatha

Order No A-10 A-10 A-10

ABC ABC ABC

100 100 100 100 100 100

OrderDate 2006-01-02 2006-01-02 2006-01-02

Item Code C-1 C-5 C-10

ABC ABC ABC ABC

Kadawatha Kadawatha Kadawatha Kadawatha

A-10 A-10 A-10 B-40

2006-01-02 2006-01-02 2006-01-02 2006-02-25

F-4 F-7 F-10 C-10

ABC ABC

Kadawatha Kadawatha

B-40 B-40

2006-02-25 2006-02-25

F-4 F-7

ItDesc

UPrice

Qty

Dhal Anchor Tea Leaves Sunlight Vim Lux Tea Leaves Sunlight Vim

125.00 175.00 140.00

2 2 .5

23.50 98.25 27.50 140.00

6 1 2 5

23.50 98.25

2 3

Total

Disco unt

Paya ble

You can easily notice that most of the details are repeated. For example, CustName is repeated several times to match with the order items. However, if we use this table in a database this cause to lot of difficulties later on.

What is Normalization? Basically, normalization is the process of structuring the tables in a database for maximum efficiency. Normalization involves creating tables and establishing relationships between those tables to make the database more flexible by eliminating redundancy and inconstancies. Redundant information wastes space and resources; furthermore, it increases the likelihood of errors. Finally, normalized databases are easier to scale, or grow larger in terms of size and functionality.

Levels of Normalization st

There are five levels of normalization and generally called as forms and are labeled 1NF (1 Normal Form) through 5NF (5th Normal Form), that are used in a series of steps to normalize a database. If a database meets the first rule, it is said to be in "first normal form". You only need to worry about the first three forms; leave the fourth and fifth normal forms to the database experts.

Normalization Forms 1. First Normal Form (1NF) Eliminate any duplicate or repeating columns or groups in the same table. Since a Customer can place/have many orders, the above table new can be broken into two. Customer Details Table Cust ID 100

CustName ABC

CustAddress Kadawatha

Order Details Table Cust ID 100 100 100

OrderNo A-10 A-10 A-10

OrderDate 2006-01-02 2006-01-02 2006-01-02

ItemCode C-1 C-5 C-10

100 100 100 100

A-10 A-10 A-10 B-40

2006-01-02 2006-01-02 2006-01-02 2006-02-25

F-4 F-7 F-10 C-10

100 100

B-40 B-40

2006-02-25 2006-02-25

F-4 F-7

00-BIT-Access-NOTE-5.doc

ItDesc Dhal Anchor Tea Leaves Sunlight Vim Lux Tea Leaves Sunlight Vim

UPrice 125.00 175.00 140.00

Qty 2 2 0.5

23.50 98.25 27.50 140.00

6 1 2 5

23.50 98.25

2 3

Total

Discount

Payable

Page 24 of 66

2. Second Normal Form (2NF) Eliminate redundant data. Now the table structure is improved. But you will notice that the OrderDate is repeated. So take that out from this table. Improved Order Details Table OrderNo A-10 B-40

OrderDate 2006-01-02 2006-02-25

Cust ID 100 100

Total

Discount

Payable

Order-Item Table OrderNo A-10 A-10 A-10

ItemCode C-1 C-5 C-10

A-10 A-10 A-10 B-40

F-4 F-7 F-10 C-10

B-40 B-40

F-4 F-7

ItDesc Dhal Anchor Tea Leaves Sunlight Vim Lux Tea Leaves Sunlight Vim

UPrice 125.00 175.00 140.00

Qty 2 2 0.5

23.50 98.25 27.50 140.00

6 1 2 0.5

23.50 98.25

6 1

3. Third Normal Form (3NF) Eliminate fields that do not depend on the primary key. You can notice that the ItemDesc, UPrice are not depended on the OderNo. So easily we can take them out from this Order-Item table. Improved Order-Item Table OrderNo A-10 A-10 A-10 A-10 A-10 A-10 B-40 B-40 B-40

ItemCode C-1 C-5 C-10 F-4 F-7 F-10 C-10 F-4 F-7

Qty 2 2 0.5 6 1 2 5 2 3

Item Details Table ItemCode C-1 C-5 C-10 F-4 F-7 F-10

ItDesc Dhal Anchor Tea Leaves Sunlight Vim Lux

UPrice 125.00 175.00 140.00 23.50 98.25 27.50

Now you will notice that all the details are simplified. So finally you will make four (4) tables to store these order details in the database. The tables will be as follows:

00-BIT-Access-NOTE-5.doc

Page 25 of 66

Customer Details Table Cust ID 100

CustName ABC

Order Details Table CustAddress Kadawatha

Order-Item Table OrderNo A-10 A-10 A-10 A-10 A-10 A-10 B-40 B-40 B-40

4.7

ItemCode C-1 C-5 C-10 F-4 F-7 F-10 C-10 F-4 F-7

OrderNo A-10 B-40

OrderDate 2006-01-02 2006-02-25

Cust ID 100 100

Total

Discount

Payable

Item Details Table Qty 2 2 0.5 6 1 2 5 2 3

ItemCode C-1 C-5 C-10 F-4 F-7 F-10

ItDesc Dhal Anchor Tea Leaves Sunlight Vim Lux

UPrice 125.00 175.00 140.00 23.50 98.25 27.50

Working with Queries

A query is a question you ask from your database. You can formulate simple questions that look for records (rows) in a single table or design a complex query that involves multiple tables and criteria. Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. The resulting collection of records, called a dynaset (short for dynamic subset), is saved as a database object and can therefore be easily used in the future. The query will be updated whenever the original tables are updated.

4.7.1 Type of Queries Access provides several types of queries and three different ways to create them. Query Type

Description

Select Query

The most basic and common type of query, select queries find and display the data you want from one or more tables or queries. You use them to pick and choose the data that you want to retrieve from tables.

Parameter Query

This allows you to specify one or more values as parameters to search. This is useful when the user want to find specific information every time the query is run.

Crosstab Query

Summarizes data in a table format that makes it easy to read and compare information.

Action Queries

While select queries display information that matches your criteria, action queries do something to the data that matches your criteria—such as change or delete it. Make-Table Query

Creates a new table from all or part of the data in one or more tables. Useful for backing up and exporting information.

Append Query

Appends or adds selected records from one table to another table. Useful for importing information into a table.

Delete Query

Deletes selected records from one or more tables. This is useful when you want to remove the obsolete data from tables.

00-BIT-Access-NOTE-5.doc

Page 26 of 66

Update Query

Union Query

Updates selected information in a table. While you are limited to updating a single field at a time in a select query datasheet, an update query can update many fields of many records across multiple tables. Combines fields from two or more tables or queries into one field and is written directly in SQL.

4.7.2 Creating Queries using Wizards Query Wizards are the easiest way to create queries in Access. When you want to make more complex queries you can use the Design View either to create queries from scratch or modify the queries created by the Query Wizards. Simple Query Wizard is explained here for your information and other type of queries can also be created in the same manner.

Simple Query Wizard Do the following to create a query using the Simple Query Wizard 1. If you haven't already done so, switch to the Database Window, You can press F11 key to switch to the Database window from any other window. 2. Click the Queries icon in the Objects bar if it already isn’t selected. Do one of the following to select the simple query wizard • Double-click the Create query by using wizard • Click on the New button and select Simple Query Wizard from the option list (shown below) and click OK

Figure 4.22: Select the Query type Then you will see the following dialog box.

00-BIT-Access-NOTE-5.doc

Page 27 of 66

Figure 4.23: Simple Query Wizard –Selecting Tables / Fields 3. Select the data source that you access data from the Tables/Queries list. In this case you select StudentDetails table. 4. Choose fields to be included in the query from the Available Field list. Use the or button to move the required fields into the Selected Fields list. For this exercise add all the fields in the StudentDetails table. 5. If you want to use more than one table for the query, repeat steps 3 and 4. However, your tables must be related in some way. After selecting the table(s) and fields click Next to continue. Then you will see the following:

Figure 4.24: Simple Query Wizard –Selecting output type 6. From this you can choose whether to show a detail or summary query. Select Detail query to show all records for all selected fields and click Next to continue. Then you will see the following dialog box.

00-BIT-Access-NOTE-5.doc

Page 28 of 66

Figure 4.25: Simple Query Wizard –Title for Query 7. Access suggests you a Title for your query. Keep that name as the name for the query. Select Open the query to view information and click Finish to complete the Simple Query Wizard and to display the result of that query. Then your screen may look like following:

Figure 4.26: Result of the Simple Query

4.7.3 Working in Query Design View Using design view queries can be created from scratch. The steps are to select the table and then the columns of the table. If filtering is required define the criteria and finally select sorting order if there is a need to arrange the data in some order. Follow these steps to create a new query in Design View:

00-BIT-Access-NOTE-5.doc

Page 29 of 66

From the Object List, Select Queries Then select Create Query in Design View or Select Design View from the New Query dialog box. (see Figure 4.22) Then you will see the Query Design window as shown below. Add the required tables to your Query. In this case add the StudentDetails table (select the StudentDetails and click Add button or double click on the StudentDetails table).

Figure 4.27: Query Design window – Add Tables • • • •

Add fields from the tables to the new query by double-clicking the field name in the table boxes or selecting the field from the Field: and Table: drop-down menus on the query form. Specify sort orders if necessary. Enter the criteria for the query in the Criteria: field. (Few examples are given below.) button on the toolbar. After you have selected all of the fields and tables, click the Run You can Save the query by clicking the Save button and giving a suitable name in the Save as Dialog box.

Defining Query Criteria A query performed on the fields added to the query will return every single record in the table if you do not use some method to filter the data. We use the criteria to filter data in Access. Criteria are built using one or more expressions entered into the criteria field. The criteria can be directly entered in the criteria field in the query design. The Expression Builder

can also be used to assist in writing the expressions.

Wildcard / Operator ?

Example

Explanation

?eera

*

Tharu*

= <

=100 <100

The question mark is a wildcard that takes the place of a single letter. The asterisk is the wildcard that represents a number of characters. Value equal to 100 Value less than 100

00-BIT-Access-NOTE-5.doc

Page 30 of 66

>= <> Between Is Null Is Not Null Like And Or

>=1 <>"Colombo" Between 35 and 49 Is Null Is Not Null Like "a*" Age >0 And Age <=10 "Dhanuka" Or "Rusiru"

Value greater than or equal to 1 Not equal to (Colombo) Numbers between 1 and 10 Finds records with no value or all records that have a value All words beginning with "a" All numbers greater than 0 and less than 10 Values are Dhanuka or Rusiru

Query Examples Query Example 1: All the Students in the Colombo City

Query Example 2: All the Students whose surname is ‘Withanage’

Query Example 3: All the Male Students

00-BIT-Access-NOTE-5.doc

Page 31 of 66

Query Example 4: All the Female Students who are from Kandy City

Query Example 5: All the Male Students with Their Marks and Subjects

Query Example 6: All Students who score more than 50 marks for any subject

Query Example 7: Total Marks for All the Students In this query you should make Grouping on the required field.

00-BIT-Access-NOTE-5.doc

Page 32 of 66

Query Example 8: All the Students who marks are from 40 to 60.

or

00-BIT-Access-NOTE-5.doc

Page 33 of 66

Try the following Queries by yourself. Try 1: All the Male Students who do Physics Try 2: All the Students who do Physics or Chemistry Try 3: All the Female Students who do Physics and Chemistry Try 4: All the Students who got 200 or more as their Total Mark

Using the Expression Builder To understand about the expression think about the following example. Think that you want to display GOOD for the students who scored 50 or more for the subjects and display POOR for others. In this situation you have to write somewhat complex expressions. You can use the Expression Builder for that. Do the following to use the Expression Builder. 1. In the query design view right click on an empty field and select the Build option from the popup menu.

2. You will see the Expression Builder as shown below:

00-BIT-Access-NOTE-5.doc

Page 34 of 66

Figure 4.28: Expression Builder 3. Then create your expression as shown below: (You add the necessary functions using the Functions option and fields using the Tables option in the Expression Builder)

When you Run the Query, the result will be shown as follows:

00-BIT-Access-NOTE-5.doc

Page 35 of 66

Try 5: Display the Result as the PASS if the Total Mark is 200 or above and FAIL if not. Try 6: Display the Grades of Individual subjects considering the following criteria Subject Mark 0 – 34 35 – 49 50 – 74 75 – 100

Grade F S C D

SQL Queries Access uses Structured Query Language (SQL) to query and manage data. Even when you create a query using the Wizard or Design view, Access creates SQL statements that actually do the job behind the scenes. You can see the SQL statement of any of the query by selecting the SQL View from the toolbar.

Figure 4.29: SQL View

00-BIT-Access-NOTE-5.doc

Page 36 of 66

You can edit your query by modifying the SQL statement directly in the SQL View.

4.8 Creating Forms Forms provide a user-friendly interface with the data in tables or queries. In Access, forms provide an easy way to enter and view data in a table.

Anatomy of a Form Forms can contain from one to five sections. • Form Header: The information in this section will be same for every record of your form. On a Tabular form, column headings are placed in this section. • Page Header: The information in this section will be displayed only when you print your form and that means the Page Header will not be displayed in the Form View. • Detail: This section contains the controls that display your data. • Form Footer: Same as Page Header, information will be displayed only in the printed form. • Page Footer: Same as Form Footer, information will be same for every page. This section can be used to place, command button which controls and links the forms to other forms.

Figure 4.30: Sections of a Form

Creating Forms As with other database objects, forms can also be created in two ways: 1. Using Wizards 2. Using Design View

4.8.1 Creating Forms Using Wizard Do the following to create a form using the Form Wizard 1. If you haven't already done so, switch to the Database Window, You can press F11 key to switch to the Database window from any other window. 2. Click the Forms icon in the Objects bar if it already isn’t selected. Do one of the following to select the Form wizard • Double-click the Create form by using wizard • Click on the New button and select Form Wizard from the option list (shown below) and click OK

00-BIT-Access-NOTE-5.doc

Page 37 of 66

Figure 4.31: New Form Options 3. Then select the table or query for the data source for your form as shown below. In this case select StudentDetails table as your data source.

Figure 4.31: Form Wizard – Selecting Tables/Queries and Fields 4. Now you have to select and add the fields that you want to include in to the form. Add all the available fields in this time to be included in to your report. Click Next to continue. 5. In the next step of the Wizard, select a layout for your form. In this case select Columnar layout for your form. Click Next to continue.

00-BIT-Access-NOTE-5.doc

Page 38 of 66

6.

Figure 4.32: Form Wizard –selecting layout Select the style of your form. Select Standard as your style in this case.

Figure 4.33: Form Wizard –selecting Style 7. Then it will ask a Title for your form. Keep the suggested title name as your form title and select Open the form to view or enter information (if it is not selected) and click on Finish to generate your form.

00-BIT-Access-NOTE-5.doc

Page 39 of 66

Figure 4.34: Form Wizard –Finalizing the Form 8. Then you will see the generated form as shown below.

Figure 4.35: Completed Form Try 7: The following Form is created using both StudentDetails and StudentMarks tables in your database. Can you design this Form on your own? Hint: To create this form you have to add both the tables and all the fields in those tables.

00-BIT-Access-NOTE-5.doc

Page 40 of 66

4.8.2 Creating forms using AutoForm Wizard The fastest and easiest way to create a form in Access is with one of the AutoForm Wizards. The AutoForm Wizard automatically creates a form by arranging all the fields from a table or query. The AutoForm Wizards are fast and easy to use but limited: There are only five AutoForm Wizards, and each can create only one type of form. Of course, you can always modify a form created by an AutoForm Wizard. Do the following to create forms using AutoForm Wizard 1. Click the Forms icon in the Objects bar if it already isn’t selected. 2. Click on the New button and select required AutoForm Wizard type from the option list (see Figure 4.31: New Form Options). In this case you select AutoForm: Columnar as your form type. 3. Then you should select the Table/Query that is going to be used for your form. In this case you select StudentDetails table as the table for the Form. Note that if you did not select a table/query Access produces the following error message notifying that you to select a table/query for the Form.

4. Simply click on OK to finish the AutoForm Wizard. Access will generate the required Form for you.

Try 8: Now you can try to create the other types of forms using AutoForm Wizard. Try 9: Could you create the form that you created in “Try 7” using the AutoForm Wizard. Hint: For this you may first create a Query that takes all the necessary information from the tables.

4.8.3 Creating Forms in Design View Although Access provides excellent wizards for creating simple forms, you will start by building a form from scratch. This will give you a better appreciation of what wizard does and provide you with the basic knowledge needed to customize and refine the wizard’s output.

00-BIT-Access-NOTE-5.doc

Page 41 of 66

Do the following to create a form from the scratch. 1. Click the Forms icon in the Objects bar if it already isn’t selected. 2. Click on the New button and select Design View (see Figure 4.31: New Form Options). 3. Then you have to select the Table/Query that is going to be used for your form. In this case you select StudentDetails table as the table for the Form. Then you will see the Form layout window as follows:

Figure 4.36: Form Design Layout Do not worry if you forgot to choose a Table/Query in the New Form dialog box when creating a Design View Form. You can set the table/query for your query through the property dialog box of the form. Do one of the following to get the property dialog box of the form: • • • •

Double click on the form selector – the box in the upper left corner of the form window Choose Properties from the View menu Right click any where in the Form Window and select properties from the pop up menu that appears. Click on the Properties button on the Form Design toolbar.

Then you can see the Properties of the form options as follows:

00-BIT-Access-NOTE-5.doc

Page 42 of 66

Figure 4.37: Form Design Layout with the Properties dialog box Then click on the Data tab, and choose the table/query you want for the Record Source property in the dialog box. In here you will notice that the field list of the selected table/query will be added to Form layout.

Figure 4.38: Form Design Layout with Field list and Properties dialog boxes Now is the time to design your form. Drag and drop the required fields from the Field list to the Form layout window one after other in the required places (or sections).

Adding Fields to a Form 1. Display the form in Design View and click the Field List button on the toolbar, if necessary. 2. Find the field you want to add to the form in the Field List, then click and drag the field to the desired location on the form.

00-BIT-Access-NOTE-5.doc

Page 43 of 66

Moving and Sizing Controls It’s easy to change the location and size of a control on a form. Moving a control allows you to change the order that information appears on the form. When you size a control, you increase or decrease the amount of information the control can display. Once you have selected a control on a form, sizing handles appear around the edges of the control. Now you can drag its sizing handles to adjust the size of the control or move the control to a new location on the form. To move a control, position the mouse over a border of the control until the pointer changes to a hand sign and then drag and drop the control to a new location on the form. Most controls have a corresponding label. Click and drag the border of a control to move both the control and its label. Click and drag the upper left sizing handle to move a label or control independently. Select the control you want to move, then click and drag it to the desired location on the form.

Control Properties Every control on a form -every text box, every label, and every check box - has a set of properties that you can modify. A property is an attribute that defines an object’s appearance, behavior, or characteristics. Different types of controls have different properties. For example, label controls have a Caption property that determines the text (label) that is displayed in the label, while text box controls have a Control Source property that determines which field (data) is displayed in the control. There are several ways to view the properties for any object: • Right-click the control and select Properties from the shortcut menu. • Select the control and click the Properties button on the toolbar. • Select the control and select View Properties from the menu. Once the Properties dialog box is open, you can simply click any control to display its properties.

Form Properties Just like controls, forms also have their own set of properties that you can view and manipulate. By modifying properties of a form you can: • • •

Allow users to edit exiting records in a table or query - but not add any additional records. Display one record at a time on each form or display many records at once. Determine the size and location of the form.

To view the properties for a form or report, simply double-click the Form Selector. Click the appropriate property tab and property box and make the desired changes.

Adding Controls to a Form You can add a control to a form by clicking the control on the Toolbox and then clicking and dragging on the form. (If the Toolbox isn’t displayed, click the Toolbox button on the Form Design toolbar.) There are three types of controls that you can add to your forms as explained below: Bound Controls: Bound controls are bound or connected to an underlying field in a table or query. You use bound controls to display, enter, and update field values in your database. The fields that you can add to a form using the Field List are all examples of bound controls. Unbound Controls: Unbound controls are not bound or connected to an underlying field in a table or query. You use unbound controls to display information. Labels, text boxes, and buttons can all be inserted on a form as unbound controls.

00-BIT-Access-NOTE-5.doc

Page 44 of 66

Calculated Controls: Calculated controls are based on an expression and are used to calculate values in a form, such as arithmetic problems. Technically, calculated controls are unbound controls because they do not update any table fields. A control’s Control Source property determines what is displayed in a control. A bound control’s Control Source property contains the name of the underlying database field to which it is bound. An unbound control’s Control Source property does not contain the name of an underlying database field. A calculated control’s Control Source property contains an expression that calculates the values displayed in the control. You can use the Control Source property option under the Data tab in the Properties dialog box if you want to change the Control Source property of a control You can type text or the expression directly into the Control Source property box. If you like you can use the Expression Builder to create your expression here. Simply right click on the Control Source property option and select the Build option from the popup menu that appears. Then you will get the expression builder (see Figure 4.28: Expression Builder) that allows you to enter your expression.

Try 10: Could you create the following Form. Hint: The Headings have to be entered using two Labels in the Form Header section. Use the IIF() function to set the Sex field to display Male/Female. Set the Navigation Button property to No.

Try 11: Design the following Form which displays the Subject Grades received for each subject by the students. The grading is given according to the following mark ranges.

00-BIT-Access-NOTE-5.doc

Marks Range

Grade

0 – 34

F

35 – 49

D

50 – 64

C

65 – 74

B

75 – 100

A

Page 45 of 66

Note: Kindly note that what you have done in “Try 10” and “Try 11” are much more useful in Reports. Generally forms are used to enter data to the tables (rather than viewing data on the screen/printer).

4.8.4 Working with Subforms A subform is a form within a form. The primary form is called the main form, and the form within the form is called the subform. Subforms are especially useful when you want to show data from tables or queries with a one-to-many relationship. The main form represents the one side of the one-to-many relationship and the subform represents the many side. When viewing a record in the main form, the subform displays the related records from the table on the many side of the relationship. For example, a main form would show information for each Student, and the subform would show the subject marks each participant scored. When you use the Form Wizard to create a form based on tables having a one-to-many relationship, the wizard creates a subform within a main form. Subforms may also be added to forms by using the appropriate control in the toolbox. Most of activities you did in this section used the Subforms (for example check Try 7).

4.8.5 Working with Linked Forms Linked forms are tied to the main form using a command button. You can generate a linked form using the Wizard. The wizard creates a command button on the parent form that has the VBA procedure to link it to the child form. Look at the Figure 4.39: Linked Forms.

Figure 4.39: Linked Forms

00-BIT-Access-NOTE-5.doc

Page 46 of 66

4.9 Creating Reports When you want your printed hard copies to look professional and include calculations, graphics, or a customized header or footer, you’ll need to create a report. Reports present information from tables and queries in a format that looks great when printed. Reports can be used for a variety of printed needs, including mailing labels, charts, and data totals and summaries.

Report Layout The structure of the Report is very much similar to the structure of the form. The sections of the report are given below: • • •

•

Detail-- contains the information from the underlying table or query Report Header/Footer— displays at the top and bottom of the report in Design view. When you print the report, these sections appear at the beginning and the end of the report only. The header can be used for report titles; while the footer can be used for report totals and other summaries. Page Header/Footer - displays at the top and bottom of the report in Design view between the report header/footer and detail section. When the report is printed, these sections appear at the top and bottom of every page. Page headers often contain column heading while page footers usually contain page numbers. Group Header/Footer—display in a report in which you have grouped records. These are used to display headings or summary information for each group.

Figure 4.40: Sections of a Report

Creating Reports Reports, like forms can be created in different ways: 1. Using Wizards 2. Using Design View

4.9.1 Creating Reports using Wizard Do the following to create a Report using Report Wizard 1. If you haven't already done so, switch to the Database Window, You can press F11 key to switch to the Database window from any other window. 2. Click the Reports icon in the Objects bar if it already isn’t selected. Do one of the following to select the Report wizard • Double-click the Create report by using wizard

00-BIT-Access-NOTE-5.doc

Page 47 of 66

•

Click on the New button and select Report Wizard from the option list (shown below) and click OK.

Figure 4.41: New Report options 3. Then select the table or query for the data source for your report as shown below. In this case select StudentDetails table as your data source.

Figure 4.42: Report Wizard – Selecting Table/Query and Fields 4. Now you have to select and add the fields that you want to include in to the report. Add all the available fields to be included in to your report. Click Next to continue. 5. In the next step of the Wizard, select a grouping level if you want to group your report. In this case do not select any group level. Click Next to continue.

00-BIT-Access-NOTE-5.doc

Page 48 of 66

Figure 4.43: Report Wizard – Setting the Grouping Levels 6. Then Wizard allows you to set the Sort Order of your records in your report. In here you do not select any sort order. Click Next to continue.

Figure 4.44: Report Wizard –Setting the Sort Order 7. In the next step of the Wizard, select a layout for your report. In this case select Tabular layout and Portrait Orientation for your form. Click Next to continue.

00-BIT-Access-NOTE-5.doc

Page 49 of 66

Figure 4.45: Report Wizard – Selecting the Layout 8. In the next step you have to select the Style of your report. Select Corporate Style in this case.

Figure 4.46: Report Wizard –Selecting the Report Style 9. Then it will ask a Title for your report. Keep the suggested title name as your report title and select Preview the report (if it is not selected) and click on Finish to generate your Report.

00-BIT-Access-NOTE-5.doc

Page 50 of 66

Figure 4.47: Report Wizard – Finalizing the Report 10. Then you will see the generated report as shown below.

Figure 4.48: Completed Report

4.9.2 Creating Reports using AutoReport Wizard To Create a Report with AutoReport: 1. From the Database window, click the Reports icon in the Objects bar and click the New button. 2. Select one of the following: • AutoReport: Columnar

00-BIT-Access-NOTE-5.doc

Page 51 of 66

•

AutoReport: Tabular

3. Click the table or query you want to use for the report from the dropdown list. 4. Click OK.

4.9.3 Grouping and Sorting Records Grouping records is a wonderful facility available in Access Reports. Look at following report that is grouped according to the StuCity.

Figure 4.49: A Grouped Report

4.9.4 Creating Reports in Design View Creating Reports in Design View is very much similar to the Creating Forms in Design View. Try 12: Create the following Reports to practice the Report Design View. Try 12-A: Used only the StudentDetails Table. Sex field is properly handled.

00-BIT-Access-NOTE-5.doc

Page 52 of 66

Try 12-B: Grouping of Records is used. Designed with Report Wizard and fine tuned in Design View.

Try 12-C: Grades for the Subjects Marks are displayed.

00-BIT-Access-NOTE-5.doc

Page 53 of 66

Try 12-D: Total Marks for individual students are calculated.

4.10 Building a Database Application An Access database application can contain some or all of the types of database objects you have created, tables for data, queries for accessing data, forms to enter and view data and reports to view data. In addition to those objects, an application will contain one or more switchboard forms

00-BIT-Access-NOTE-5.doc

Page 54 of 66

that connect all these objects together and allow you to navigate to the various objects without the need to run them directly from the database windows.

4.10.1 Creating an Application Using Wizards You can create an application using the database application wizards. Do the following to create an application from a template: 1. Choose File New to open the New file task pane. 2. Choose the On My Computer option to open the Template dialog box. 3. Select the Database tab to see the list of available template on your computer. (See Figure 4.50)

Figure 4.50: Templates

4. Select whatever template you want and simply follow the steps of the Wizard to create your database application. This is very simple but this may not give an application with the features you want.

4.10.2 Building Applications from Scratch Building a database application is simply a matter of creating the various tables, queries, forms and reports, and then linking them all together using switchboards. Creating the Switchboard Do the following to create a switchboard in the Switchboard Manager

00-BIT-Access-NOTE-5.doc

Page 55 of 66

1. Choose Tools Database Utilities Switchboard Manager. If no switchboard form exists in the database, Access will prompt following message. You should select Yes in here to create a switchboard.

2. If this is the first Switchboard, the Switchboard Manager will display the following dialog box to you.

Figure 4.51: Switchboard Manager

You can notice that the Switchboard Manager will have the default Main Switchboard with it that never be deleted. Once you create a Switchboard, the Switchboard form will be added to the database. Once you create the switchboard form, then you can add items to the switchboard to make it useful or if you want you can add more switchboards. Adding More Switchboards When you have the switchboard dialog box active (See Figure 4.51) do the following to add new switchboards: 1. Click on the New button of the switchboard dialog box. Then you will see the Create New dialog box as follows:

00-BIT-Access-NOTE-5.doc

Page 56 of 66

2. Enter the Name for your new switchboard and click OK. You will notice that the new switchboard is added to the Switchboard dialog box. You can add two switchboards to the Switchboard Manager named Add Records and View Reports

Figure 4.52: Switchboard Manager with More Switchboards

Adding Items to a Switchboard When you have the required switchboards created, the next step is to add the required items (menu options) to these switchboards. Do the following to add items to a Switchboard: 1. Choose the switchboard that you want to add items to, and then click the Edit button to open the Edit Switchboard Page dialog box. Select Add Records switchboard to add items to it. Your Edited Switchboard Page will look like Figure 4.53.

Figure 4.53: Edit Switchboard Page

2. Click New to add a new item to the switchboard. Then the Edited Switchboard Item dialog box will appear as in Figure 4.54.

00-BIT-Access-NOTE-5.doc

Page 57 of 66

Figure 4.54: Edit Switchboard Item

3. Enter the text for the item. This is the text that will be displayed for the form item when the switchboard is run. Type Add New Student Record as your Text. 4. Choose a command for the item using the Command drop-down list. You can select one of the following: • • • • • • • •

Go to Switchboard : Open an another switchboard. Open Form in Add Mode : This will open the a form in data entry mode so that you can enter new records. Open Form in Edit Mode : This is to edit the existing records. Open Report : To open a report. Design Application : Run the Switchboard Manager. Exit Application : Close the current database. Run Macro : Execute a macro. Run Code : Execute the VBA code.

5. You will get another dialog box depending on the command you select in the command option. For example if you select Open Form in Add Mode, you will get the dialog box with all the Forms in your database listed. Select one of the forms you have already created. 6. Repeat these steps to add the relevant items to your switchboards. Create the following switchboards:

00-BIT-Access-NOTE-5.doc

Page 58 of 66

You will notice that a switchboard can be edited in Design View, but it should be done with care. Because the switchboard form is autogenerated, any modification can cause to loose the configuration of the form.

00-BIT-Access-NOTE-5.doc

Page 59 of 66

Assignment 1: Drug Management System Create a database to handle the Drugs of a Pharmacy by considering following: A. A table should be created to record the following details. Code of the Drug Generic Name and the Trade Name of the Drugs and The Form and Type (Strength) of the Drugs B. Balance Stock must be kept in a separate table C. Receiving and Issuing must be recorded with the following details Receiving / Issuing Data Receiving / Issuing Quantity Notes for the Receiving / Issuing You are free to add any relevant fields to these tables with a justification. Primary keys should be assigned properly. D. Create the Following Queries 1. To display the particular Form of drugs. For example : Capsules 2. To display the drugs that has No Stock at the moment. 3. To display the drugs that has a given stock limit e.g. 10 to 100 4. To display the stocks of a given drug Form. E.g. Tablets 5. All the Receiving of a given drug Form. 6. Display Total of all the receivings of drugs 7. Display Total of Drug receiving where the Balance Stock is Zero (0). 8. Display all the drugs received on or after a given date 9. Display all the drug received on or after a given date for a given type of Drug 10. Do the queries 5 – for Issuing of drugs. E. Create Forms for the followings: 1. Enter a New Drug 2. Enter received of Drugs. 3. Enter Issued of Drugs F. Create the Following Reports 1. Display the details of all the drugs in the system. This should includes Code of the Drug, Generic Name, Trade Name, Form and Type (Strength) of the Drugs 2. Design a report to display Code of the Drug, Generic Name and the Stock 3. Display all the receiving details 4. Display all the issuing details 5. Design a report to display Code of the Drug, Generic Name, Receiving, Issuing and Balance Stock G. Created a Switchboard (or switchboards) that connects all the objects you have created above.

00-BIT-Access-NOTE-5.doc

Page 60 of 66

Assignment 2: You are required to design a database that handles the data of a Video Rental Shop. Design the relevant tables, queries, forms and reports that handle the data of that Video Rental Shop. It is assumed that you design a Switchboard(s) to connect these objects. Tip : following details could be used to design your tables. • • • •

Should have a table that keeps the details of the Members of the Video Rental Shop. Should have a table to store the details about the Videos available in the Shop. Should record customer details. System should the able to handle the Details of the Invoices. This should help to find out the overduse of customers.

00-BIT-Access-NOTE-5.doc

Page 61 of 66

Quiz 1. In Access columns (in a table) are called? A. Rows. B. Records. C. Fields. D. Cells. 2. Which of the following is/are Relational Database Management Systems used today? A. Access B. Paradox C. BASE D. Word 3. Which is/are NOT a type of Microsoft Access database object? A. Tables B. Queries C. Forms D. Workbooks 4. Which of the following database objects asks a question from the user and then displays the results? A. Tables. B. Queries. C. Forms. D. Reports. 5. Which of the following database objects makes it easy to view, edit, and enter information? A. Tables. B. Queries. C. Forms. D. Reports. 6. Design View lets you view and modify the structure of any database object. A. True B. False 7. Click the Save button on the toolbar to save the records. A. True B. False 8. Which of following is/are NOT data type(s) in Access A. Text B. Number C. Date/Time D. Graphic 9. The fastest and easiest way to create a form or report is using the Form Wizard or the Report Wizard. A. True B. False 10. What is the maximum number of characters that can be entered into a text field? A. Unlimited B. 255 C. 1 D. 254

00-BIT-Access-NOTE-5.doc

Page 62 of 66

11. Which of the following criterion finds records whose City field does not equal to Matara A. DOES NOT EQUAL Matara B. <>”Matara” C. <>’Matara’ D. DOES NOT EQUAL “Matara” 12. Memo data type field is used? A. To add an electronic Post-It-Note reminder to any record. B. For long text entries into the table. C. For short text entries of no more than 255 characters. D. To store objects created in other programs such as a graphic or Microsoft Word document. 13. When your query results wanted to be sorted by Last Name. Last Name field must appear in your query result. A. True B. False 14. Which of the following statement(s) about the AutoForm Wizard is NOT true? A. The AutoForm Wizard is the fastest and easiest way to create a form in Microsoft Access. B. The AutoForm Wizard can only create five types of forms: Datasheet, Columnar, Tabular, PivotTable, or PivotChart. C. Forms created with the AutoForm Wizard usually come out looking sharp and professional and don’t require any further clean-up work. D. Once a form is created using AutoForm Wizard is cannot be modified. 15. Reports and forms get their information from? A. Tables. B. Queries. C. Forms. D. Modules. 16. The first step in creating a form or report with the Form Wizard or Report Wizard is ? A. Selecting how the form or report should be formatted. B. Selecting the underlying table or query on which you want to base the form or report. C. Reading several screens of mostly useless information and clicking Next. D. Selecting the fields that you want to appear in the form or report. 17. You want a report to group total sales by month. Where would you place the calculated control (i.e. =SUM([Sales]) expression) to calculate the totals for each month? A. In the Month Group Footer section. B. In the Page Footer section. C. In the Report Footer section. D. In the Summary section. E. In the Detail section. 18. Controls and their corresponding text labels cannot be moved independently of one another. A. True B. False 19. The only way to find and replace information in Microsoft Access is with an Update Query. A. True B. False 20. The criteria BETWEEN 1/1/95 AND 12/31/95 would: A. Display records between the dates 1/2/99 and 1/1/00. B. Display records whose dates equaled 1/1/99 or 12/31/99. C. Display records between the dates 1/1/95 and 12/31/95. D. Do nothing - this criterion has not been entered using the proper syntax.

00-BIT-Access-NOTE-5.doc

Page 63 of 66

21. In Advanced Filter, you add field into the design grid by? A. Selecting the fields from the Add Field List on the toolbar. B. Double-clicking the field in the field list. C. Selecting Edit Add Field from the menu D. Dragging and dropping the field from the field list to the design grid. 22. The only way you can rearrange the order of fields in a datasheet is by reordering the fields in table Design View. A. True B. False 23. Which of the following is NOT a field property? A. Field Size. B. Format. C. Color. D. Indexed. 24. Indexing a field dramatically speeds up queries; therefore you should always index every field in a table. A. True B. False 25. Which of the following statements is NOT true? A. Indexed property has three settings: No, Yes (Duplicates OK), and Yes (No Duplicates). B. Primary key fields are automatically indexed. C. Yes (No Duplicates) index option prevents duplicate entries in your table. D. You can index any type of field: text, date/time, AutoNumber, number, currency, 26. Which of the following fields would NOT make a suitable primary key? A. AutoNumber B. National Identity Card Number of a Person C. Invoice number. D. Date of Invoicing 27. Text entered in the Description box will appear in a pop-up window whenever a user selects that field. A. True B. False 28. The Field Size of property works differently, depending on the field selected A. True B. False 29. What is Format properties you should use to display the full name of the month? A. MONTH. B. FULLMONTH. C. mm. D. mmmm. 30. What is the field that helps you to create a drop-down list that can be used to pick an item from the list? A. A memo field. B. A lookup field. C. An OLE field. D. A hyperlink field. 31. Primary key can be set by selecting the field(s) and clicking the Primary Key button on the toolbar. A. True B. False 32. You display the relationships in a database by? A. Selecting View Relationships from the menu. B. Clicking the Relationships button in the Database window.

00-BIT-Access-NOTE-5.doc

Page 64 of 66

C. Clicking the Relationships button on the toolbar. D. Selecting Edit Relationships from the menu. 33. You add a table to the Relationships window by? A. Selecting Edit Add Table from the menu. B. Using the Show Table button on the toolbar. C. Selecting the table from the Table list on the toolbar. D. Selecting Tools Add Table from the menu. 34. Which of the following criterion is NOT written using the proper syntax? A. "Dhanuka Dilshan" B. Between 1/1/2000 AND 12/31/2000 C. NO VALUE D. =<500 35. Which of the following expressions is NOT written in the correct syntax? A. [Total Order] * [Tax Rate] B. "Total Order" * 0.1 C. [tblCustomer]![Cost]*[tblEmployees]![Comis] D. 100+10 36. Expression Builder will help you to create expressions. A. True B. False 37. How can you add a table to the query design window? A. Select Edit Add Table from the menu. B. Using the Show Table button on the toolbar. C. Select the table from the Table list on the toolbar. D. Select Insert Table from the menu 38. Which of the following is NOT a report section? A. Report Header section. B. Page Header section. C. Summary section. D. Detail section. 39. Which of the following expressions is incorrect? A.=Total for: [Employee]. B.=[InvoiceDate]+30. C.=[LastName]&" "&[FirstName]. D.=[Units]*[UnitPrice]. 40. The two views for displaying tables are: A. Design and Worksheet B. Rough and Worksheet C. Design and Datasheet D. Preview and Datasheet 41. If you delete a record, you can use the Undo icon to reverse the deletion. A. True B. False 42. When you specify Qty>89 in a query, what this means? A. Find records where Qty is above 89 B. Find records where Qty is on or above 89 C. Find records where Qty is between 1 and 89 D. Find records where Qty is not equal 89 43. Which of the following is not part of the normalisation process?

00-BIT-Access-NOTE-5.doc

Page 65 of 66

A. Removing non-related data B. Removing related data C. Removing redundant data D. Setting required values in columns 44. Primary key can be used to identify a row in a table A. True B. False 45. Which of the following is not a data type in Access? A. Text B. Memo C. Currency D. AutoText 46. Which data type cannot be sorted? A. Text B. Date/Time C. OLE Object D. Number 47. We use referential integrity to? A. Ensure that related records between tables are valid B. Ensure that all references are equal C. Ensure that all controls are bound D. Ensure that all databases are valid 48. Which of the following is not a Text function? A. LTrim B. RTrim C. Minute D. Len 49. You can use the Expression Builder to create Conditions. A. True B. False 50. You can protect certain fields from editing by changing their properties. A. True B. False

00-BIT-Access-NOTE-5.doc

Page 66 of 66