SummarySlideshow Chapter2

Access 2013: Design View,

Queries, Forms and Reports

Design View

Queries
Forms
Reports
Access 2013: Design View,
Queries, Forms and Reports
Ways to create a table in MS Access
Design View Datasheet View
• The Design View gives us more control in setting the

properties for tables, forms, etc.
• Although, you can create a table in the Design View, but

data can be added only in the Datasheet View.
©Oxford University Press

Access 2013: Design View, Queries, For 2
ms and Reports
• In a database with thousands of records, finding
information is difficult. To make this job easier, Access
2013 provides features like:
• Provide an easy-to-use method for entering and

Forms editing data for those who are not familiar with
Access
• Allow you to select data from a table or tables as

per your requirement
Queries • They allow you to change and re-arrange data,
and then use it as a source for forms and reports
Reports • Give us data in a printer-friendly format

ms and Reports
Create Table in Design View
• Open Database1. To add more tables to the database:
1
Click the CREATE tab 1
2 2
2 Under Tables group, click

the Table button to open
the Datasheet View
Or
Under Tables group, click
the Table Design button to
open the Design View The Design
page opens

ms and Reports
• The Table Design page consists of two parts with
various sub-parts:
Field Definition
Grid
Field Selector
Field Properties
area
Data Type column
Description column

ms and Reports
• The top part of the Table design page consists of:
Field Selector
• Click on any field selector

to select a field.
• You can then insert a row

above the selected field,
delete a row or drag a
row up or down to
reposition the field in the
table.
• The field selector also

identifies the primary
key field of the table by
displaying the Primary
key icon (a key with a
right-pointing arrow).

ms and Reports
• The top part of the Table design page consists of:
Field Name column
• It contains the field

names for the table you
create.
Description column
• It contains an optional
description of the field.

ms and Reports
Data Type column
• It specifies the type of data

that the field can contain
• By default, the ID field in

a new table is assigned the
AutoNumber data type
and if you add a new field
in Design View, it is
assigned the Short Text
data type
• You can change a field’s

type by clicking on the
arrow next to the entries
in the Data Type column
• Select a new data type

from the list that appears
on clicking the arrow

ms and Reports
• To create a table named EmpInformation having fields –
EmpID, EmpName, EmpSal and EmpDeptNo:
1
Create a database using
Blank desktop database
2 Click the CREATE tab.

Under Tables group, click 2
3 4
on Table Design
1
3 In the Field Definition
Grid, under the Field
Name column, type EmpID
4 In the Data Type column , The Design View opens.

select the data type from the Notice that the DESIGN tab
drop-down list appears under TABLE TOOLS

ms and Reports
• Then,
5
Move to the next column
and type a small description
about the field
7
6
6
Add three more fields,
EmpName, EmpSal, and 5
EmpDeptNo with data types
Short Text, Number and
Number respectively
7
Click the EmpID field
selector and then click on
the DESIGN tab. In the
Tools group, click the The field EmpID will now
Primary Key button become the primary key

ms and Reports
• Now,
8
Click the FILE tab and
select Save
8
9 Enter Table Name as

EmpInformation and click
OK 9
• To add data in a table, you must switch

Thefrom
Save AsDesign
dialog
box appears
View to Datasheet View
10 Select the Datasheet View
from the View option of the
DESIGN tab 10

ms and Reports
Setting the Primary Key
• A primary key field contains unique data for each record.
– Every table needs a primary key to identify records

uniquely.
– Access sorts table data by the primary key field.
– Access does not add an ID field automatically to new tables

in Design View.

ms and Reports
• Two ways of setting the primary key in an Access 2013
table are:
1
Select the field to be set as
primary key
2 Click Primary Key in the 2 1

Tools group of the
DESIGN tab
• Alternatively,
After you set the primary key, a 1key icon
1 Right-click the field selector 2
will appear in the gray selector area to the
of the column that will be left of the field’s name
the primary key
2 Select Primary Key from

the shortcut menu

ms and Reports
Removing the Primary Key
• To remove the primary key:
1 Click Primary Key in the

Tools group or in the
shortcut menu 1
• The small key in the row selector will disappear.
• The Primary Key button works as a toggle to turn key

fields ON or OFF.

