ain100
DESCRIPTION
2 day Microsoft Access class. Covers, tables, filters, queries, external data, froms, reports, action queries, macros and navigation forms.TRANSCRIPT
Accelerated Computer Training for Working Professionals Factory 2010 Case
Orange Coast Database Associates Course (800)355-9855 or http://ocdatabases.itgo.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
AIN100 Introduction to MicrosoftAccess
Factory AIN1001
AIN100 - Introduction to Access
Quick introduction to Access forexperienced Windows users
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://[email protected]
FactoryAIN1002
AIN100 - Introduction to Access
Introduction (s) Facilities Course Materials
– Student Questionnaire– Syllabus– PowerPoint handouts for all sessions– Evaluation form– Training certificate
FactoryAIN1003
MS Access Introductory (100 Level)Curriculum
AIN100
AIA101
AMP110Macros
AIN104Reports& Forms
AIN102Queries
AIN100T Incl. A & B
FactoryAIN100 V1.24
AIN100 – 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
Two levels – Day 1 (AIN100A) and Day 2(AIN100B)
FactoryAIN1005
AIN100 - Introduction to Access
Databases
Tables
External Data
Datasheet
Filters
Queries
Reports
Forms
Macros
Switchboards/Navigation Forms
Course Topics:
FactoryAIN1006
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.
FactoryAIN1007
AIN100 - Introduction to Access
4 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:
FactoryAIN1008
Database Design – Critical Topic
What do I want?– (Outputs)
What have I got?– (Inputs)
What do I need to do to getthere?– (Process)
FactoryAIN1009
Define Your Needs First
Draw apicture
Write adescription
PRODUCTS
EMPLOYEESC.C.Toys
ORDERS
Note: we have a full day course (DBD201) on this important topic.
FactoryAIN10010
AIN100 - Introduction to Access
Session 1– Tables and Data
Session 2– Filters
– Queries
Session 3– Reports
– Forms
Session 4– Forms (cont’d)
– External Data
– Automating Tasks
Course Schedule (3 hour sessions):
FactoryAIN10011
Factory Access Tables & Data1
Introduction to Microsoft Access
Module
Introduction to Access, Tables, andRelationships
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
FactoryAccess Tables & Data2
Module Outline
Topic 1: Introduction & Tables
Topic 2: Manipulating and sorting Tables
Topic 3: Relating Tables
Three parts:
FactoryAccess Tables & Data3
Module Hands On
– Create working folder with supplied files
– Create a database
– Create 3 tables (w/validation rules and masks)
– Populate tables
– Import and Export Tables
– Create permanent links
– Create a simple query
– Compute calculated field(s)
– Create a multi-table query with summary aggregates
Students “hands-on”:
Factory Access Tables & Data4
Introduction to Microsoft Access
Part 1:
Introduction to Access and Tables
FactoryAccess Tables & Data5
But first - Create Folder
Create a folder yourname (or similar) inMy Documents – this is where yourdatabase and other files will go.
Optionally, copy in the labor.txt andemployees.xls files from the instructorsupplied location or CD.
FactoryAccess Tables & Data6
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
FactoryAccess Tables & Data7
Database Terminology
Database – in Access a repository in which tables,reports, queries, and other objects are stored
Table – stores facts about one subject (entity)
Record - contains related information about anentity “instance”
Field - contains a fact about an entity
Relational Database - allows you to link records fromtwo or more tables based on the contents of acommon field. Access is a relational database.
FactoryAccess Tables & Data8
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 – like a book index. Speeds searches andjoins. Can have many per table.
Table structure - the arrangement of informationwithin a record, the type of characters, fieldlength, limitations, etc.
FactoryAccess Tables & Data9
Database Terminology (cont.)
Object – used in database world as generic term forqueries, reports, indexes, tables, etc. Not the sameas programming object.
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
FactoryAccess Tables & Data10
Sample Application – Factory2000
Simple cost accountingapplication
Employees work on workorders
Clock labor hours for eachwork order
FactoryAccess Tables & Data11
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?
FactoryAccess Tables & Data12
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
FactoryAccess Tables & Data13
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
FactoryAccess Tables & Data14
Creating an Access 2000 Database
Create thedatabase inyour desktopfolder with ameaningfulname
FactoryAccess Tables & Data15
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
FactoryAccess Tables & Data16
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.
FactoryAccess Tables & Data17
Setting Properties in the Table Designer
FactoryAccess Tables & Data18
Hands On - Create Tables
Employees
Work_Orders
Labor
FactoryAccess Tables & Data19
Employees Table Definition
EmpNo text(2), primary key
Fname text(10)
Lname text (15), required
Rate currency, required
MgrNo text(2)
(Instructor led)
All with captions
FactoryAccess Tables & Data20
Work_Orders Table Definition
WoNo text(2), primary key
Descr text(15)
StdHrs number(single,2), required
Accum number(single,2), required
(On your own)
Add captions, too
FactoryAccess Tables & Data21
Labor Table Definition
EmpNo text(2), primary key
WoNo text(2), primary key
Start datetime, required
End datetime
Hours number(single,2)
(On your own)
Add captions, too
FactoryAccess Tables & Data22
Field Validation & Masks
Add some validations– Test a numeric field for > 0
– Try checking one field against another
– Etc.
Add captions if not added already
Add an input mask
Change a display format
Etc.
FactoryAccess Tables & Data23
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
FactoryAccess Tables & Data24
Datasheet View
FactoryAccess Tables & Data25
Entering & Editing Records
Enter table data from thehandouts or use your owndata
FactoryAccess Tables & Data26
Employees Table Data
FactoryAccess Tables & Data27
Work_Orders Table Data
FactoryAccess Tables & Data28
Labor Table Data
FactoryAccess Tables & Data29
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
FactoryAccess Tables & Data30
Quick Reports
Report Wizard
– Module 2 covers reports
– For now, be aware a quick report can beproduced using auto report or a 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
31
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
32
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)
33
One Field Sort
Select the column to be used in the sort
Click the desired Sort Ascending orDescending toolbar button
34
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
35
Factory Access Tables & Data36
Introduction to Microsoft Access
Topic 3
Relating Tables
FactoryAccess Tables & Data37
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
FactoryAccess Tables & Data38
Referential Integrity
Referential integrity - makes certain thatrelated records are present
Orphan record - a child record without acorresponding parent record
FactoryAccess Tables & Data39
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
Transient link - defined using the query-by-example feature of Access
FactoryAccess Tables & Data40
Creating a Permanent Link
Drag fromPK to FK
FactoryAccess Tables & Data41
Create Permanent Links
Link all Factory2000 tables
Enforce referential integrity
Normal, default joins for now
FactoryAccess Tables & Data42
Permanent Links
Factory Access Tables & Data43
Introduction to Microsoft Access
End of Module
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
44
Multi-case Access - Filters1
Microsoft Access
Module:
Filters
Multi-caseAccess - Filters2
Module Hands On
– Create a filter
Students “hands-on”:
Multi-caseAccess - Filters3
Datasheets and Filters
Datasheet window - displays the contents of a tablein a spreadsheet-like format
– Each row contains a record
– Each column contains a field
Filters
– Datasheet can have a filter to show only recordsthat meet a given criteria
– Filters can be saved like a query
Multi-caseAccess - Filters4
Employees Table before Filter
Multi-caseAccess - Filters5
Filtering a Table
Use the filter to display records that meetcertain criteria
Enter criteria in the filter cells
Execute by clicking the Apply Filter/Sortbutton of the toolbar
– Filter By Form
– Filter By Selection
Multi-caseAccess - Filters6
Filter Buttons
Multi-caseAccess - Filters7
Create a Filter on Employees Table
Rate over 15
Sort by last name
Save as fltEmployeeRate
Multi-caseAccess - Filters8
Filter Design
Multi-caseAccess - Filters9
Filter Results
Multi-case Access - Filters10
Microsoft Access
End of Module
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
Multi-caseAccess - Filters11
Factory Access - Accelerated Queries1
Microsoft Access
Module:
Queries
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
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
Students “hands-on”:
FactoryAccess - Accelerated Queries3
Creating a Query
Activate the Create tab of the Databasewindow
Click the Query Design button
Interact with the dialog boxes
FactoryAccess - Accelerated Queries4
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
FactoryAccess - Accelerated Queries5
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
FactoryAccess - Accelerated Queries6
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#
FactoryAccess - Accelerated Queries7
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
FactoryAccess - Accelerated Queries8
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
FactoryAccess - Accelerated Queries9
Simple Query (Find all records with an “S in the
description field)
Query by Example (QBE) Grid
FactoryAccess - Accelerated Queries10
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
FactoryAccess - Accelerated Queries11
Joining two TablesDrag fromPK to FK
Double Click tablename to add
FactoryAccess - Accelerated Queries12
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
FactoryAccess - Accelerated Queries13
Multi-table Query Design
FactoryAccess - Accelerated Queries14
Multi-table Query Results
FactoryAccess - Accelerated Queries15
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
FactoryAccess - Accelerated Queries16
Query with Calculated Field(use the builder)
FactoryAccess - Accelerated Queries17
Query with Calculated Field
Use qryLaborDetail
Add a calculated field whichshows the cost of a laborticket
Save your query asqryLaborDetail_2
FactoryAccess - Accelerated Queries18
Query with Calculated Field Results
FactoryAccess - Accelerated Queries19
Concatenated Fields
Can “add” two character fieldstogether
Use & operator
Example: fname & “ “ & lname
FactoryAccess - Accelerated Queries20
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
FactoryAccess - Accelerated Queries21
Concatenated Fields Results
FactoryAccess - Accelerated Queries22
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
FactoryAccess - Accelerated Queries23
Query with Aggregate
FactoryAccess - Accelerated Queries24
Query with Aggregate
Modify qryLaborDetail2
Drop work order fields
Sum the hours and cost
Save your query asqryLaborGrouped
FactoryAccess - Accelerated Queries25
Query with Aggregate Results
FactoryAccess - Accelerated Queries26
Parameter Queries
Accept a variable when run
Order Noparameter
FactoryAccess - Accelerated Queries27
Parameter Queries
Create a parameter query
Base on qryLaborDetail_3
Show all fields
Prompt for order no
Save as qryParam
FactoryAccess - Accelerated Queries28
Parameter Query Results
Factory Access - Accelerated Queries29
Accelerated Introduction to Access
End of Module
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
FactoryAccess - Accelerated Queries30
FactoryAccess - Accelerated Queries31
Factory Access - Reports & Forms1
Microsoft Access
Module -
Reports
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
FactoryAccess - Reports & Forms2
Module Hands On
– Part 1 Create a summary report
Format the report
Add conditional formatting
– Part 2 Add a calculated field
Add summary fields
– Sub-reports (optional) Create a sub-report
Create a master-detail report
Students “hands-on”:
Day 1
FactoryAccess - Reports & Forms3
Creating reports
Two basic methods:– Report Wizard
– Report Designer
Common approach is to start w/the Wizard then modify the result
FactoryAccess - Reports & Forms4
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
FactoryAccess - Reports & Forms5
FactoryAccess - Reports & Forms6
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
FactoryAccess - Reports & Forms7
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
FactoryAccess - Reports & Forms8
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
FactoryAccess - Reports & Forms9
Report Designer
FactoryAccess - Reports & Forms10
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.
FactoryAccess - Reports & Forms11
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)
FactoryAccess - Reports & Forms12
Report Designer Results
FactoryAccess - Reports & Forms13
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)
FactoryAccess - Reports & Forms14
Report DesignerF
unct
ion
FactoryAccess - Reports & Forms15
Report Designer
Open the report Labor indesign view
– Add a cost column (unboundcontrol)
– Format to taste
– Save report as rptLaborCost
FactoryAccess - Reports & Forms16
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
FactoryAccess - Reports & Forms17
Labor report with Cost Column
FactoryAccess - Reports & Forms18
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
FactoryAccess - Reports & Forms19
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
FactoryAccess - Reports & Forms20
FactoryAccess - Reports & Forms21
Sub Reports
Create a subreport namedsrptLaborDetail_3
– Base on qryLaborDetail_3
– Be sure to use tabular format
– Eliminate extraneous titleinformation
FactoryAccess - Reports & Forms22
Labor Sub Report
FactoryAccess - Reports & Forms23
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
FactoryAccess - Reports & Forms24
Sub Reports
Drag new subreport to main report, or usesub/form subreport control
Save as rptWork_OrdersFull
FactoryAccess - Reports & Forms25
Full Work Orders Report Results
Factory Access - Reports & Forms26
Microsoft Access
[End of Module]P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
Module -
Reports
FactoryAccess - Reports & Forms27
Factory Access - Reports & Forms1
Introduction to Microsoft Access
Module -
Forms
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
FactoryAccess - Reports & Forms2
Module Hands On
– Part 1 Build two simple data entry forms
– Part 2 Create an unbound form
Create a sub-form
Build a master-detail form using above sub-form
Add totals
Students “hands-on”:
FactoryAccess - Reports & Forms3
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
FactoryAccess - Reports & Forms4
Form Wizard
Specify source table or query, fields, etc. (again, notefields can be bound or unbound)
Generally pick columnar style
Modify or preview result
Numerous properties can be set– Format (Appearance, scroll bars, etc.)
– Data (recordsource, etc.)
– Event (These can call macros and program code)
– Other
FactoryAccess - Reports & Forms5
FactoryAccess - Reports & Forms6
Form Wizard(Instructor Led)
Build a simple data entry form basedon the employees table– Modify the form when click finish
– Set the caption property to “MaintainEmployee Information”
– Make text labels “semi-bold”
– Size the form as desired
– Set record selector to “NO” if desired
– Save as frmEmployees
FactoryAccess - Reports & Forms7
Basic Employees form
FactoryAccess - Reports & Forms8
Form Wizard(On your own)
Build a simple data entry form basedon the work_orders table– Modify the form when click finish
– Set the caption property to “WorkOrders”
– Make text labels “semi-bold”
– Size the form as desired
– Set record selector to “NO” if desired
– Save as frmWork_Orders
FactoryAccess - Reports & Forms9
Basic Work Orders Form
FactoryAccess - Reports & Forms10
Form Designer
Design surface for forms
Add controls from toolbox
Some controls themselves have wizards whichcan invoke certain actions (run query, openreport, etc)
FactoryAccess - Reports & Forms11
FactoryAccess - Reports & Forms12
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
FactoryAccess - Reports & Forms13
Fix the query and form then rerun
Change the variable names to look at the form
FactoryAccess - Reports & Forms14
Criteria Selection Form Results
FactoryAccess - Reports & Forms15
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
FactoryAccess - Reports & Forms16
Subforms
Same idea as sub reports
Main form uses parent table; sub form useschild table
Make sure you have a permanent link
Create sub form first, drag and drop to mainform
Can also create with form wizard directly
FactoryAccess - Reports & Forms17
Typical Subform
FactoryAccess - Reports & Forms18
Subforms
Create a subform namedsfrmLaborDetail
– Use the form wizard
– Base on qryLaborDetail_3 query
– Pick tabular style (like a table orspreadsheet grid)
FactoryAccess - Reports & Forms19
Labor Detail Subform
FactoryAccess - Reports & Forms20
Subforms
Modify the frmWork_Orders form. Itwill be the new main form
– Drag and drop the sub formsfrmLaborDetail
– Remove extraneous labels
– Size and format as you like
– Save as frmWork_OrdersDetail
FactoryAccess - Reports & Forms21
Placing a subform on a main form
FactoryAccess - Reports & Forms22
Note link master and link child
FactoryAccess - Reports & Forms23
Work Orders Detail Form
FactoryAccess - Reports & Forms24
Sub Forms via the Wizard
Sub Forms can also be built automatically
Select parent table fields
Then child table fields
The child table will go in the subform
FactoryAccess - Reports & Forms25
Main and sub forms via Wizard(Instructor Demonstration)
FactoryAccess - Reports & Forms26
Totals on Master-Detail Forms
Common to add totals– Total of invoice line items
– Total of purchase order line items
– Etc.
Several ways– Add a total field to the subform footer (simplest)
– Add a total field to the main report using a domain aggregate
– Add a total field to the main report using an SQL aggregate
– Probably others!
FactoryAccess - Reports & Forms27
Adding a total to a sub form
Works just like totals in reports
Add the total field in the footer
– Add the sum so that control source reads =sum([fieldname]) with the equals sign
If desired can hide the footer field and add afield to the main form that references the totalfield on the subform
FactoryAccess - Reports & Forms28
Add a total to a sub form
Use the sfrmLaborDetail
Save as sfrmLaborDetail_Total
Expand the footer and add a field forhours
In the control source make sureexpression is =sum([hours])
Now add the sub form to thefrmEmployees main form
FactoryAccess - Reports & Forms29
Master-detail form with total
FactoryAccess - Reports & Forms30
Domain Aggregates(Concept demonstration)
Similar to Spreadsheet functions
Will total, etc., values in a field across several rows(records)
Can be used to populate a control on a form; say totalsor counts of “columns” in a sub form
There are several: dlookup(), dsum(), dcount(), davg(),etc.
– Syntax: Dname(“fieldname”,”source”, “criteria”)
– Note: quotes are mandatory
FactoryAccess - Reports & Forms31
Domain Aggregates
Use builder to help build domain aggregates
FactoryAccess - Reports & Forms32
Work Orders Formw/ Domain Aggregate
Factory Access - Reports & Forms33
Introduction to Microsoft Access
[End of Module]P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
Module -
Forms
FactoryAccess - Reports & Forms34
FactoryAccess - Reports & Forms35
Factory Access Accel. Introduction v1.021
Introduction to Access
Module
Introduction to Access, ExternalData
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
FactoryAccess Accel. Introduction v1.022
Module Outline
Topic 1: Export tables
Topic 2: Import data
Topic 3: Link tables
Three short topics:
FactoryAccess Accel. Introduction v1.023
Module Hands On
– Export work_orders and employees tables
– Import work_orders text file
– Link employees excel file
Students “hands-on”:
FactoryAccess Accel. Introduction v1.024
Sample Application – Factory2000
Simple cost accountingapplication
Employees work on workorders
Clock labor hours for eachwork order
FactoryAccess Accel. Introduction v1.025
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?
FactoryAccess Accel. Introduction v1.026
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
FactoryAccess Accel. Introduction v1.027
Exporting a table
Very simple
Click File|Export (or save as)
Specify type: Access canexport to many types: Excel,Text, etc.
FactoryAccess Accel. Introduction v1.028
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!
FactoryAccess Accel. Introduction v1.029
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.
FactoryAccess Accel. Introduction v1.0210
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
FactoryAccess Accel. Introduction v1.0211
Importing a table from a Text File
Importing a Text File
FactoryAccess Accel. Introduction v1.0212
Importing from a text file
Import the instructor-suppliedlabor text file into the previouslycreated labor table
Save the specification
FactoryAccess Accel. Introduction v1.0213
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.
FactoryAccess Accel. Introduction v1.0214
Linking a table
Linking a Table
FactoryAccess Accel. Introduction v1.0215
Linking a table
Linking a Table
FactoryAccess Accel. Introduction v1.0216
Linking a table
Linking a Table
FactoryAccess Accel. Introduction v1.0217
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]
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
Module
Introduction to Access, ExternalData
19
Factory Access - Automating Tasks1
Module - Automating the Application -
Advanced Queries, Macros, ControlWizards
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.dhdursoassociates.com
Microsoft Access
FactoryAccess - Automating Tasks2
Module Outline
Part 1: Advanced Queries
Part 2: Macros
Part 3: Control Wizards
Three parts:
FactoryAccess - Automating Tasks3
Module Hands On
– Create action queries
Maketable
Append
Delete
– Create macros
Simple
Run from form
Add where criteria
Add a condition
– Use the control wizard to close a form
Students “hands-on”:
FactoryAccess - Automating Tasks4
MS Access Training
Part 1Advanced Queries
FactoryAccess - Automating Tasks5
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
FactoryAccess - Automating Tasks6
FactoryAccess - Automating Tasks7
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
FactoryAccess - Automating Tasks8
MakeTable Query
Create an extract of theEmployees table
– Save table as Temp_Employees
– Select MgrNo = 13
– Save your query asqryMake_Temp_Employees
FactoryAccess - Automating Tasks9
MakeTable Query Results
FactoryAccess - Automating Tasks10
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
FactoryAccess - Automating Tasks11
FactoryAccess - Automating Tasks12
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
FactoryAccess - Automating Tasks13
Append Query Results
FactoryAccess - Automating Tasks14
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
FactoryAccess - Automating Tasks15
Delete Query
Delete from temp_employees
– All records with rate less than 20
FactoryAccess - Automating Tasks16
Delete Query Results
FactoryAccess - Automating Tasks17
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
Part 2
Introduction toMacros
FactoryAccess - Automating Tasks19
Introduction to Macros
Two ways of “programming” a taskin Access– Macros
– Visual Basic for Applications (VBA)
We will cover Macros primarily
We will also take a look at controlwizards which do use VBA
Introduction to Macros
Three types of macros
– Standalone Appear in the navigation pane
– Embedded Embedded in forms and reports
– Data macro Stored with a table
FactoryAccess - Automating Tasks20
FactoryAccess - Automating Tasks21
Introduction to Macros
Elements of Macros– Events – what “fires” the macro
– Actions – what the macro does
– Action arguments – control the specifics of the action
– Conditions – circumstance under which the macro “fires”
Macro designer – Where macro is defined
FactoryAccess - Automating Tasks22
FactoryAccess - Automating Tasks23
Introduction to Macros
Create and run a simple macro, mcrFirst,to become familiar with the concepts
– Action = msgbox
– Arguments: “Hello, Macro”
information type
title = “My First Macro”
FactoryAccess - Automating Tasks24
Designing the First Macro
FactoryAccess - Automating Tasks25
My First Macro
FactoryAccess - Automating Tasks26
Automating Import/Export withMacros
Can string together several macros one afterthe other
Useful to do an import (or links, even) followedby data manipulation
As an example we will automate our prior threeaction queries
FactoryAccess - Automating Tasks27
Import Macro
Design a simple macro toimport a table
Action is transfertext
Arguments are labor textfile to be imported
Save as mcrImport
FactoryAccess - Automating Tasks28
Designing Import Macro
FactoryAccess - Automating Tasks29
Macro “Programs”
Create a new macro to run the actionqueries created earlier
Use open query action
Use the action query name as theargument
Add a msgbox
Open the import table when done
Save as mcrActionQueries
FactoryAccess - Automating Tasks30
More on Macros
Macros can be invoked depending on“condition”
Uses a condition column
Macro will run iff condition is true
Macros can be run one after another like a“program”. Example:– Do a maketable query , then an append query from
a different file. I.E different downloads from an ERPsystem
FactoryAccess - Automating Tasks31
MS Access Training
Part 3Control Wizards
FactoryAccess - Automating Tasks32
Control Wizards
Will create program code for you to perform atask
Form operations
Report operations
Etc.
Invoked by making sure the wizard button isdepressed when adding a control
FactoryAccess - Automating Tasks33
Using the Control Wizard
Reached from more option…
FactoryAccess - Automating Tasks34
Using the Control Wizard
FactoryAccess - Automating Tasks35
Form Events
Forms and controls have “events”
These “fire” on certain actions. Examples:
– Click a button (onclick event)
– Before table is updated (beforeupdate event)
– After table is updated (afterupdate event)
– Etc.
FactoryAccess - Automating Tasks36
Form Events (cont’d)
When an event “fires” can execute:
– VBA Code
– Macro
This provides a method for “running” anapplication
FactoryAccess - Automating Tasks37
Control wizards
Create a criteria selection form
Add a button to run the actionqueries
Add a button to close the form
Run the form
That’s it!
FactoryAccess - Automating Tasks38
Form with Button to Launch Macro
Factory Access - Automating Tasks39
Microsoft Access
End of Module
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
FactoryAccess - Automating Tasks40
Factory Access Navigation1
Module – Navigation
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
Microsoft Access
Access navigation
Newer Access 2010 databases use the newNavigation forms
Older MDB databases still have theSwitchboard Manager tool available
FactoryAccess Navigation2
Creating a Navigation form
Select the desired style
FactoryAccess Navigation3
Navigation Forms
Drag and drop forms and reports one by one
FactoryAccess Navigation4
Adjust properties
Adjust properties as desired and save asfrmNavigation
FactoryAccess Navigation5
Set startup options (File | Options)
Set the Access options to open the navigationform when it starts up
FactoryAccess Navigation6
FactoryAccess Navigation7
Navigation Form
Create a navigation form foryour application
FactoryAccess Navigation8
Switchboards
Create via a switchboard manager tool
Creates a tree of launch forms – mainform is called switchboard and is placedwith your other form objects
FactoryAccess Navigation9
Launching Switchboard Manager
FactoryAccess Navigation10
Switchboard Manager
Yes – we want to create a new switchboard!
FactoryAccess Navigation11
Switchboard Manager
FactoryAccess Navigation12
Switchboards
Create a switchboard for yourapplication
Factory Access Navigation13
Microsoft Access
End of Module
P.O. Box 6142Laguna Niguel, CA 92607949-489-1472http://www.d2associates.com
Please fill out and turn in yourend-of-course evaluations.
FactoryAccess Navigation14
FactoryAccess Navigation15
Accelerated Computer Training for Working Professionals
Orange Coast Database Associates Course (800)355-9855 or http://ocdatabases.itgo.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