6/17/2020

IN3193

Database
Management

Instructor
Dr. Muhammad Waqar
muhammad.aslam@purescollege.ca

Forms in MS Access
• A form is a database object that you can use to create a user
interface for a database application.
• Think of forms as windows through which people see and reach
your database in a more visually attractive and efficient way.
• It is mainly used to ease the process of data entry or editing.
• Data in a form can be selected from one or more tables.
• Forms can also be used to control access to data, like which fields
or rows of data are visible to which users.
• Help you to display live data with easy creation of new data.

1
 6/17/2020

Create a Form
• To understand form download Fourth Coffee database from google
class.
• Open Coffee inventory table from this database

Creating a Form
• There are four primary ways to create the form as mentioned
below:

• Form Wizard
• Form
• Multiple Item
• Split Form

2
 6/17/2020

Create Using Form Wizard

• This option allows the user to create the form with the wizard and
select the column from the available list of column form in legacy
Select window format.

Select the Fields

3
 6/17/2020

Select the Lay Out

Final Form

4
 6/17/2020

Create using Form

• It is the simplest way to create the form which will:

• By default, populate all the column from the selected table in 'form
view,'
• The user can delete non-required column manually

10

Steps

10

5
 6/17/2020

11

Created Form from Selected Table

11

12

Deleting a Field from Form

12

6
 6/17/2020

13

Create form by 'Multiple Item.'

• It is another type wherein all the records already created will be
displayed in Form with an option to Add new record.

13

14

Step 1

14

7
 6/17/2020

15

Step 2

15

16

Create form by 'Split from'.

• It is a mix of simple form and split form in a way that this form
provides the view of Form and datasheet in a split window.
• Whatever the user enters in Form is visible directly in Datasheet
view immediately and vice versa.

16

8
 6/17/2020

17

Step 1

17

18

Step 2

18

9
 6/17/2020

19

Edit the existing record via Form.

• One of the features of forms is that we can edit the values and data
directly from the form.
• It is more user-friendly because as compared to row format in the
table, forms have better visibility of selected fields and the user can
do the direct updates.
• These values updated from forms will also be reflected in original
tables immediately.

19

20

Step 1

20

10
 6/17/2020

21

Step 2

Save the form and then open the original table to see if it has been
updated.

21

22

Add a record to a Form.

• Forms also give the flexibility to Add records.
• Again, this is a user-friendly and appealing way of adding records
as compared to adding records in a row form.
• Here, we will take the example of Split from “Coffee Inventory
Form” created above.

22

11
 6/17/2020

23

Step 1

23

24

Step 2

Save the form and then open the original table to see if it has been
updated.

24

12
 6/17/2020

25

Applying Theme
• The Themes command applies a predefined color and font scheme
to a form or report. A theme modifies a form by controlling the color
and fonts of its text.
• To apply a theme, first change to Layout view. On the Form Layout
Tools Design contextual tab, in the Themes group, click the
Themes button to view a gallery of theme styles from which to
choose.
• You can point to each option to see the name of that format and a
live preview before it is applied to the form.

25

26

Applying Theme

26

13
 6/17/2020

27

Sorting and Filtering in Form

• Sorting data in a form can help make it much more effective and
easy to use.
• Sorting helps users review and locate the records they want without
having to browse the data.
• To find one or more specific records in a form, you can use a filter.
• A filter limits a view of data to specific records without requiring you
to alter the design of the form.
• You also can use a tool called filter by form to filter on several fields
in a form or to find a specific record.

27

28

Sorting in Form
• You can sort data in the Form view of a form.
• The order that is chosen when a form is designed becomes that
object’s default sort order.
• But when viewing the form, users can sort the records in whatever
way is most useful for them.
• You can sort the records in a form on one or more fields.

28

14
 6/17/2020

29