ms and Reports
To add a Lookup Field
• Lookup field is a field with a list of values from which a
user can choose the desired value. Rather than typing
individual values, the values appear in a drop-down list.
1 In Datasheet View click the field
header and select Lookup &
Relationship from the drop-down list
Or
In Add & Delete group on FIELDS tab,
click More Fields button. In the Basic
Types section, click Lookup &
Relationship
Or
In Design View, click drop-down menu
in Data Type cell next to the new-field Any of these actions will
“Grade”. Select Lookup Wizard open the Lookup Wizard

ms and Reports
• Then,
2 Click I will type in the
values that I want and
then click Next
3 Leave the number of

columns set to 1 and click in
the first cell in the Col1 3
column.
Enter the data and press
Tab (not Enter) after each
one to move to the next row
2
4 5
The column “Grade” appears
4 in the Datasheet View
Click Next
The Lookup Wizard
screen 3
2 of 3 appears
5 Assign a name to the new

field. Click Finish

ms and Reports
• To enter the data:
6
Click in the field
6
7
Click the down-arrow and
select the data you want in
the field 7
A down-arrow
appears

ms and Reports
Field Properties Pane
• In the Design View, the Field Properties pane
appears on the lower half of the screen.
– You can use it to control data entered in a field and the

appearance of data on screen.
– It allows you to specify certain field characteristics and

properties, e.g., size, format, and validation rules.

ms and Reports
Property Description
Field Size • This is the maximum size for data stored in Text or
Number fields.
• For Text data, the field size determines the maximum
number of characters. The Short Text data type has
maximum 255 characters.
• For Number data, field size determines the range and
how many bytes can be used for storage.
Format • This specifies how data will be displayed.
Number and • You can choose from the seven pre-defined formats,
Currency i.e., General Number, Currency, Euro, Fixed,
Formats Standard, Percent, and Scientific.
• You can also create custom formats.
Date and • You can select from pre-defined formats, i.e., General
Time Formats Date, Long Date, Medium Date, Short Date, Long
Time, Medium Time, and Short Time.

ms and Reports
Logical • The default display is Yes/No.

Formats • Other pre-defined formats for logical data are
True/False and On/Off.
Input Mask • This facilitates data entry and controls what the user
enters in the field.
• For example, the input mask for a phone number could
be (_ _ _) _ _ _- _ _ _ _
Caption • This is an alternative name for the field to make the
field name more explanatory.
• It can contain up to 2048 characters.
Default Value • This value is automatically filled in the field when you
add a new record to the table.
Required • Enter Yes if the field should always receive a value
during data entry.
• The default value of it is No, which means that the field
can be left blank.

ms and Reports
Validation • The validation rule limits the value that the field will
Rule accept.
• For example, if the validation rule for a field is <= 100,
it means that the field must be less than or equal to 100.
• If you enter a value greater than 100, you will get an
error message.
• Validation Text This is the error message that
appears when the value entered in a field violates the
validation rule.
Allow Zero • This property is used for Text and Memo data types.
Length • If Required is Yes and Allow Zero Length is No,
data must be entered in the field during data entry.
• If both properties are set to Yes, the field can be left
empty.

ms and Reports
Queries
• An Access query is a method of getting answers to
questions about data.
– The query can involve a single or multiple tables to answer
a question about data.
– Some examples of simple query based on single table are:
• List of students who got grade A1 in all five subjects?
• List of employees who get salaries above Rs.50000 a
month?
• Access 2013 saves each query as a new query in the
database.
– A saved query can be run again at any time in the future.

ms and Reports
• An Access query can be created through:
• Query Wizard
• Query Design

ms and Reports
Using the Query Wizard
• We will use the table EmpInformation to create a query
using the Query Wizard:
1 Click Query Wizard in
the Queries group of the
CREATE tab
3
2
Select Simple Query 1
Wizard and click OK
2
3
In the Tables/Queries
box, select the table that Simple Query
The New Wizard
Query
contains your data screenbox
dialog 1 ofappears
3 appears
ms and Reports
• Then,
To add a field, select it in
4 the Available Fields list
and then click , or just
double-click it
5 Click on Next after

selecting the fields
4
6 Select Detail or
Summary and click on
Next
If needed, remove fields by
7 selecting them in the Selected 65 7
Fields
Type list andname
a query clicking
in the
text box, select Open the
query to view
The query
Simple resultWizard
Query will appear
information, and click on in the Datasheet View
screen 3
2 of 3 appears
Finish
ms and Reports
Using Query Design
• Follow these steps to create a query in Query Design:
1 Click Query Design in the
Queries group of the
CREATE tab
2 Click the table you will use

