ain106 access reporting and analysis

Accelerated Computer Training for Working Professionals Factory 2010 Case

Orange Coast Database Associates Course (800)355-9855 or http://www.dhdursoassociates.com

Orange CoastDatabase Associates

Specializing in Microsoft Office,

Access, SQL, and related technologiesClasses custom designed forWorking Professionals

http://www.dhdursoassociates.com

San Juan Capistrano, CA

(800)355-9855

AIN106 Introduction to MicrosoftAccess Reports and Analysis

Factory AIN1061

AIN106 - Introduction to Access

Quick introduction to Access forexperienced Windows users

P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://[email protected]

FactoryAIN1062


Introduction (s) Facilities Course Materials (Varies by class)

– Student Questionnaire– PowerPoint handouts for all sessions– Evaluation form– Training certificate– Collaterals (Maps, Catalogs, etc.)

FactoryAIN1063

MS Access Introductory (100 Level)Standard Curriculum

AIN100

AIA101

AMP110Macros

AIN104Reports& Forms

AIN102Queries

AIN100T Incl. A & B

FactoryAIN1064

MS Access Introductory (100 Level)Specialized Curriculum

AIN100

TBDAIN106Reports

& Analysis

TBD

Incl. A & B

FactoryAIN106 V1.25

AIN106 – Introduction to Access

Quick pace for experienced windows users

Assumes no prior knowledge of Access

End-user, not programmer, oriented

Somewhat structured towards personal use foranalysis and reporting

FactoryAIN1066


Databases

Tables

Datasheet

External Data

Queries

Reports

Criteria Selection Forms

Course Topics:

FactoryAIN1067

Introduction to Access

Industry Standard Approach:

Tables Table Designer

Queries Query designer

Reports Wizard then modify

Forms Wizard then modify

Macros Macro Designer

Access has many ways to accomplish tasks. As an acceleratedcourse we will cover only the most common.

FactoryAIN1068


2 Sessions

Lecture

Demo

Student “hands-on” - by the end of theclass the student will have constructed asmall, functional application

Exercises are cumulative – later examplesbuild on objects created earlier

Course Format:

FactoryAIN1069

Database Design – Critical Topic

What do I want?– (Outputs - Reports)

What have I got?– (Inputs – Tables, Import data)

What do I need to do to getthere?

– (Processes, Queries)

FactoryAIN10610

Understand your database

Draw apicture

Write adescription

Plan yourqueries

PRODUCTS

EMPLOYEESC.C.Toys

ORDERS

Note: we have a full day course (DBD201) on this important topic.

FactoryAIN10611


Session 1– Tables and Data

– Starting Queries

Session 2– Queries

– Reports

– Criteria Selection Forms

Course Schedule (3 hour sessions):

Notes

FactoryAIN10612

Factory Access Introduction v1.021

Microsoft Access

Module

Introduction to Access, Tables andData

FactoryAccess Introduction v1.022

Module Outline

Part 1: Introduction & Tables

Part 2: Manipulating and Sorting Tables

Part 3: Table Relationships

Three parts:


Module Hands On

– Create a database based on Factory2000 case

– Create 3 tables (w/validation rulesand masks)

– Populate tables

– Sort & Manipulate tables

– Create table relationships (permanentlinks)

Students “hands-on”:



Module Part 1:

Introduction to Access and Tables


Access Modes

Menu-driven interface - Interact with a databaseand its objects using menu commands

Program mode - Lets you store instructions in a VBAprogram file

This course concentrates on the menu interface butdoes cover macros which can be used to “program” aseries of actions


Relational Database Concepts

Most prominent type of database – Access is aRelational Database

Based on set theory (Mathematically based) A table is a relation between columns and rows Each row must be unique Each column may contain only one type of data and

must have a unique name Each data element may contain only one value Information from multiple tables can be combined

using a column of common information


Database Terminology

