Creating Database Objects Tvet

Blue Nile College, COVID-19 Emergency Response Reading Material
TVET-PROGRAME TITLE:- IT Support Service Level II
MODULE TITLE:- Operating Database Application
MODULE CODE: EIS ITS2 M04 0919
NOMINAL DURATION: 60 Hours
MODULE DESCRIPTION:
This module defines the competency required to operate database applications and
perform basic operations.
LEARNING OUTCOMES
At the end of the module the learner will be able to:
LO1. Create database objects
LO2. Customize basic settings
LO3. Create reports
LO4. Create forms
LO5. Retrieve information
Operating Database Application Page 1

LO 1: Create Database Objects
Information Sheet-1 Database Design Principles
The Database Design Principles

Usability: Any information which we are storing in any organization should be meaningful for that
organization. If we are storing those factors which are actually not fit with organization’s requirement then
this is just waste of resources.
Extensibility: As we know that everyday new business requirements come up and every day there is a need to
change or enhance information system to capture new requirements. So information design should be
extensible so that it can adopt new requirements without much efforts or without major breaking changes.
Data Integrity: Now at this point we understand that information is very much important for any organization.
Based on the historic information, every organization makes different strategies, decisions for growth. One
small mistake in data can lead to major issues with any organization’s key decision and hence a big risk for
growth.
Entity Integrity: Involves the structure (primary key and its attributes) of the entity. If the primary key is
unique and all attributes are scalar and fully dependent on the primary key, then the integrity of the entity is
good. In the physical schema, the table’s primary key enforces entity integrity.
Domain Integrity: It defines that data should be of correct type and we should handle optional data in correct
way. We should apply Null ability to those attributes which are optional for organization. We can define
proper data types for different attributes based on organization’s requirement so that correct format data
should present in system.
Referential Integrity: This defines if any entity is dependent on another one then parent entity should be there
in the system and should be uniquely identifiable. We can do this by implementing foreign keys.
User defined integrity: There are few business rules which we cannot validate just by primary keys, foreign
keys etc. There has to be some mechanism so that we can validate complex rules for integrity. We can
implement these rules in following ways:
Performance: As we know that information should be readily available as requested. Performance of the
system should be up to the mark. As data in increasing day by day so at some time there will be impact on
performance if database design is poor or we’ll not take any actions to improve performance.
Availability: The availability of information refers to the information’s accessibility when required regarding
uptime, locations, and the availability of the data for future analysis. Disaster recovery, redundancy,
archiving, and network delivery all affect availability.
Security: For any organizational asset, the level of security must be secured depending on its value and
sensitivity. A sometime organization has suffered a lot because of data leaks which results in loss of faith and
tends to business risk. So security is one of the most important aspect of good database design.

Information Sheet 2 Opening& Designing Database Application
 Introduction to Database
 A database can best be described as a way of storing large amounts of information.
 The data can be retrieved and we can even ask questions of the data and get answers.
 For example: You may want to know how many Students enrolled in every occupational
level.
 MS Access (MS Office Access 2007) is a database management tool that enables one to store
relevant data.
 This also has the capabilities to retrieve, sort, summarize report and result immediately and
effectively.
 It can combine data from various files (tables) through creating relationships and can make data
entry more efficient and accurate through the use of forms.
 Microsoft Access (MS Access) enables to manage all important information from a single
database file.
 Within the file, can use the different objects/items:
 The design process
 Determine the purpose of your database. This helps prepare you for the remaining steps.
 Find and organize the information required. Gather all of the types of information you might want
to record in the database, such as product name and order number.
 Divide the information into tables. Divide your information items into major entities or subjects,
such as Products or Orders. Each subject then becomes a table.
 Turn information items into columns . Decide what information you want to store in each table.
Each item becomes a field, and is displayed as a column in the table. For example, an Employees
table might include fields such as Last Name and Hire Date.
 Specify primary keys. Choose each table’s primary key. The primary key is a column that is used
to uniquely identify each row. An example might be Product ID or Order ID.
 Set up the table relationships. Look at each table and decide how the data in one table is related to
the data in other tables. Add fields to tables or create new tables to clarify the relationships, as
necessary.
 Refine your design. Analyze your design for errors. Create the tables and add a few records of
sample data. See if you can get the results you want from your tables. Make adjustments to the
design, as needed.

 Apply the normalization rules. Apply the data normalization rules to see if your tables are
structured correctly. Make adjustments to the tables, as needed.
Information Sheet:3 Database OBJECTS
What does Database Object mean?

A database object in a relational database is a data structure used to either store or reference data. The most
common object that people interact with is the table. Other objects are indexes, stored procedures, sequences,
views and many more.
When a database object is created, a new object type cannot be created because all the various object types
created are restricted by the very nature, or source code, of the relational database model being used, such as
Oracle, SQL Server or Access. What is being created is instances of the objects, such as a new table, an index
on that table or a view on the same table.
The different type of Database objects
. Tables: - Recently, we define it as a file but technically, it was defined as a container or a worksheet-like
container where the collection of data has been stored.
Tables - A table is a collection of data about a specific topic, such as products or suppliers
 Basic Component of a Table:

 Meta Data – Database Structure
 Field – Column – Data
 Fieldname
 Record - Row - Information
Metadata – is a “data about data” or synonymously called table structure that defines what type of data your
data is?
Queries –Queries used to view, change, and analyze data in different ways. You can also use them as a
source of records for forms, reports.
Forms - A form is a type of a database object that is primarily used to enter or display data in a database.
You can also use a form as a switchboard that opens other forms and reports in the database, or as a custom
dialog box that accepts user input and carries out an action based on the input.
Reports - A report is an effective way to present your data in a printed format. Because you have control
over the size and appearance of everything on a report,

Information Sheet-4 Creating Data base Objects
Data Base Tables: - Recently, we define it as a file but technically, it was defined as a container or a
worksheet-like container where the collection of data has been stored.
 Before we proceed to creating your first table, we need to know first the basic components of a table:
 Meta Data – Database Structure
 Field – Column – Data
 Fieldname
 Record - Row - Information
Data types in Microsoft Access

 The different kinds of MsAccess2007 data types are:
 Text: allows for the storage of any kind of data, characters, digits and special characters.
 Memo: is used for texts of more than 255 characters such as comments or explanations.
 Number: for numerical data used in mathematical calculations.
 Date/Time: for the introduction of date and time from the year 100 to 9999.
 Currency: For monetary/economic values and numerical data used in mathematical calculations in
which the data involved contains between one and four decimals.
 Autonumber: a unique sequential number (increasing one by one), or a number that Access assigns
every time it adds a new record to a table.
 Yes/No:Yes and No values, and fields that contain one of two values (Yes/No, True/False or
Activated/Deactivated).
 OLE Object: an object such as a Microsoft Excel spreadsheet, a Microsoft Word document,
graphics, images, sounds, or other binaries.
 Used to embed or link to documents from other programs like Excel and Word.
 Hyperlink: text or a combination of text and numbers stored as text and used as a hyperlink address.
 Attachment: Used to store files in an Access database.
 The attachment data type lets you store one or more files per record.
 Lookup wizard…: A lookup wizard field lets the user choose from a predefined set of options, like a
"male" or "female" selection or a "country" selection.
Designing a table involves:
 Entering unique names of the columns of the table in the ―field name” column of the design view. N
 Names of fields and objects in Microsoft Access can be up to 64characters long.

 They can include any combination of letters, numbers, spaces, and special characters except a period (.),