and then click on Add, or
just double-click the table. 1
You can repeat this step to
add several related tables
2 3
3
Click on Close when you The Query Design
screen
The Showappears
Table
finish adding tables
dialog box appears

ms and Reports
• Select the fields to be included in the query by using one
of the following ways:
4 Double-click the field. It
will be placed in the first
vacant column of the grid
Or
Click the field and drag it to
the required column in the
grid
Or
Double-click the asterisk
(*) to include all the Required fields are
columns of the table added to the grid
Or
Type the field name directly
in the required column in
the grid
ms and Reports
• Now,
5 To change the order, select
the entire column and drag
it to the new position
6
To hide one or more 5
columns, clear the Show
checkbox for those 8
6 7
7 Choose the field to sort by
and choose Ascending or
Descending from the
corresponding Sort box
8
In the Criteria of EmpSal
field, type >=50000

ms and Reports
Running a Query
• To run a query:
1
Click Run in the Results
group of the DESIGN tab
• To close the query
1
Click in the Query
Design screen
The output for the
query appears

ms and Reports
Saving a Query
• You can save a query in one of the following ways:
1
Click the FILE tab and
select Save option
Or
2 Click the Save button on
the Quick Access 3
Toolbar
When you save a query for the

3 first time, a Save As dialog
box appears
Type the Query Name
and click on OK

ms and Reports
Forms
• Forms provide an easy way to enter, edit, delete and
view data in a table.
• An Access form can be created using:
• Form Command
• Split Form
• Form Wizard

ms and Reports
Using the Form Command
• To create a form using the Form command:
Label Text box
1 Click the table – Control Control
EmpInformation in the
Navigation pane
3
2
Click Form in the Forms
group of the CREATE tab
2
• ADESIGN,
The simple form that uses all
ARRANGE
3 theFORMAT
and fields in the table
tabs nowis
Select the text box and createdon the ribbon
appear
resize it • Form
The form is displayed
Layout in a Design
Layout ViewView
View View

ms and Reports
• In the DESIGN tab, do the following:
4 Click on Logo in the
Header/Footer group
5 Select a picture and click

OK
4
6
6 Click Title in the
Header/Footer group. 9
Type ‘Employee Information’ 7
Click on Date and Time

7 in the Header/Footer
group 8
New Title of the Form
8 Select a format and click 5
OK
TheAInsert
Date and Time
Picture
9 Click the Themes down-
dialog
dialog boxbox appears
appears
arrow of Themes group
and select a required theme
ms and Reports
• Select the FORMAT tab and do the following:
Select the label and using
10 the Font colour button in
the Font group, change the
text colour to blue
11
Click anywhere inside the
blank area of the form
12
Click the down-arrow of
Shape Fill in the Control
Formatting group
The colour of the form
would look like this
13
Select a colour from the
colour palette

ms and Reports
• To save the form:
14 Click the FILE tab. Select

the Save option
15
Type the form name and 15
click on OK
The Save As dialog

box appears

ms and Reports
Using Split Form
• To create a form using the Split Form option:
Click the down-arrow of
1 the More Forms option
in the Forms group of the
CREATE tab and click
Split Form
2 Click the Form View

button at the right end of 1
the Status bar to enter
data
23
3 Access creates the form in the
The two Views are separated by
Click the Layout View to upper half of the window and
a bar that can be used to resize
format the form displays the Datasheet Layout
Form
View
the height of the two Views
in the bottom halfView

ms and Reports
Using the Form Wizard
• Follow these steps to use the Form Wizard:
1 Click Form Wizard under
Forms group of the
CREATE tab
1
2 Select the table from the
Tables/Queries drop-
down menu and select the
fields you want in the form.
Click on Next
23
3
Select a form layout and
click Next Form
Form Wizard
Wizard screen
screen
231 of 3 appears

ms and Reports
• Then,
4
Give a name to the form.
5 Select the option to Open 4

the form to view or
enter information
6 5
Click on Finish to create
the form
The formatted form will
6
appear on the screen
Click to add
new record

