book

Microsoft® Access 2003 Forms, Reports and Queries

by Paul McFedries

August 2004

Intermediate to advanced

384 pages

9h 12m

English

Que

Read now

Unlock full access

Dedication
What's in the BookThis Book's Special FeaturesCASE STUDY
Sorting RecordsSorting on a Single FieldSorting on Multiple FieldsFiltering Table DataFiltering by SelectionFilter Excluding SelectionFiltering By FormLearning About Filter CriteriaCreating a FilterAdding a Field to the Criteria GridEntering Criteria and Applying the FilterWorking with QueriesCreating a QueryCreating a New Query ObjectSelecting the Fields to Include in the QueryEntering the Query CriteriaExcluding a Field from the Query ResultsSelecting Only the Top N ValuesSetting Field PropertiesRunning the QueryQuerying Notes for Business UsersCASE STUDY: Querying for a Mail MergeQuerying the Customers TableRunning the Mail MergeCreating Queries with the Query WizardsCreating Crosstab QueriesCreating Find Duplicates QueriesCreating Find Unmatched QueriesWorking with a Query DynasetUnderstanding the Datasheet ViewNavigating FieldsEntering DataAdding More RecordsNavigating RecordsSelecting a RecordCopying a RecordDeleting a RecordFormatting the DatasheetChanging the Datasheet Column SizesChanging the Datasheet Row HeightsWorking with Query PropertiesFrom Here
Using Operands in Criteria ExpressionsLiteralsIdentifiersFunctionsUsing Operators in Criteria ExpressionsComparison OperatorsArithmetic OperatorsThe Like OperatorThe Between...And OperatorThe In OperatorThe Is Null OperatorCompound Criteria and the Logical OperatorsEntering And CriteriaEntering Or CriteriaUsing the Logical OperatorsUnderstanding Operator PrecedenceSetting Up a Calculated ColumnCalculating Inventory ValueCalculating Discounted Invoice TotalsUsing the Built-In FunctionsUsing Text FunctionsExtracting a First Name or Last NameFormatting Words with Proper CaseUsing Date and Time FunctionsCalculating a Person's AgeCalculating How Long Someone Has Been EmployedUsing Math FunctionsGenerating Random Numbers Between Two ValuesGenerating Random N-Digit NumbersUsing Financial FunctionsCreating a Mortgage Payment CalculatorWorking with the Expression BuilderFrom Here
Relational Database FundamentalsThe Pitfalls of a Nonrelational DesignHow a Relational Design Can HelpStep 1: Separate the DataStep 2: Add Foreign Keys to the TablesStep 3: Establish a Link Between the Related TablesTypes of Relational ModelsThe One-To-Many ModelThe One-to-One ModelThe Many-to-Many ModelEnforcing Referential IntegrityEstablishing Table RelationshipsUnderstanding Join LinesTypes of JoinsAdding Tables to the Relationships WindowJoining TablesEditing a RelationshipRemoving a JoinWorking with Multiple Tables in a QueryAdding Multiple Tables to a QueryAdding Fields from Multiple TablesNesting Queries Within QueriesJoining Tables Within the Query Design WindowCreating Other Types of JoinsCreating Outer JoinsUsing Outer Joins to Find Records Without Matching Records in a Related TableFinding Customers Without Matching OrdersFinding Products Without an Assigned CategoryCreating Self-JoinsCreating Theta JoinsCreating a Unique Values QueryCASE STUDY: Drilling Down to the Order DetailsAdding a Subdatasheet to a QueryWorking with Query SubdatasheetsFrom Here
Creating a Totals QueryDisplaying the Total Row in the Design GridSetting Up a Totals Query On a Single FieldSetting Up a Totals Query On Multiple FieldsFiltering the Records Before Calculating TotalsCreating a Totals Query for Groups of RecordsGrouping on Multiple FieldsCreating a Totals Query Using a Calculated FieldCreating a Totals Query Using Aggregate FunctionsCombining Aggregate Functions and TotalsCalculating Units Left In StockCreating Queries That Make DecisionsMaking Decisions with the IIf FunctionDetermining Whether Stock Needs to Be ReorderedMaking Decisions with the Switch FunctionCASE STUDY: Calculating a Customer Discount RateCalculating a Simple Discount RateCalculating a Complex Discount RateRunning Parameter QueriesCreating a Simple Query ParameterSpecifying the Parameter Data TypeRunning Action QueriesModifying Table Data with an Update QueryRemoving Records from a Table with a Delete QueryCreating New Tables with Make-Table QueriesAdding Records to a Table with an Append QueryFrom Here