Database – in Access an “.mdb” file in whichtables, reports, queries, and other objectsare stored

Table – stores facts about one subject(entity)

Record - contains related information aboutone entity “instance”

Field - contains a fact about an entity


Database Terminology (cont.)

Key - used to order, identify, and retrieve recordsin the database

Primary key - unique identifier for a particularrecord. Only one per table.

Index – Internal structure that speeds up searchesand joins. Also used to enforce uniqueness. Can haveseveral per table.

Table structure - the arrangement of informationwithin a record, the type of characters, fieldlength, limitations, etc.


Database Terminology (cont.)

Form - paper-like method of accessing and enteringdata in a table

Query – retrieves information from Access tables

Report - provides the ability to arrange table dataas well as to perform calculations and then print apaper-based report

Object – used in database world as generic term forqueries, reports, indexes, tables, etc. Not the sameas programming object.


Sample Case – Factory2000

Implement a simple costaccounting application

Employees work on workorders

– Clock labor hours worked for eachwork order

– Only one labor ticket peremployee per work order

– Each work order has a standardtime budget

Some data periodicallyimported and/or exported


Existing Method of RecordingFactory2000 Labor Hours

EmpNo fName lName Rate Wono Descr Hours

12 Bob Smith 15 A1 Casting 12

13 Mary Chavez 20 A1 Casting 22

14 Alicia Parks 25 B3 Ass’ly 15

14 Alicia Parks 25 C2 Screws 18

This what is called a “flat file”. What problems do you seewith this approach?


Factory2000 Relational Database Structure

EmpNo fName lName Rate

12 Bob Smith 15

13 Mary Chavez 20

14 Alicia Parks 25

EmpNo Wono Start End Hours

12 A1 1/1/01 1/31/01 20

14 C2 2/1/01 2/28/01 42

14 B3 1/1/01 2/28/01 40

employees

Labor

Wono Descr Std

A1 Casting 30

B3 Fitting 50

C2 Screws 70

Work_orders


Creating a Database

Ways to create a database

– Create a database using an Accesstemplate or Wizard – (not alwaysrecommended)

– Create a blank database and then insertyour own objects – we will use thismethod in this course


Creating a Table

Click new from tables tab Naming a Table

– Up to 64 “standard” characters can be used– Do not use an extension– No leading spaces or control characters, in fact

try to avoid spaces altogether

Add fields one by one Set Field Properties in Field Properties

box


Designing Fields

Field Type – text, datetime, number, etc.

Field Width – 10 characters, etc.

Caption – “Column title” for display

Format – Determines how the field is displayed(long date, short date, etc.)

Click toolbar key symbol to make primary key

Set indexed for secondary keys, required, etc.


Table Designer


Hands On - Create Table Structures

Employees

Work_Orders

Labor


Employees Table Definition

EmpNo text(2), primary key

Fname text(10)

Lname text (15), required

Rate currency, required

MgrNo text(2)

Add captions, too

(Instructor led)


Work_Orders Table Definition

WoNo text(2), primary key

Descr text(15)

StdHrs number(single,2), required

Accum number(single,2), required

Add captions, too

(On Your Own)


Labor Table Definition

EmpNo text(2), primary key

WoNo text(2), primary key

Start datetime, required

End datetime

Hours number(single,2)

Add captions, too

(On Your Own)


Field Validation & Masks

Add some validations

– Require start date

– End >= start date

– Etc.

Add some input masks & formats


Datasheets

Datasheet window - displays the contents of a tablein a spreadsheet-like format

– Each row contains a record

– Each column contains a field

Speedbar - appears at the bottom of the windowand is used to navigate through the records in atable


Datasheet View


Entering & Editing Records

Enter table data from thehandouts


Employees Table Data


Work_Orders Table Data


Labor Table Data

To be added later



Module Part 2:

Manipulating and Sorting Tables


Printing a Table

Access allows you to create “quick and dirty”reports by clicking the toolbar Print button

