Transcript Table, query tips

Access
Chapter 5-Table Tricks, Advanced
Queries and Custom Forms
Lookup Tables
 Use to limit user input into a table


Data validation
Data integrity
 Wizard shields you from relationship complexity,
Key columns
 “Lookup table” must have a defined relationship
with the main table

Can use SQL statement as data source
PgP MIS 342 Access
5-2
Lookup Tables
 Examine Wizard results by viewing ‘field
properties’ on the Lookup tab
PgP MIS 342 Access
5-3
Northwind.mdb Orders Table
Properties
Examine the properties
PgP MIS 342 Access
5-4
Table Input Masks
 Enforces predefined format
 Study input mask characters
 Experiment with
the Wizard
PgP MIS 342 Access
5-5
Table Input Masks
 Note literal display characters can be stored
in database. You may not think this is
necessary, but it is wise to store the
characters if you plan on exporting the table
to a text or other external file.
 Consider when interfacing with other
systems.

XML, EDI, other databases
PgP MIS 342 Access
5-6
Table Input Mask Exercise
 Using blank Access database file,
 Create new table, with two fields






PhoneNumberWithMaskCharacters
PhoneNumberWithoutMaskCharacters
Use Input Mask Wizard to setup fields
Enter data into both fields of a record
Export table to Excel file
Examine results
PgP MIS 342 Access
5-7
Table Input Mask Exercise
 PhoneNumberWithMaskCharacters

!\(999") "000\-0000;0;_
 PhoneNumberWithoutMaskCharacters

!\(999") "000\-0000;;_
 Sections separated by ‘;’
 What does each section mean?
PgP MIS 342 Access
5-8
Table Input Mask Exercise
 Section 1-The Mask

19 possible characters, search Help for
InputMask Property
 Section 2-Store which characters?


0- store all characters
1 or blank- store only typed characters
 Section 3- Placeholder character
PgP MIS 342 Access
5-9
Query-Pattern Match
 Query criteria, Like combined with wildcard
characters-*,?,#
PgP MIS 342 Access
5-10
Parameter Query
 Specify criteria at run time
 Access provides the Prompt box,…
 End user supplies the parameter
 Offers a degree of flexibility
 But-user needs to know value to enter!
PgP MIS 342 Access
5-11
Custom Forms
 Start from scratch,
 Modify existing form or…
 Begin with the Form Wizard and modify the
results…
 Modifications are done in Design View
PgP MIS 342 Access
5-12
Custom Forms-Items Needed
 Learn the available objects

Text Box, Command Buttons…
 Refer to the Field List
 Realize different objects have different
properties
 Learn how to select, move and position
controls
PgP MIS 342 Access
5-13
Custom Forms-Labels vs Text
Box
 Label- displays static text
 Text Box- the most basic “data bound”
control. Used to display data from tables,
queries, calculated fields or expressions
PgP MIS 342 Access
5-14
Custom Forms-Tabs
 Efficient use of space
 Requires good logical grouping of fields to
be effective
 The more familiar you are with positioning,
resizing and aligning of controls, the easier
this will be
PgP MIS 342 Access
5-15
Form Filtering
 Use to target certain field values
 Filters that you use often can be saved as a
query
 Can also apply queries as filters


Filter by Form
Apply query as filter
PgP MIS 342 Access
5-16