Microsoft Access 2003 Forms, Reports, and Queries

by McFedries, Paul

• ISBN13: 9780789731524
• ISBN10: 0789731525
• Edition: 1st
• Format: Paperback
• Copyright: 2004-08-18
• Publisher: Que Publishing

Summary

When it comes to job-productivity, we all want to be able to perform well, especially when it comes to challenging assignments. Anyone who works with Microsoft Access on a regular basis knows that this program can prove to be one of the most challenging. If you are looking for a way to get the most you can out of the primary Access tasks,Microsoft Access 2003 Forms, Reports and Queriesis the learning tool that you need. Focusing only on the forms, reports and queries functions, this book provides you with practical know-how, real-world examples and techniques that you can put to use immediately. Learn to condense mountains of information into manageable molehills of useful knowledge, so that you can perform at your best!

Author Biography

Paul McFedries is the president of Logophilia Limited, a technical writing company. Now primarily a writer, Paul has worked as a programmer, consultant, spreadsheet developer and website developer. He has written more than 40 books that have sold nearly three million opies worldwide. These books include Formulas and Functions for Excel (Que, 2004), The Absolute Beginner's Guide to VBA (Que, 2004) and The Complete Idiot's Guide to Windows XP (Alpha, 2001).

Table of Contents

Introduction	1	(1)
What's in the Book	2	(1)
This Book's Special Features	2	(5)
I CREATING POWERFUL QUERIES
Creating a Basic Query	7	(30)
Sorting Records	7	(2)
Sorting on a Single Field	8	(1)
Sorting on Multiple Fields	8	(1)
Filtering Table Data	9	(6)
Filtering by Selection	9	(2)
Filter Excluding Selection	11	(1)
Filtering By Form	11	(1)
Learning About Filter Criteria	12	(2)
Creating a Filter	14	(1)
Working with Queries	15	(1)
Creating a Query	16	(6)
Creating a New Query Object	16	(2)
Selecting the Fields to Include in the Query	18	(1)
Entering the Query Criteria	19	(1)
Excluding a Field from the Query Results	19	(1)
Selecting Only the Top N Values	19	(1)
Setting Field Properties	20	(1)
Running the Query	21	(1)
Querying Notes for Business Users	22	(4)
Querying for a Mail Merge	23	(3)
Creating Queries with the Query Wizards	26	(3)
Creating Crosstab Queries	26	(2)
Creating Find Duplicates Queries	28	(1)
Creating Find Unmatched Queries	28	(1)
Working with a Query Dynaset	29	(5)
Understanding the Datasheet View	29	(1)
Navigating Fields	30	(1)
Entering Data	30	(1)
Adding More Records	31	(1)
Navigating Records	31	(1)
Selecting a Record	32	(1)
Copying a Record	32	(1)
Deleting a Record	33	(1)
Formatting the Datasheet	33	(1)
Working with Query Properties	34	(3)
From Here	35	(2)
Building Criteria Expressions	37	(28)
Using Operands in Criteria Expressions	38	(1)
Literals	38	(1)
Identifiers	38	(1)
Functions	39	(1)
Using Operators in Criteria Expressions	39	(6)
Comparison Operators	39	(1)
Arithmetic Operators	40	(1)
The Like Operator	41	(1)
The Between...And Operator	41	(1)
The In Operator	41	(1)
The Is Null Operator	41	(1)
Compound Criteria and the Logical Operators	42	(1)
Using the Logical Operators	43	(1)
Understanding Operator Precedence	44	(1)
Setting Up a Calculated Column	45	(2)
Calculating Inventory Value	46	(1)
Calculating Discounted Invoice Totals	47	(1)
Using the Built-In Functions	47	(15)
Using Text Functions	49	(3)
Using Date and Time Functions	52	(5)
Using Math Functions	57	(2)
Using Financial Functions	59	(3)
Working with the Expression Builder	62	(3)
From Here	63	(2)
Working with Multiple-Table Queries	65	(28)
Relational Database Fundamentals	65	(5)
The Pitfalls of a Nonrelational Design	65	(3)
How a Relational Design Can Help	68	(1)
Step 1: Separate the Data	68	(1)
Step 2: Add Foreign Keys to the Tables	69	(1)
Step 3: Establish a Link Between the Related Tables	70	(1)
Types of Relational Models	70	(2)
The One-To-Many Model	70	(1)
The One-to-One Model	70	(1)
The Many-to-Many Model	71	(1)
Enforcing Referential Integrity	72	(1)
Establishing Table Relationships	72	(4)
Understanding Join Lines	73	(1)
Types of Joins	73	(1)
Adding Tables to the Relationships Window	74	(1)
Joining Tables	75	(1)
Editing a Relationship	76	(1)
Removing a Join	76	(1)
Working with Multiple Tables in a Query	76	(4)
Adding Multiple Tables to a Query	77	(1)
Adding Fields from Multiple Tables	77	(2)
Nesting Queries Within Queries	79	(1)
Joining Tables Within the Query Design Window	80	(1)
Creating Other Types of Joins	80	(6)
Creating Outer Joins	80	(4)
Creating Self-Joins	84	(1)
Creating Theta Joins	85	(1)
Creating a Unique Values Query	86	(7)
Drilling Down to the Order Details	88	(4)
From Here	92	(1)
Creating Advanced Queries	93	(24)
Creating a Totals Query	93	(10)
Displaying the Total Row in the Design Grid	94	(1)
Setting Up a Totals Query On a Single Field	94	(1)
Setting Up a Totals Query On Multiple Fields	95	(1)
Filtering the Records Before Calculating Totals	96	(1)
Creating a Totals Query for Groups of Records	97	(1)
Grouping on Multiple Fields	98	(1)
Creating a Totals Query Using a Calculated Field	99	(1)
Creating a Totals Query Using Aggregate Functions	100	(1)
Combining Aggregate Functions and Totals	101	(2)
Creating Queries That Make Decisions	103	(5)
Making Decisions with the IIf Function	103	(1)
Making Decisions with the Switch Function	104	(2)
Calculating a Customer Discount Rate	106	(2)
Running Parameter Queries	108	(2)
Creating a Simple Query Parameter	108	(2)
Specifying the Parameter Data Type	110	(1)
Running Action Queries	110	(7)
Modifying Table Data with an Update Query	110	(2)
Removing Records from a Table with a Delete Query	112	(1)
Creating New Tables with Make-Table Queries	113	(1)
Adding Records to a Table with an Append Query	114	(1)
From Here	115	(2)
Creating PivotTable Queries	117	(32)
What Is a PivotTable?	117	(3)
How PivotTables Work	118	(1)
Some PivotTable Terms	119	(1)
Creating a One-Dimensional PivotTable	120	(10)
Display Data Field Details	120	(2)
Displaying the Sum of the Data Field Values	122	(1)
Hiding and Showing the Data Details	123	(1)
Inserting an AutoCalc Data Field Summary Calculation	124	(2)
Changing the AutoCalc Calculation Type	126	(1)
Creating a Calculated Field	126	(2)
Removing a PivotTable Field	128	(1)
Creating a Multiple-Field One-Dimensional PivotTable	128	(2)
Creating a Two-Dimensional PivotTable	130	(8)
Analyzing Customer Orders by Product Category	131	(2)
Adding a Temporal Dimension to the PivotTable	133	(5)
Filtering a PivotTable	138	(5)
Using the PivotTable AutoFilters	139	(1)
Displaying Only the Top or Bottom Items	139	(3)
Grouping Field Items	142	(1)
Adding a Filter Field	142	(1)
Pivoting a PivotTable	143	(3)
Moving a Field to a Different Area	144	(1)
Changing the Field Order	144	(2)
Formatting a PivotTable	146	(3)
From Here	146	(3)
Querying with SQL Statements	149	(22)
Viewing the SQL Statement	149	(1)
Using SQL to Perform a Select Query	150	(9)
Understanding the SELECT Statement	150	(3)
Using SQL with Multiple-Table Queries	153	(3)
Adding a Calculated Column to the SELECT Statement	156	(1)
Using SQL to Total and Group Records	157	(1)
Using SQL to Set Up a Parameter Query	158	(1)
The Full SQL Select Syntax	158	(1)
Using SQL to Perform Action Queries	159	(2)
Using SQL to Perform an Update Query	159	(1)
Using SQL to Perform a Delete Query	160	(1)
Using SQL to Perform a Make-Table Query	160	(1)
Using SQL to Perform an Append Query	161	(1)
Using SQL to Create Subqueries	161	(5)
Using a Subquery to Define a Field	162	(1)
Determining Whether a Unit Price Is Greater Than the Average	163	(1)
Using a Subquery to Define Criteria for a Field	163	(1)
Using Subqueries That Return Dynasets	164	(1)
In Predicate: Customers Who Have Placed Orders	165	(1)
All Predicate: Products Cheaper Than All the Condiments	165	(1)
Using SQL to Create Union Queries	166	(5)
From Here	168	(3)
II CREATING FORMS
Creating and Using a Form	171	(18)
Creating a Form with AutoForm	173	(2)
Running AutoForm Directly on a Table or Query	173	(1)
Running the AutoForm Wizard	174	(1)
Creating Simple Forms with the Form Wizard	175	(2)
Navigation in a Form	177	(1)
Creating a Form in Design View	177	(12)
Displaying the Design View	178	(1)
A Tour of the Design View Window	178	(1)
Understanding Form Controls	179	(1)
Adding Fields to the Form	180	(1)
Changing the Size of the Form	181	(1)
Viewing the Form	181	(1)
Assigning an AutoFormat in Design View	182	(1)
Working with the Form Header and Footer	183	(1)
Working with Form Properties	184	(1)
Formatting the Background	185	(1)
Protecting the Form and Data from Other Users	186	(2)
From Here	188	(1)
Working with Form Controls	189	(18)
Manipulating Form Controls	189	(10)
Inserting Controls on a Form	190	(1)
Selecting Controls	190	(1)
Formatting Controls	191	(1)
Adding Conditional Formatting	191	(2)
Sizing Controls	193	(1)
Moving Controls	194	(3)
Copying Controls	197	(1)
Deleting Controls	197	(1)
Grouping Controls	197	(1)
Ordering Overlapped Controls	197	(1)
Converting an Unbound Control to a Bound Control	198	(1)
Changing a Control's Type	198	(1)
Setting the Tab Order	199	(1)
Adding Labels to the Form	199	(2)
Inserting a Label	200	(1)
Editing the Label Caption	200	(1)
Using Labels to Create Keyboard Shortcuts for Controls	200	(1)
Adding Text Boxes to the Form	201	(6)
Inserting a Text Box	201	(1)
Using Text Boxes As Calculated Controls	202	(2)
Creating a Mortgage Calculator	204	(1)
From Here	205	(2)
Designing Forms for Efficient and Accurate Data Entry	207	(24)
Preventing Errors by Validating Data	208	(5)
Helping Users with Text Prompts	208	(1)
Preventing Errors with Data Validation Expressions	209	(1)
Using Input Masks for Consistent and Accurate Data Entry	210	(3)
Using Controls to Limit Data-Entry Choices	213	(12)
Working with Yes/No Fields	214	(2)
Using Option Buttons to Present a Limited Number of Choices	216	(3)
Using an Option Group to Select the Shipper	219	(1)
Using Lists to Present a Large Number of Choices	220	(5)
Data Entry with ActiveX Controls	225	(6)
Entering Numbers Using a Spin Button	225	(2)
Entering Numbers Using a Scrollbar	227	(1)
Entering Dates Using a Calendar	228	(1)
From Here	229	(2)
Designing Forms for Business Use	231	(18)
Using Forms in a Business Context	231	(2)
Why Collect the Data?	232	(1)
What Is the Data?	232	(1)
Who Are Your Users?	232	(1)
Ten Design Guidelines for Business Forms	233	(2)
Make Forms Fast	233	(1)
Make Forms Foolproof	233	(1)
Mimic Paper Forms When Practical	233	(1)
Give Users What They Need, and Then Stop	233	(1)
Don't Neglect the Keyboard	234	(1)
Watch the Field Order (and the Tab Order, Too)	234	(1)
Watch Your Screen Resolution and Colors	234	(1)
Make Form Text Readable	235	(1)
Go Easy on the Extras	235	(1)
Organize Your Form Controls	235	(1)
Organizing Controls on the Form	235	(5)
Making Good Use of Lines and Rectangles	236	(1)
Organizing with Option Groups	237	(1)
Organizing with a Tab Control	238	(2)
Enhancing Form Text	240	(2)
Formatting Text	241	(1)
Text Formatting Tips and Guidelines	241	(1)
Fancier Form Formatting	242	(7)
Working with Colors	242	(3)
Adding Images to Your Forms	245	(1)
Adding Special Effects	246	(1)
Create a Shadow Effect for Text	246	(1)
From Here	247	(2)
Creating Specialized Forms	249	(18)
Creating a Multiple-Table Form	249	(4)
Understanding Subforms	249	(1)
Creating a Form and Subform with the Form Wizard	250	(2)
Creating a Subform in the Form Design View	252	(1)
Working with Form Command Buttons	253	(4)
Creating a Switchboard Form	256	(1)
Creating a Form Pop-Up Box or Dialog Box	257	(1)
Creating a Pop-Up Form	257	(1)
Creating a Modal Form	258	(1)
Using a Custom Form with a Parameter Query	258	(4)
Creating the Custom Form	259	(1)
Adjusting the Parameter Query	260	(1)
Using the Custom Form and Parameter Query	260	(2)
Creating a Startup Form	262	(1)
Creating a PivotChart Form	262	(5)
From Here	264	(3)
III DESIGNING AND CUSTOMIZING
Creating and Publishing a Report	267	(28)
Creating a Report with AutoReport	267	(2)
Running AutoReport Directly on a Table or Query	268	(1)
Running the AutoReport Wizard	269	(1)
Creating Simple Reports with the Report Wizard	269	(2)
Creating a Report in Design View	271	(20)
Displaying the Design View	272	(1)
The Architecture of Access Reports	272	(2)
Understanding Report Controls	274	(1)
Adding Fields to the Report	275	(1)
Adding Labels to the Report	275	(2)
Adding Page Numbers to the Report	277	(1)
Adding the Date and Time to the Report	277	(1)
Changing the Size of a Report Section	278	(1)
Previewing the Report	278	(1)
Assigning an AutoFormat in Design View	279	(1)
Working with Report Properties	280	(1)
Formatting the Background	281	(2)
Manipulating Report Controls	283	(8)
Publishing a Report	291	(4)
Publishing on Paper	291	(1)
Publishing to Email	292	(1)
Exporting to Another Format	292	(1)
Publishing to Word or Excel	292	(1)
From Here	293	(2)
Designing Effective Business Reports	295	(16)
Using Reports in Business	295	(3)
What's In the Report?	296	(1)
What Is the Goal of the Report?	296	(2)
Who Are Your Readers?	298	(1)
Ten Design Guidelines for Business Reports	298	(3)
Copy Legacy Reports When Practical	298	(1)
Give Users What They Need, Then Stop: Part 1	299	(1)
Give Users What They Need, Then Stop: Part 2	299	(1)
Use Page Numbers	300	(1)
Use Dates and Times	300	(1)
Watch the Field Order	300	(1)
Watch Your Screen Resolution and Colors	300	(1)
Make Report Text Readable	301	(1)
Always Sort and/or Group Data	301	(1)
Organize the Report Layout	301	(1)
Organizing Controls on the Report	301	(2)
Making Good Use of Lines and Rectangles	302	(1)
Creating Page Breaks	303	(1)
Enhancing Report Text	303	(2)
Formatting Text	304	(1)
Text Formatting Tips and Guidelines	304	(1)
Fancier Report Formatting	305	(6)
Working with Colors	306	(1)
Adding Images to Your Reports	307	(1)
Adding Special Effects	308	(1)
Create a Shadow Effect for Text	308	(1)
From Here	309	(2)
Designing Advanced Reports	311	(18)
Sorting and Grouping a Report	311	(4)
Setting Up Sorting Options	312	(1)
Setting Up Grouping Options	312	(2)
Sorting and Grouping Using an Expression	314	(1)
Adding Calculations to a Report	315	(5)
Inserting a Text Box	316	(1)
Using Text Boxes As Calculated Controls	316	(2)
Creating a Invoice Report	318	(2)
Advanced Methods for Launching a Report	320	(6)
Launching a Report with a Command Button	320	(2)
Launching a Report with a Macro	322	(4)
Controlling Report Output	326	(3)
Adding Page Breaks After Sections	326	(1)
Starting Sections at the Top of a Row or Column	327	(1)
Avoiding Widowed Records	328	(1)
From Here	328	(1)
Creating Specialized Reports	329	(20)
Creating a Multiple-Column Report	329	(7)
Setting Up the Report	330	(1)
Tweaking the Page Setup	330	(1)
Troubleshooting Multiple Columns	331	(2)
Using Multiple Columns to Reduce Report Page Count	333	(3)
Creating Mailing Labels	336	(3)
Running the Label Wizard	336	(2)
Creating a Custom Label	338	(1)
Creating a Mail Merge Report	339	(2)
Creating a Multiple-Table Report	341	(6)
Understanding Subreports	342	(1)
Creating a Report and Subreport with the Report Wizard	343	(2)
Creating a Subreport in the Report Design View	345	(2)
Creating a PivotChart Report	347	(2)
From Here	347	(2)
Index	349