Sorting
• Create Coffee Inventory form for Coffee Inventory table in Fourth
Coffee Database
• Right-click the Product Name field to display the shortcut menu.
• Click Sort A to Z. The form is sorted by Product Name from A to Z.
The record with the first alphabet is displayed first.
• Click the Next record button on the record navigator at the bottom
of the form. Continue clicking through all the records to see the
records in order according to price.
• On the Home tab, in the Sort & Filter group, click the Remove Sort
button. The records have resorted back to their original order.

29

30

Filtering
• Common filters are built into every view that displays data.
• The filters available depend on the type and values of the field. When
you apply the filter, only records that contain the values that you are
interested in are included in the view.
• Filter settings remain in effect until you close the form—even if you
change to another view.
• If you save the form while the filter is applied, it will be available the
next time you open the form.
• To permanently remove a filter, on the Home tab, in the Sort & Filter
group, click the Advanced button and then click Clear All Filters.

30

15
 6/17/2020

31

Filtering
• Open Coffee Inventory form for Coffee Inventory table in Fourth Coffee
Database
• Right-click the Product Name field to display the shortcut menu, click
Text Filters, and then select Contains. Click Contains to display the
Custom Filter dialog box.
• In the product name contains box, type vanilla and then click OK.
• Click the Next record button on the record navigator at the bottom of
the form. Continue clicking to see the five records that contain the word
“vanilla” in the Product Name field.
• Similarly Right-click the Pounds field to display the shortcut menu, click
Numbers Filters, and then select less than and use a value of 29 to see
all records with pounds less than 29.

31

32

Filtering by Form
• Filter by form is useful when you want to filter several fields in a form or
if you are trying to find a specific record.
• To use filter by form, open the form in Form view and make sure the
view is not already filtered
• On the Home tab, in the Sort & Filter group, click Advanced and then
click Filter By Form. Click the down arrow in a field to display the
available values.
• Enter the first set of values on the Look for tab at the bottom of the
page and then click the Or tab and enter the next set of values. Each
time you click the Or tab, Access creates another Or tab; so you can
continue to add additional filter values. Click the Toggle Filter button to
apply the filter.

32

16
 6/17/2020

33

Filtering by Form
• Open Coffee Inventory form for Coffee Inventory table in Fourth Coffee
Database
• On the Home tab, in the Sort & Filter group, click the Advanced Filter
Options button and then click Filter By Form. A form filter appears.
• Place the insertion point in the Pounds Box and then click the down
arrow on the right to display the list of options.
• Select 25. An or option appears at the bottom, click there an chose
second Pounds option as 30.
• Nine values with Pounds equal to 25 or 30 will be displayed

33

34

Reports
• A report is a database object that is used to organize and display data
pulled from tables and queries.
• You can create a report using the Report button, Report Wizard button,
Report Design button, and Blank Report button, depending on the
amount of customization desired.
• You can also create labels using the Labels button.
• After creating a report, you can instantly apply a Theme to create a
professional look.
• You can also sort and filter data in a report to display the records to suit
your needs.

34

17
 6/17/2020

35

Reports
• A report’s record source is the table or query that provides the data
used to generate a report.
• Before you can create a report, you need to define the record source
by clicking in the Navigation Pane on the table or query on which you
want to base the report.
• Then, click the Report button and a report is automatically generated
based on the table or query you selected.
• You can modify a report’s design, print, or save and close a report. You
should save a report’s design if you are likely to use it again. To save a
report, click the Save button on the File tab or in the Quick Access
Toolbar.

35

36

Report Generation
• Open Coffee Inventory table in Fourth Coffee Database
• On the Create tab, in the Reports group, click the Report button.
• The report appears in Layout view. Notice the Report Layout tools that
appear in the Ribbon.
• You can change the layout, font, color, page numbering etc. of the page
from those tools.
• Click the Product ID header to select it. Position the pointer over the
right border until you see a double-sided arrow. Click and drag, resizing
the column to remove excess white space.

36

18
 6/17/2020

37