– A row/column presentation is used

– Field names appear along the top

– Records are rows

– Fields are columns

– A grid is printed around each field


Quick Reports

Report Wizard

– Module 2 covers reports

– For now, be aware a quick report can beproduced using the report wizard


Manipulating the Datasheet

Gridlines – controlled by Cells Effectsdialog box

Can control background color, fonts,etc.

Fields can be resized and moved

Also, hidden and locked like aspreadsheet


Record Pointer

Record pointer (thick black arrow head) helps youkeep track of where you are in a table

The character is used in the left-hand table cellsto indicate the pointer location

The pointer moves by using keyboard, scrollbar, orspeedbar commands

=> Changes are saved when you move to next record


Find Command

Use the Edit | Find command sequence orclick the Find toolbar button

Either command activates the Find in fielddialog box

Searches can have “Wildcards” (in factsophisticated Unix style searches are available)


One Field Sort

Select the column to be used in the sort

Click the desired Sort Ascending orDescending toolbar button


Multiple Field Sort

Filter - used to sort data or restrictrecords shown

Use Records | Filter | Advanced Filter/Sortcommand sequence

Activates the filter window

Specify the fields and sort order

Execute by clicking the Apply Filter/Sortbutton of the toolbar



Module Part 3:

Table Relationships


Relating Tables

Command sequence – Tools | Relationships

Parent table - table used as the main tablefor a relationship

Child table - the related table, often hasseveral records for each record in theparent table


Referential Integrity

Orphan record - a child record without acorresponding parent record

Referential integrity - makes certain thatrelated records are present


Table Links

In this context means relate two or moretables on columns of data

Permanent link –– established using the Relationships command

– is always in effect once defined; the wizards anddesigners will take advantage of this fact

Transient link - defined using the query-by-example feature of Access. Lasts for theduration of the query.


Creating a Permanent Link

Drag fromPK to FK


Create Permanent Links

Link all Factory2000 tables

Enforce referential integrity

Normal, default joins for now


Permanent Links


Enter Labor Table Data



End of Module

P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com

Factory Access Accel. Introduction v1.021


Module

Introduction to Access, ExternalData


FactoryAccess Accel. Introduction v1.022

Module Outline

Topic 1: Export tables

Topic 2: Import data

Topic 3: Link tables

Three short topics:


Module Hands On

– Export work_orders and employees tables

– Import work_orders text file

– Link employees excel file



Sample Application – Factory2000

Simple cost accountingapplication

Employees work on workorders

Clock labor hours for eachwork order


Recording Factory2000 Labor Hours

EmpNo fName lName Rate Wono Descr Hours

12 Bob Smith 15 A1 Casting 12

13 Mary Chavez 20 A1 Casting 22

14 Alicia Parks 25 B3 Ass’ly 15

14 Alicia Parks 25 C2 Screws 18

What problems do you see with this approach?


Factory2000 Relational Database Structure

EmpNo fName lName Rate MgrNo

12 Bob Smith 15 13

13 Mary Chavez 20

14 Alicia Parks 25 12

EmpNo Wono Start End Hours

12 A1 1/1/01 1/31/01 20

14 C2 2/1/01 2/28/01 42

14 B3 1/1/01 2/28/01 40

employees

Labor

Wono Descr Std

A1 Casting 30

B3 Fitting 50

C2 Screws 70

Work_orders


Exporting a table

Very simple

Click File|Export (or save as)

Specify type: Access canexport to many types: Excel,Text, etc.


Exporting tables

Export Work_Orders to CSVtext file.

Export Employees to anExcel spreadsheet.

Note you can also export toanother Access table. Evenone elsewhere on a LAN!


Using External Data

Two methods:

– Import the data into a new (or existing table) in yourdatabase

– Link to the data. A table link appears in yourdatabase but the data stays in the original file.


Importing Files

Another very important feature