What Is a PivotTable?How PivotTables WorkSome PivotTable TermsCreating a One-Dimensional PivotTableDisplay Data Field DetailsDisplaying the Sum of the Data Field ValuesHiding and Showing the Data DetailsInserting an AutoCalc Data Field Summary CalculationChanging the AutoCalc Calculation TypeCreating a Calculated FieldRemoving a PivotTable FieldCreating a Multiple-Field One-Dimensional PivotTableCreating a Two-Dimensional PivotTableAnalyzing Customer Orders by Product CategoryAdding a Temporal Dimension to the PivotTableWorking with the Built-In PivotTable Date FeaturesAdding Custom Date FieldsViewing Product Orders by Day of the WeekViewing Employee Orders by Custom QuarterFiltering a PivotTableUsing the PivotTable AutoFiltersDisplaying Only the Top or Bottom ItemsGrouping Field ItemsAdding a Filter FieldPivoting a PivotTableMoving a Field to a Different AreaChanging the Field OrderFormatting a PivotTableFrom Here
Viewing the SQL StatementUsing SQL to Perform a Select QueryUnderstanding the SELECT StatementThe SELECT VerbThe FROM ClauseThe WHERE ClauseThe ORDER BY ClauseUsing SQL with Multiple-Table QueriesUsing SQL to Create Inner JoinsUsing SQL to Create Outer JoinsUsing SQL to Create Self-JoinsUsing SQL to Create Theta JoinsFinding Customers Where the Ship Address Is Different from the Customer AddressAdding a Calculated Column to the SELECT StatementUsing SQL to Total and Group RecordsUsing SQL to Set Up a Parameter QueryThe Full SQL SELECT SyntaxUsing SQL to Perform Action QueriesUsing SQL to Perform an Update QueryUsing SQL to Perform a Delete QueryUsing SQL to Perform a Make-Table QueryUsing SQL to Perform an Append QueryUsing SQL to Create SubqueriesUsing a Subquery to Define a FieldDetermining Whether a Unit Price Is Greater Than the AverageUsing a Subquery to Define Criteria for a FieldUsing Subqueries That Return DynasetsIn Predicate: Customers Who Have Placed OrdersAll Predicate: Products Cheaper Than All the CondimentsUsing SQL to Create Union QueriesFrom Here
Creating a Form with AutoFormRunning AutoForm Directly on a Table or QueryRunning the AutoForm WizardCreating Simple Forms with the Form WizardNavigation in a FormCreating a Form in Design ViewDisplaying the Design ViewA Tour of the Design View WindowUnderstanding Form ControlsBound Controls (Fields)Unbound ControlsCalculated ControlsAdding Fields to the FormChanging the Size of the FormViewing the FormAssigning an AutoFormat in Design ViewWorking with the Form Header and FooterWorking with Form PropertiesFormatting the BackgroundSetting the Background ColorSetting the Background PictureCASE STUDY: Protecting the Form and Data from Other UsersFrom Here
Manipulating Form ControlsInserting Controls on a FormSelecting ControlsFormatting ControlsAdding Conditional FormattingSizing ControlsSizing to the GridSizing to the CaptionMaking Controls the Same SizeMoving ControlsAligning to the GridAligning Control EdgesAdjusting the Spacing Between ControlsCopying ControlsDeleting ControlsGrouping ControlsOrdering Overlapped ControlsConverting an Unbound Control to a Bound ControlChanging a Control's TypeSetting the Tab OrderAdding Labels to the FormInserting a LabelEditing the Label CaptionUsing Labels to Create Keyboard Shortcuts for ControlsAdding Text Boxes to the FormInserting a Text BoxUsing Text Boxes As Calculated ControlsCASE STUDY: Creating a Mortgage CalculatorFrom Here
Preventing Errors by Validating DataHelping Users with Text PromptsPreventing Errors with Data Validation ExpressionsUsing Input Masks for Consistent and Accurate Data EntryUsing the Input Mask WizardCreating a Custom Input Mask ExpressionUsing Controls to Limit Data-Entry ChoicesWorking with Yes/No FieldsUsing Check BoxesUsing Toggle ButtonsUsing Option Buttons to Present a Limited Number of ChoicesRunning the Option Group WizardCreating an Option Group By HandCASE STUDY: Using an Option Group to Select the ShipperUsing Lists to Present a Large Number of ChoicesStarting the List Box or Combo Box WizardGetting List Values from a Table or Query FieldSpecifying Custom List ValuesGetting List Values from the Current TableCreating a Multiple-Column ListModifying List Box and Combo Box PropertiesData Entry with ActiveX ControlsEntering Numbers Using a Spin ButtonEntering Numbers Using a ScrollbarEntering Dates Using a CalendarFrom Here
Using Forms in a Business ContextWhy Collect the Data?What Is the Data?Who Are Your Users?Ten Design Guidelines for Business Forms1. Make Forms Fast2. Make Forms Foolproof3. Mimic Paper Forms When Practical4. Give Users What They Need, and Then Stop5. Don't Neglect the Keyboard6. Watch the Field Order (and the Tab Order, Too)7. Watch Your Screen Resolution and Colors8. Make Form Text Readable9. Go Easy on the Extras10. Organize Your Form ControlsOrganizing Controls on the FormMaking Good Use of Lines and RectanglesOrganizing with Option GroupsOrganizing with a Tab ControlEnhancing Form TextFormatting TextText Formatting Tips and GuidelinesFancier Form FormattingWorking with ColorsCreating a Custom ColorUsing Color EffectivelyAdding Images to Your FormsAdding Special EffectsCreate a Shadow Effect for TextFrom Here
Creating a Multiple-Table FormUnderstanding SubformsCreating a Form and Subform with the Form WizardCreating a Subform in the Form Design ViewCreating a Subform Using Another Table or QueryCreating a Subform Using Another FormWorking with Form Command ButtonsCASE STUDY: Creating a Switchboard FormCreating a Form Pop-Up Box or Dialog BoxCreating a Pop-Up FormCreating a Modal FormUsing a Custom Form with a Parameter QueryCreating the Custom FormAdjusting the Parameter QueryUsing the Custom Form and Parameter QueryCreating a Startup FormCreating a PivotChart FormFrom Here
Creating a Report with AutoReportRunning AutoReport Directly on a Table or QueryRunning the AutoReport WizardCreating Simple Reports with the Report WizardCreating a Report in Design ViewDisplaying the Design ViewThe Architecture of Access ReportsUnderstanding Report ControlsBound Controls (Fields)Unbound ControlsCalculated ControlsAdding Fields to the ReportAdding Labels to the ReportAdding Page Numbers to the ReportAdding the Date and Time to the ReportChanging the Size of a Report SectionPreviewing the ReportAssigning an AutoFormat in Design ViewWorking with Report PropertiesFormatting the BackgroundSetting the Background ColorSetting the Background PictureManipulating Report ControlsInserting Controls on a ReportSelecting ControlsFormatting ControlsAdding Conditional FormattingSizing ControlsSizing to the GridSizing to the CaptionMaking Controls the Same SizeMoving ControlsAligning to the GridAligning Control EdgesAdjusting the Spacing Between ControlsCopying ControlsDeleting ControlsGrouping ControlsOrdering Overlapped ControlsPublishing a ReportPublishing on PaperPublishing to EmailExporting to Another FormatPublishing to Word or ExcelFrom Here
Using Reports in BusinessWhat's In the Report?What Is the Goal of the Report?Who Are Your Readers?Ten Design Guidelines for Business Reports1. Copy Legacy Reports When Practical2. Give Users What They Need, Then Stop: Part 13. Give Users What They Need, Then Stop: Part 24. Use Page Numbers5. Use Dates and Times6. Watch the Field Order7. Watch Your Screen Resolution and Colors8. Make Report Text Readable9. Always Sort and/or Group Data10. Organize the Report LayoutOrganizing Controls on the ReportMaking Good Use of Lines and RectanglesCreating Page BreaksEnhancing Report TextFormatting TextText Formatting Tips and GuidelinesFancier Report FormattingWorking with ColorsCreating a Custom Report ColorUsing Color Effectively in ReportsAdding Images to Your ReportsAdding Special EffectsCreate a Shadow Effect for TextFrom Here
Sorting and Grouping a ReportSetting Up Sorting OptionsSetting Up Grouping OptionsSorting and Grouping Using an ExpressionAdding Calculations to a ReportInserting a Text BoxUsing Text Boxes As Calculated ControlsCASE STUDY: Creating a Invoice ReportAdvanced Methods for Launching a ReportLaunching a Report with a Command ButtonLaunching a Report with a MacroCreating the MacroAssociating the Macro with a Command ButtonAssociating the Macro with a Report EventControlling Report OutputAdding Page Breaks After SectionsStarting Sections at the Top of a Row or ColumnAvoiding Widowed RecordsFrom Here
Creating a Multiple-Column ReportSetting Up the ReportTweaking the Page SetupTroubleshooting Multiple ColumnsCASE STUDY: Using Multiple Columns to Reduce Report Page CountCreating Mailing LabelsRunning the Label WizardCreating a Custom LabelCreating a Mail Merge ReportCreating a Multiple-Table ReportUnderstanding SubreportsCreating a Report and Subreport with the Report WizardCreating a Subreport in the Report Design ViewCreating a Subreport Using Another Table or QueryCreating a Subreport Using Another ReportCreating a PivotChart ReportFrom Here

Overview

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 Queries is 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!