Report Wizard
• The Report Wizard displays a series of questions about the report you
want, and then it creates the report for you based on your answers.
• The Report Wizard knows what makes a good report, so the questions
are designed to help you create a professional report with little effort.
• The Report Wizard is usually the easiest way to create a report when
you want to choose which fields to include. It guides you through a
series of questions and then generates a report based on your
answers.
• If you want to skip steps such as Sorting or Grouping in the Report
Wizard, click the Next button to go to the next screen. You can click the
Finish button anytime it is available to create the report with the choices
you have specified.

37

38

Using Report Wizard

• On the Create tab, in the Reports group, click the Report Wizard
button. The first screen of the Report Wizard appears.
• Select the Coffee Inventory table in the Tables/Queries menu.
• Click the >> button to move all the fields into the Selected Fields list.
• Click the Scheduled Order Date field to select it and then click the <
button to move it back to the Available Fields list.
• Click the Next button and then Finish.
• The report is created in Print Preview mode.
• You can change the mode from right bottom corner and change the
layout of the report accordingly.

38

19
 6/17/2020

39

Report Design View

• When you want a customized report, you can create it in Design view,
which offers you many options for creating the report exactly the way
you want it.
• Design view gives you the most options for creating a report, because it
shows you the underlying structure of the report.
• It also provides you with more design tools and capabilities.

39

40

Example Report Design View

• Click the Coffee Inventory table in the Navigation Pane to select it.
• On the Create tab, in the Reports group, click the Report Design
button. A new blank report is displayed in Design view.
• If the Field List pane is not already displayed, click the Add Existing
Fields button on the Design tab in the Tools group. The Show All Tables
link appears.
• Click the Show all tables link and then the plus (+) box beside Coffee
Inventory to display the fields in the table.
• Double-click Product ID. The field is inserted onto the design grid.
• Double-click Product Name and Pounds to add them to the design grid.
• Click the Close button on the Field List pane.

40

20
 6/17/2020

41

Grouping and Sorting Data in Report

• Sorting organizes data into a particular sequence, such as alphabetic
order or from smallest to largest numbers.
• For example, you can sort a customer list in alphabetic order by last
name or by customer ID number.
• You can sort data by clicking the Design button on the Ribbon, and
using Grouping & Sort pane.
• Sorting data in a report is similar to sorting in a table.
• In Layout view, select the field you want to sort and then click the
Ascending or Descending button on the Home tab in the Sort & Filter
group. Click the Remove Sort button to remove the sort order. You can
sort as many fields as you like one at a time
• You can also easily sort data by right-clicking in a field and choosing
the type of sort you want from the shortcut menu.

41

42

Grouping and Sorting Data in Report

• The Group & Sort pane gives you more sorting options.
• You can use the pane to specify the sort order or to view the results of
sorting using the shortcut menu.
• To specify a sort, go to layout view and Design tab, click the Group &
Sort button and then click on Add a sort and then select field and sort
type.
• More Options button to display additional commands for creating
detailed sorts.
• Click the Less Options button to return to the basic sorting options.
• To delete a sort in the Group, Sort, and Total pane, click X at the end of
the sort line.

42

21
 6/17/2020

43

Filtering Data Within a Report

• A filter displays only data that meets the criteria you have specified and
hides the rest. It does not modify the table data or the design of the
report. After you remove a filter, all the records are displayed again.
• Filtering data in Layout view of a report is very similar to filtering data in
a table.
• You can apply common filters using the commands in the Sort & Filter
group or by right-clicking a field and choosing a filter from the shortcut
menu.

43

44

Finding Data Within a Report

• When you want to quickly locate records in a report, you can use the
Find command, which searches all the records of the report for any
term you specify.
• Sometimes, you may need to quickly find records within a report while
in Report view or Report Layout view.
• To accomplish this, you can use the Find command in the Find group
on the Home tab.

44

22
 6/17/2020

45