Start from new table dialog, pickImport Table

It will start a wizard

Use advanced to select fields, etc.

Specifications can be saved for lateruse


Importing a table from a Text File

Importing a Text File


Importing from a text file

Import the instructor-suppliedlabor text file into the previouslycreated labor table

Save the specification


Linking a table

Use new table dialog

Select Link Table

This will set up a connection to anexternal table. It does not becomepart of the Access database

Access can link to many types: Excel,ODBC connection, etc.


Linking a table

Linking a Table


Linking a table

Linking a Table


Linking a table

Linking a Table


Link a table

Link the EmployeesExcel

Give it a name likeEmployees_xls

Factory Access Accel. Introduction v1.0218

Introduction to Microsoft Access

[End of Module]


Module

Introduction to Access, ExternalData

Notes

This slide intentionally left blank

19

Notes


20

Factory Access - Accelerated Queries1

Microsoft Access

Module:

Queries


FactoryAccess - Accelerated Queries2

Module Hands On

– Create a simple query

– Create a multi-table query

– Create calculated fields

– Create a summary query

– Concatenate fields

– Create a parameter query



Creating a Query

Activate the Create tab of the Databasewindow

Click the Query Design button

Interact with the dialog boxes


Query Results

Results placed in a dynaset in a window

Dynaset can be used like a regular table– Most useful when you work with fields

from several tables– Any changes that are made to a dynaset

field are also made to the originalrecord


Query Design Window

QBE grid - controls which fields appear inthe dynaset

Parts of the grid

– Field - contains the name of the field

– Table - contains the field’s table name

– Sort - controls the order of displayed data

– Show - displays a field in the dynaset

– Criteria - enter a condition to display selectedrecords


Criteria

Logical Comparison Operators– <, >, =, =<, >=. <>

Special Operators– LIKE

– IS NULL

– BETWEEN

– IN

Special characters– [ square brackets around names]

– (parentheses around function arguments)

– # pound sign around dates#


Like (“Wild Card Matches”)

ANSI standard

Wherecustomer_last_name like“Jo%”

Like “Jo_”

Microsoft Access

Wherecustomer_last_name like“Jo*”

Like “Jo?”

• * means match any string of characters; ? meansmatch one character.• Access actually allow more sophisticated Unix typesearch patterns as well – see help


Create a Simple Query

Use Work_Orders table

Show fields: all except accum

Sort by WoNo

Only records where descriptioncontains an “s”

Save your query as qrySimple


Simple Query (Find all records with an “S in the

description field)

Query by Example (QBE) Grid


Joining Tables

Tables can be joined for performing a query

– Must have a common field

– Linking in QBE grid performed via a dragoperation

– Join types: inner, left outer, right outer

– Use show tables dialog to bring up list of tables


Joining two TablesDrag fromPK to FK

Double Click tablename to add


Create a multi-table query

Join Labor and Employees

Show following fields: all from labor,fname, lname and rate fromemployees

Sort by empno then wono

Save your query as qryLaborDetail


Multi-table Query Design


Multi-table Query Results


Calculated Fields

Can embed calculations in a query

Creates a new temporary field whichappears in the dynaset

Store your calculations there

Can use an expression “builder” to help


Query with Calculated Field(use the builder)


Query with Calculated Field

Use qryLaborDetail

Add a calculated field whichshows the cost of a laborticket

Save your query asqryLaborDetail_2


Query with Calculated Field Results


Concatenated Fields

Can “add” two character fieldstogether

Use & operator

Example: fname & “ “ & lname


Concatenated Fields

Create a new labordetailquery

Add a column with theemployee’s full name

This can be used later inreports, etc.

Save your query asqryLaborDetail_3


Concatenated Fields Results


Summarizing Columns

SQL aggregate functions– Sum

– Count

– Avg

– Etc.

Activated by clicking the Total toolbar button

Adds a Total line to the QBE query grid