ms and Reports
Reports
• Reports are an effective way to present your data in
printed format.
– You can control the size and appearance of everything on a
report.
– Thus, you can display the information the way you want to
see it.
• An Access report can be created using:
• Report Command
• Report Wizard

ms and Reports
Using the Report Command
• Follow these steps to use the Report command:
1 Open the database. Select
the base table or query in
the Navigation Pane
2 Click on Report in the

Reports group of the
CREATE tab
2
3
Click the Layout View
button to format the report
4
Click the Print Preview to The report is displayed on
see how the report will look Report
the screenPrint
Layout
Preview
View
View
when printed on paper
ms and Reports
Using the Report Wizard
• Follow these steps to use Report Wizard:
1 Click on Report Wizard
in the Reports group of
the CREATE tab
2 Choose the table or query,

add the fields that you want
to see in the report results,
and click on Next
3
2
3 Select the grouping level
within the report and click
Report
Here
Report Wizard level is
the Wizard
grouping
on Next
screen 321of
screenby
screen of55appears
appears
EmpDeptNo.
ms and Reports
• Then,
Sort data by required
4
fields. Click the
Ascending button to
change it to Descending.
Click Next
5 Select the type of Layout

within the report. In the
Orientation section,
select either Portrait or
Landscape. Click on
Next
6 Type the title for the

report, which will also be 564
the name assigned to the The report appears on
the screen
report. Select Preview
the report and click on Report Wizard
Finish screen 5
4 of 5 appears
ms and Reports
Printing a Report
• To print a report:
1 Click the Print Preview
button on the right side of
the status bar. Click Print
Or 1
Select the Print option of

the FILE menu
2
2
Select the required options
and click on OK The Print dialog box
appears

ms and Reports
Fill in the blanks
1. Design
The __________ View has more control about setting the
properties.
2. Form
__________ can provide an easy to use interface for entering
and editing data.
3. Click ______________
Print option of the FILE tab to print a report.
Tables
4. The Table Design button is present under _________ group of
the CREATE tab.
5. The data in __________

Description column of Field Definition Grid is
optional.
©Oxford University Press Access 2013: Design View, Queries, For 44

ms and Reports
True or False?
1. False
You can also enter data in Design View: ________
2. The Field selector is present on the right of Description column:

False
________
3. When you add a new field in Design View, it is assigned the Short
Text data type: ________
True
4. The icon of a primary key is like a lock: ________

False
5. You can also assign the AutoNumber data type to any field:
________
True

ms and Reports

SummarySlideshow Chapter2

Uploaded by

Copyright:

Available Formats

SummarySlideshow Chapter2

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

SummarySlideshow Chapter2

Uploaded by

Copyright:

Available Formats

Access 2013: Design View,

Queries, Forms and Reports

Design View Datasheet View

• The Design View gives us more control in setting the

• Although, you can create a table in the Design View, but

©Oxford University Press

• Provide an easy-to-use method for entering and

• Allow you to select data from a table or tables as

Reports • Give us data in a printer-friendly format

©Oxford University Press

2 Under Tables group, click

©Oxford University Press

Data Type column

©Oxford University Press

• Click on any field selector

• You can then insert a row

• The field selector also

©Oxford University Press

Field Name column

• It contains the field

©Oxford University Press

• It specifies the type of data

• By default, the ID field in

• You can change a field’s

• Select a new data type

©Oxford University Press

2 Click the CREATE tab.

4 In the Data Type column , The Design View opens.

©Oxford University Press

©Oxford University Press

9 Enter Table Name as

• To add data in a table, you must switch

©Oxford University Press

– Every table needs a primary key to identify records

– Access sorts table data by the primary key field.

– Access does not add an ID field automatically to new tables

©Oxford University Press

2 Click Primary Key in the 2 1

2 Select Primary Key from

©Oxford University Press

1 Click Primary Key in the

• The small key in the row selector will disappear.

• The Primary Key button works as a toggle to turn key

©Oxford University Press

©Oxford University Press

3 Leave the number of

5 Assign a name to the new

©Oxford University Press

©Oxford University Press

– You can use it to control data entered in a field and the

– It allows you to specify certain field characteristics and

©Oxford University Press

©Oxford University Press

Logical • The default display is Yes/No.

©Oxford University Press

©Oxford University Press

©Oxford University Press

©Oxford University Press

5 Click on Next after