QUERY
• A query is a set of instructions used for working with data.
• Creating a query is similar to asking the database a question.
• Running a query performs these instructions and provides the
answers.
• You can sort, group, or filter the results that a query returns.
• Basic queries can be used to extract useful information from one or
more tables in the database, while more advanced queries can be used
to manipulate data in tables

45

46

Creating a Query
• A query can get its data from one or more tables, from existing queries,
or from a combination of the two.
• The tables or queries from which a query gets its data are referred to
as its record source.
• When one table provides the information that you need, you can create
a simple select query using the Query Wizard.
• You can also use a query to find records with duplicate field values in a
single table.

46

23
 6/17/2020

47

Creating a Query
• To create a simple select query, click the Query Wizard button in the
Queries group on the Create tab.
• Click Simple Query Wizard and then click OK. Specify the table you
want to use as the record source and the fields that you want to show.
Name the query and then click Finish.
• When you close the query, it is automatically saved.
• You can also save a query as a PDF, XPS, or as a Query, Form, or
Report using the Save As Object option.

47

48

Simple Query Exercise

• Open Fourth coffee data base and open Coffee Inventory table.
• Click the Query Wizard button in the Queries group on the Create tab.
Click Simple Query Wizard

48

24
 6/17/2020

49

Query Exercise
• In the Tables/Queries drop-down list, Table: Coffee inventory table
would be automatically selected.
• Under Available Fields, select product name and Pounds fields.
• Select Finish
• Query will be created and will be shown on left hand navigation pane
and a name will be automatically given to it
• This query can be executed anytime by clicking on the query name on
the left hand menu. That will show the product name and Pounds for
that product.

49

50

Creating a Find Duplicates Query

• As a general rule, duplicate data should be eliminated from a database
whenever possible to minimize redundancy and increase accuracy.
• The first step in this process is finding duplicate data.
• Two or more records are considered duplicates only when all the fields
in your query results contain the same values.
• If the values in even a single field differ, each record is unique.
• You can also use the Find Duplicates Query Wizard to find records that
contain some matching field values.
• You should include the field or fields that identify each record uniquely,
typically the primary key.
• The query returns matching records where the values in the specified
fields match character for character.

50

25
 6/17/2020

51

Find Duplicates Query Exercise

• Create two duplicate records in coffee inventory table
• Remember to use unique value for product id as it is a primary key
• Create duplicate records for product name and pounds, you can leave
the ordered date blank
• On the Create tab, in the Queries group, click the Query Wizard button.
• Click Find Duplicates Query Wizard and then click OK.
• Click Table: Coffee Inventory and then click Next. The next screen in
the Find Duplicates Query Wizard appears.
• Click product name and Pounds to move them to the Duplicate-value
fields box. These are the fields that you think may include duplicate
information.

51

52

Find Duplicates Query Exercise

• Click Next to display the next screen in the Find Duplicates Query
Wizard. This screen asks if you want to show other fields of the
duplicate record besides just the ones with the duplicate data.
• Click on Product id and Scheduled order date to move them to the
Additional query fields box.
• Click Finish to see the result of this query.
• This will show all the fields with duplicate values for Product name and
Pounds.

52

26
 6/17/2020

53

Creating a Query from Multiple Tables

• If the data you need is spread out in more than one table, you can build
a query that combines information from multiple sources.
• You can also create a query that finds records in one table that have no
related records in another table.
• When you need to include multiple tables in your query, you can use
the Simple Query Wizard to build a query from a primary table and a
related table.
• The process is similar to creating a query from a single table, except
that you include fields from additional tables.

53

54

Creating a Query from Multiple Tables

• Open Customer and order summary table.
• These table are related to each other by customer id
• Create a relationship between these tables using Database Tools tab.
• On the Create tab, in the Queries group, click the Query Wizard button.
• Click Simple Query Wizard and then click OK.
• In the Tables/Queries drop-down list, click Table: Customer.
• Under Available Fields, click Customer id, Last Name, First Name,
• In the Tables/Queries drop-down list, click Table: Order Summary
• Under Available Fields, click Customer id, Due date and priority.
• Click the Next button to display the final screen, and then click Finish
• This query shows the details of all those customers who have placed
orders.