All selected columns must be in group by or anaggregate


Query with Aggregate


Query with Aggregate

Modify qryLaborDetail2

Drop work order fields

Sum the hours and cost

Save your query asqryLaborGrouped


Query with Aggregate Results


Parameter Queries

Accept a variable when run

Order Noparameter


Parameter Queries

Create a parameter query

Base on qryLaborDetail_3

Show all fields

Prompt for order no

Save as qryParam


Parameter Query Results

Factory Access - Accelerated Queries29

Accelerated Introduction to Access

End of Module


Notes



Notes



Notes



Factory Access - Automating Tasks1

Module - Advanced Queries

P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.dhdursoassociates.com

Microsoft Access

FactoryAccess - Automating Tasks2

Module Outline

Part 1: Advanced Queries

One part:


Module Hands On

– Create action queries

Maketable

Append

Delete

Update



MS Access Training

Part 1Advanced Queries


Advanced Queries

Several types of special-use queries– Action

Make table*

Append*

Delete*

Update

– Other Pass-through, crosstab etc.

We will cover those with an *; others as time andinterest permit

Reached through Query menu bar when querydesigner is open



MakeTable Query

Allows you to create a new table based ona query

Can be output to current database oranother one

Very simple

– Design your query as you normally would

– Pull down the query menu and select maketable. Assign the new table name


MakeTable Query

Create an extract of theEmployees table

– Save table as Temp_Employees

– Select MgrNo = 13

– Save your query asqryMake_Temp_Employees


MakeTable Query Results


Append Query

Appends records to an existing table

Design query then pull down query menu andselect table to append to

Must indicate destination field for each sourcefield in query. This is done in a new QBE gridrow.

Can have additional “criteria” fields with nodestination



Append Query

Create and execute an append query

Select from Employees all recordswith MgrNo =12

Append to table Temp_Employees

Save the query asqryAppend_Temp_Employees


Append Query Results


Delete Query

Deletes records from an existing table

Go to query design window then pulldown query menu and select table todelete from

Establish the criteria in the criteria row


Delete Query

Delete from temp_employees

– All records with rate less than 20


Delete Query Results


Other Advanced Queries

Union – “Adds” results of two queries into one result

Update – Update records in an existing table

SQL Pass-through – Send an SQL statement directlyto “back-end” database (Oracle, DB2, etc.)

Crosstab – Creates a crosstab result (similar to aspreadsheet)

Etc.

Factory Access - Automating Tasks18

Microsoft Access

End of Module


Notes


Notes


Factory Access - Reports & Forms1

Microsoft Access

Reports Module


FactoryAccess - Reports & Forms2

Module Hands On

– Create a summary report

– Format the report

– Add a calculated field

– Add summary fields

– Add conditional formatting

– Sub-reports (optional) Create a sub-report

Create a master-detail report



Creating reports

Two basic methods:– Report Wizard

– Report Designer

Common approach is to start w/the Wizard then modify the result


Report Wizard

Building a Report by Using a Report Wizard

– Use the Report tab of the database window

– Can base report on table or query

– Report Wizard - a series of dialog boxes that stepsyou through the process of building a report



Report Wizard

After selecting the table or query you givethe Wizard additional information…

– Grouping – for summaries, etc.

– Sorting – for sort order within group, pluswhich summary function to use

– Format and style


Report Wizard

Create a labor report

– Use qryLaborDetail query

– Select wono, empno, fname,lname, hours

– Group by wono

– Use summary options: sum hours,

– Layout and style to taste

– Save as rptLabor


Report Design Window

Menu Bar and Toolbar Rulers - vertical and horizontal

Report Layout

Toolbox

Field List Box - used for adding fields to a reporttemplate

Properties Sheet or Section Detail Sheet - controls theappearance of a report object

Scroll Bars


Report Designer


Redesigning a Report

Able to move field and title boxes around thedesign template via a drag operation

