book

Microsoft® Access® 2010 Inside Out

Name: Microsoft® Access® 2010 Inside Out
Author: Jeff Conrad and John Viescas
ISBN: 9780735649132

by Jeff Conrad and John Viescas

August 2010

Intermediate to advanced

1500 pages

54h 49m

English

Microsoft Press

Read now

Unlock full access

Microsoft® Access® 2010 Inside Out
Dedication
A Note Regarding Supplemental Files
Acknowledgments
About the CD
What’s on the CD
Sample Applications
System Requirements
Support Information
Conventions and Features Used in This Book

Text Conventions
Design Conventions
Syntax Conventions
Introduction
Getting Familiar with Access 2010
About This Book
I. Understanding Access
1. What Is Access?
What Is a Database?
Relational DatabasesDatabase Capabilities
Access as an RDBMS
Data Definition and StorageData ManipulationData Control
Access as an Application Development System
Deciding to Move to Database Software
Extending the Power of Access to the Web
2. Exploring the Access 2010 Interface
Opening Access for the First Time
Getting Started with Access 2010
Opening an Existing DatabaseExploring the Microsoft Office Backstage ViewInfo TabRecent TabNew TabPrint TabSave & Publish TabHelp TabTaking Advantage of the Quick Access Toolbar
Understanding Content Security
Enabling a Database That Is Not TrustedUnderstanding the Trust CenterEnabling Content by Defining Trusted Locations
Understanding the Office Fluent Ribbon
Home TabCreate TabExternal Data TabDatabase Tools TabCustomizing the Ribbon
Understanding the Navigation Pane
Exploring Navigation Pane Object ViewsWorking with Custom Categories and GroupsExploring the Navigation Options Dialog BoxCreating and Modifying a Custom CategoryCreating and Modifying Groups in a Custom CategoryCreating Object Shortcuts in Custom GroupsHiding Custom Groups in a CategoryHiding and Renaming Object ShortcutsRevealing Hidden ShortcutsSorting and Selecting Views in the Navigation PaneManually Sorting Objects in the Navigation PaneSearching for Database Objects
Using the Single-Document vs. the Multiple-Document Interface
Modifying Global Settings via the Access Options Dialog Box
3. Access 2010 Overview
The Architecture of Access
Exploring a Desktop Database—Housing Reservations
TablesTable Window in Design ViewTable Window in Datasheet ViewQueriesQuery Window in Design ViewQuery Window in Datasheet ViewFormsForm Window in Design ViewForm Window in Layout ViewForm Window in Form ViewReportsReport Window in Design ViewReport Window in Print PreviewReport Window in Layout ViewReport Window in Report ViewMacrosModules
What Happened to Project Files (ADP)?
The Many Faces of Access
II. Creating a Database and Tables
4. Designing Client Tables
Creating a New Database
Using a Database Template to Create a DatabaseCreating a New Empty Database
Creating Your First Simple Table by Entering Data
Creating a Table Using Application Parts
Creating a Table Using Data Type Parts
Creating a Table in Design View
Defining Fields
Understanding Field Data TypesSetting Field PropertiesCompleting the Fields in the Companies TableDefining Simple Field Validation RulesDefining Input Masks
Defining a Primary Key
Defining a Table Validation Rule
Understanding Other Table Properties
Defining Relationships
Defining Your First RelationshipCreating a Relationship on Multiple Fields
Adding Indexes
Single-Field IndexesMultiple-Field Indexes
Setting Table Design Options
Creating a Default Template for New Databases
Printing a Table Definition
Database Limitations
5. Modifying Your Table Design
Before You Get Started
Making a Backup CopyChecking Object Dependencies
Deleting Tables
Renaming Tables
Changing Field Names
Moving Fields
Inserting Fields
Copying Fields
Deleting Fields
Changing Data Attributes
Changing Data TypesChanging Data LengthsDealing with Conversion ErrorsChanging Other Field Properties
Reversing Changes
Using the Table Analyzer Wizard
Taking a Look at Lookup Properties
Working with Multi-Value Lookup Fields
Changing the Primary Key
Compacting Your Database
6. Designing Web Tables
Working with the Web
Creating a New Web Database
Using a Database Template to Create a Web DatabaseCreating a New Empty Web Database
Creating Your First Simple Web Table by Entering Data
Creating a Web Table Using Application Parts
Using Data Type Parts
Creating Web Tables in Datasheet View
Defining Web FieldsUnderstanding Web Field Data TypesSetting Field Properties for Web DatabasesCompleting the Fields in the Vendors Web TableCreating Calculated FieldsDefining Field Validation Rules for Web Databases
Defining a Table Validation Rule for Web Databases
Defining a Primary Key for Web Databases
Understanding Other Web Table Properties
Creating Lookup Fields in a Web Database
Creating Relationships Using Lookup Fields
Defining a Restrict Delete RelationshipDefining a Cascade Delete Relationship
Using the Web Compatibility Checker
Analyzing the Web Compatibility Issues TablePreparing a Client Database for the Web
7. Creating Table Data Macros
Uses of Data Macros
The Data Macro Design Facility—An Overview
Redesigning the Macro Window from Previous Versions of AccessTouring the New Logic Designer
Working with Before Events
Before ChangeIncluding CommentsGrouping MacrosUsing If Blocks to Create Conditional ExpressionsRaising Errors in Data Macros to Cancel EventsTesting Your Data MacroDefining Multiple ActionsCollapsing and Expanding ActionsMoving ActionsPreventing Duplicate Records Across Multiple FieldsBefore Delete
Working with After Events
After InsertAfter UpdateAfter Delete
Working with Named Data Macros
Creating Named Data MacrosSaving Named Data MacrosCalling Named Data MacrosRenaming and Deleting Named Data MacrosAnalyzing Errors in the USysApplicationLog TableUsing ParametersUsing Local VariablesWorking with Return Variables
Debugging Data Macros
Understanding Recursion in Data Macros
Sharing Data Macro Logic
8. Importing and Linking Data
A Word About Open Database Connectivity (ODBC)
Creating a Data Source to Link to an ODBC Database
Importing vs. Linking Database Files
Importing Data and Databases
Importing dBASE FilesImporting SQL TablesImporting Access Objects
Importing Spreadsheet Data
Preparing a SpreadsheetImporting a SpreadsheetFixing Errors
Importing Text Files
Preparing a Text FileSetting Up Delimited DataSetting Up Fixed-Width DataImporting a Text FileFixing Errors
Modifying Imported Tables
Linking Files
Security ConsiderationsPerformance ConsiderationsLinking Access TablesLinking dBASE FilesLinking Text and Spreadsheet FilesLinking SQL TablesModifying Linked TablesUnlinking Linked TablesUsing the Linked Table Manager
Collecting Data via Email
Collecting Data via HTML FormsFilling Out the HTML FormHaving Outlook Automatically Process the RepliesCollecting Data Using InfoPath FormsFilling Out the InfoPath FormManually Processing the RepliesManaging and Resending Data Collection Messages from Access
Importing and Linking SharePoint Data
Importing a List from a SharePoint SiteLinking a SharePoint List into Access
Saving Import Procedures
III. Building Queries
9. Creating and Working with Simple Queries
Selecting Data from a Single Table
Specifying FieldsSetting Field PropertiesEntering Selection CriteriaWorking with Dates and Times in CriteriaAND vs. ORBetween, In, and LikeUsing ExpressionsCreating Text ExpressionsDefining Arithmetic ExpressionsUsing the Expression BuilderSpecifying Field NamesSorting Data
Testing Validation Rule Changes
Checking a New Field Validation RuleChecking a New Table Validation Rule
Working in Query Datasheet View
Moving Around and Using Keyboard ShortcutsWorking with SubdatasheetsChanging DataUnderstanding Record IndicatorsAdding a New RecordSelecting and Changing DataReplacing DataCopying and Pasting DataDeleting RowsWorking with HyperlinksActivating a HyperlinkInserting a New HyperlinkEditing an Existing HyperlinkSorting and Searching for DataSorting DataSearching for and Filtering DataUsing FindFiltering by SelectionUsing the Filter WindowUsing Filter by Form
10. Building Complex Queries
Selecting Data from Multiple Tables
Creating Inner JoinsBuilding a Query on a QueryUsing Outer JoinsBuilding a Simple Outer JoinSolving a Complex “Unmatched” Problem
Using a Query Wizard
Summarizing Information with Totals Queries
Totals Within GroupsSelecting Records to Form GroupsSelecting Specific GroupsBuilding Crosstab QueriesCreating a Simple Crosstab QueryPartitioning Data in a Crosstab Query
Using Query Parameters
Customizing Query Properties
Controlling Query OutputWorking with Unique Records and ValuesDefining a SubdatasheetOther Query Properties
Editing and Creating Queries in SQL View
Limitations on Using Select Queries to Update Data
Creating PivotTables and PivotCharts from Queries
Building a Query for a PivotTableDesigning a PivotTableDesigning a PivotChart
Creating Queries for the Web
11. Modifying Data with Action Queries
Updating Groups of Rows
Testing with a Select QueryConverting a Select Query to an Update QueryRunning an Update QueryUpdating Multiple FieldsCreating an Update Query Using Multiple Tables or Queries
Creating a New Table with a Make-Table Query
Creating a Make-Table QueryRunning a Make-Table Query
Inserting Data from Another Table
Creating an Append QueryRunning an Append Query
Deleting Groups of Rows
Testing with a Select QueryUsing a Delete QueryDeleting Inactive Data
Troubleshooting Action Queries
Solving Common Action Query Errors and ProblemsLooking at an Error Example
IV. Creating Forms
12. Using Forms in an Access Application
Uses of Forms
A Tour of Forms
Headers, Detail Sections, and FootersMultiple-Page FormsContinuous FormsSplit FormsSubformsPop-Up FormsModal FormsSpecial ControlsOption Buttons, Check Boxes, Toggle Buttons, and Option GroupsList Boxes and Combo BoxesTab ControlsAttachment ControlsActiveX ObjectsCommand ButtonsNavigational ControlWeb Browser ControlPivotTables and PivotCharts
Understanding Web Form Limitations
Moving Around on Forms and Working with Data
Viewing DataMoving AroundKeyboard Shortcuts
Adding Records and Changing Data
Adding a New RecordChanging and Deleting Data
Searching for and Sorting Data
Performing a Simple SearchUsing the Search BoxPerforming a Quick Sort on a Form FieldAdding a Filter to a Form
Printing Forms
13. Building a Form
Forms and Object-Oriented Programming
Starting from Scratch—A Simple Input Form
Building a New Form with Design ToolsThe Form Design Tools Contextual Ribbon TabsThe Field ListThe Property SheetBuilding a Simple Input Form for the tblCompanies TableMoving and Sizing ControlsThe Font GroupThe Control Formatting GroupSetting Text Box PropertiesSetting Label PropertiesSetting Form PropertiesCustomizing Colors and Checking Your Design Results
Working with Quick Create and the Form Wizard
Creating a Form with the Quick Create CommandsCreating the Basic Products Form with the Form WizardModifying the Products Form
Simplifying Data Input with a Form
Taking Advantage of Combo Boxes and List BoxesUsing Toggle Buttons, Check Boxes, and Option Buttons
Working with Application Part Forms
14. Customizing a Form
Aligning and Sizing Controls in Design View
Sizing Controls to Fit ContentAdjusting the Layout of Controls“Snapping” Controls to the GridLining Up Controls
Enhancing the Look of a Form
Lines and RectanglesColors and Special EffectsFonts
Setting Control Properties for Client Forms
Formatting PropertiesSpecifying a Format for Numbers and CurrencySpecifying a Format for TextSpecifying a Format for Date/TimeSpecifying a Format for Yes/No FieldsAdding a Scroll BarEnabling and Locking ControlsSetting the Tab OrderAdding a Smart TagUnderstanding Other Control Properties for Client Forms
Setting Client Form Properties
Allowing Different ViewsSetting Navigation OptionsDefining a Pop-Up and/or Modal FormControlling Edits, Deletions, Additions, and FilteringDefining Window ControlsSetting the Border StyleUnderstanding Other Client Form Properties
Setting Client Form and Control Defaults
Changing Control DefaultsDefining a Template Form
Working with Web Forms in Layout View
Starting from Scratch—A Simple Input Web Form
Understanding Control Layouts and Control AnchoringLining Up ControlsMoving Controls Within a Control LayoutFormatting a Column of ControlsResizing ControlsRemoving a Control Layout for Client FormsSplitting and Merging CellsInserting Rows and ColumnsUsing Web-Compatible ControlsAdding Gridlines to Web FormsAdding Some Space with Control PaddingCreating a TitleMoving Controls to Different Sections on Web Forms
Setting Control Properties for Web Forms
Setting Web Form Properties
Using Themes
Working with Shared Resources
15. Advanced Form Design
Basing a Form on a Multiple-Table Query
Creating a Many-to-One FormDesigning a Many-to-One QueryDesigning a Many-to-One Form
Creating and Embedding Subforms
Specifying the Subform SourceDesigning the Innermost SubformDesigning the First Level SubformEmbedding a SubformSpecifying the Main Form SourceCreating the Main FormCreating a Subdatasheet Subform
Displaying Values in an Option Group
Using Conditional Formatting in Client Forms
Working with the Tab Control
Creating Multiple-Page Client Forms
Working with Client PivotChart Forms
Building a Client PivotChart FormEmbedding a Linked PivotChart
Working with Navigation Controls
Using Web Browser Controls
V. Working with Reports
16. Using Reports
Uses of Reports
A Tour of Reports
Print Preview—A First LookHeaders, Detail Sections, Footers, and GroupsSubreportsObjects in ReportsReport View—A First Look
Printing Reports
Print Setup
17. Constructing a Report
Starting from Scratch—A Simple Report
Building the Report QueryDesigning the ReportGrouping, Sorting, and Totaling InformationCompleting the Report
Using the Report Command
Using the Report Wizard
Specifying Report Wizard OptionsViewing the Result
Working with Web Reports in Layout View
Modifying a Report Command-Created Web Report in Layout ViewCompleting the Web Report
Building a Web Report in Layout View
Starting with a Blank Web ReportAdding Grouping and SortingWorking with Control LayoutsAdding Totals to RecordsUsing GridlinesAdding the Final Formatting Touches
Understanding Web Report Limitations
18. Advanced Report Design
Building a Query for a Complex Report
Creating the Basic Facility Occupancy By Date Report
Defining the Grouping and Sorting Criteria
Setting Section and Report Properties
Section Properties for ReportsReport Properties
Using Calculated Values
Adding the Print Date and Page NumbersPerforming CalculationsPerforming a Calculation on a Detail LineAdding Values Across a GroupCreating a Grand TotalHiding Redundant Values and Concatenating Text StringsCalculating PercentagesUsing Running SumTaking Advantage of Conditional Formatting
Creating and Embedding a Subreport
Understanding Subreport ChallengesBuilding a Client Report with a SubreportBuilding the Subreport QueryDesigning the SubreportEmbedding a Subreport
Adding a PivotChart to a Client Report
Designing the PivotChart FormEmbedding a PivotChart in a Client Report
VI. Automating an Access Application Using Macros
19. Understanding Event Processing
Access as a Windows Event-Driven Application
Understanding Events in WindowsUsing Access Events to Build an Application
Summary of Form and Report Events
Understanding Event Sequence and Form Editing
20. Automating a Client Application Using Macros
Uses of Macros
The Macro Design Facility—An Overview
Working with the Logic DesignerSaving Your MacroTesting Your Macro
Defining Multiple Actions
Working with Submacros
Understanding Conditional Expressions
Using Embedded Macros
Editing an Embedded MacroCreating an Embedded MacroDeleting an Embedded Macro
Using Temporary Variables
Trapping Errors in Macros
Understanding Macro Actions That Are Not Trusted
Making Your Application Come Alive with Macros
Referencing Form and Report ObjectsRules for Referencing Forms and ReportsRules for Referencing Form and Report PropertiesRules for Referencing Form and Report Controls and Their PropertiesRules for Referencing Subforms and SubreportsOpening a Secondary FormCreating the SeeCityInformation MacroSynchronizing Two Related FormsCreating the SyncWeddingListAndCity MacroValidating Data and Presetting ValuesValidating DataUnderstanding the ValidateCitySetStateAndZip MacroPassing Status Information Between Linked FormsPresetting Values
21. Automating a Web Application Using Macros
Creating Web Macros
Using Macro Objects for Common Functionality
Working with Web Form and Control Events
Passing Parameters to Forms and Reports
Exploring the Invoice Audit Web Form Macros
Using the SetProperty Action with Web Form ControlsCalling Named Data Macros and Using Return VariablesUsing BrowseTo Macro Actions to Browse to Forms and ReportsTrapping Error Messages
Checking SharePoint User Permission Group Levels
Performing Different Actions When Opening a Web Form in a Browser
Avoiding Type Coercion Issues
VII. Working with the Web
22. Using Web Applications in a Browser
Working with SharePoint
Publishing Your Database to an Access Services Site
Assigning a Web Display FormUnderstanding the Publish ProcessAnalyzing Publish Errors
Working with Your Application in a Web Browser
Using FormsUsing ReportsUsing Datasheet FormsWaiting for Server ProcessingUnderstanding Session Management
Exploring the Access Services Shell
Downloading a Web Application Back into AccessSetting Site PermissionsReviewing the Modify Application Page
Working with the Recycle Bin
Extending Your Access Services Application
Using Your Published Web Database in Access
Making Changes to a Published Web ApplicationResolving Synchronization ConflictsWorking OfflineSaving a Web Application as a Local Database
Instantiating an Access Services Template
Using an Installed Web TemplateInstantiating a Custom Template
23. Using Business Connectivity Services
Understanding Web Services
Introducing Business Connectivity Services
Using XML
Exploring XMLWell-Formed XML
Working with BDC Model Definition Files
Generating Entities
Connecting Data Services in Access
A. Installing Your Software
Installing the Office System
Choosing Options When You Have No Previous Version of the Office SystemChoosing Options to Upgrade a Previous Version of the Office System
Converting from a Previous Version of Access
Conversion Issues
Installing the Office 64-Bit Version
Installing the Sample Files
B. About the Authors
Index
VIII. Automating an Access Application Using Visual Basic
24. Understanding Visual Basic Fundamentals
The Visual Basic Development Environment
ModulesModule ObjectsForm and Report ModulesThe Visual Basic Editor WindowWorking with Visual Basic Debugging ToolsSetting BreakpointsUsing the Immediate WindowExecuting Visual Basic Commands in the Immediate WindowUsing BreakpointsWorking with the Watch WindowExamining the Procedure Call Sequence (Call Stack)
Variables and Constants
Data TypesVariable and Constant Scope
Declaring Constants and Variables
Const StatementSyntaxNotesExampleDim StatementSyntaxNotesExamplesEnum StatementSyntaxNotesExampleEvent StatementSyntaxNotesExamplePrivate StatementSyntaxNotesExamplePublic StatementSyntaxNotesExampleReDim StatementSyntaxNotesExampleStatic StatementSyntaxNotesExamplesType StatementSyntaxNotesExample
Collections, Objects, Properties, and Methods
The Access Application ArchitectureThe DAO ArchitectureThe ADO ArchitectureReferencing Collections, Objects, and PropertiesWhen to Use “!” and “.”Assigning an Object Variable—Set StatementSyntaxNotesExamplesObject MethodsWorking with DAO RecordsetsManipulating Complex Data Types Using DAOWorking with ADO RecordsetsOther Uses for Object Methods
Functions and Subroutines
Function StatementSyntaxNotesExampleSub StatementSyntaxNotesExample
Understanding Class Modules
Property GetSyntaxNotesExamplesProperty LetSyntaxNotesExamplesProperty SetSyntaxNotesExamples
Controlling the Flow of Statements
Call StatementSyntaxNotesExamplesDo...Loop StatementSyntaxNotesExampleFor...Next StatementSyntaxNotesExampleFor Each...Next StatementSyntaxNotesExampleGoTo StatementSyntaxNotesExampleIf...Then...Else StatementSyntaxNotesExampleRaiseEvent StatementSyntaxNotesExampleSelect Case StatementSyntaxNotesExampleStop StatementSyntaxNotesWhile...Wend StatementSyntaxNotesExampleWith...End StatementSyntaxExample
Running Macro Actions and Menu Commands
DoCmd ObjectSyntaxNotesExamplesExecuting an Access CommandSyntaxNotesExamplesActions with Visual Basic Equivalents
Trapping Errors
On Error StatementSyntaxNotesExamples
Some Complex Visual Basic Examples
A Procedure to Randomly Load DataA Procedure to Examine All Error Codes
Working with 64-Bit Access Visual Basic for Applications
Using Declare StatementsUsing LongPtr Data TypesUsing PtrSafe AttributesSupporting Older Versions of AccessUnderstanding Pointer Valued Functions and LongPtr Type CoercionUsing LongLong Data TypesWorking with .MDE and .ACCDE files in 64-Bit Environments
25. Automating Your Application with Visual Basic
Why Aren’t We Using Macros?
When to Use MacrosWhen to Use Visual Basic
Assisting Data Entry
Filling In Related DataHandling the NotInList EventFixing an E-Mail HyperlinkProviding a Graphical CalendarWorking with Linked PhotosDeleting and Updating an Image Path
Validating Complex Data
Checking for Possible Duplicate NamesTesting for Related Records When Deleting a RecordVerifying a PrerequisiteMaintaining a Special Unique ValueChecking for Overlapping Data
Controlling Tabbing on a Multiple-Page Form
Automating Data Selection
Working with a Multiple-Selection List BoxProviding a Custom Query By FormSelecting from a Summary ListFiltering One List with Another
Linking to Related Data in Another Form or Report
Linking Forms Using a FilterLinking to a Report Using a FilterSynchronizing Two Forms Using a Class Event
Automating Complex Tasks
Triggering a Data Task from a Related FormLinking to a Related TaskCalculating a Stored Value
Automating Reports
Allowing for Used Mailing LabelsDrawing on a ReportDynamically Filtering a Report When It Opens
Calling Named Data Macros
IX. After Completing Your Application
26. The Finishing Touches
Creating Custom Ribbons with XML
Creating a USysRibbons TableCreating a Test FormBuilding the Ribbon XMLHiding Existing Ribbon ElementsTesting Your XMLCreating TabsAdding Built-In Groups to Tabs
Loading Ribbon XML
SyntaxNotes
Using Ribbon Attributes
Creating VBA CallbacksDynamically Updating Ribbon ElementsLoading Images into Custom ControlsHiding Options on the Backstage ViewAdding Options to the Backstage ViewCreating a Custom Quick Access ToolbarSetting Focus to a Tab
Disabling Layout View
Controlling How Your Application Starts and Runs
Setting Startup Properties for Your DatabaseStarting and Stopping Your ApplicationCreating an AutoKeys Macro
Performing a Final Visual Basic Compile
27. Distributing Your Application
Using Linked Tables in a Desktop Database
Taking Advantage of the Database Splitter WizardCreating Startup Code to Verify and Correct Linked Table Connections
Understanding Runtime Mode
Creating a Database Template
Creating Custom Data Type Parts
Creating an Execute-Only Database
Creating an Application Shortcut
Encrypting Your Database
Packaging and Signing Your Database
28. Article 1 Designing Your Database Application
Application Design Fundamentals
Step 1: Identifying TasksStep 2: Charting Task FlowStep 3: Identifying Data ElementsStep 4: Organizing the DataStep 5: Designing a Prototype and a User InterfaceStep 6: Constructing the ApplicationStep 7: Testing, Reviewing, and Refining
An Application Design Strategy
Analyzing the TasksSelecting the DataOrganizing Tasks
Data Analysis
Choosing the Database SubjectsMapping Subjects to Your Database
Database Design Concepts
Waste Is the ProblemNormalization Is the SolutionField UniquenessRule 1: Each field in a table should represent a unique type of informationPrimary KeysRule 2: Each table must have a unique identifier, or primary key, that is made up of one or more fields in the tableFunctional DependenceRule 3: For each unique primary key value, the values in the data columns must be relevant to, and must completely describe, the subject of the tableField IndependenceRule 4: You must be able to make a change to the data in any field (other than to a field in the primary key) without affecting the data in any other fieldEfficient Relationships Are the ResultForeign KeysOne-to-Many and One-to-One RelationshipsCreating Table Links
When to Break the Rules
Improving Performance of Critical TasksCapturing Point-in-Time DataCreating Report Snapshot Data
29. Article 2 Understanding SQL
SQL SELECT Queries
Aggregate Functions: AVG, CHECKSUM_AGG, COUNT, MAX, MIN, STDEV, STDEVP, SUM, VAR, and VARPBETWEEN PredicateSyntaxNotesExampleColumn-NameSyntaxNotesExamplesComparison PredicateSyntaxNotesExamplesEXISTS PredicateSyntaxNotesExampleExpressionSyntaxNotesExamplesFROM ClauseSyntaxNotesExampleGROUP BY ClauseSyntaxNotesExampleHAVING ClauseSyntaxNotesExampleIN ClauseSyntaxNotesExampleIN PredicateSyntaxNotesExamplesLIKE PredicateSyntaxNotesExamplesNULL PredicateSyntaxExampleORDER BY ClauseSyntaxNotesExamplesPARAMETERS DeclarationSyntaxNotesExampleQuantified PredicateSyntaxNotesExamplesSearch ConditionSyntaxNotesExampleSELECT StatementSyntaxNotesExamplesSubquerySyntaxNotesExamplesTRANSFORM StatementSyntaxNotesExampleUNION Query OperatorSyntaxNotesExampleWHERE ClauseSyntaxNotesExamples
SQL Action Queries
DELETE StatementSyntaxNotesExamplesINSERT Statement (Append Query)SyntaxNotesExamplesSELECT . . . INTO Statement (Make-Table Query)SyntaxNotesExampleUPDATE StatementSyntaxNotesExample
30. Article 3 Exporting Data
Exporting to Another Access Database
Exporting to a Spreadsheet or to a dBASE File
Exporting to a Text File
Exporting to a Mail Merge Document in Word
Exporting to an ODBC Database
Exporting Data to SharePoint
31. Article 4 Function Reference
32. Article 5 Color Names and Codes
33. Article 6 Macro Actions
About the Authors
Copyright

Content preview from Microsoft® Access® 2010 Inside Out

Summarizing Information with Totals Queries

Sometimes you aren’t interested in each and every row in your table—you’d rather see calculations across groups of data. For example, you might want the total product purchase amount for all companies in a particular state, or you might want to know the average of all sales for each month in the last year. To get these answers, you need a totals query.

Totals Within Groups

If you’re the housing facilities manager, you might be interested in producing sales and usage numbers by facility or by date range. For this series of exercises, open HousingDataCopy.accdb and start a new query with tblFacilities and tblReservations in the query design grid. Include in the Field row the FacilityName field from tblFacilities ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780735649132Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft® Access® 2010 Inside Out

by Jeff Conrad and John Viescas

Summarizing Information with Totals Queries

Totals Within Groups

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

More than 5,000 organizations count on O’Reilly

Julian F.

Addison B.

Amir M.

Mark W.

You might also like

Microsoft® Access® 2010 In Depth

Access® 2010 Bible

Microsoft® Access® 2013 Step by Step

Microsoft® Access® 2010 24-Hour Trainer

Publisher Resources