54

27
 6/17/2020

55

Finding Unmatched Records

• To view only the records in one table that do not have a matching
record in another table, you can create a Find Unmatched query.
• On the Create tab, in the Queries group, click Query Wizard, and then
click Find Unmatched Query Wizard to start the wizard.
• Select Customers Table as your first table. This table will contain the
records you’ll want to display.
• Select Table: Order Summary to select the table that is related to the
Customers table to find those customers who did not place an order.
• Click Customer Id in the Fields in “Customers” list. Click Customer Id in
the Fields in “Order Summary” list. Click the <=> button to display them
in the Matching fields box. These fields contain data that is in both
tables.

55

56

Finding Unmatched Records

• Click the Next button to display the next screen in the Find Unmatched
Query Wizard.
• In the Available fields box, double-click Last Name, First Name, and
Customer Id
• Click the Next button to display the final screen in the Find Unmatched
Query Wizard.
• This query shows records of those customers who did not place an
order.

56

28
 6/17/2020

57

Adding Criteria to a Query

• Not all queries must include criteria, but if you are not interested in
seeing all the records that are stored in the underlying record source,
you can add criteria to a query when designing it.
• A query criterion is a rule that identifies the records you want to include
in the query result.
• A criterion is similar to a formula. Some criteria are simple and use
basic operators and constants. Others are complex and use functions,
special operators, and include field references.
• Criteria can look very different from each other, depending on the data
type of the field to which they apply and your specific requirements.

57

58

Adding Criteria to a Query

• Open any query that you have already created.
• Go to design view
• In the design view you will see all the tables and related field for the
query.
• You can select or de-select any field in design view
• You can also add criteria in design view e.g. if you want a customer
with last name smith, then type that in criteria and it will show only
those customers matching the criterion

58

29
 6/17/2020

59

SORTING AND FILTERING DATA

• Data can be sorted or filtered in a query.
• To sort a data query must be in datasheet view
• Right click the filed for which to you want to apply a sort
• It will show the sorting options depending on the datatype of the filed
• A filtering operation can also be applied by right clicking on the field
• Depending on the filed type one can chose any filtering operation

59

60

Macros
• A macro in Access is a tool that allows you to automate tasks and add
functionality to your forms, reports, and controls.
• For example, if you add a command button to a form, you associate the
button's OnClick event to a macro, and the macro contains the
commands that you want the button to perform each time it is clicked.
• Macro can be considered as a simplified programming language that
you write by building a list of actions to perform.
• Macros enable you to add functionality to forms, reports, and controls
without writing code in a Visual Basic for Applications (VBA) module.
• Macros provide a subset of the commands that are available in VBA,
and most people find it easier to build a macro than to write VBA code.

60

30
 6/17/2020

61

Macros
• To create a Macro, go to Create tab and click on Macro in the Macro
and Code group.
• That will create a Macro and will ask you to add a list of actions.
• You can click on Action Catalogs to see a list of actions available
• Show all Actions lists all actions available including those which are not
recommended by MS Access
• You can click on Add new action and select any action. Alternatively,
you can also search for an action in Action list available.

61

62

Open Form using Macros

• In the Action catalog, search for open and that will give you all the
actions related to open.
• Select Open Form.
• It will create a detailed action list for the form.
• Select the form you want to open with this Macro
• Select the appropriate Data Mode
• Click on Run to see how it works.
• You can save this Macro and it will appear in the list of Objects

62

31
 6/17/2020

63

Creating a Button for Macros

• Go to Create and create a Blank Form
• Open this Form in Design View
• Create a button from the Design tab
• Go to property sheet and rename the button as desired
• In the property sheet, click on event and link a macro to this button
• Now go back to Form View and you will see the button there
• You can click on the button to see the action performed by this button

63

Thank You

64

32