an exclamation point (!), an accent grave (`), and brackets ([ ]). They also can't begin with leading spaces.
Normalization
 Normalization is the process of efficiently organizing data in a database.
 There are two goals of the normalization process:
1. Eliminating redundant data (for example, storing the same data in more than one table) and
2. Ensuring data dependencies make sense (only storing related data in a table).
 Both of these are valuable goals as they reduce the amount of space a database consumes and ensure that
data is logically stored.
Information Sheet-5 Modifying Database Object
Modify your new database created from a template
Every template that is included with Access is a complete tracking application that contains predefined tables,
forms, reports, queries, macros, and relationships. These templates are designed to be immediately useful out-
of-the-box, so that you can create a new database that is based on a template and get up and running quickly.
However, there might be times when you want to modify your new database — for example, to add or rename
a field, or change a report. You can easily add a field to a table in Datasheet view. However, you can also add
a field to a table in Design view. To learn more about adding a field to a table in Datasheet view, see the
article Add or delete a column in a datasheet. When you add a new field to a table, the field is not
automatically added to your existing forms and reports. You must manually add the field to those forms and
reports in order for it to appear in them.
If possible, you should avoid deleting a field from a database that was generated from one of the supplied
templates — it is likely that the field is employed in other database objects, such as forms and reports. Thus,
deleting the field will create consequences when you attempt to use the other database objects that employ the
field — the database objects will not work as expected. You will have to remove any references to the field
from all of the objects that employ it in order for those other objects to work correctly.
When you decide that you must delete a field from a database that was generated from a template, you can do
so in either Datasheet view or Design view. Remember that if other database objects reference the deleted
field, you must modify those other objects to remove the reference. For example, if a report includes a control
that is bound to the deleted field and you run the report, an error message appears, because Access cannot find
the data for the field.
Before you can delete a field, you must ensure that it doesn't participate in any table relationships. If you try
to delete a field for which relationships exist, Access warns you that you must first delete the relationships.
Information Sheet-6 Create Data base Relation ship
 Table Relationship

 Newcomers to the world of databases often have a hard time seeing the differences between a
database and a spreadsheet.
 They see tables of data and recognize that databases allow you to organize and query data in new
ways, but fail to grasp the significance of the relationship that gives relational database
technology& its name.
 Relationships allow you to describe the connections between different database tables in powerful
ways.
 Once you’ve described the relationships between your tables, you can later leverage that
information to perform powerful cross-table queries, known as joins.
 A relationship is a logical connection between two tables.
 Keys are fields that are part of a table relationship. There are two kinds of keys
 Primary key
 A table can have only one primary key.
 A primary key is used to identify each record that you store in the table.
 It will not allow a duplication of the Primary Key thus make it unique.
 Primary Key is the unique identification of one record.There is a uniquely identification number,
such as
 ID number
 A serial number
 A code that serves as a primary key
 Foreign key
 A table can also have one or more foreign key.
 A foreign key contains values that correspondent to values in the primary key of another table
 You use table relationship to combine data from related table
Customer Employee
c-id e- id
c-name e- name
c-age e- age
c-sex e- sex
c-phone e- address
c-address e- salary
c-id
Primary key Foreign key
 Types of Database Relationships

 One-to-one relationships: occur when each entry in the first table has one, and only one,
counterpart in the second table.
 One-to-many relationships: Is the most common type of database relationship. .
 Many-to-many relationships: occur when each record in the first table corresponds to
one or more records in the second table and each record in the second table corresponds
to one or more records in the first table.
ER Diagrams (Entity Relationship Diagrams)
 An Entity Relationship Diagram (ERD) is a visual representation of different data using conventions
that describe how these data are related to each other.
 For example, the elements writer, novel, and consumer may be described using ER diagrams this way:
ER diagram with basic objects
 In the diagram, the elements inside rectangles are called entities while the items inside diamonds denote
the relationships between entities.
 This ER diagram tutorial for beginners covers most things related to ER diagram, for quick
navigation use the links below.
 ER Diagram Usage
 ER Diagrams Symbols and Notations
 How to Draw ER Diagrams
 ER Diagram Templates
 Benefits of ER Diagrams
ER Diagrams Usage
 ER diagrams are most often associated with complex databases that are used in software engineering
and IT networks.

 In particular, ER diagrams are frequently used during the design stage of a development process in order
to identify different system elements and their relationships with each other.
 For example, inventory software used in a retail shop will have a database that monitors elements such
as purchases, item, item type, item source and item price. Rendering this information through an ER
diagram would be something like this:
ER diagram example with entity having attributes
In the diagram, the information inside the oval shapes is attributes of a particular entity.
1.1.1 ER Diagram Symbols and Notations
Elements in ER diagrams
 There are three basic elements in an ER Diagram:
1. Entity 3. Relationship
2. Attribute
 There are more elements which are based on the main elements.
 Weak entity  Multivalve attribute

 Derived attribute  Recursive relationship.
 Weak relationship and
 Cardinality and ordinalily are two other notations used in ER diagrams to further define relationships.
Entity
 An entity can be a person, place, event, or object that is relevant to a given system.
 For example, a school system may include students, teachers, major courses, subjects, fees, and other
items.
 Entities are represented in ER diagrams by a rectangle and named using singular nouns.
Weak Entity
 A weak entity is an entity that depends on the existence of another entity.
 In more technical terms it can defined as an entity that cannot be identified by its own attributes.
 It uses a foreign key combined with its attributed to form the primary key.
 An entity like order item is a good example for this. The order item will be meaningless without an order
so it depends on the existence of order.
Weak Entity Example in ER diagrams
Attribute
 An attribute is a property, trait, or characteristic of an entity, relationship, or another attribute.
 For example, the attribute Inventory Item Name is an attribute of the entity Inventory Item. An entity can
have as many attributes as necessary.
 Meanwhile, attributes can also have their own specific attributes.
 For example, the attribute ―customer address‖ can have the attributes number, street, city, and state.
 These are called composite attributes.
 Note that some top level ER diagrams do not show attributes for the sake of simplicity.
 In those that do, however, attributes are represented by oval shapes.

Attributes in ER diagrams, note that an attribute can have its own attributes (compositeattribute)
Multivalued Attribute
 If an attribute can have more than one value it is called a multivalued attribute.
 It is important to note that this is different to an attribute having its own attributes. For example a teacher
entity can have multiple subject values.
Example of a multi valued attribute
Derived Attribute
 An attribute based on another attribute.
 This is found rarely in ER diagrams.
 For example for a circle the area can be derived from the radius.
Derived Attribute in ER diagrams
Relationship
 A relationship describes how entities interact.
 For example, the entity ―carpenter‖ may be related to the entity ―table‖ by the relationship ―builds‖ or
―makes‖.
 Relationships are represented by diamond shapes and are labeled using verbs.
Using Relationships in Entity Relationship Diagrams
Recursive Relationship

 If the same entity participates more than once in a relationship it is known as a recursive relationship.
 In the below example an employee can be a supervisor and be supervised, so there is a recursive
relationship.
Example of a recursive relationship in ER diagrams
Cardinality and Ordinality
 These two further defines relationships between entities by placing the relationship in the context of
numbers.
 In an email system, for example, one account can have multiple contacts.
 The relationship in this case follows a ―one to many‖ model.
 There are number of notations used to present cardinality in ER diagrams.
 Chen, UML, Crow’s foot, Bachman are some of the popular notations. Greatly supports Chen, UML and
Crow’s foot notations. The following example uses UML to show cardinality.
Cardinality in ER diagrams using UML notation
1.1.2 ER Diagram Templates
 Below are some ER diagram templates so you can get started quickly.
 Clicking on the image and in the new page that opens click the ―Use as Template‖ button.
 For more templates check out ER diagram templates article.

ER Diagram Template of exam database (Click on the image to use as template)

 A basic ER diagram template for a quick start
Basic ER Diagram template (Click to use as template)

1.1.3 Benefits of ER diagrams
 ER diagrams constitute a very useful framework for creating and manipulating databases.
 First, ER diagrams are easy to understand and do not require a person to undergo extensive training to be
able to work with it efficiently and accurately.
 This means that designers can use ER diagrams to easily communicate with developers, customers, and end
users, regardless of their IT proficiency.
 Second, ER diagrams are readily translatable into relational tables which can be used to quickly build
databases.
 In addition, ER diagrams can directly be used by database developers as the blueprint for implementing
data in specific software applications.
 Lastly, ER diagrams may be applied in other contexts such as describing the different relationships and
operations within an organization.
Information Sheet-7 Adding ,Modifying& Deleting Records
Ways to Add, Edit, and Delete records
There are several ways to update data in an Access database. You add a record to your database when you have a
new item to track, such as a new contact to the Contacts table. When you add a new record, Access appends the
record to the end of the table. You also change fields to stay up-to-date, such as a new address or last name. To
maintain data integrity, the fields in an Access database are set to accept a specific type of data, such as text or
numbers. If you don't enter the correct data type, Access displays an error message. Finally, you can delete a
record when it is no longer relevant and to save space.
You use a form to manually update data. Data entry forms can provide an easier, faster, and more accurate way
to enter data. Forms can contain any number of controls such as lists, text boxes, and buttons. In turn, each of the
controls on the form either reads data from or writes data to an underlying table field.

Datasheets are grids of data that look like Excel worksheets. You can change data by working directly in
Datasheet view. If you are familiar with Excel, datasheets should be relatively easy to understand. You can
change data in tables, query result sets, and forms that display datasheets. Typically, you use datasheets when
you need to see many records at once.
Understanding data entry symbols
The following table shows some of the record selector symbols you might see when updating data and what they
mean.
Symbol Meaning
This is the current record; the record has been saved as it appears. The current record is indicated by a
change in color in the record selector.
You are editing this record; changes to the record aren't yet saved.
This record is locked by another user; you can't edit it.
This is a new record in which you can enter information.
This is the primary key field and contains a value that uniquely identifies the record.
Information Sheet 8
Saving a database or an object in a different format:
Saving a database
Saving your work in Access is a little different from saving in most Office apps. Changes to data, the primary
reason for saving your work in most apps, are automatically saved in In Access, instead of saving data changes,
you save changes to the database design, or you save the whole database, data and all, with a new filename as a
backup, or in a different format, such as an earlier Access file format, a database template, or a compiled
database (a database where you can't change the design). You can also save individual database objects as new
objects.
Operation Sheet1 Opening and designing DB application and principles
 Steps to create Create a blank database

On the Getting Started with Microsoft Office Access page, under New Blank Database, click Blank
Database.

 In the Blank Database pane, type a file name in the File Name box. If you do not supply a file name
extension, Access adds it for you. To change the location of the file from the default, click Browse for a
location to put your database (next to the File Name box), browse to the new location, and then
click OK.
 Click Create.
 Access creates the database with an empty table named Table1, and then opens Table1 in Datasheet
view. The cursor is placed in the first empty cell in the Add New Field column.
 Begin typing to add data, or you can paste data from another source, as described in the section Copy
data from another source into an Access table.
 Steps to Open an existing Access database

 Click the Microsoft Office Button ,
 click Open.In the Open dialog box, browse to the database that you want to open.
 Double-click the database to open it in the default mode specified in the Access
 Options dialog box or the mode that was set by an administrative policy.
 Click Open to open the database for shared access in a multi-user environment so that you and other
users can read and write to the database.
 Click the arrow next to the Open button and then click Open Read-Only to open the database for read-
only access so that you can view but not edit it. Other users can still read and write to the database.
 Click the arrow next to the Open button
 click Open Exclusive to open the database with exclusive access. When you have a database open with
exclusive access, anyone else who tries to open the database receives a "file already in use" message.
Operation Sheet-2 Creating Database Objects (Tables )
 Steps to Create a new table in a new database
 Click the Microsoft Office Button , and then click New.
 In the File Name box, type a file name for the new database.
 To browse to a different location to save the database, click the folder icon.
 Click Create.
 Steps to Create a new table in an existing database
 Click the Microsoft Office Button , and then click Open.
 In the Open dialog box, select the database that you want to open, and then click Open.

 On the Create tab, in the Tables group, click Table.
 Steps to Create a new table by using a table template
 In the Open dialog box, select and open the database in which you wish to create a table.
 On the Create tab, in the Tables group, click Table Templates and then select one of the available
templates from the list.
 Steps To Create a new table by importing or linking to external data

 In the Open dialog box, select and open the database in which you wish to create a new table.
 On the External Data tab, in the Import group, click one of the available data sources.
 Follow the instructions in the dialog boxes that appear at each step.
 Steps to Use a SharePoint site to create a table
 In the Open dialog box, select the database in which you want to create the new table, and then
click Open.
 On the Create tab, in the Tables group, click SharePoint Lists.
 Steps to Create a SharePoint list that is based on a template
 Click either Contacts, Tasks, Issues, or Events.
 In the Create New List dialog box, type the URL for the SharePoint site where you want to create
the list.
 Enter a name for the new list and its description in the Specify a name for the new list and
Description boxes.

 To open the linked table after it is created, select the Open the list when finished check box
(selected by default).
 Steps to Create a new custom list
 Click Custom.
 In the Create New List dialog box, type the URL for the SharePoint site where you want to
create the list.
 Enter a name for the new list and its description in the Specify a name for the new list and
Description boxes.
 To open the linked table after it is created, select the Open the list when finished check box
(selected by default).
 Steps to import the data from an existing list
 Click Existing SharePoint List.
 In the Get External Data dialog box, type the URL for the SharePoint site that contains the data that
you want to import.
 Click Import the source data into a new table in the current database, and then click Next.
 Select the check box next to each SharePoint list that you want to import.
 Steps to Link to an existing list

 Click Existing SharePoint List.
 In the Get External Data - SharePoint Site dialog box, type the URL for the SharePoint site that
contains the list to which you want to link.
 Click Link to the data source by creating a linked table, and then click Next.
 Select the check box next to each SharePoint list to which you want to link.
 Steps to set or change a table's primary key
 Select the table whose primary key you want to set or change.
 On the Home tab, in the Views group, click View, and then click Design View.
 In the table design grid, select the field or fields that you want to use as the primary key.
 On the Design tab, in the Tools group, click Primary Key.
 A key indicator appears to the left of the field or fields that you specify as the primary key.
 Steps to Remove the primary key
 Select the table whose primary key you want to remove.

 Click the row selector for the current primary key. If the primary key consists of multiple fields,
hold down CTRL, and then click the row selector for each field.
 On the Design tab, in the Tools group, click Primary Key.
 Steps to Set a table's properties

 Select the table whose properties you want to set.
 On the Design tab, in the Show/Hide group, click Property Sheet.The table property sheet is
shown.
 On the property sheet, click the General tab.
 Click the box to the left of the property that you want to set, and then enter a setting for the
property.
 To save your changes, press CTRL+S.
 Steps to Add a field by using a field template

 On the Home tab, in the Views group, click View, and then click Datasheet View.
 On the Datasheet tab, in the Fields & Columns group, click New Field.
 Select one or more fields in the Field Templates pane, and then drag them to the table where you
want to insert the new column.
 Steps to Open a table in Datasheet view
 In the Navigation Pane, right-click the table that you want to open.
 On the shortcut menu, click Datasheet view.
 Steps to Rename a field

 Right-click the heading of the field that you want to rename (for example, Field1).
 On the shortcut menu, click Rename Column.
 Enter the new name in the field heading.
 Steps to Change a field's data type

 On the Ribbon, click the Datasheet tab .
 In the Data Type list, in the Data Type & Formatting group, select the data type that you want.
 Steps to Set other field properties

 In Datasheet view, click the field for which you want to set the property.
 On the Datasheet tab, in the Data Type & Formatting group, select the properties that you want.
 Steps to Set field properties in Design view

 Open a table in Design view
 In the Navigation Pane, right-click the table.
 On the shortcut menu, click Design view.
 Change a field's data type

 In the table design grid, locate the field for which you want to set the data type.
 In the Data Type column, choose a data type from the list.
 To save your changes, press CTRL+S.
 Find links to more information about data types and field properties in the See Also section.
Operation Sheet3 Modifying Database Objects
 Turn on the Name AutoCorrect option

 Click File>Options.
 In the Access Options dialog box, in the left pane, click Current Database.
 Under Name AutoCorrect Options, select the Track name AutoCorrect info check box, and then
select the Perform name AutoCorrect check box.
 If you want to keep a table that logs each change that is performed by Name AutoCorrect, select the
Log name AutoCorrect changes check box.
 Click OK.
 To save your changes, click Save on the Quick Access Toolbar.
 Rename a field in Datasheet view

 In the Navigation Pane, double-click the table in which you want to rename the field.
o The table is opened in Datasheet view.
 Right-click the column heading for the field that you want to rename, and then click Rename
Field on the shortcut menu.
 Type the new name for the field and then press ENTER.
 Rename a field in Design view

 In the Navigation Pane, right-click the table in which you want to rename the field, and then
click Design View on the shortcut menu..
 Click the cell in the Field Name column for the field that you want to rename.
 Edit the text to rename the field.
 Rename a table
 In the Navigation Pane, right-click the table that you want to rename ,and then click Rename on
the shortcut menu.

 Type the new name and then press ENTER.

 Change the text that appears in a column heading

 In the Navigation Pane, right-click the table in which you want to change the caption, and then click
Design View on the shortcut menu.
 Click the cell in the Field Name column for the field whose Caption property you want to set.
 In the bottom section, under Field Properties, on the General tab, click Caption.
 Type a new caption for the field.
Operation Sheet-4 Creating Database Relationship


Create, edit or delete a relationship
After you have created a table for each subject in your database, you must provide Office Access 2007 with the
means by which to bring that information back together again when needed. You do this by placing common
fields in tables that are related, and by defining table relationships between your tables. You can then create
queries, forms, and reports that display
 Steps to Create a table relationship by using the Relationships document tab

 In the Open dialog box, select and open the database.
 On the Database Tools tab, in the Show/Hide group, click Relationships.
4. If you have not yet defined any relationships, the Show Table dialog box automatically appears. If it does not
appear, on the Design tab, in the Relationships group, click Show Table.
The Show Table dialog box displays all of the tables and queries in the database. To see only tables, click Tables.
To see only queries, click Queries. To see both, click Both.
5. Select one or more tables or queries and then click Add. After you have finished adding tables and queries to the
Relationships document tab, click Close.
6. Drag a field (typically the primary key) from one table to the common field (the foreign key) in the other table.
To drag multiple fields, press the CTRL key, click each field, and then drag them.

The Edit Relationships dialog box appears.
7. Verify that the field names shown are the common fields for the relationship. If a field name is incorrect, click on
the field name and select the appropriate field from the list.
To enforce referential integrity for this relationship, select the Enforce Referential Integrity check box. For more
information about referential integrity, see the section Enforce Referential Integrity
8. Click Create.
Access draws a relationship line between the two tables. If you selected the Enforce Referential Integrity check
box, the line appears thicker at each end. In addition, again only if you selected the Enforce Referential
Integrity check box, the number 1 appears over the thick portion on one side of the relationship line, and the
infinity symbol (∞) appears over the thick portion on the on the other side of the line, as shown in the following
figure.
NOTES

 To create a one-to-one relationship Both of the common fields (typically the primary key and foreign key fields)
must have a unique index. This means that the Indexed property for these fields should be set to Yes (No
Duplicates). If both fields have a unique index, Access creates a one-to-one relationship.
 To create a one-to-many relationship The field on the one side (typically the primary key) of the relationship
must have a unique index. This means that the Indexed property for this field should be set to Yes (No
Duplicates). The field on the many side should not have a unique index. It can have an index, but it must allow
duplicates. This means that the Indexed property for this field should be set to either No or Yes (Duplicates OK).
When one field has a unique index, and the other does not, Access creates a one-to-many relationship.
Create a table relationship by using the Field List pane
In Office Access 2007, you can add a field to an existing table that is open in Datasheet view by dragging it from
the Field List pane. The Field List pane shows fields available in related tables and also fields available in other
tables in the database. When you drag a field from an "other" (unrelated) table and then complete the Lookup
Wizard, a new one-to-many relationship is automatically created between the table in the Field List pane and the
table to which you dragged the field. This relationship, created by Access, does not enforce referential integrity
by default. To enforce referential integrity, you must edit the relationship. See the section Edit a table
relationship for more information.
Open a table in Datasheet view
1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.
3. In the Navigation Pane, right-click the table to which you want to add the field and create the relationship, and
then click Datasheet View on the shortcut menu.
Open the Field List pane
 On the Datasheet tab, in the Fields & Columns group, click Add Existing Fields.
Steps to Add a field and create a relationship from the Field List pane
1. On the Datasheet tab, in the Fields & Columns group, click Add Existing Fields.
The Field List pane appears.
2. Under Fields available in other tables, click the plus sign (+) next to a table name to display the list of fields in
that table.
3. Drag the field that you want from the Field List pane to the table that is open in Datasheet view.

4. When the insertion line appears, drop the field into position.
The Lookup Wizard starts.
5. Follow the instructions to complete the Lookup Wizard.
The field appears in the table in Datasheet view.
When you drag a field from an "other" (unrelated) table and then complete the Lookup Wizard, a new one-to-
many relationship is automatically created between the table in the Field List and the table to which you dragged
the field. This relationship, created by Access, does not enforce referential integrity by default. To enforce
referential integrity, you must edit the relationship. See the section Edit a table relationship for more
information.Steps to Edit a table relationship
You change a table relationship by selecting it in the Relationships document tab and then editing it.
1. Carefully position the cursor so that it points to the relationship line, and then click the line to select it.
The relationship line appears thicker when it is selected.
2. With the relationship line selected, double-click it. –or–
On the Design tab, in the Tools group, click Edit Relationships.
Open the Edit Relationships dialog box

3. On the Database Tools tab, in the Show/Hide group, click Relationships.
The Relationships document tab appears.
If you have not yet defined any relationships and this is the first time you are opening the Relationships
document tab, the Show Table dialog box appears. If the dialog box appears, click Close.
4. On the Design tab, in the Relationships group, click All Relationships.

All tables with relationships are displayed, showing relationship lines. Note that hidden tables (tables for which
theHidden check box in the table's Properties dialog box is selected) and their relationships will not be shown
unless Show Hidden Objects is selected in the Navigation Options dialog box.
For more information about the Show Hidden Objects option, see the article Guide to the Navigation Pane.
5. Click the relationship line for the relationship that you want to change. The relationship line appears thicker
when it is selected.
6. Double-click the relationship line. -or-
7. Make your changes, and then click OK.
The Edit Relationships dialog box allows you to change a table relationship. Specifically, you can change the
tables or queries on either side of the relationship, or the fields on either side. You can also set the join type, or
enforce referential integrity and choose a cascade option. For more information about the join type and how to
set it, see the section Set the join type. For more information about how to enforce referential integrity and
choose a cascade option, see the section Enforce referential integrity.
Set the join type
You should think about the result you will most often want from a query that joins the tables in this relationship,
and then set the join type accordingly.
Set the join type

1. In the Edit Relationships dialog box, click Join Type.
The Join Properties dialog box appears.

2. Click your choice, and then click OK.

The following table (using the Customers and Orders tables) shows the three choices that are displayed in
the Join Properties dialog box, the type of join they use, and whether all rows or matching rows are returned for
each table.
CHOICE RELATIONAL LEFT RIGHT
JOIN TABLE TABLE
1. Only include rows where the joined fields from both tables Inner join Matching Matching
are equal. rows rows
2. Include ALL records from 'Customers' and only those Left outer join All rows Matching
records from 'Orders' where the joined fields are equal. rows
3. Include ALL records from 'Orders' and only those records Right outer join Matching All rows
from 'Customers' where the joined fields are equal. rows
When you choose option 2 or option 3, an arrow is shown on the relationship line. This arrow points to the side
of the relationship that shows only matching rows.
Make changes in the Join Properties dialog box

All tables that have relationships are displayed, showing relationship lines. Note that hidden tables (tables for
which the Hidden check box in the table's Properties dialog box is selected) and their relationships will not be
shown unless Show Hidden Objects is selected in the Navigation Options dialog box.

7. Click Join Type
8. In the Join Properties dialog box, click an option, and then click OK.
9. Make any additional changes to the relationship, and then click OK.
To Turn referential integrity on or off

All tables with relationships are displayed, showing relationship lines. Note that hidden tables (tables for which
the Hidden check box in the table's Properties dialog box is selected) and their relationships will not be shown
unless Show Hidden Objects is selected in the Navigation Options dialog box.

7. Check Enforce Referential Integrity.
8. Make any additional changes to the relationship, and then click OK.
After you have enforced referential integrity, the following rules apply:
 You cannot enter a value in the foreign key field of a related table if that value doesn't exist in the primary key
field of the primary table — doing so creates orphan records.
 You cannot delete a record from a primary table if matching records exist in a related table. For example, you
cannot delete an employee record from the Employees table if there are orders assigned to that employee in the
Orders table. You can, however, choose to delete a primary record and all related records in one operation by
selecting the Cascade Delete Related Records check box.
 You cannot change a primary key value in the primary table if doing so would create orphan records. For
example, you cannot change an order number in the Orders table if there are line items assigned to that order in
the Order Details table. You can, however, choose to update a primary record and all related records in one
operation by selecting the Cascade Update Related Fields check box.
 The common field from the primary table must be a primary key or have a unique index.
 The common fields must have the same data type. The one exception is that an AutoNumber field can be related
to a Number field that has a FieldSize property setting of Long Integer.
 Both tables exist in the same Access database. Referential integrity cannot be enforced on linked tables. However,
if the source tables are in Access format, you can open the database in which they are stored and enable
referential integrity in that database.
 Steps to Turn cascade update and/or cascade delete on or off

 On the Design tab, in the Relationships group, click All Relationships.

 All tables that have relationships are displayed, showing relationship lines. Note that hidden tables
(tables for which the Hidden check box in the table's Properties dialog box is selected) and their

relationships will not be shown unless Show Hidden Objects is selected in the Navigation
Options dialog box.
 For more information about the Show Hidden Objects option, see the article Guide to the Navigation
Pane.
 Click the relationship line for the relationship that you want to change. The relationship line appears
thicker when it is selected.
 Double-click the relationship line. -or-
 On the Design tab, in the Tools group, click Edit Relationships.
 The Edit Relationships dialog box appears.
 Select the Enforce Referential Integrity check box.
 Select either the Cascade Update Related Fields or the Cascade Delete Related Records check box,
or select both.
 Make any additional changes to the relationship, and then click OK.
Steps to delete a table relationship
 To remove a table relationship, you must delete the relationship line in the Relationships document
tab. Carefully position the cursor so that it points to the relationship line, and then click the line. The
relationship line appears thicker when it is selected. With the relationship line selected, press
DELETE. Note that when you remove a relationship, you also remove referential integrity support
for that relationship, if it is enabled. As a result, Access will no longer automatically prevent the
creation of orphan records on the "many" side of a relationship.

 The Relationships document tab appears.

 If you have not yet defined any relationships and this is the first time you are opening the
Relationships document tab, the Show Table dialog box appears. If the dialog box appears,
click Close.
 On the Design tab, in the Relationships group, click All Relationships.
 For more information about the Show Hidden Objects option, see the article Guide to the Navigation
Pane.
 Click the relationship line for the relationship that you want to delete. The relationship line appears
thicker when it is selected.

 Press the DELETE key. -or-

 Right-click and then click DELETE.
 Access might display the message Are you sure you want to permanently delete the selected
relationship from your database?. If this confirmation message appears, click Yes.
Operation Sheet 5 Adding ,Modifying Deleting Records
 Add a record to a table or form
 Open the table in Datasheet View or the form in Form View.

 On the Home tab, in the Records group, click New, or click New (blank) record, or press Ctrl+Plus
Sign (+).
 Find the record with an asterisk in the record selector, and enter your new information.
 Click or otherwise place the focus on the first field that you want to use, and then enter your data.
 To move to the next field in the same row, press TAB, use the Right or Left arrow keys, or click the
cell in the next field.
 When you view another record or close the table or form, Access saves the new record that you
added. To explicitly save changes to the current record, press Shift+Enter.
 To Delete a record
The deletion process is fairly simple, except when the record is related to other data and resides on the "one" side
of a one-to-many relationship. To maintain data integrity, by default, Access does not let you to delete related
data. For more information, see Guide to table relationships.
Open the table in Datasheet View or form in For
 m View.
 Select the record or records that you want to delete.
 Press DELETE, select Home> Records >Delete, or press Ctrl+Minus Sign
 To Edit data in a text box or field

 Open the table or query in Datasheet View or form in Form View.
 Click the field or navigate to the field by using the TAB or arrow keys, and then press F2.
 Place the cursor where In Form view, you can click a field's label to select the field. In you want to
enter information.
 Enter or update the text that you want to insert. If you make a typing mistake, press BACKSPACE.
 If a field has an input mask, enter the data according to the format.
Operation Sheet 6 Saving & Compiling Database Objects
 To reuse a database or a database object
 Open the database or database object.

 On the File tab, click Save As.
 Do one of the following steps:
o To save a database in a different format, click Save Database As.

o To save a database object in a different format, click Save Object As.

 Note: The option Save As Client Object is only available in a web database.
 Click the format you want to use for the new copy.
LAP Test Practical Demonstration
Name: _______________________________Date: ______________________________________

Time started: ________________________Time finished: _______________________________
Task 1
o Show all the necessary steps & ways to open new MS Access 2007 Database. (5 Points)
o Show all the necessary steps to open Existing MS Access 2007 Database. (5 Points)
Task 2
 Create a new table in a new database
 Create a new table in an existing database
 Create a new table by using a table template
 Create a new table by importing/linking to external data
o Set a table's primary key o Remove the primary key
o Set or change the primary key
 Set a table's properties
 Table field
o Add a field to a table o Add a field from an existing table
o Add a field by entering data o Set field properties
o Add a field by using a field
template
 Task 3
 Rename a table
 Change the text that appears in a column heading
 Task4
First Create a Database name By ABC and Create the tables Below
 Creating a table called TblStudent that has fields with data typeas shown below :
- StudentID(Type=Text, Size=10, Primary Key)
- StudentName(Type=Text,Size=20, Require=Yes)
- Sex(Type=Text,Size=1,Require=Yes)
- DOB(Type=Date/Time,Format=dd/mm/yy)
- Address(Type=Text,Size=Default, Requre=Yes)
- Photo(Type=Text)
- Phone(Type=Text)
 Creating a table called TblTeacher that has fields with data type as shown below :

- TecherID(Type=Text, Size=10, Primary Key)

- TeacherName(Type=Text,Size=20, Require=Yes)
- Sex(Type=Text,Size=1,Require=Yes)
- Address(Type=Text,Size=Default, Requre=Yes)
- Photo(Type=OLE Object)
- Phone(Type=Text)
 Creating a table called TblSubject that has fields with data type as shown below :
- SubjectID(Type=Text, Size=10, Primary Key)
- SubjectName(Type=Text,Size=30, Require=Yes)
- Hour(Type=Text,Size=20)
- Description(Type=Memo)
- Fee(Type=Currency)
 Creating a table called TblLab that has fields with data type as shown below :
- LabID(Type=Text, Size=10, Primary Key)
- LabName(Type=Text,Size=30, Require=Yes)
- Capacity(Type=Number)
 Creating a table called TblTime that has fields with data type as shown below :
- TimeID(AutoNumber,Primary Key)
- TimeAvailable(Type=Text,Size=20)
 Creating a table called TblStudy that has fields with data type as shown below :
- StudyID(Type=Text, Size=10, Primary Key)
- StudentID(Type=Text, Size=10)
- TecherID(Type=Text, Size=10)
- StartDate(Type=Date/Time(dd/mm/yy))
- EndDate(Type=Date/Time(dd/mm/yy))
- Finished(Type=Yes/No)
- GetCertificate(Type=Text,Size=50)
 Creating a table called TblStudyDetail that has fields with data type as shown below :
- StudyDetailID(AutoNumber,Primary Key)
- StudyID (Number)
- SubjectID (Type=Text, Size=10)
- TimeID(Number)
- LabID(Type=Text, Size=10)
 After you created all tables already, build the relationships between table and table as figure
below.
 Task 5
 Open an existing table & perform the following
 Add abebe, taye& kebede in the field first name
 Rename the name aster by astuka
 Change the Salary 2500 by 4300 in the salary field
 Task 6
 Save your file by giving a file name tvetTraining

LO2: Customize basic settings
Information Sheet 1 Adjusting page layout and settings
Understanding Layout view
Layout view is the most intuitive view to use for report modification, and can be used for nearly all the
changes you would want to make to a report in Access. In Layout view, the report is actually running, so you
can see your data much as it will appear when printed. However, you can also make changes to the report
design in this view. Because you can see the data while you are modifying the report, it's a very useful view
for setting column widths, add grouping levels, or performing almost any other task that affects the
appearance and readability of the report.
Information Sheet-2 Open and Viewing different tools
Introduction
Toolbar Sometimes referred to as a bar or standard toolbar, the toolbar is a row of boxes, often at the top of
an application window, that control various functions of the software. The boxes often contain images that
correspond with the function they control, as demonstrated in the image below.
Fig. 2.1 toolbars

A toolbar often provides quick access to functions that are commonly performed in the program. For
example, a formatting toolbar in a Microsoft Access gives you access to things like making text bold or
changing its alignment, along with other common buttons
 Common computer software toolbars
 Commands bar - A bar that shows available commands in a program.
 Formatting toolbar - Toolbar that shows text formatting options.
 Formula bar - Bar in a spreadsheet program that allows you to edit a formula.
 Menu bar - A bar at the top of the screen that gives access to all of the menus.
 Navigation bar - Gives access to all navigation features in a browser.
 Places bar - A pane that shows common places to access files.
 Scroll bar - A bar on the bottom or side of the window to scroll through a page.
 Split bar - A bar that divides the window into multiple sections.
 Status bar - One of the few bars at the bottom of the window that shows the status.
 Title bar - A bar at the very top of a window that describes the program or window.
Information Sheet-3 Formatting Fonts
A font is a graphical representation of text that may include a different typeface, point size, weight, color, or
design. The image shows some examples of different computer fonts. Software programs like Microsoft
Word, Microsoft Excel, and Ms-access allow users to change the font used when typing text in the document
or spreadsheet, as do web designers.
Formatting Fonts Sometimes users want to specify or change the appearance of a group of words or
characters, or even of a single word or character, and they are not familiar enough with all of Word's font
formatting options to know how to achieve the effect that they are seeking. Although most users are probably
familiar with some different font families and sizes and with the italic and bold typefaces, many users do not
know how to determine and apply the settings that are needed to add a colored background to their text or to
type
Operation Sheet 1
Adjusting page layout and settings
 Steps to Change page setup
Use the Page Size and Page Layout groups on the Page Setup tab to change size, orientation, margins,
and so on.
 Click the Page Setup tab.

 In the Page Size group, click Size to select a different paper size.
 In the Page Size group, click Margins to make adjustments to the report's margins.
 In the Page Layout group, click Portrait or Landscape to change the paper orientation.

Operation Sheet 2 Adjusting page layout and settings
 Steps to create a new toolbar

 On the View menu, point to Toolbars, and then click Customize.
 Click the Toolbars tab, and then click New.
 In the Toolbar Name box, type a name for your new custom toolbar.
 In the Make toolbar available to box, click the template or open document where you want to
store the toolbar.
 Click OK.
 The Customize dialog box appears.
o Click the Commands tab. Click the category that you want to select your button from. Under
Commands, drag the button that you want to the new toolbar.
o Click Close.
 Steps to delete a custom toolbar
 On the View menu, point to Toolbars, and then click Customize.
 Click the Toolbars tab.
 Under Toolbars, click the custom toolbar that you want to delete, and then click Delete.
Note You cannot delete a built-in toolbar. When you select a built-in toolbar in the Toolbars list, the
Delete button is unavailable, and the Reset button becomes available. If you click the Reset button,
the built-in toolbar returns to its original default appearance.
Operation Sheet 3
Formatting Fonts
 To Changing font type
 Highlight the text you want to change.
 Click the down arrow next to the font field on the format bar. (If you want to change the font to
bold, italic, or underlined, click on the B, I, or U on the format bar.)
 After clicking the down arrow for the font, you should be able to select from each of the installed
fonts on your computer. Click the font you want to use and the highlighted text will change.
 Changing font size
 Click the down arrow next to the size box on the format bar. Often, the default size is 12, as shown
in the above example.
 After clicking the down arrow for the size, you should have a selection of different sizes to
choose. Some fonts may not scale properly, so they may have limited size options.
 To Changing font color
 Click the down arrow next to the color icon. It is usually displayed as the letter "A" with a red
underline, as shown in the example above.
 After clicking the down arrow for the color, select the color you want to make the text.

LAP Test Practical Demonstration

Na
me: ________________________________Date: ________________________________
Time started: ________________________Time finished: ____________________________
 Task 1
 Adjust the paper size in to landscape
 Task2
 Create a new toolbar Based on the procedure
 Task3
o Make the type Bold
o Make the font size 20
o Make the font color red

LO 3: Create Database Report
Information Sheet-1 Designing reports in a logical sequence
What is a report? A report is an objectaseinReport

Microsoft Access that is used to display and print your data in an
organized manner. Reports provide a means of organizing and summarizing data. Reports are often used to
present an overview highlighting main points and trends. A report can be a simple list, a status report or a
monthly production report. A report is made from the data available
 Decide how to lay out your report when you design a report, you must first consider how you want the
data arranged on the page and how the data is stored in the database. During the design process, you
might even discover that the arrangement of data in the tables will not allow you to create the report
that you want. This can be an indication that the tables are not normalized — this means that the data is
not stored in the most efficient manner.
 Make a sketch of your report This step is not required — you might find that the Access Report
Wizard or the Report tool(both of which are available on the Create tab, in the Reports group)
provide a sufficient starting design for your report
 Decide which data to put in each report section Each report has one or more report sections. The
one section that is present in every report is the Detail section. This section repeats once for each
record in the table or query that the report is based on. Other sections are optional and repeat less
often and are usually used to display information that is common to a group of records, a page of
the report, or the entire report
 Decide how to arrange the detail data Most reports are arranged in either a tabular or a stacked
layout, but Access gives you the flexibility to use just about any arrangement of records and fields
that you want.
 Types of lay out
 Tabular layout A tabular layout is similar to a spreadsheet. Labels are across the top, and the data
is aligned in columns below the labels. Tabular refers to the table-like appearance of the data. This is
the type of report that Access creates when you click Report in the Reports group of the Create
tab. The tabular layout is a good one to use if your report has a relatively small number of fields that
you want to display in a simple list format. The following illustration shows an employee report that
was created by using a tabular layout.
 Stacked layout A stacked layout resembles a form that you fill out when you open a bank account
or make a purchase from an online retailer. Each piece of data is labeled, and the fields are stacked
on top of each other. This layout is good for reports that contain too many fields to display in a
tabular format — that is, the width of the columns would exceed the width of the report. The
following illustration shows an employee report that was created by using a stacked layout.
 Mixed layout You can mix elements of tabular and stacked layouts. For example, for each record,
you can arrange some of the fields in a horizontal row at the top of the Detail section and arrange
other fields from the same record in one or more stacked layouts beneath the top row. The following
illustration shows an employee report that was created by using a mixed layout. The ID, Last Name,
and First Name fields are arranged in a tabular control layout, and the Job Title and Business Phone
fields are arranged in a stacked layout. In this example, gridlines are used to provide a visual
separation of fields for each employee.

 Justified layout If you use the Report Wizard to create your report, you can choose to use a
justified layout. This layout uses the full width of the page to display the records as compactly as
possible. Of course, you can achieve the same results without using the Report Wizard, but it can be
a painstaking process to align the fields exactly. The following illustration shows an employee report
that was created by using the Report Wizard's justified layout.
 Creating Reports using a wizard Creation of a report by using a wizard is a fastest and easy way to
create a report by following the series of steps and choosing a pre-defined template for your report.
 Creating Reports using design view Creation of a report by design view is a user customized way
setting-up your report so to meet the users desired output and format.
Information Sheet 2 Modifying Report
2.1 Understand Layout view
Layout view is the most intuitive view to use for report modification, and can be used for nearly all the
changes you would want to make to a report in Access. In Layout view, the report is actually running, so
you can see your data much as it will appear when printed. However, you can also make changes to the
report design in this view. Because you can see the data while you are modifying the report, it's a very
useful view for setting column widths, add grouping levels, or performing almost any other task that affects
the appearance and readability of the report. The following illustration shows a Customer Phone Book
report in Layout view.
The report you see in Layout view does not look not exactly the same as the printed report. For example,
there are no page breaks in Layout view. Also, if you have used Page Setup for format your report with
columns, the columns are not displayed in Layout view. However, Layout view gives you a very close
approximation of the printed report. If you want to see how the report will look when printed, use Print
Preview.
Understand Design view
Design view gives you a more detailed view of the structure of your report. You can see the header and
footer bands for the report, page, and groups. The report is not actually running in Design view, so you
cannot see the underlying data while working; however, there are certain tasks you can perform more easily
in Design view than in Layout view. You can:
Fig.2.1 design view

Information Sheet 3 Distributing Report

3.1 Introduction
Report is a document containing information organized in a narrative, graphic, or tabular form, prepared on
ad hoc, periodic, recurring, regular, or as required basis. Reports may refer to specific periods, events,
occurrences, or subjects, and may be communicated or presented in oral or written form. Liberate your
Access reports and distribute them far and wide. For example, email them to one or more users, export them
to SharePoint document libraries, and archive them on network folders.
When you distribute an Access report, you create a static report of data at a certain moment in time, such as
daily, weekly, or monthly. By doing so, you have a single point of truth that information workers can use to
make good business decisions, answer questions, find alternatives, determine best plans, evaluate risks, and
improve quality. The term "business representative" is often used as a general reference for sales
professionals, customer service professionals or anyone serving as an interface between customers and a
company However, this term has a very specific meaning and application in the business world.
 Methods to distribute report
 Personalized emails: Segment your email list down to the exact audience that would benefit most
from your piece of content. Write a custom email to each of these audience members to add a level
of personalization to your message. Explain what the content is, and why you think he or she will
enjoy it..
Guest posting: Write an article that discusses -- in a non-promotional way -- the key findings or
points within your content, and send it to the editor of an online publication that reaches your
target audience. But be strategic about it. Make sure the publication not only helps you achieve
your own reach goals, but also, has something to gain by sharing your insights, from your
particular brand.
 Follow-up emails: Encourage your sales team to include a link to your content in their follow-up
emails to prospective clients, to answer their questions and position your company as a resource
they can trust. Note: This tactic works best when the content you create is educational and
addresses specific questions or concerns your leads have --and is actionable enough for them to
immediately apply it to their own plans or strategies.
Lead interviews: Work with your sales reps to identify prospective clients you can interview for your
content. Include a quote in your content, and share it with them once it's published. Not only can
that keep your leads engaged over time, but they'll appreciate the opportunity to be featured -- and
you benefit from the additional exposure to their networks when the content is shared with that
audience.
 Proposal references: The best proposals are often supported with relevant data that corroborates
the solutions you’re suggesting to a prospect. And while we suggest citing a variety of authentic,
reliable sources -- otherwise, you might look biased -- referencing your own research content can
be effective. Not only is it another way to distribute your work, but also, it illustrates the time and
thought your company has invested in this school of thought.
 That said, some prospective clients like proposals to be brief. In these cases, if you preemptively
anticipate additional questions, you can amend your proposal with a link to the content as a source
of further reading and information.

o Client drip campaigns

 If your content is related to your clients’ respective industries, or products and services, sharing it
with them can enhance your collaborations and further nurture that relationship. Remember, it’s
called client retention for a reason
Operation Sheet 1 Designing report
 To Creating Reports Using the Report Button

 Open the Navigation pane.
 Click the table or query on which you want to base your report.
 Activate the Create tab.
 Click the Report button in the Reports group. Access creates your report and displays your
report in Layout view. You can modify the report.
To save a eport
 Click the Save button on the Quick Access toolbar. Access saves the report unless you are
saving for the first time. If you are saving for the first time, the Save As dialog box appears.
 Type the name you want to give your report.
 Click OK. Access saves the report. You can now access the report by using the Navigation pane.
To create a report by using the Report Wizard:
 Open the Report Wizard
 Activate the Create tab.

 Click Report Wizard in the Reports group. The Report Wizard appears.
Operation Sheet 2 Modifying Report
 T0 Switch between views
 Right-click the report in the Navigation Pane, and then click the view you want on the shortcut
menu.1313
 Right-click the report's document tab or title bar, and then click the view you want on the
shortcut menu.
 On the Home tab, in the View group, click the View button to toggle between available views.
Alternatively, you can click the arrow under View, and then select one of the available views
from the menu.

 Right-click in a blank area of the report itself, and then click the view you want. If the report is
open in Design view, you must right-click outside of the design grid.
 Click one of the small view icons on the Access status bar.
 To Modify your report in Layout view
 Click an item in the column that you want to adjust.

o A border is drawn around the item to indicate that the field is selected.
 Drag the right or left edge of the border until the column is the width you want.
 To Change row or field height in Layout view
 Click an item in the row that you want to adjust.

o A border is drawn around the item to indicate that the field is selected.
 Drag the top or bottom edge of the border until the row is the height you want.
 Add a field in Layout view
 On the Design tab, in the Tools group, click Add Existing Fields.
o The list of available fields is displayed. If there are fields available in other tables, these
will be displayed under Fields available in other tables:.
 Drag a field from the Field List onto the report. As you move the field, a highlighted area will
indicate where the field will be placed when you release the mouse button.
Note: To add multiple fields at once, hold down the CTRL key and click each field in the Field List
that you want. Then, release the CTRL key and drag the fields onto the report. The fields will be
placed adjacent to each other.
 Create a new control layout
 You create a new report by clicking Report in the Reports group on the Create tab.
 You create a new report by clicking Blank Report in the Reports group on the Create tab, and
then dragging a field from the Field List pane to the report.
On an existing report, you can create a new control layout by doing the following:
 Select a control that you want to add to the layout.
 If you want to add other controls to the same layout, hold down the SHIFT key and also select
those controls.
 Do one of the following:
o On the Arrange tab, in the Table group, click Tabular or Stacked.
o Right-click the selected control or controls, point to Layout, and then click Tabular or
Stacked.
 To Change page setup
 Click the Page Setup tab.

 In the Page Size group, click Size to select a different paper size.
 In the Page Size group, click Margins to make adjustments to the report's margins.
 In the Page Layout group, click Portrait or Landscape to change the paper orientation.
 To Change the formatting of a field
 Select the field that you want to format.
 On the Format tab, use the tools in the Font group to apply the formatting you want.
 To Add or modify a logo or other image
To Add a logo
 On the Design tab, in the Header/Footer group, click Logo.
 Browse to the folder where your logo file is stored, and double-click the file.
 Add or edit a report title
To Add a title to a report
 On the Design tab, in the Header/Footer group, click Title.
o A new label is added to the report header, and the report name is entered for you
as the report title.
 When the label is created, the text in the label is selected for you so that if you want to
change the text, you can just begin typing the title you want.
 Press ENTER when you have finished.
To Edit the report title
 Double-click the label containing the report title to place the cursor in the label.
 Type the text you want to use as the report title, and press ENTER when you have finished.
 Add page numbers
 On the Design tab, in the Header/Footer group, click Page Numbers.

o The Page Numbers dialog box is displayed.
 Choose the format, position, and alignment you want for the page numbers.
 Clear the Show Number of First Page check box if you do not want a number on the first page.
 Click OK.
The page numbers are added to the report. Switch to Print Preview to see how the numbers will look
when you print the report.
 Add the date or time
 On the Design tab, in the Header/Footer group, click Date and Time.
o The Date and Time dialog box is displayed.
 Clear the Include Date check box if you do not want to include the date.
 If you want to include the date, click the date format you want.
 Clear the Include Time check box if you do not want to include the time.
 If you want to include the time, click the time format you want.
o A sample of the date and time, in the formats you have chosen, appears in the Sample
area of the dialog box.
 Click OK.

 Add line numbers

 On the Design tab, in the Controls group, click Text Box.
 Click in an open area of the section where you want the line numbers to appear. In most cases,
this will be the Detail section. You will move the text box to its final location later.
o When you click the report, Access creates a new, unbound text box.
 Click the label (just to the left of the new text box), and press DELETE.
 Click once in the new text box to select it, and then click again to position the cursor in the text
box.
 Type =1 and then press ENTER.
 If the property sheet is not already displayed, press F4 to display it.
 On the Data tab of the property sheet, set the Running Sum property to Over All.
o Note: If this is a grouped report, and you want the numbering to start at 1 for each
group, set the property to Over Group.
 Resize the text box to a smaller width by positioning the pointer over the sizing handle on the
right edge of the text box and dragging it to the left. Leave enough room for the largest line
number you expect to see on this report.
 If needed, make room for the text box on the far left edge of the Detail section by dragging the
existing controls in that section to the right, or by resizing the leftmost control in that section.
 Drag the new text box to the location that you want it on the report.
 Switch to Report view, Print Preview, or Layout view to see the line numbers.
 To Make a group header appear at the top of each page
 Double-click the group header section selector (the horizontal bar above the group header
section).
To Sort Fields
When creating labels, you can sort on any field and you can have multiple levels of sort. For example, you
can sort by last name and then by first name.
1. Click to choose the fields you want to sort by. Click the single right-arrow to select a single field,
click the double right-arrow to select all fields, click the single left-arrow to deselect a single field,
click the double left-arrow to deselect all fields.
2. Click Next. The Label Wizard moves to the next page.

Title the report
1. Type a title for your report. The title will appear in the Navigation pane.
2. Click Finish. Access displays the labels in Print Preview.
To Print a Report
1. click the Print button in the Print group.
2. The Print dialog box opens
3. select your print options.
To change to Print Preview:
1. Open your report.
2. Activate the Home tab.
3. Click the down-arrow under the View button. A menu appears.
4. Click Print Preview. Access changes to Print Preview.

Several options are available to you in Print Preview.

Print Preview Options
Print Displays the Print dialog box. You can select such options as the printer, print range,
and number of copies. Use this option to print your report.
Size Click the Size button to set the size of the paper you are going to print on.
Portrait Click the Portrait button to print with the shortest side of the paper as the top.
Landscape Click the Landscape button to print with the longest side of the paper as the top.
Margins Click the Margins button to select a margin size of Normal, Wide, or Narrow. Margins
define the amount of white space that surrounds your report.
Print Data Only Prints the report data without other elements such as titles, and labels.
Zoom Changes the cursor to a magnifying glass. When the magnifying glass displays a minus
sign (–), you can zoom out. When the magnifying glass displays a plus sign (+), you can
zoom in.
Click the down-arrow under the Zoom button to display a menu and choose a zoom
level.
One Page Displays one page of the report in the Access window.
Two Page Displays two pages of the report in the Access window.
More Pages Displays a menu from which you can choose the number of page you want to display.
To create a report based on a table using the Report command
 Choose the table you wish to use as the source of your report. To do that, you can either open
the table or highlight the table name in the Navigation Pane. In our example, we used the
open Books table to create the report.
 Select the Report command on the Create tab on the Ribbon, as seen above.
 The report is automatically generated and includes every field in the table in order of their
appearance in the table. This can be seen in the example below, which was created from the
table above.

The layout and formatting of the report can be manipulated in Layout View.
Creating a report based on a query
Access 2007 can also create a report using a query as the source. The process for creating a report
based on a query is identical to the process for creating a report based on a table, which was outlined
on the previous page. And just like when making a report from a table, every field and record that
appears in the query results will appear on the report.
To limit the number of records in a report
It is possible to limit the number of records in a report, but only if the report was based on a query.
The limit is set in the query itself, using the query design screen.
To limit the records returned in a query:
 Open the query in Design View.
 Use the Return option in the Query Setup command group to set the number of records you
want to see in the query results and the final report.
 Click Run! to make sure the query results look like you want the report to look.

 Create the report using the Report command on the Create tab.
 Format the report as desired.
To add a level of grouping to a report:
 With the report open, select the Group & Sort command from the Grouping &
Totals command group on the Format tab on the Ribbon.
 Open a Group, Sort, and Total dialog box in the lower portion of the window.
 In the Group, Sort, and Total dialog box, select Add a group.
 Select the field you wish to group by from the drop-down list. We chose to group our list
by Category.

 When you release the mouse button, the report will now appear with items grouped. Our
report is grouped on Category now, as seen below.
The Group, Sort, and Total dialog box will remain open until you close it.
To Format a report in Layout View
Access 2007 opens the created report in Layout View so you can easily make modifications. In
Layout View, you can change the look of your report in many different ways, including:
 Deleting columns and other report elements
 Moving and resizing columns
 Adding a logo
 Changing the title and other text on the report headings
 Applying a report style with AutoFormat

 Modifying the page layout

To delete a column or other report element:
 Highlight the element by clicking on it.
 Hit the Delete button on your keyboard.
To move a column or other report element
 Drag and drop the element to a new location on the report.
To resize a column or other report element
To resize a column or other report element:

 Drag and drop the edge of the element to the new size on the report.
To add a logo to the report
 Click on the Logo command on the Format tab on the Ribbon.
 When the Insert Picture dialog box opens, find the picture file.
 Click OK.
To modify the title of a report:

 Click on the Title command on the Format tab on the Ribbon.

 When the highlight appears, type in the new title.

To modify text in report headings
If you don't like the standard font face and size Access used to create your report, you can modify
them using common Microsoft Office text formatting commands. You can modify the size, font face,
font color, alignment, and much more. They all work basically the same way.
 Highlight the text you want to change.
 Select the formatting option you wish from the lists that appear when you click on a
command.
 The change appears when you release the mouse button.

To apply an AutoFormat style
Like with forms, Access 2007 offers a variety of report styles in the AutoFormat command. To
apply a style:
 Click on the AutoFormat command on the Ribbon.
 Select a format from the drop-down list. The change is applied instantly.

To change the page layout

When a report is created, it opens in Layout View, like the one in the picture below. The dotted lines
are showing where the edge of the page will be in Report View.
 Switch to Print Preview using the Views command on the Ribbon.
 Select the layout option you wish to alter from the Page Layout command group on the
Ribbon.
Page Layout Option Description

Margins To set the margins for narrow, wide, or normal
Saving a report
When you have created and modified a report and try to close it, Access 2007 will prompt you to
name and save the report. If you do not need this report again, you do not need to save it. However, if
you think you may want to publish it again, it is best to save.

To save a report
As with all Access objects, to save a report:
 Right-click on the report tab.
 Choose Save from the list that appears.
 When the Save as dialog box opens, give the report a name.
 Click OK.!
 On the Format tab of the property sheet, set the Repeat Section property to Yes.
Name
LAP Test Practical Demonstration :
_____
_____
______________________Date:_____________________________
Time started: _____________________Time finished: ____________________________
Task 1
 Create Reports
 Use the Report Button
 Use the Report Wizard
Task2
 Modify a Report
 Change view
 Change the Size of a Field or Label
 Report Properties
o Add a field
o Delete a field
o Move a column
o Change a title

LO 4: Create database Forms

Information Sheet 1 Using wizard to create a simple form
1.1Introduction
A form is a screen that allows you to enter, change, and view the data in a database. Think of forms as
windows into your data that help users understand and work with that data.
Forms are made up of controls, such as text boxes, buttons, document tabs, and drop-down lists, grouped in
a way that makes them easy to use and helps you get work done.The controls in the form are usually bound,
or connected, to the tables or queries in your database — but not always. For example, a control that displays
your corporate logo doesn't have to be bound to a table field. It can just point to the image it displays.In
addition to entering data, you can use forms in other ways. For example, you can create a form that asks for
input, and then generates a custom report based on that input.
In Access 2007, a form is an object that generally serves three purposes:

 To allow users to perform data entry. Data can be inserted, updated, or
deleted from a table using a Form object.
 To allow users to enter custom information, and based on that information perform a task.
For example, you may want to ask a user for parameters before running a report.
 To allow users a method of navigating through the system. For example, you may create a
form where a user can select a form to load, a report to run, etc.
1.2 Creating Forms using a wizard

Creation of a form by using a wizard is the Ms Access pre-defined way of creating a form by simply
following the series of steps and choosing which field and format you would want for your form.
Self-check 1 Using wizard to create a simple form
Name: __________________________ Date: ___________________________________
Time started: __________________ Time finished: ____________________________
Directions: Answer all the questions listed below.
1. Form is made up of? (1 points)

2. Way of creating forms? (2 Point)
3. Is a screen that allows you to enter, change, and view the data in a database? (1 points)
4. The most important tools can be found in? (1 point)
5. A form is an object that generally serves three purposes? (3 points)
6. List & Discuss on the different types of Controls in MS access? (12 point)
: Satisfactory rating – above 17 points Satisfactory & below 16 points Unsatisfactory
(You can ask you teacher for the copy of the correct answer)
Information Sheet 2 Opening Existing Database& Modifying Records
What Database
A database is an organized collection of data, generally stored and accessed electronically from a
computer system. Where databases are more complex they are often developed using formal design and
modeling techniques.

 Types of databases. Databases have evolved since their inception in the 1960s, beginning with …
 Relational database. A relational database, invented by E.F. Coded at IBM in 1970, is a tabular …
 Distributed database. A distributed database is a database in which portions of the database are …
 Cloud database. A cloud database is a database that has been optimized or built for a virtualized
Open an existing database
There are the different methods you can use to open existing Access databases. You can open databases from
Windows Explorer or from within Access itself. You can open multiple databases at once, and you can also
create desktop shortcuts that open database objects directly.
Information Sheet 3 Rearranging Objects with in the form
Objects: A Form object refers to a particular Microsoft Access form. A Form object is a member of the
Forms collection, which is a collection of all currently open forms. Within the Forms collection, individual
forms are indexed beginning with zero.
Forms are made up of controls, such as text boxes, buttons, document tabs, and drop-down lists,
grouped in a way that makes them easy to use and helps you get work done.The controls in the form are
usually bound, or connected, to the tables or queries in your database — but not always. For example, a
control that displays your corporate logo doesn't have to be bound to a table field. It can just point to the
image it displays. In addition to entering data, you can use forms in other ways. For example, you can create
a form that asks for input, and then generates a custom report based on that input.
Operation Sheet 1 Using a wizard to create a simple Form

Steps to create a form
1. Open the Navigation pane.
2. Click the table or query on which you want to base your form.
3. Activate the Create tab.
4. Click Form in the Forms group. Access creates a form.
You can use the Navigation bars to move through the records on a form.

1 Go to First Record
2 Go to Previous Record
3 The Current Record
4 Go to Next Record
5 Go to Last Record
6 Create a New (Blank) Record
Tip: After you create a form, you can save it. You can open a saved form at any time.
Steps to save a form
1. Click the Save button on the Quick Access toolbar. Access saves the form unless you are saving for
the first time. If you are saving for the first time, the Save As dialog box appears.
2. Type the name you want to give the form.
3. Click OK. Access saves the form. You can now access the form by using the Navigation pane.
You can also save by right-clicking a form’s tab and then selecting Save from the menu that appears.
Access saves the form unless you are saving for the first time. If you are saving for the first time, the Save
As dialog box appears. Type the name you want to give the form and then click OK. Access saves the form.
You can now access the form by using the Navigation pane.
Create a Split Form
A split form is a form in which the same data is displayed in two views simultaneously. One part of
the form displays in Form view (stacked fields), while the other part displays in Datasheet view. The two
views are synchronized, so as you select a field in one view, it is automatically selected in the other view.
You can add, change, or delete the data in either view. Using a split form gives you the benefits of two types
of forms in a single form. For example, you can use the datasheet portion to locate records and the form
portion to edit records.
To create a split form

4. Click Split Form in the Forms group. Access creates a split form.

Create a Multiple Items Form

You can use the Multiple Items button on the Forms tab to create a form that displays multiple records, one
record per row.
To create a multiple items form

4. Click Multiple Items in the Forms group. Access creates a multiple items form.

Tip: A view is a way of looking at an Access object. Forms have three views: Form view, Layout view, and
Design view. You can enter, edit, and view data in Form view. You can modify a form in Layout view or
Design view. In Layout view, you can see your data, and the form you see closely resembles what your form
will look like when you view it in Form view. You can make most, but not all, changes to your form in
Layout view. Design view displays the structure of your form. In this view you cannot see the underlying
data, but you can perform some tasks in Design view that you cannot perform in Layout view. This tutorial
focuses on Layout view.
To change the view
1. Open the form.
2. Activate the Format tab.
3. Click the down-arrow under the View button. A menu appears.
4. Click the view you want.
To change the size of a field
1. Click a side of the field and drag to change the width of the field.
2. Click the top or bottom of a field and drag to change the height of a field.
To move a datasheet
1. Click the datasheet to select it.
2. Click and drag the four-sided arrow in the upper-right corner to move the datasheet.
To resize a datasheet
1. Click the datasheet to select it.
2. Click a side of the datasheet and drag to change the width.
3. Click the top or bottom of the datasheet and drag to change the height.
To apply an AutoFormat
The AutoFormat option on the Format tab enables you to apply formats quickly, such as background
colors, field colors, field label colors, and fonts.
2. Click AutoFormat. The AutoFormat menu appears.
3. Click the format you want to apply.
To change a form title
When you create a form, by default, Access uses the form name as the title. You can change the title.
2. Click the Title button.
3. Type the new title.
To add the date and time
You can easily add the date and time to your form.
2. Click the Date and Time button. The Date and Time dialog box appears. Select the date and time
format you want. The date and time appear on your form.
Operation Sheet 2 Opening &Modifying Existing database
 To Open a database from within Access
 On the getting started page of Access, Click Open Other Files.
 On the Open area of the backstage view, click Browse.

 Click a shortcut in the Open dialog box, or in the Look in box, click the drive or folder that contains the
database that you want.
 In the folder list, browse to the folder that contains the database.
 When you find the database, do one of the following:
o Double-click the database to open it in the default mode specified in the Access Options dialog box
or the mode that was set by an administrative policy.
o Click Open to open the database for shared access in a multi-user environment so that you and other
users can read and write to the database.
o Click the arrow next to the Open button and then click Open Read-Only to open the database for
read-only access so that you can view but not edit it. Other users can still read and write to the
database.
o Click the arrow next to the Open button and then click Open Exclusive to open the database with
exclusive access. When you have a database open with exclusive access, anyone else who tries to
open the database receives a "file already in use" message.
o Click the arrow next to the Open button and then click Open Exclusive Read-Only to open the
database for read-only access. Other users can still open the database, but they are limited to read-
only mode.
 To open one of the last several databases you had open,
 click the file name in the Recent list on the getting started page. Access opens the database with the
same option settings it had the last time you opened it. If the list of recently used files is not displayed:
 Click File>Options.
 In the Access Options dialog box, click Client Settings or Advanced.
 Under Display, type a number in the Show this number of Recent Databases box.
Create a desktop shortcut to open a database object
 Open the database containing the object for which you want to create a shortcut.
 Resize the Access window and minimize any other open windows so that you can see the desktop
behind the Access window.
 In the Navigation Pane, find the object for which you want to create the shortcut.
 Drag the object from the Navigation Pane to the desktop. When you release the mouse button, the
shortcut is created on the desktop.
 If you want the shortcut in a location other than the desktop, use Windows Explorer to move the
shortcut to the location you want.
Operation Sheet 3 Rearranging objects within the form
 To Add a Command Button Using a Control Wizard
o Open the form in Design View
 Verify the Control Wizards command is selected in the Controls group on the Form Design Tools
Design Contextual tab
 Click the Button command in the Controls group on the Form Design Tools Design Contextual tab
 Click on your form where you want the command button to be inserted

Figure 31. Command Button Wizard - Choose a Category & Action

 Select a category and action
 Click Next
Figure 3.2. Command Button Wizard - Choose the Command Button Options
 Set your command button option
 Click Next
 Figure 3.3. Command Button Wizard - Choose How the Button Should Work
 Choose how the button should work
 Click Next

Figure 3.4. Command Button Wizard - Choose Text or Picture Display

 Choose to show Text or a Picture on the command button
 Click Next
Figure 3.5. Command Button Wizard - Enter Command Button Name

 Enter a name for the command button
 Click Finish
 Save the form

LAP Test Creating Database Form
Name: ___________________________________ Date: ____________________________________

Time started: ___________________________ Time finished: _______________________________
 Task 1
 Create a form by using a wizard
 Task 2
 Create the Navigation Buttons
 Create a label that displays your name
 Task 3
o Create a text box
o Insert Picture
o Insert Chart
o Create a link to open another form
o Create control button that closes the form
o Save the Form
o Open Existing form

LO 5: Create database Queries
Information Sheet 1 Accessing & Locating Required records
Locate specific records in a database: As your database grows, locating specific records will involve more
than a quick glance at a datasheet. In this article, learn five ways to locate specific records based on your
needs.
Browse through all records you can browse through records by using the TAB key when you want to move
through one record at a time, in order, to locate a specific record. You can also browse through records in a
table in Datasheet view using the record navigation buttons. The record navigation buttons are available at
the bottom of the table or form.
Browse through all records You can browse through records by using the TAB key when you want to move
through one record at a time, in order, to locate a specific record. You can also browse through records in a
table in Datasheet view using the record navigation buttons. The record navigation buttons are available at
the bottom of the table or form.
The techniques that you can use to search and filter records are very useful for finding specific records for
the case at hand. However, you might want to perform the same search or filter operation regularly. Instead
of reproducing a set of search and filter steps every time, you can create a query. A query is a powerful and
flexible way to locate specific records because it lets you perform customized searches, apply customized
filters, and sort records. You can build your own queries to help you focus on specific records and to answer
specific questions. Once created, a query can be saved and reused, and can also be used in building forms
and reports.
Information Sheet 2 Creating Simple Query to Retrieve Information
A query is a request for data results, for action on data, or for both. You can use a query to answer a simple
question, to perform calculations, to combine data from different tables, or even to add, change, or delete
table data. A query is a derived item in the database meant
to answer specific questions that relate to the information in the database. It is the means to retrieve relevant
information in one or more tables.
2.1Types of Queries
 Select Query
 The select query is the simplest type of query and because of that, it is also the most commonly used
one in Microsoft Access databases.

 A select query is the most common type of query.

 It retrieves data from one or more tables and displays the results in a datasheet where you can update
the records (with some restrictions).
 You can also use a select query to group records and calculate sums, counts, averages, and other types
of totals.
 It can be used to select and display data from either one table or a series of them depending on what is
needed.
 In the end, it is the user-determined criteria that tell the database what the selection is to be based on.
 After the select query is called, it creates a "virtual" table where the data can be changed, but at no
more than one record at a time.
 Action Query
 Action queries are very popular in data management because they allow for many records to be
changed at one time instead of only single records like in a select query.
 When the action query is called, the database undergoes a specific action depending on what was
specified in the query itself.
 This can include such things as creating new tables, deleting rows from existing ones and updating
records or creating entirely new ones.
 Four kinds of action queries are:
 Append Query
 An append query adds a group of records from one or more tables to the end of one or more
tables. For example, suppose that you acquire some new customers and a database containing a
table of information on those customers. To avoid typing all this information into your own
database, you'd like to append it to your Customers table.
 Delete Query
 A delete query deletes a group of records from one or more tables. For example, you could use a
delete query to remove products that arediscontinued or for which there are no orders. With
delete queries, you always delete entire records, not just selected fields within records.
 Make Table Query
 As the name suggests, it creates a table based on the set results of a query. A make-table query
creates a new table from all or part of the data in one or more tables. Make-table queries are
helpful for creating a table to export to other Microsoft Access databases or a history table that
contains old records.
 Update Query
 Allows for one or more field in your table to be updated.
 An update query makes global changes to a group of records in one or more tables.
 For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries
by 5 percent for the people within a certain job category. With an update query, you can change
data in existing tables.

 Parameter Query
 In Microsoft Access, a parameter query works with other types of queries to get whatever results you
are after.
 This is because, when using this type of query, you are able to pass a parameter to a different query,
such as an action or a select query.
 It can either be a value or a condition and will essentially tell the other query specifically what you want
it to do.
 It is often chosen because it allows for a dialog box where the end user can enter whatever parameter
value they wish each time the query is run. The parameter query is just a modified select query.
 A parameter query is a query that when run displays its own dialog box prompting you for information,
such as criteria for retrieving records or a value you want to insert in a field.
 You can design the query to prompt you for more than one piece of information; for example, you can
design it to prompt you for two dates.
 Access can then retrieve all records that fall between those two dates.
 Parameter queries are also handy when used as the basis for forms, reports, and data access pages.
Information Sheet 3 Developing Query With Multiple Criteria
3.1 Introduction to query criteria

A criterion is similar to a formula — it is a string that may consist of field references, operators, and
constants. Query criteria are also referred to as expressions in Access.
The following tables shows some sample criteria and explains how they work.
Criteria Description
This criterion applies to a Number field, such as Price or UnitsInStock. It includes
>25 and <50 only those records where the Price or UnitsInStock field contains a value greater than
25 and less than 50.
DateDiff ("yyyy", This criterion applies to a Date/Time field, such as BirthDate. Only records where the
[BirthDate], Date()) > number of years between a person's birthdate and today's date is greater than 30 are
30 included in the query result.
This criterion can be applied to any type of field to show records where the field value
Is Null
is null.
As you can see, criteria can look very different from each other, depending on the data type of the field to
which they apply and your specific requirements. Some criteria are simple, and use basic operators and
constants. Others are complex, and use functions, special operators, and include field references.
This topic lists several commonly used criteria by data type. If the examples given in this topic do not
address your specific needs, you might need to write your own criteria. To do that, you must first familiarize

yourself with the full list of functions, operators, special characters, and the syntax for expressions referring
to fields and literals.
Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the query
in Design view. You then identify the fields for which you want to specify criteria. If the field is not already
in the design grid, you add it by either dragging it from the query design window to the field grid, or by
double-clicking the field (Double-clicking the field automatically adds it to the next empty column in the
field grid.). Finally, you type the criteria in the Criteria row
Criteria that you specify for different fields in the Criteria row are combined by using the AND operator. In
other words, the criteria specified in the City and BirthDate fields are interpreted like this:
City = "Chicago" AND BirthDate< DateAdd("yyyy", -40, Date())
 The City and BirthDate fields include criteria.

 Only records where the value of the City field is Chicago will satisfy this criterion.
 Only records of those who are at least 40 years old will satisfy this criterion.
 Only records that meet both criteria will be included in the result.
What if you want only one of these conditions to be met? In other words, if you have alternate criteria, how
do you enter them?
If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you
use both the Criteria and the or rows in the design grid.
Information Sheet 4 Selecting & Displaying Data
4.1 Introduction
Data selection is defined as the process of determining the appropriate data type and source, as well as
suitable instruments to collect data. The primary objective of data selection is the determination of

appropriate data type, source, and instrument(s) that allow investigators to adequately answer research
questions.
4.2 The function of displaying data
 Displaying data in research is the last step of the research process.
 It is important to display data accurately because it helps in presenting the findings of the
research effectively to the reader.
 To make the findings more visible and make comparisons easy.
Operation Sheet-1 Accessing &Locating Records
Steps to browse through all records you can browse through records by using the TAB key when you
want to move through one record at a time, in order, to locate a specific record. You can also
browse through records in a table in Datasheet view using the record navigation buttons. The
record navigation buttons are available at the bottom of the table or form.
1. Go to the first record 5. Go to the last record

2. Go to the previous record 6. Open a new (blank) record
3. Current Record box 7. Filter indicator
4. Go to the next record 8. Search box
Steps to Search for a specific record
 Open the table or form, and then click the field that you want to search.
 On the Home tab, in the Find group, click Find, or press CTRL+F.
The Find and Replace dialog box appears, with the Find tab selected.
 In the Find What box, type the value for which you want to search.
 To change the field that you want to search or to search the entire underlying table, click the
appropriate option in the Look In list.
 In the Search list, select All, and then click Find Next.
 When the item for which you are searching is highlighted, click Cancel in the Find and Replace
dialog box to close the dialog box. Records that match your conditions are highlighted
Steps to create a query to find a specific record
 On the Create tab, in the Queries group, click Query Design.
 In the Show Table dialog box, double-click Issues, and then click Close.
 In the query designer, double-click the asterisk (*) in the Issues table. This helps make sure that the
query will display all the fields from the records it returns.
Issues.* appears in the first column of the design grid, in the Field row. This indicates that all the
fields from the Issues table should be returned.
 In the query designer, double-click Status on the Issues table.
Status appears in the second column in the design grid, in the Field row.

 In the second column of the design grid, clear the check box in the Show row. This helps make sure
that the query does not display the Status field.
If you do not clear the Show check box in the Status column, the Status field will be displayed two
times in the query results.
 In the second column of the design grid, in the Criteria row, type ="Closed". This is your search
criterion. This is how you make sure that the query will return only those records where the value of
Status is "Closed."
Note: In this example, only one search criterion is used. You can use many search criteria for any
given search by adding criteria to more fields, and by using the
 On the Design tab, in the Results group, click Run.
Note: Unless you have already begun tracking issues and therefore have data in the Issues table —
and you have set the status of at least one issue to "Closed" — the query will not return any results.
However, you can save the query, and use it at any time in the future.
 Press CTRL+S to save the query.

 In the Save As dialog box, type a name for the query in the Query Name field, such as Closed
Issues, and then click OK.
Operation Sheet-2 Creating Simple Query to Retrieve Information
The simplest way to create a query is by using the Query Wizard. It presents a list of tables and queries you can select
from the current database.
StepsTo use the Query Wizard, 17
1. on the Ribbon, you can click the Create tab and, in the Other section, click Query Wizard . This would
display the New Query dialog box:
2. On the New Query dialog box, you can click Simple Query Wizard and click OK. The first page of the Simple
Query Wizard expects you to choose the origin of the query as a table or an already created query. After selecting
the table or query, the second page of the wizard would present the fields of that list and you can select those you
want:
The next page of the wizard allows you to specify the name of the query:

Steps to create Query in Design view

Like other objects of a database, a query can be designed. You design a query using the Design View:
 To display a query in Design View, from the Navigation Pane, you can right-click a query and click Design
View
 To start designing a new query, in the Other section of the Create tab of the Ribbon, click Query Design
This would display the Show Table dialog box that allows you to specify the table or query that holds the fields you
want to use in the intended query
If the Show Tables dialog box is closed or for any reason you want to display it:

 In the Query Setup section of the Design tab of the Ribbon, you can click the Show Table button
 You can right-click anywhere on the query window and click Show Table...
When a query is displaying in Design View, the Design tab of the Ribbon displays the buttons used for a query:
The Query Window

The Query window allows you to design and manage various aspects of a query. You can right-click the title bar of
the Query window to access a menu:
One of the operations you can perform on the Query window consists of resizing its top and bottom sections by
dragging the splitter bar up or down:
Selecting the Columns

To create the fields for a query, you use the table(s) or query( queries) displayed in the upper section of the window.

Once you have decided on the originating object(s), you can select which fields are relevant for your query:
 To select one field from the list, just click it
 To select many fields on the same range, you can click one of them, press and hold Shift. Then click one field
on the other end of the desired range
 To select fields at random, click one of the desired fields, press and hold Ctrl; then click each one of the desired
fields
 To select all fields, you can click the * line on the list of fields
To Add Columns
To make a field participate in a query, you have various options:
 Once you have made your selection on the list in the top part of the query window, you can drag it and drop it
in the bottom section of the query window
 You can also select more than one field and drag them:

 Instead of dragging a field or all fields, you can either double-click a field to add it to the query, or double-click
the line with * to add all fields to the query
 In the bottom part of the query window, click an empty Field box to show a combo box. Then click the arrow
of that combo box and select an item from the list:
Executing a Query
To execute a query:
 If the query is currently closed, from the Navigation Pane:
o You can double-click it
o You can right-click it and click Open
 If the query is already opened and it is in Design View, on the Ribbon:
o You can click the Run button
o You can click the View button or you can click the arrow of the View button and click
Datasheet View
If you manually write a SQL statement and want to execute it, change the view to Datasheet View.
Selecting a Column
Some operations require that you select a column from the bottom section of the query window:
 To select a field in the lower section of the view, click the tiny bar of the column header:

The whole column will be selected

 To select a range of columns, click the column header of one at one end, press and hold Shift, then click the
column header at the other end
Since selecting a column in the Query window is a visual operation, there is no equivalent in SQL.
Removing a Column From a Query
As seen above, a query is built by selecting columns from the originating list and adding them. If you do not need a
column anymore on a query, which happens regularly during data analysis, you can either delete it or replace it with
another column:
 To delete a column:
o Once it is selected, you can press Delete
o Right-click the column header and click Cut
 To delete a group of columns, select them and press Delete
Replacing a Column
To replace a column, click the arrow on the combo box that displays its name and select a different field from the list:
Moving a Column
Columns on a query are positioned incrementally as they are added to it. If you do not like the arrangement, you can

move them and apply any sequence of your choice. Before moving a column or a group of columns, you must first
select it. Then:
To move a field
1. click its column header once.
2. Click it again and hold your mouse down,
3. drag in the direction on your choice
4. To move a group of columns, first select the group and then proceed as if it were one column
To Create a select query in a desktop database:

 Click Create > Query Design.
 In the Show Table box, double-click the Products table > Close.
 To add the fields to the design grid, double-click the Product Name and List Price fields.
 In the criteria row, under List Price add a criteria. For example, >=10 to show a list of products
more than or equal to $10.00.
5. To see the query results, on the Design tab, click Run.

Steps To Create an Append Query
1. Click the QUERY DESIGN icon (located in the OTHER group of the CREATE ribbon). The
QUERY DESIGN window then opens along with the SHOW TABLE dialog form.
2. The next step is to add tblMoreNames to the QUERY DESIGN window. Do this by clicking ADD
in the SHOW TABLE dialog form. Notice it is the table containing the data to behappended that we
have selected.
3. Click the APPEND icon from the QUERY TYPE group of the DESIGN ribbon. As you do this, you
will see the APPEND dialog box open.
4. You are now asked to select the name of the original table to which the new data is to be appended.
So select tblContacts from the drop down list.
5. You are also asked whether this table is stored in the current database or in an external database. In
this exercise both tables are stored in the current database. This is the default button displayed in the
option group, so there should not be any need to change it.
6. Click OK to close the dialog box.
7. Next we are going to select the fields from tblMoreNames to be appended. To do this drag and drop
the Initials and LastName fields from the table (in the top half of the window) down onto the design
grid.
8. Next we are going to tell Access which fields the data from Initials and Lastname will be
appended to. To do this go down to the APPEND TO row of the design grid (see figure 3 below),
and ]select FirstName in the Initials column, and Surname in the LastName Column.
Figure 2: The Query Design Grid.
9. We could add query criteria at this stage, but this particular exercise does not require any. If we did,
however, this is added in the CRITERIA row just like it is with a select query.

10. If you want to view the data that is going to be appended, click the VIEW icon from the RESULTS
group of the DESIGN ribbon. It is especially important to do this if any if any criteria is applied in
step 9 above.
11. Once you are satisfied the correct data is going to be appended, click the RUN icon, again from the
RESULTS group of the DESIGN ribbon.
12. A dialog box opens informing us that 10 rows are going to be appended, and asking us to confirm
that we want to go ahead with this operation. Click YES to complete.
To see the result of our Append Query, re-open tblContacts.
Steps to Create Delete Query

1. Open the Employees table.
It should open and look like this:
Note that Tom Gumman and Tina Gumman are currently employees.
2. Close the table.
3. Click the Create tab.

In the Ribbon, click Query Design.
4. When the Show Table window appears, click Employees.

Then click the button.
5. Click the button.
6. In the Ribbon, click Delete.
To Add fields
1. In the field list of the Employees table, click-and-drag the * field to the first column of the
design grid.
2. Release the mouse button.

The query design grid should look like this:
3. In the field list of the Employees table, double-click the Last Name field.

The query design grid should now look like this:
To Add criteria
1. In the Last Name column of the design grid, click in the Criteria row.
2. Type: Gumman then press the ENTER key. It should look like this:
3. In the Ribbon, click Run.

When the alert window appears, click the button.
TIP: Be careful when designing Delete queries. Once a record is deleted, the deletion cannot
be undone.
You may want to run the query as a regular query first, to verify it's choosing the correct
records, and then switch the type to a Delete query.
4. On the Title Bar, click the icon.
5. When the Save As window appears, type:
Delete Old Employees
in the Query Name box.

7. Close the query window.
To Verify The results
1. Open the Employees table. It should look like this:
Note that Tom Gumman and Tina Gumman are no longer listed as employees.
2. Exit Microsoft Access.
Example 2
Stage 1 - Creating the initial Select Query

1. Select the CREATE TAB of the Access Ribbon.

2. Click the QUERY DESIGN icon. It is located in the OTHER group of the CREATE ribbon.
3. Select tblContacts from the SHOW TABLE dialogue box.
4. Drag the asterix (*) from tblContacts down to the first column of the DESIGN GRID. This is a way
of getting the query results to display all fields from the table without having to select each one
individually.
5. Then Drag the Company field from tblContacts down to the second column of the grid. We have
added this field separately because we are going to enter a criteria in this column.
6. Click on the CRITERIA row of the Company column, and add the criteria: "Company 5"
The Select Query has now been created. It should look like this:
Figure 2: The Select Query created in

the first stage of the Delete Query.
It is advisable to run the query at this point and check the results are correct. They should look like this:
Figure 2: The results from the Select Query.
As you can see, our select query has found four records from tblContacts matching the criteria of Company
5". Since this is the correct result for the dataset we are working with, we can move onto the second stage
of the process: converting the Select Query to a Delete Query.
Stage 2 - Converting the Initial Select Query to a Delete Query
1. If you look at the QUERY TYPE group of the DESIGN ribbon, you will notice that the SELECT
QUERY icon is highlighted orange. We need to change this to DELETE QUERY. To do this just
click the DELETE QUERY icon further along the group.
Figure 3: The QUERY TYPE group of the DESIGN ribbon.

The DELETE QUERY icon is highlighted orange.

2. After clicking the DELETE icon, you will notice that the row of SHOW tick boxes disappears from
the DESIGN GRID, along with the row for SORT. A new row entitled DELETE has taken their
place. Access has filled in the values of FROM and WHERE in the first and second columns
respectively. These are SQL Keywords: the FROM keyword indicates the first column contains
fields from tblContacts, and WHERE indicates the Company column contains a criteria against the
data stored in this field.
Figure 4: The QUERY DESIGN GRID for our DELETE Query. Notice the new row for DELETE
containing the SQL FROM and WHERE Keywords.
3. Click RUN from the QUERY RESULTS group.

4. Click YES when prompted whether we want to delete the number of rows matching our query
criteria. This will be four rows for the dataset we have been working with.
We can now go back and open the tblContacts table. As you can see from Figure 5 below, all Company
5 contacts have been removed by our DELETE QUERY.
Steps to Create Make table Query

Using our scenario, we will create a make-table query for all customers from the specified area that have
ordered products in the past 12 months. We will need to perform the following steps to create the query:

1. Create a new query, by using the Customers and Orders tables.
Steps to
2. From the Query Type button on the toolbar, select Make Table
The Make Table dialog box appears, where you should enter the name for the new table. Here we can
also select whether we want to create the new table in the current database or in another database.
Ensure that the current database is selected and click OK.
3. Select the mailing information fields, in our case CustomerTitle, CustomerName, Address, City,
Postcode from the Customers table, and OrderDate from the Orders table.
4. Specify the chosen City criteria in the City field and add the criteria required in the OrderDate field to
only show records from the last 12 months - using the DateAdd function: >=DateAdd("yyyy",-1,Now())

The query design should appear like the example below:
5. To check that the results are returned that we expect, click on the datasheet button on the toolbar.
Once you have verified this, switch back to query design view.
6. In query design, deselect the Show: property for the OrderDate field, as we do not need this to be
visible in our new table.
7. Click on the Run button on the toolbar. Microsoft Access now displays a message to indicate how
many records will be copied to the new table.
8. Click Yes to complete the query, and create the new table.
Using an Update QueryExample 1
1. When the Show Table window appears, select the tables that you wish to use in the query
2. Click on the Add button. When you have finished adding your tables

3. Click on the Close button.
In this example, we've selected the Big and Bot tables. You can select multiple tables by holding down the
Ctrl key while selecting the table names.
4. Next, right-click somewhere next to the tables (but not on a table) in the query editor,
5. select Query Type > Update Query from the popup menu.
6. Next, build the query like the one below:

This query will update the MFG field in the Bot table with the value in the MFG field in the Big table when
the PART values match.
The SQL for this query is as follows:
UPDATE Big INNER JOIN Bot ON Big.PART = Bot.PART
SET Bot.MFG = [Big].[MFG];
Example 2
Here are the steps that we follow to produce the required outcome:
1. Create a new query using the Products table and the Suppliers table. Include the fields that you are
going to use to update the data (ProductID, ProductName and UnitPrice from the Products table, and
CompanyName from the Suppliers table)
We have also included criteria in the CompanyName field to limit the results to only those of the
Supplier that we are updating the records for.
Also, in the image below, you will see that we have included an additional field, just to test our
expression. This will give us a value for the 3% increase, just to check that the results will be returned
correctly. This field will be removed before we run the update, but we will use the expression later.

Creating a SELECT query, that will later be changed to the UPDATE Query
2. To check what results this will produce, run the query by clicking on the datasheet button
Checking the results before running the Update Query

3. When we are happy with the resulting data, we can switch back to design view to convert the query to
an Update query.
From the Query Type button on the toolbar, select Update Query
In the curUnitPrice column, in the Update To cell, type in the expression[curUnitPrice]*1.03 and press
enter. This expression will update the original Unit Price by 3 Percent.
The query design should now look like:
The Update Query design

4. We now need to Run this query, using the Run button to update the data in our Products table that
meets the criteria that is applied. The warning dialog box indicates the number of records that will be

update, click Yes to accept this:
Example 3
1. Open the Products table.
2. Note the price (to customers) of French Truffles ($8.99) and Blue Mountain Coffee ($9.99).
3. Note the cost (to the store) of French Truffles ($6.15) and Blue Mountain Coffee ($8.00).
4. Close the Products table.

5. Make sure the Create tab is selected.
In the Ribbon, click Query Design.
6. When the Show Table window appears, click Products.
The query window should look like this:

8. In the Ribbon, click Update.

10. In the field list of the Products table, scroll down and double-click the Price field.
The query design grid should now look like this:

11. In the Price column, click in the Update To row.
12. Type:
[Cost]*1.25
Then press the ENTER Key.

TIP: Make sure that you've enabled all content in the database. To do that, click
the button if the Security Warning bar is showing.
This will raise prices for all products by 25%.
TIP: The square brackets [ ] tell Access that Cost is a field.
13. In the Ribbon, click Run.
14. In the Title Bar, click the icon.
15. When the Save As window appears, type:
Update Product Prices
in the Query Name box.
16. Close the query window.

To Verify results
1. Open the Products table. It should look like this:
Note the updated price of French Truffles ($7.69) and Blue Mountain Coffee ($10.00).
2. Close the Products table.
Parametric Query
Example 1
1. View the query in design view
2. In the criteria cell for the appropriate field(s), type in the desired expression within the square brackets([ ])
For example, using a parameter to query for date valWhen the query is run, Microsoft Access displays this
text to prompt the user for the criteria. The text of the prompt must be different from the field name,
although it can include the field name.
3. Run the parameter query
4. When you are prompted to enter a parameter value, enter the value of the data that you want to view and
click OK.:
Steps To create and run a parameter query:
1. Create a query as you normally would, modifying the table joins if necessary, selecting the fields
to include in your query, and adding any non variable criteria to the appropriate fields in
the Criteria: row.
2. Locate the field or fields where you would like the variable criteria to appear, and place your
cursor in the Criteria: row.
3. Type the phrase you would like to appear in the prompt that will pop up every time you run your
query. Make sure to enclose the phrase in brackets [ ]. For example, in our parameter query that
searches for orders placed on a certain date, we might type our criteria like this: [What date?].
4. On the Query Design tab, click the Run command to run your query. A dialog box will appear
with the prompt you specified. Enter your search term, then click OK to view your query results.

To run an existing parameter query, simply open it.
LAP Test Creating Database Queries

Name: __________________________ Date: ________________________________________
Time started: ____________________ Time finished: ________________________________
 Tsak1. Perform the following by using a navigation button
 Go to the first record  Go to the next record
 Go to the previous record  Go to the last record
 Current Record box  Open a new (blank) record
 Task.2 Create Query
 Create query using query button
 Create query using query wizard
Create the following types of queries
 Create Select Query  Create Update Query
 Create Action Query  Create Make table Query
 Create Append Query  Create Delete Query

Creating Database Objects Tvet

Uploaded by

Copyright:

Available Formats

Creating Database Objects Tvet

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Creating Database Objects Tvet

Uploaded by

Copyright:

Available Formats

Blue Nile College, COVID-19 Emergency Response Reading Material

TVET-PROGRAME TITLE:- IT Support Service Level II

MODULE TITLE:- Operating Database Application

MODULE CODE: EIS ITS2 M04 0919

NOMINAL DURATION: 60 Hours

At the end of the module the learner will be able to:

LO1. Create database objects

LO2. Customize basic settings

LO3. Create reports

LO4. Create forms

LO5. Retrieve information

Operating Database Application Page 1

LO 1: Create Database Objects

Information Sheet-1 Database Design Principles

The Database Design Principles

Operating Database Application Page 2

Information Sheet 2 Opening& Designing Database Application

Operating Database Application Page 3

Information Sheet:3 Database OBJECTS

What does Database Object mean?

 Basic Component of a Table:

Operating Database Application Page 4

Information Sheet-4 Creating Data base Objects

Data types in Microsoft Access

Operating Database Application Page 5

Information Sheet-5 Modifying Database Object

Modify your new database created from a template

Information Sheet-6 Create Data base Relation ship

Operating Database Application Page 6

Primary key Foreign key

 Types of Database Relationships

Operating Database Application Page 7

ER Diagrams (Entity Relationship Diagrams)

ER diagram with basic objects

Operating Database Application Page 8

ER diagram example with entity having attributes

1.1.1 ER Diagram Symbols and Notations

 There are three basic elements in an ER Diagram:

 Weak entity  Multivalve attribute

Operating Database Application Page 9

 Derived attribute  Recursive relationship.

 Weak relationship and

 A weak entity is an entity that depends on the existence of another entity.

Weak Entity Example in ER diagrams

 An attribute is a property, trait, or characteristic of an entity, relationship, or another attribute.

 Meanwhile, attributes can also have their own specific attributes.

 These are called composite attributes.

 In those that do, however, attributes are represented by oval shapes.

Operating Database Application Page 10

Example of a multi valued attribute

 An attribute based on another attribute.

 This is found rarely in ER diagrams.

Derived Attribute in ER diagrams

 A relationship describes how entities interact.

Using Relationships in Entity Relationship Diagrams

Operating Database Application Page 11

Example of a recursive relationship in ER diagrams

Cardinality and Ordinality

 The relationship in this case follows a ―one to many‖ model.