Able to resize the report bands using selectionhandles

Able to add additional titles using label control

Able to align text as well as change the fontand size of type used

Can add background fill effects, etc.


Report Designer

Open rptLabor in design view– Format headings

– Remove extra subtotal row andreduce band height

– Fill title with grey background

– Etc.

– Save with same name(rptLabor)


Report Designer Results


Report Designer

Adding Fields– Use toolbox, or

– Drag and drop from field list

– Set control source on property sheet if appropriate(see below)

– Put SQL function around the control source for aggregatefields

Two types– Bound

– Unbound (no control source)


Report DesignerF

unct

ion


Report Designer

Open the report Labor indesign view

– Add a cost column (unboundcontrol)

– Format to taste

– Save report as rptLaborCost


Report Designer

One more change to Laborreport– Add group and grand totals to

labor cost for the report

– Add an unbound textboxcontrol

– Use a formula, =sum(…), asthe control source

– Save as rptLaborCost_2


Labor report with Cost Column


Conditional Formatting

Format, Conditional Formatting commandsfrom Menu Bar

Can be used for forms or reports– Examines the contents of a field

– Based upon field’s contents able to specify: Font/Font size/Font color

Background color

Text attributes (boldface, underline, italics)

Try flagging stdhrs >= 70


Subreports

Can have sub reports added to a mainreport

– to show detail– To add related information such as customer data

on an invoice

First create the sub report Then create the main report and drag the

sub report and drop it in place, or Use the subform/sub-report control



Sub Reports

Create a subreport namedsrptLaborDetail_3

– Base on qryLaborDetail_3

– Be sure to use tabular format

– Eliminate extraneous titleinformation


Labor Sub Report


Subreports

Make sure you have a permanentlink between work_orders andqryLaborDetail_3

Create a main reportrptWork_Orders_Main

– Base on work_orders table

– Expand the detail band and drag thesubreport srptLaborDetail_3 intoposition

Format to taste


Sub Reports

Drag new subreport to main report, or usesub/form subreport control

Save as rptWork_OrdersFull


Full Work Orders Report Results


Microsoft Access

[End of Module]P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com

Reports Module

Notes


Notes




Module -




Module Hands On

– Create an unbound form



Forms

Form - provides for customized data entryinstead of using the datasheet window

Also used to establish criteria for reports andqueries

Found on the Forms tab of the databasewindow

Form Wizards - a series of dialog boxesprompting you about your form requirements


Form Designer

Design surface for forms

Add controls from toolbox

Some controls themselves have wizards whichcan invoke certain actions (run query, openreport, etc)



Form Designer

Build a criteria selection form for theparameter query qryParam (use designview)

– Add an unbound control named txtOrderNo

– Make sure the control wizard button is depressed

– Add a button which will run the param query

– Run the form. What happened? Why?

– Save as frmParam


Fix the query and form then rerun

Change the variable names to look at the form


Criteria Selection Form Results


Subforms

Same idea as sub reports

Main form uses parent table; sub form useschild table

Several ways to create them

– Create sub form first, drag and drop to main form

– Use subform/subreport control

– Create with form wizard directly



[End of Module]P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com

Module -


Notes



Notes



Accelerated Computer Training for Working Professionals

Orange Coast Database Associates Course Material (800)355-9855 or http://www.dhdursoassociates.com

Orange CoastDatabase Associates

Specializing in Microsoft Office,

Access, SQL, and related technologiesComputer Training, Programming & Consulting

32422 Alipaz St., Suite A-15

San Juan Capistrano, CA

(800)355-9855 (Toll Free) | (949)489-1472 (Direct) | (949)485-6284 (Fax)

http://www.dhdursoassociates.com | [email protected]

Accelerated Computer Training

ain106 access reporting and analysis

Documents

introduction tables

factory introduction

access quick introduction

entity7 access introduction

ain106 introduction

access tables report

microsoft access reports

database terminology