Docstoc

Symbols A

Document Sample
Symbols A Powered By Docstoc
					                                                           INDEX



        Symbols               action queries
                                 append queries, 309
                                                           aligning controls
                                                              control edges, 35-36, 134
+ (addition) operator, 234       delete queries, 306-307      spacing between
* (asterisk), 213                make-table queries,           controls, 36
                                  307-308                     to grid, 35, 134
/ (division) operator, 234
                                 overview, 304             All Pages value (Page
= (equality) operator, 233       update queries, 304-306    Header property), 118
^ (exponential)               ActiveX controls             ALL predicate (SELECT
 operator, 234                  calendars, 71               statement), 344
> (greater than)                overview, 67-68
                                                           Allow Additions property
 operator, 233                  scrollbars, 69-71
                                                            (forms), 27
>= (greater than or equal)      spin buttons, 68-69
                                                           Allow Deletions property
 operator, 233                ADC (Access Data
                                                            (forms), 27
\ (integer division)           Collection)
                                managing replies, 75       Allow Edits property
 operator, 234
                                replying to email, 74       (forms), 27
< (less than) operator, 233
                                sending email, 72-74       And filter, 206
<= (less than or equal)
                              Add Existing Fields          And operator, 236-237,
 operator, 233
                               command (Design              298-299
* (multiplication)             menu), 120                  append queries,
  operator, 234
                              Add New Record                309, 353-354
<> (not equal)                 command button, 101         Apply Form Filter
 operator, 233
                              addition operator (+), 234    command button, 101
- (unary) operator, 234
                              ages, calculating, 249-250   arguments (of
                              aggregate functions           functions), 242
             A                  aggregate operations,      arithmetic
                                  287-288                   operators, 234-235
Abs function, 251
                                combining with totals,     Arrange menu commands
Access Data Collection.           295-296                    Control Margins, 38
 See ADC                        table of, 294-295            Group, 38, 135
Access reports.                                              Size To Fit, 34, 132
 See reports                                                 Size To Grid, 34, 132
362   Index    Arrange menu commands


          Size To Narrowest, 34
          Size To Shortest, 34
                                                   B                   overview, 141-142
                                                                       page breaks, 149-150
          Size To Tallest, 34          backgrounds                     Shadow effects, 154-155
          Size To Widest, 34             form backgrounds              special effects, 154
          Tab Order, 40                     color, 23-24               text formatting
          Ungroup, 39, 136                  pictures, 24-25               tips and
       Asc function, 243                 report backgrounds, 126            guidelines, 151
                                            color, 127                    with property
       Ascending sorts, 200
                                            pictures, 127-128               sheets, 150
       Associate Label dialog                                             with Ribbon, 150
        box, 42                        base controls, 35
                                       Beep action (macros), 171     buttons
       associating macros                                              command buttons
          with command                 Between...And
                                                                          associating macros
           buttons, 172                 operator, 235
                                                                            with, 172
          with report events, 173      Bound Column property              launching reports
       asterisk (*), 213                (lists), 67                         with, 167-169
       Atn function, 251               bound controls                  form command
                                         adding to forms, 17-18          buttons, 99-102
       audience of business
                                         adding to reports, 120        option buttons
        reports, 144
                                         converting unbound               advantages, 59
       Auto Dialer command                controls to, 39                 creating manually, 61
        button, 101                      overview, 16                     creating with
       AutoCalc data fields              properties, 16-17, 119             Option Group
         calculation type              Bring To Front                       Wizard, 59-60
           commands, 320                command, 39                    spin buttons, 68-69
         inserting into
           PivotTables, 318            business forms. See forms
         summary commands,             business queries, 216-217                  C
           318-319                     business reports              calculated columns
       AutoFilter feature                audience of, 144               adding to SELECT
        (PivotTables), 332               colors                          statements, 349
       AutoFormat Wizard,                   custom colors, 152          calculating discounted
        19-20, 125-126                      design tips, 152-153         product totals, 241
                                         contents of, 142-143           calculating inventory
       Average command                   design guidelines,              values, 240
        (AutoCalc), 319                    145-147                      overview, 239-240
       Avg function, 288, 294            goals of, 142, 144          calculated controls
                                         images, 153-154                overview, 17, 119
                                         organizing with lines and      text boxes, 44-46,
                                           rectangles, 148-149            163-165
                                                                                        Companies table   363


calculated fields                    calculated fields                   business reports
   creating in                          creating in                         custom colors, 152
    PivotTables, 321                     PivotTables, 321                   design tips, 152-153
   creating totals queries              creating totals queries          customizing, 89-90, 152
    with, 293                            with, 293                       hue, 90
calculating                       calendars, 71                          luminance, 90
   ages, 249-250                                                         report backgrounds, 127
                                  captions
   customer discount rates                                               saturation, 90
                                    labels, 42
      simple discount rates,        sizing report controls            Column Count property
        300-301                       to, 132                          (lists), 67
      sliding-scale discount                                          Column Heads
                                  CDate function, 247
        rates, 301-302                                                 property (lists), 67
   discounted product             changing
                                    form record                       Column Widths property
     totals, 241
                                      sources, 15-16                   (lists), 67
   inventory values, 240
   length of                        form size, 18                     columns
     employment, 250-251            label captions, 42                  calculated columns
   mortgage payments,               PivotTable field                       adding to SELECT
     255-256                          order, 338                             statements, 349
   units left in stock,             record sources, 117-118                calculating discounted
     295-296                        relationships, 271                       product totals, 241
                                  character spacing, 86                    calculating inventory
calculations
                                                                             values, 240
   adding to reports              check boxes, binding to
                                                                           overview, 239-240
      overview, 161-162            Yes/No fields, 57
                                                                        definition of, 314
      text boxes, inserting,      child tables, 264                     starting report sections
        162-163                   Chr function, 243                       at top of, 174-175
      text boxes as
        calculated controls,      classes, entity, 262                Combo Box Wizard, 63
        163-165                   Clear All Sorts                     Command Button
      Totals list, 162             command, 200                        Wizard, 100-102, 172
   calculated columns             Close action                        command buttons,
      adding to SELECT             (macros), 171                       99-102
        statements, 349           Close Form command                    associating macros
      calculating discounted       button, 101                            with, 172
        product totals, 241                                             launching reports
                                  Color dialog box, 89
      calculating inventory                                               with, 167-169
        values, 240               colors
                                    adding to forms,                  commands. See specific
      overview, 239-240
                                      23-24, 88-89                     commands
   calculated controls
      overview, 17, 119                custom colors, 89-90           Companies table, 262
      text boxes, 44-46,               design tips, 91
        163-165


            How can we make this index more useful? Email us at indexes@quepublishing.com
364   Index    comparison operators


       comparison operators,          calculated controls, 119            step-by-step
        233-234                           overview, 17                      process, 33
       Conditional command                text boxes, 44-46,          spacing between, 36, 135
        (Design menu), 32, 131              163-165                   stacked control
                                      changing control                  layouts, 37-38
       conditional formatting,
                                        type, 40                      tab controls
        applying to controls,
                                      check boxes, binding to             adding to
        31-33, 130-131
                                        Yes/No fields, 57                   forms, 84-85
       Conditional Formatting         formatting                          layouts, 37-38
        dialog box, 32, 131               conditional                     tab order, 40
       constants, 232                       formatting,               text boxes
       Contacts table, 262-263              31-33, 130-131                adding to forms,
                                          in reports, 130                   43-44
       Control Margins
                                      grouping, 38-39, 135                as calculated
        command (Arrange
                                      labels                                controls, 44-46
        menu), 38
                                          adding to forms,                mortgage calculator
       Control Source property              41-42                           example, 46-47
        (lists), 66                       adding to reports,              overview, 43
       controlled tables, 264               120-121                   toggle buttons, binding
       controls                           captions, 42                  to Yes/No fields, 58
         ActiveX controls                 creating keyboard           unbound controls
            calendars, 71                   shortcuts with, 42-43         converting to bound
            overview, 67-68               overview, 41                      controls, 39
            scrollbars, 69-71         logos, 121-122                      overview, 17, 119
            spin buttons, 68-69       margins, 38                     ungrouping, 136
         adding to forms, 30          moving, 34-36, 133-134          Z-order, 39, 136
         adding to reports, 129       organizing                    converting unbound
         aligning                         lines/rectangles, 82-83    controls to bound
            control edges,                option groups, 83-84       controls, 39
              35-36, 134                  overview, 81
                                                                    copying
            to grid, 35, 134              tab controls, 84-85
                                                                      legacy reports, 145
         base controls, 35            overlapping controls,
                                                                      records, 226
         bound controls                 39, 136
                                      selecting, 30-31, 129-130     Cos function, 251
            adding to
              forms, 17-18            sizing, 131-133               Count command
            adding to reports, 120        to caption, 132            (AutoCalc), 319
            converting unbound            to control’s              Count function, 294
              controls to, 39               content, 34             Count operation, 288
            overview, 16                  to grid, 33-34, 132
                                          making controls same      Create menu commands
            properties, 16-17, 119
                                            size, 34, 133             Form, 9
                                                                      Form Design, 15
                                                                                   data entry with forms   365


  Labels, 185                          Imp, 237                      data entry with forms
  Multiple Items, 11                   Not, 237                        ActiveX controls
  Report, 113-114                      Or, 236-237                         calendars, 71
  Report Design, 117                   Xor, 237                            overview, 67-68
  Report Wizard, 114                operator precedence,                   scrollbars, 69-71
  Split Form, 11                      238-239                              spin buttons, 68-69
criteria expressions                overview, 231-232                  case study: selecting
 (queries)                          selecting criteria for               shipper (Northwind
   arithmetic operators,              queries, 213                       2007 database), 62
     234-235                        structure, 231                     data validation
   Between...And                 crosstab queries, 220-221                 input masks, 52-55
     operator, 235                                                         option buttons, 59-61
                                 Crosstab Query Wizard,
   calculated columns                                                      overview, 50
                                  220-221
       calculating discounted                                              rules, 51-52
                                 custom colors, 89-90, 152                 text prompts, 50
         product totals, 241
       calculating inventory     custom date fields, adding                Yes/No fields, 56-58
         values, 240              to PivotTables, 328                  dynasets, 224-225
       overview, 239-240         custom input mask                     email
   comparison operators,          expressions, 54-55                       managing Access data
     233-234                                                                 collection replies, 75
                                 custom totals, 295
   Expression Builder, 256                                                 managing ADC
                                 customers                                   replies, 75
   functions                       customer orders,
       arguments, 242                                                      overview, 72
                                     analyzing by product                  replying to ADC
       date/time functions,          category, 324-326
         246-250                                                             email messages, 74
                                   discount rates,                         sending ADC email
       financial functions,          calculating
         253-255                                                             messages, 72-74
                                      simple discount                  lists
       math functions,                  rates, 300-301
         251-253                                                           Combo Box
                                      sliding-scale discount                 Wizard, 63
       overview, 233, 241               rates, 301-302
       syntax, 242                                                         getting list values
                                   finding customers                         from current
       text functions,               without matching
         243-246                                                             table, 64-65
                                     orders, 276                           getting list values
   identifiers, 232
   In operator, 235                                                          from table or query
   Is Null operator, 235                        D                            fields, 64
   Like operator, 235                                                      List Box Wizard, 63
                                 data details (PivotTables),               multi-column
   literals, 232
                                  hiding/showing, 318                        lists, 65-66
   logical operators, 236
       And, 236-237              Data Entry property                       overview, 62-63
       Eqv, 237                   (forms), 27



           How can we make this index more useful? Email us at indexes@quepublishing.com
366   Index     data entry with forms


            properties, 66-67             option buttons                 referential integrity, 266
            specifying custom list           advantages, 59              relational models
              values, 64                     creating manually, 61          foreign keys, 263-264
          overview, 49                       creating with                  many-to-many model,
       data pivoting (in                       Option Group                   265-266
        PivotTables)                           Wizard, 59-60                one-to-many
         changing field order, 338        overview, 50                        model, 264
         moving fields, 337               rules, 51-52                      one-to-one
         overview, 336-337                text prompts, 50                    model, 265
                                          Yes/No fields, 56-57              separation of data,
       data separation
                                             check boxes, 57                  262-263
        (relational database
                                             toggle buttons, 58          relationships
        design), 262-263
                                        databases                           editing, 271
       data types, 304                                                      establishing, 267-270
                                          nonrelational
       data validation                      databases, disadvantages        many-to-many,
         case study: selecting              of, 259-261                       265-266
           shipper (Northwind             querying. See queries             one-to-many, 264
           2007 database), 62             records                           one-to-one, 265
         input masks                         adding to                   tables. See tables
             custom input mask                 dynasets, 225           datasheet view
               expressions, 54-55            adding with append          formatting
             Input Mask                        queries, 309                 column sizes, 227
               Wizard, 53                    copying, 226                   row heights, 228
             overview, 52-53                 deleting, 227               navigating fields in, 224
         lists                               filtering before            overview, 223-224
             Combo Box                         calculating             Date & Time command
               Wizard, 63                      totals, 290              (Design menu), 123
             getting list values             finding records
               from current                                            Date and Time dialog
                                               without matching
               table, 64-65                                             box, 123
                                               records, 276-277
             getting list values             form record sources,      date filters, 205
               from table or query             changing, 15-16         Date function, 247
               fields, 64                    grouping with             date/time functions,
             List Box Wizard, 63               SELECT                   246-250
             multi-column                      statements, 350
               lists, 65-66                                            date/time literals, 232
                                             navigating, 225-226
             overview, 62-63                 removing with delete      DateAdd function, 248
             properties, 66-67                 queries, 306-307        DateDiff function, 248
             specifying custom list          selecting, 226            DatePart function, 248
               values, 64                    sorting, 199-201
                                             widowed records,
                                               preventing, 175
                                                                                            dialog boxes   367


dates                             Decrease Vertical Spacing              data content, 78
  adding to reports, 123           command, 36                           field order, 80
  business reports, 146           Default Value property                 foolproof forms, 79
  filters, 205                     (text boxes), 44, 163                 images, 91-92
  functions, 246-250                                                     keyboard shortcuts, 80
                                  Default View property
  literals, 232                                                          mimicking paper
                                   (queries), 228
  two-dimensional                                                          forms, 79
    PivotTables                   delete queries, 306-307,               organizing controls
      built-in date features,      352-353                                   lines/rectangles, 82-83
       326-328                    Delete Record command                      option groups, 83-84
      custom date                  button, 101                               overview, 81
       fields, 328                DELETE statement                           tab controls, 84-85
      employee orders,             (SQL), 352-353                        overview, 77
       viewing by custom                                                 readability, 80
                                  DeleteObject action
       quarter, 330-331                                                  screen resolution, 80
                                   (macros), 171
      product orders,                                                    simplicity, 79-81
       viewing by day of          deleting                               speed, 79
       week, 328-329                joins, 271                           text formatting
                                    PivotTable fields, 322                   character spacing, 86
DateSerial function, 248            records, 227, 306-307                    overview, 86-87
DateValue function, 248           dependent tables, 264                      with property
Day function, 248                 Descending sorts, 200                        sheets, 87
DDB function, 254                 Design grid, 212, 288-289                  with Ribbon, 87
Decimal Places property                                                      shadow effect, 92-93
                                  Design menu commands                       tips and guidelines,
 (text boxes), 44, 163              Add Existing Fields, 120                   87-88
decision-making queries             Conditional, 32, 131                     type sizes, 86
  case study: calculating           Date & Time, 123                         type styles, 86
    customer discount               Group & Sort, 157                        typefaces, 86
    rates, 300-302                  Insert Page Number, 122              users, 78-79
  IIf function                      Logo, 23, 122
      combining with And            Property Sheet, 24                detail section
       and Or, 298-299              Title, 23                          (reports), 118
      determining whether         Design view (forms), 15             dialog boxes. See also
       stock needs                                                     wizards
       reordered, 298             designing business                     Associate Label, 42
      syntax, 297                  reports, 145-147                      Color, 89
  overview, 296-297               designing forms                        Conditional
  Switch function, 299              colors                                Formatting, 32, 131
decorative typefaces, 86               adjusting, 88-89                  Date and Time, 123
                                       custom colors, 89-90              Edit Relationships,
Decrease Horizontal                    design tips, 91                    269-271
 Spacing command, 36                data collection goals, 78


            How can we make this index more useful? Email us at indexes@quepublishing.com
368   Index    dialog boxes


          Export - RTF File, 138      DISTINCTROW                  email
          Field Properties,            predicate (SELECT             ADC email messages
           214-215                     statement), 344                  managing replies, 75
          Grouping Intervals, 115     division (/) operator, 234        replying to, 74
          Insert ActiveX                                                sending, 72-74
                                      drilling down to order
           Control, 68-71                                            overview, 72
                                       details, 282-286
          Insert Picture, 91,                                        publishing reports
           122, 153                   Duplicate Record                to, 138
          Join Properties, 270, 276    command button, 101
                                                                   employee orders,
          Microsoft Office            dynasets, 210, 216            viewing by custom
           Genuine Advantage, 4         adding records, 225         quarter, 330-331
          modal dialog boxes, 104       copying records, 226
                                                                   Enter Key Behavior
          New Label, 187                datasheet view, 223-224
                                                                    property (text
          New Label Size, 187           deleting records, 227
                                                                    boxes), 44
          Page Numbers, 122             entering data, 224-225
          Page Setup, 137,              navigating, 224-226        entity classes, 262
           178-179                      returning with sub-        equality (=) operator, 233
          Publish as PDF or              queries, 356-358          Eqv operator, 237
           XPS, 139                        All predicate, 358
                                                                   events, associating
          Query Parameters, 304            In predicate, 357-358
                                                                    macros with, 173
          Send Object As, 138           selecting records, 226
          Show Table, 211, 269                                     excluding fields from
                                                                    query results, 213-214
          Summary Options, 115
          Tab Order, 41
                                                  E                Exp function, 251
       discount rates, calculating    Edit Form Filter             exponential (^)
          simple discount rates,       command button, 101          operator, 234
            300-301                   Edit Relationships dialog    Export - RTF File dialog
          sliding-scale discount       box, 269-271                 box, 138
            rates, 301-302            editing                      exporting reports
       discounted product totals,       form record                  to PDF or XPS, 139-140
        calculating, 241                  sources, 15-16             to Word, 138-139
       displaying                       form size, 18              Expression Builder, 256
          data details                  label captions, 42
                                                                   Expression Is condition
           (PivotTables), 314-318       PivotTable field
                                                                    (forms), 32
          sum of data field values        order, 338
           (PivotTables), 316-317       record sources, 117-118    Expression operation, 288
       DISTINCT predicate               relationships, 271         expressions
        (SELECT                                                      arithmetic operators,
        statement), 344                                                234-235
                                                                     Between...And
                                                                       operator, 235
                                                                               Filter property (queries)   369


calculated columns
    calculating discounted
                                              F                       including in queries,
                                                                        212-213
      product totals, 241      Field Has Focus                        moving in
    calculating inventory       condition (forms), 32                   PivotTables, 337
      values, 240              Field Properties dialog                order of, 80, 146
    overview, 239-240           box, 214-215                          overview, 16
comparison operators,                                                 properties, 16-17, 119,
                               Field Value Is condition
  233-234                                                               214-215
                                (forms), 32
custom input mask                                                     removing from
  expressions, 54-55           fields                                   PivotTables, 322
Expression Builder, 256           adding to forms, 17-18              row fields, 314
filter criteria, 207-208          adding to reports, 120              sorting records on
functions                         AutoCalc data fields                   multiple fields,
    arguments, 242                    calculation type                     200-201
    date/time functions,                commands, 320                    single field, 200
      246-250                         inserting into                  total queries
    financial functions,                PivotTables, 318                 creating on multiple
      253-255                         summary commands,                    fields, 289-290
    math functions,                     318-319                          creating on single
      251-253                     calculated fields                        field, 289
    overview, 233, 241                creating in                     Yes/No fields, 56-57
    syntax, 242                         PivotTables, 321                 check boxes, 57
    text functions,                   creating totals queries            toggle buttons, 58
      243-246                           with, 293
                                                                   files, publishing
identifiers, 232                  changing field order
                                                                     reports to
In operator, 235                    (PivotTables), 338
                                                                       PDF format,
Is Null operator, 235             column fields, 314
                                                                        139-140
Like operator, 235                criteria, defining with
                                                                       XPS format,
literals, 232                       subqueries, 356
                                                                        139-140
logical operators, 236            defining with
                                    subqueries, 355                Filter By Selection
    And, 236-237
                                  definition of, 313-314            feature, 203
    Eqv, 237
    Imp, 237                      displaying, 314-316              Filter Excluding Selection
    Not, 237                      excluding from query              feature, 203
    Or, 236-237                     results, 213-214               filter fields
    Xor, 237                      filter fields                        adding, 335-336
operator precedence,                  adding, 335-336                  definition of, 314
  238-239                             definition of, 314           Filter property
overview, 231-232                 grouping in                       (queries), 229
selecting criteria for              PivotTables, 335
  queries, 213
structure, 231


         How can we make this index more useful? Email us at indexes@quepublishing.com
370   Index     filtering


       filtering                         Find Duplicates                formatting
           PivotTables                    queries, 222                     business reports
              adding filter fields,      Find Duplicates Query                colors, 152-153
                335-336                   Wizard, 222                         images, 153-154
              AutoFilter                                                      with property
                                         Find Next command
                feature, 332                                                    sheets, 150
                                          button, 100
              displaying only top                                             with Ribbon, 150
                or bottom items,         Find Record command                  Shadow effects,
                333-335                   button, 100                           154-155
              grouping field             Find Unmatched queries,              special effects, 154
                items, 335                222-223                             text formatting, 150
           records before                Find Unmatched Query                 tips and
             calculating totals, 290      Wizard, 222-223                       guidelines, 151
           table data                                                      controls, 31-32, 130
                                         finding records without
              And filter, 206                                              datasheets
                                           matching records
              applying filters,                                               column sizes, 227
                                            customers without
                209-210                                                       row heights, 228
                                             matching orders, 276
              compared to                                                  PivotTables, 339
                                            products without
                queries, 210                                               report backgrounds, 126
                                             assigned categories, 277
              creating filters,                                               color, 127
                208-210                  first names, extracting              pictures, 127-128
              date filters, 205            from database, 245              text
              Filter By Selection        First operation, 288                 character spacing, 86
                feature, 203             Fix function, 251                    overview, 86-87
              filter criteria, 207-208                                        with proper case, 246
                                         foolproof forms, 79
              Filter Excluding                                                with property
                Selection                footers                                sheets, 87
                feature, 203                form footers, 20-22               shadow effect, 92-93
              filtering in place, 204       page footers, 118                 with Ribbon, 87
              number filters, 205           report footers, 119               tips and guidelines,
              Or filter, 206             foreign keys, 263-264                  87-88
              overview, 201-202          Form command (Create                 type sizes, 86
              text filters, 205           menu), 9                            type styles, 86
       filters                                                                typefaces, 86
                                         Form Design command
           applying, 209-210              (Create menu), 15             forms
           compared to queries, 210                                        ActiveX controls
                                         Form Wizard, 12-14,
           creating, 208-210                                                 calendars, 71
                                          96-98
       financial functions,                                                  overview, 67-68
                                         Format function, 243                scrollbars, 69-71
         253-255
                                         Format property (text               spin buttons, 68-69
                                          boxes), 44, 163                  advantages, 8-9
                                                                                           forms   371


AutoFormat                          tabular control                  lists
 Wizard, 19-20                        layouts, 37-38                     Combo Box
backgrounds                         text boxes, 43-46                      Wizard, 63
   color, 23-24                     toggle buttons, 58                   getting list values
   pictures, 24-25                  unbound controls,                      from current table,
colors                                17, 39                               64-65
   adjusting, 88-89                 Z-order, 39                          getting list values
   custom colors, 89-90          data validation                           from table or query
   design tips, 91                  case study: selecting                  fields, 64
command buttons,                      shipper (Northwind                 List Box Wizard, 63
 99-102                               2007 database), 62                 multi-column
controls                            input masks, 52-55                     lists, 65-66
   aligning control                 option buttons, 59-61                overview, 62-63
     edges, 35-36                   overview, 50                         properties, 66-67
   aligning to grid, 35             rules, 51-52                         specifying custom list
   base controls, 35                text prompts, 50                       values, 64
   bound controls, 16-18            Yes/No fields, 56-58             logos, 22-23
   calculated controls, 17       Design view, 15                     main forms, 96
   changing control              designing                           modal forms, 104
     type, 40                       data collection                  multiple items
   check boxes, 57                    goals, 78                        forms, 11-12
   conditional                      data content, 78                 navigating, 14
     formatting, 31-33              field order, 80                  organization
   formatting, 31-32                foolproof forms, 79                  field order, 80
   grouping, 38-39                  keyboard shortcuts, 80               lines/rectangles, 82-83
   inserting, 30                    mimicking paper                      option groups, 83-84
   labels, 41-43                      forms, 79                          overview, 81
   margins, 38                      organizing                           tab controls, 84-85
   mortgage calculator                controls, 81                   overview, 7
     example, 46-47                 overview, 77                     parameter
   moving, 34-36                    readability, 80                    queries, 105-107
   organizing, 81                   screen resolution, 80            PivotChart
   overlapping, 39                  simplicity, 79-81                  forms, 108-110
   selecting, 30-31                 speed, 79                        pop-up forms, 103-104
   sizing, 33-34                    users, 78-79                     properties, 20
   spacing between               email, 72-75                        protecting, 26-27
     controls, 36                Form Wizard, 12-14                  record sources, 15-16
   stacked control               headers/footers, 20-22              resizing, 18
     layouts, 37-38              images, 91-92                       split forms, 11
   tab order, 40                 Layout view, 25-26                  standard forms, 9-11
                                 layouts, 9                          startup forms, 107-108



        How can we make this index more useful? Email us at indexes@quepublishing.com
372   Index    forms


          subforms                                                 grouping
              creating with Form
                                                   G                 controls, 38-39, 135
                Wizard, 96-98         generating random              levels, 115
              creating with Subform    numbers                       records, 350
                Wizard, 98-99           random N-digit               reports
              overview, 95-96            numbers, 253                   options, 158-160
          switchboard forms,            random numbers                  overview, 157
            102-103                      between two values,            with expressions, 161
          text formatting                252-253                     totals, 291-292
              character spacing, 86                                Grouping Intervals dialog
                                      Go to First Record
              overview, 86-87                                       box, 115
                                       command button, 100
              with property
                sheets, 87            Go to Next Record
              with Ribbon, 87          command button, 101                     H
              shadow effect, 92-93    Go to Previous Record
                                                                   headers
              tips and guidelines,     command button, 101
                                                                     form headers, 20-22
                87-88                 goals of business reports,     page headers, 118
              type sizes, 86           142-144                       report headers, 118
              type styles, 86         greater than
              typefaces, 86                                        hiding data details
                                       operator (>), 233            (PivotTables), 318
          titles, 23
          viewing, 18-19              greater than or equal        Hour function, 248
                                       operator (>=), 233
       FROM clause (SELECT                                         hue, 90
        statement), 344               grids
                                         aligning controls to,
       functions. See also specific        35, 134                              I
        functions                        sizing controls to,
         aggregate functions                                       identifiers, 231-232
                                           33-34, 132
            combining with totals,                                 IIf function
              295-296                 Group, Sort, and Total          combining with And and
            table of, 294-295          pane                             Or, 298-299
         arguments, 242                 grouping reports,             determining whether
         date/time functions,            159-160                        stock needs
           246-250                      sorting reports, 158            reordered, 298
         financial functions,           Totals list, 162              syntax, 297
           253-255                    Group & Sort command         images
         math functions, 251-253       (Design menu), 157            adding to business
         overview, 233, 241           Group By operation, 288         reports, 153-154
         syntax, 242                  Group command                  adding to forms, 91-92
         text functions, 243-246       (Arrange menu), 38, 135       setting as form
       FV function, 242, 254                                          backgrounds, 24-25
                                                                     setting as report
                                                                      backgrounds, 127-128
                                                                                           Len function   373


Imp operator, 237                IPmt function, 254                                 L
In operator, 235                 IRR function, 254
                                 Is Null operator, 235               Label Wizard
Increase Horizontal
                                                                       custom labels, 187
 Spacing command, 36             IsDate function, 248                  mailing labels, 185-186
Increase Vertical Spacing        items, 313                          labels
 command, 36
                                                                        adding to forms, 41-42
inner joins, 268, 346                           J                       adding to reports,
Input Mask Wizard, 53                                                    120-121
                                 Join Properties dialog                 captions, 42
input masks
                                  box, 270, 276                         creating keyboard
   custom input mask
    expressions, 54-55           joins                                   shortcuts with, 42-43
   Input Mask Wizard, 53            creating, 269-270                   mailing labels
   overview, 52-53                  deleting, 271                          creating with Label
                                    inner joins, 268, 346                   Wizard, 185-186
Insert ActiveX Control
                                    join lines, 267                        custom labels, 187
 dialog box, 68, 70-71
                                    outer joins                            overview, 184-185
INSERT INTO                            applications, 276
 statement (SQL),                                                    Labels command (Create
                                       creating, 275, 346-347         menu), 185
 353-354                               finding customers
Insert Page Number                                                   last names, extracting
                                         without matching
 command (Design                                                       from database, 245
                                         orders, 276
 menu), 122                            finding products              Last operation, 288
Insert Picture dialog box,               without assigned            launching reports
 91, 122, 153                            categories, 277                with command
InStr function, 243                 self-joins, 278, 347-348             buttons, 167-169
                                    temporary joins                     with macros, 169-173
InStrRev function, 243                with Query Design                 overview, 167
Int function, 251                     window, 274                    Layout view, 25-26,
integer division (\)                theta joins, 279-280,             136-137
  operator, 234                       348-349
                                                                     layouts
integrity, referential, 266                                             controls, 37-38
INTO predicate                                  K                       forms, 9
  INSERT statement,                                                     PivotTables, 314
                                 Keep Together property
   353-354                                                           LCase function, 244
                                  (reports), 175
  SELECT statement, 353                                              Left function, 244
                                 keyboard shortcuts
inventory values,                                                    left-outer joins, 268, 275
                                   creating with
  calculating, 240
                                     labels, 42-43                   legacy reports,
invoice reports, 165-167           in form design, 80                  copying, 145
Invoices table, 265              keys, foreign, 263-264              Len function, 244


           How can we make this index more useful? Email us at indexes@quepublishing.com
374   Index     length of employment


       length of employment,            Logo command (Design        make-table queries,
         calculating, 250-251            menu), 23, 122              307-308, 353
       less than                        logos                       many-to-many relational
         operator (<), 233                 forms, 22-23              model, 265-266
       less than or equal                  reports, 121-122         margins, 38
         operator (<=), 233             LTrim function, 244         math functions, 251-253
       Like operator, 235               luminance, 90               Max command
       Limit To List property                                        (AutoCalc), 319
        (lists), 67                                 M               Max function, 288, 294
       lines                                                        Maximize action
          business reports, 148-149     macros
                                                                     (macros), 171
          forms, 82-83                    actions, 171-172
                                          associating with          Microsoft Office Genuine
       List Box Wizard, 63                                           Advantage dialog box, 4
                                            command buttons, 172
       List Rows property                 associating with report   Mid function, 244
        (lists), 67                         events, 173             Min function, 288,
       lists                              creating, 169-170          294, 319
          Combo Box Wizard, 63            definition of, 169
                                                                    Min Max Buttons
          getting list values from        launching reports
                                                                     property (forms), 26
            current table, 64-65            with, 169-173
          getting list values from        running, 170              Minimize action
            table or query fields, 64                                (macros), 171
                                        Mail Merge
          List Box Wizard, 63            queries, 217-219           Minute function, 248
          multi-column lists, 65-66      reports, 187-189           MIRR function, 254
          overview, 62-63                running, 219               Mod operator, 234
          properties, 66-67
                                        Mail Report action,         modal dialog boxes, 104
          specifying custom list
                                         101, 168
            values, 64                                              modal forms, 104
          Totals list, 162              mailing labels
                                                                    Modal property
                                          creating with Label
       literals, 54-55, 232                                          (forms), 104
                                           Wizard, 185-186
       loading Northwind 2007             custom labels, 187        modifiers (input
         database, 3-4                    overview, 184-185          masks), 54-55
       Log function, 251                main forms, 96              monospaced fonts, 86
       logical operators                main reports, 190           Month function, 248
          And, 236-237                                              MonthName
                                        Make Horizontal Spacing
          Eqv, 237                                                   function, 248
                                         Equal command, 36
          Imp, 237
                                        Make Vertical Spacing       mortgage calculator,
          Not, 237
                                         Equal command, 36           46-47, 255-256
          Or, 236-237
          Xor, 237
                                                                               Northwind 2007 database   375


Moveable property
 (forms), 26
                                 Multiple Items command
                                  (Create menu), 11
                                                                                   N
MoveSize action                  multiple-items                      Name property (text
 (macros), 171                    forms, 11-12                        boxes), 44, 163
moving                           multiple-table forms                names, extracting from
  form controls, 34-36             overview, 95-96                    database, 245
     aligning control              subforms                          navigating
       edges, 35-36                   creating with Form               datasheet view, 224
     aligning to grid, 35              Wizard, 96-98                   dynasets, 224-226
     spacing between                  creating with Subform            forms, 14
       controls, 36                    Wizard, 98-99                 nesting queries within
  PivotTable fields, 337         multiple-table queries.              queries, 273-274
  report controls, 133-134        See also joins                     New Label dialog
     aligning control              adding fields from                 box, 187
       edges, 134                    multiple tables,
     aligning to grid, 134                                           New Label Size dialog
                                     272-273
     spacing between                                                  box, 187
                                   adding multiple tables to
       controls, 135                 queries, 271-272                nonrelational
MsgBox action                      case study: drilling               databases, disadvantages
 (macros), 171                       down to order details,           of, 259-261
Multi Select property                282-286                         Normal command
 (lists), 67                       joining tables with                (AutoCalc), 320
                                     Query Design                    Northwind 2007 database
multi-column lists, 65-66
                                     window, 274                       case study: selecting
multiple-column reports            nesting queries within                shipper
  case study, 181-184                queries, 273-274                     Combo Box
  overview, 177-178                overview, 271                            Wizard, 63
  page setup options,              unique values queries,                 getting list values
    178-179                          creating, 280-282                      from current table,
  setting up, 178
                                 multiple-table reports                     64-65
  troubleshooting, 180-181
                                   main reports, 190                      getting list values
multiple-field                     overview, 189                            from table or query
 one-dimensional                   subreports                               fields, 64
 PivotTables, 322-323                 creating with Report                List Box Wizard, 63
multiple fields                        Wizard, 191-192                    multi-column
  creating totals queries             creating with                         lists, 65-66
   on, 289-290                         Subreport Wizard,                  properties, 66-67
  grouping totals on,                  192-194                            Shipper ID
   291-292                            overview, 190-191                     field, 62-63
  sorting records on,            multiplication (*)                       specifying custom list
   200-201                        operator, 234                             values, 64
                                                                       loading, 3-4

           How can we make this index more useful? Email us at indexes@quepublishing.com
376   Index    not-equal joins


       not-equal joins, 268         Open Report command         ORDER BY clause
       not-equal operator (), 233    button, 101                 (SELECT statement),
                                    opening reports, 117         345-346
       Not operator, 237
                                    OpenReport action           Order By property
       Not with Rpt Ftr
                                     (macros), 171               (queries), 229
        value (Page Header
        property), 118              operands, 231               orders
                                                                  viewing by custom
       Not with Rpt Hdr             operators, 231
                                                                   quarter, 330-331
        value (Page Header            arithmetic operators,
                                                                  viewing by day of week,
        property), 118                  234-235
                                                                   328-329
       Not with Rpt Hdr/Ftr           Between...And, 235
                                      comparison operators,     organizing
        value (Page Header
                                        233-234                   form controls
        property), 118
                                      In, 235                        lines/rectangles, 82-83
       Now function, 249                                             option groups, 83-84
                                      in filter criteria
       NPer function, 254               expressions, 207-208         overview, 81
       NPV function, 254              Is Null, 235                   tab controls, 84-85
                                      Like, 235                   report controls
       numbers
                                      logical operators, 236         lines and rectangles,
         filters, 205
                                         And, 236-237,                 148-149
         numeric literals, 232
                                           298-299                   overview, 147
         random numbers,
                                         Eqv, 237                    page breaks, 149-150
           generating
             random N-digit              Imp, 237               Orientation property
              numbers, 253               Not, 237                (queries), 229
             random numbers              Or, 236-237, 298-299   orphans, 266
              between two values,        Xor, 237
                                                                outer joins, 268
              252-253                 precedence, 238-239
                                                                  applications, 276
         serial numbers, 246-247    option buttons                creating, 275, 346-347
       numeric literals, 232          advantages, 59              finding customers
                                      creating manually, 61         without matching
                                      creating with Option
                     O                  Group Wizard, 59-60
                                                                    orders, 276
                                                                  finding products without
       one-to-many relational       Option Group                    assigned categories, 277
        model, 264                   Wizard, 59-60              Output All Fields
       one-to-one relational        option groups, adding to     property (queries), 228
        model, 265                   forms, 83-84               output of reports
       Open Form command            Or filter, 206                page breaks, 174
        button, 101                 Or operator, 236-237,         preventing widowed
       Open Report action, 168       298-299                        records, 175
                                                                  starting sections at top of
                                                                    row/column, 174-175
                                                                                           PivotTables   377


OutputTo action                  Percent of Grand                    pivoting (in PivotTables)
 (macros), 171                    Total command                         changing field order, 338
overlapping                       (AutoCalc), 320                       moving fields, 337
  controls, 39                   Percent of Parent                      overview, 336-337
  report controls, 136            Column Item command                PivotTables
                                  (AutoCalc), 320                      data fields, 314
                                                                       data pivoting
            P                    Percent of Parent Row
                                                                           changing field
                                  Item command
page breaks, 149-150, 174         (AutoCalc), 320                            order, 338
                                                                           moving fields, 337
page footers, 21, 118            Percent of Row
                                                                           overview, 336-337
Page Header                       Total command
                                                                       fields
 property, 118                    (AutoCalc), 320
                                                                           changing order
page headers, 21, 118            Perry, Greg, 50                             of, 338
page numbers,                    Picture Alignment                         column fields, 314
 122-123, 146                     property                                 filter fields, 314
                                   forms, 25                               moving, 337
Page Numbers dialog
                                   reports, 128                            row fields, 314
 box, 122
                                 Picture property                      filtering
page setup, 137, 178-179                                                   adding filter fields,
                                   forms, 24
Page Setup dialog box,             reports, 128                              335-336
 137, 178-179                                                              AutoFilter
                                 Picture Size Mode
paired controls,                                                             feature, 332
                                  property
 moving, 35                                                                displaying only top
                                   forms, 25
paper forms,                                                                 or bottom items,
                                   reports, 128
 mimicking, 79                                                               333-335
                                 Picture Tiling property                   grouping field
parameter queries                  forms, 25                                 items, 335
  creating, 302-303,               report images, 128, 154             formatting, 339
    350-351                      pictures                              items, 313
  overview, 105-107, 302            adding to business                 layout, 314
  parameter data                     reports, 153-154                  one-dimensional
    types, 304                      adding to forms, 91-92               PivotTables
parent tables, 264                  setting as form                        AutoCalc data fields,
Payment Calculation                  backgrounds, 24-25                      318-320
 section (mortgage                  setting as report                      calculated fields, 321
 calculator), 46                     backgrounds, 127-128                  data field details,
PDF, publishing reports          PivotCharts                                 displaying, 314-318
 to, 139-140                       forms, 108-110                          multiple-field
                                   reports, 194-195                          one-dimensional
Percent of Column
                                                                             PivotTables, 322-323
 Total command
 (AutoCalc), 320

           How can we make this index more useful? Email us at indexes@quepublishing.com
378   Index    PivotTables


            removing fields, 322      Print Current Form              publishing reports
            sum of data field          command button, 101              to email, 138
              values, displaying,     Print Record command              on paper, 137-138
              316-317                  button, 101                      to PDF or XPS, 139-140
          overview, 311-313                                             to Word, 138-139
                                      Print Report command
          two-dimensional                                             PV function, 254
                                       button, 101, 168
           PivotTables
            built-in date features,   Print Table command
              326-328                  button, 101                                Q
            creating, 323-324         printing reports, 137-138       QBE (query by example)
            custom date               PrintOut action                  grid, 212
              fields, 328              (macros), 172
            customer orders,                                          queries
                                      product category,                 action queries
              analyzing by product
                                       analyzing customer                  append queries, 309
              category, 324-326
                                       orders by, 324-326                  delete queries,
            employee orders,
              viewing by custom       product orders                         306-307
              quarter, 330-331          analyzing by category,             make-table queries,
            product orders,              324-326                             307-308
              viewing by day of         viewing by custom                  overview, 304
              week, 328-329              quarter, 330-331                  update queries,
                                        viewing by day of week,              304-306
       placeholders, 54                                                 append queries, 353-354
                                         328-329
       Pmt function, 254                                                business applications,
                                      products, finding without
       Pop Up property                 assigned categories, 277          216-217
        (forms), 104                                                    compared to filters, 210
                                      Products table, 265               creating
       pop-up forms, 103-104
                                      properties. See also specific        creating query
       PPmt function, 254              properties                            objects, 211-212
       precedence of operators,         bound controls, 16-17              entering query
        238-239                         fields, 214-215                      criteria, 213
       Preview Report command           forms, 20, 26                      excluding fields from
        button, 101                     queries, 228-229                     results, 213-214
                                        reports, 126                       including external
       previewing reports,
        124-125                       Property Sheet command                 tables, 212
                                       (Design menu), 24                   overview, 210-211
       primary tables, 264
                                      property sheets, 87, 150             returning top N
       Principal Calculation                                                 values, 213-214
        section (mortgage             proportional fonts, 86
                                                                           selecting fields for
        calculator), 46               protecting forms, 26-27                inclusion, 212-213
       Print a Form command           Publish as PDF or XPS                setting field
        button, 101                    dialog box, 139                       properties, 214-215
                                                                                         queries   379


creating subreports from,        delete queries, 352-353             parameter queries
 193-194                         dynasets, 216                          creating, 302-303
criteria expressions                adding records, 225                 overview,
   arithmetic operators,            copying records, 226                  105-107, 302
     234-235                        datasheet view,                     parameter data
   Between...And                      223-224                             types, 304
     operator, 235                  deleting records, 227            properties, 228-229
   calculated columns,              entering data, 224-225           query objects, 211-212
     239-241                        navigating, 224-226              running, 215-216
   comparison operators,            selecting records, 226           select queries
     233-234                     Find Duplicates                        ALL predicate, 344
   Expression Builder,            queries, 222                          calculated
     256                         Find Unmatched                           columns, 349
   functions, 233,                queries, 222-223                      DISTINCT
     242-255                     Mail Merges, 217                         predicate, 344
   identifiers, 232                 querying Customers                  DISTINCTROW
   In operator, 235                   table, 218                          predicate, 344
   Is Null operator, 235            running mail                        FROM clause, 344
   Like operator, 235                 merge, 219                        inner joins,
   literals, 232                 make-table queries, 353                  creating, 346
   logical operators,            multiple-table queries.                ORDER BY
     236-237                      See also joins                          clause, 345-346
   operator precedence,             adding fields                       outer joins,
     238-239                          from multiple                       creating, 346-347
   overview, 231-232                  tables, 272-273                   overview, 342
   selecting criteria for           adding multiple tables              parameter
     queries, 213                     to queries, 271-272                 queries, 350-351
   structure, 231                   case study: drilling                self-joins,
crosstab queries, 220-221             down to order                       creating, 347-348
datasheet view                        details, 282-286                  syntax, 343-344, 351
   formatting, 227-228              joining tables with                 theta joins, creating,
   navigating fields                  Query Design                        348-349
     in, 224                          window, 274                       TOP n [PERCENT]
   overview, 223-224                nesting queries within                predicate, 344
decision-making queries               queries, 273-274                  totaling and grouping
   case study: calculating          overview, 271                         records, 350
     customer discount              unique values queries,              WHERE clause, 345
     rates, 300-302                   creating, 280-282              subdatasheets, 284-286
   IIf function, 297-299         nesting, 273-274                    subqueries
   overview, 296-297             overview, 199, 210                     defining field
   Switch function, 299                                                   criteria, 356
                                                                        defining fields, 355


        How can we make this index more useful? Email us at indexes@quepublishing.com
380   Index     queries


             determining whether        Query Parameters dialog          form record sources,
               unit price is greater     box, 304                          changing, 15-16
               than average, 355        Quit action (macros), 172        grouping with SELECT
             overview, 354                                                 statements, 350
                                        Quit Application
             returning dynasets,                                         navigating, 225-226
                                         command button, 101
               356-358                                                   record sources, 15-16,
          totals queries                                                   117-118
             aggregate operations,                   R                   removing, 306-307
               287-288                                                   selecting, 226
             calculating units left     random numbers,                  sorting, 199
               in stock, 295-296         generating                          on multiple fields,
             combining aggregate          random N-digit                      200-201
               functions and                numbers, 253                     on single field, 200
               totals, 295-296            random numbers                 widowed records,
             creating on multiple           between two values,            preventing, 175
               fields, 289-290              252-253
                                                                       Recordset Type property
             creating on single         Rate function, 255               forms, 27
               field, 289               readability                      queries, 229
             creating with                 business reports, 147       rectangles
               aggregate functions,        forms, 80                      adding to business
               294-295                  Record Locks property              reports, 148-149
             creating with               (forms), 27                      adding to forms, 82-83
               calculated fields, 293
             custom totals, 295         record sources, 15-16,         referential integrity, 266
             filtering records           117-118                       Refresh Form Data
               before calculating       records                         command button, 101
               totals, 290                 adding                      relational databases.
             grouping on multiple              to dynasets, 225         See databases
               fields, 291-292                 with append
                                                                       relationships
             grouping totals, 291               queries, 309
                                                                          editing, 271
             Total row, 288-289            copying, 226
                                                                          establishing
          union queries, 280-282,          deleting, 227
                                                                             join lines, 267-268
           359-360                         filtering before
                                                                             join types, 268
          update queries, 352                calculating totals, 290
                                                                             joining tables,
       query by example (QBE)              finding records without
                                                                               269-270
        grid, 212                            matching records
                                                                             Relationships
                                               customers without
       Query Design                                                            window, 269
                                                matching orders, 276
        window, 274                                                       many-to-many, 265-266
                                               products without
       query objects, 211-212.                                            one-to-many, 264
                                                assigned
        See also queries                                                  one-to-one, 265
                                                categories, 277
                                                                       Relationships
                                                                        window, 269
                                                                                           reports   381


reorder levels, 297                    text formatting,                 date/time, adding, 123
Replace function, 244                    150-151                        detail section, 118
                                    calculations                        events, associating
Report command (Create
                                       overview, 161-162                  macros with, 173
 menu), 113-114
                                       text boxes, inserting,           footers, 118-119
Report Design command                    162-163                        headers, 118
 (Create menu), 117                    text boxes as                    grouping
Report Design view,                      calculated controls,              options, 158-160
 116-117                                 163-165                           overview, 157
Report Wizard, 114-116                 Totals list, 162                    with expressions, 161
  creating subreports,              case study: invoice                 launching
   191-192                           report, 165-167                       overview, 167
  grouping reports                  controls                               with command
     options, 158-160                  aligning control                      buttons, 167-169
     overview, 157                       edges, 134                        with macros, 169-173
     with expressions, 161             aligning to grid, 134            Layout view, 136-137
  sorting reports                      calculated                       mail merge reports,
     options, 158                        controls, 119                    187-189
     overview, 157                     conditional                      multiple-column reports
     with expressions, 161               formatting, 130-131               case study, 181-184
                                       fields (bound                       overview, 177-178
reports
                                         controls), 119-120                page setup options,
  AutoFormat, 125-126
                                       formatting, 130                       178-179
  backgrounds
                                       grouping, 135                       setting up, 178
     color, 127
                                       inserting, 129                      troubleshooting,
     pictures, 127-128
                                       labels, 120-121                       180-181
  business reports
                                       logos, 121-122                   opening in Report
     audience of, 144
                                       moving, 133-134                    Design view, 117
     colors, 152-153
                                       overlapping                      overview, 113
     contents of, 142-143
                                         controls, 136                  page breaks, 174
     design guidelines,
                                       selecting, 129-130               page numbers, 122-123
      145-147
                                       sizing, 131-133                  PivotChart reports,
     goals of, 142-144
                                       spacing between, 135               194-195
     images, 153-154
                                       unbound controls, 119            previewing, 124-125
     organizing with
                                       ungrouping, 136                  printing, 137-138
      lines and rectangles,
                                       Z-order, 136                     properties, 126
      148-149
                                    creating                            publishing
     overview, 141-142
                                       with Report com-                    to email, 138
     page breaks, 149-150
                                         mand, 113-114                     on paper, 137-138
     Shadow effects,
                                       in Report Design                    to PDF or XPS,
      154-155
                                         view, 116-117                       139-140
     special effects, 154
                                       with Report                         to Word, 138-139
                                         Wizard, 114-116

           How can we make this index more useful? Email us at indexes@quepublishing.com
382   Index     reports


          record sources              Restore action               screen resolution, 80, 146
            15-16, 117-118             (macros), 172               scrollbars, 69-71
          sections                    returning top N values,      Second function, 249
              resizing, 124            213-214
              starting at top of                                   sections of reports
                                      Ribbon, 87, 150                 resizing, 124
                row/column,
                174-175               Right function, 244             starting at top of
          single-column               right-outer joins,                row/column, 174-175
            reports, 177                268, 275                   security, 26-27
          sorting                     Rnd function, 252            select queries.
              options, 158                                          See SELECT
                                      Round function, 252
              overview, 157                                         statement (SQL)
              with expressions, 161   Row Source property
                                       (lists), 66                 SELECT
          subreports
                                                                    statement (SQL)
              creating with Report    Row Source Type
                                                                     ALL predicate, 344
                Wizard, 191-192        property (lists), 66
                                                                     calculated columns, 349
              creating with           rows                           defining fields, 355
                Subreport Wizard,       definition of, 314           determining whether
                192-194                 starting report sections       unit price is greater
              overview, 189-191           at top of, 174-175           than average, 355
          titles, 122
                                      RTrim function, 244            DISTINCT
          widowed records,
                                      rules for data                   predicate, 344
            preventing, 175
                                       validation, 51-52             DISTINCTROW
       resizing                                                        predicate, 344
          form controls               Run Macro command
                                                                     FROM clause, 344
             making controls           button, 101
                                                                     inner joins, creating, 346
               same size, 34          Run Query command              INTO predicate, 353
             step-by-step              button, 101                   ORDER BY clause,
               process, 33            running                          345-346
             to control’s               macros, 170                  outer joins, 346-347
               content, 34              Mail Merge, 219              overview, 342
             to grid, 33-34             queries, 215-216             parameter queries,
          forms, 18                                                    350-351
          report controls, 131-133
             making controls
                                                   S                 self-joins, 347-348
                                                                     subqueries
               same size, 133         sans serif typefaces, 86          defining field
             to caption, 132                                              criteria, 356
                                      saturation, 90
             to grid, 132                                               overview, 354
          report sections, 124        Save action (macros), 172
                                                                        returning dynasets,
       resolution (screen),           Save Record command                 356-358
        80, 146                        button, 101                   syntax, 343-344, 351
                                                                     theta joins, 348-349
                                                                                            SQL statements   383


   TOP n [PERCENT]                   Shipper ID field, 62-63             report controls, 131-133
    predicate, 344                   specifying custom list                 making controls same
   totaling and grouping              values, 64                             size, 133
    records, 350                  Show Table dialog box,                    to caption, 132
   WHERE clause, 345               211, 269                                 to grid, 132
selecting                                                                report sections, 124
                                  ShowAllRecords action
   controls, 30-31                 (macros), 172                      sliding-scale discount
   records, 226                                                         rates, calculating,
                                  simplicity
   report controls, 129-130                                             301-302
                                     in form design, 79-81
self-joins, 268, 278,                in report design, 145-146        SLN function, 255
 347-348                                                              snaked-column reports.
                                  Sin function, 252
Send Object As dialog                                                  See multiple-column
                                  single-column
 box, 138                                                              reports
                                    reports, 177
Send Report to File                                                   sorting
                                  Size Mode property
 action, 101, 168                                                       records, 199
                                   (report images), 153-154
sending email, 72-74                                                        on multiple fields,
                                  Size To Fit command                        200-201
SendObject action                  (Arrange menu), 34, 132                  on single field, 200
 (macros), 172
                                  Size To Grid command                  reports
serial numbers, 246-247            (Arrange menu), 34, 132                  options, 158
serif typefaces, 86               Size To Narrowest                         overview, 157
SetValue action                    command (Arrange                         with expressions, 161
 (macros), 172                     menu), 34                          Space function, 244
Sgn function, 252                 Size To Shortest                    spacing
shadow effect, 92-93,              command (Arrange                     character spacing, 86
 154-155                           menu), 34                            control spacing, 36, 135
Shipper ID field                  Size To Tallest command             Special Effect property
 (Northwind 2007                   (Arrange menu), 34                  (report images), 154
 database), 62-63                 Size To Widest command              special effects, adding to
shippers, selecting                (Arrange menu), 34                  reports, 154
 (Northwind 2007                  sizing                              speed of forms, 79
 databases)                          controls                         spin buttons, 68-69
   Combo Box Wizard, 63                 making controls same
                                                                      Split Form command
   getting list values from               size, 34
                                                                       (Create menu), 11
     current table, 64-65               step-by-step
   getting list values from               process, 33                 split forms, 11
     table or query fields, 64          to control’s                  SQL statements
   List Box Wizard, 63                    content, 34                   DELETE, 352-353
   list properties, 66-67               to grid, 33-34                  INSERT INTO,
   multi-column lists, 65-66         forms, 18                           353-354
                                                                        overview, 341

            How can we make this index more useful? Email us at indexes@quepublishing.com
384   Index     SQL statements


          SELECT                       Sqr function, 252            overview, 354
             ALL predicate, 344        stacked control              returning dynasets,
             calculated                 layouts, 37-38               356-358
               columns, 349                                            All predicate, 358
                                       Standard Deviation
             DISTINCT                                                  In predicate, 357-358
                                        command
               predicate, 344                                     Subreport Wizard, creat-
                                        (AutoCalc), 319
             DISTINCTROW                                           ing subreports with, 192
               predicate, 344          Standard Deviation
                                                                     from another report, 194
             FROM clause, 344           Population command
                                                                     from table or queries,
             inner joins,               (AutoCalc), 319
                                                                       193-194
               creating, 346           standard forms, 9-11
                                                                  subreports
             INTO predicate, 353       startup forms, 107-108       creating with Report
             ORDER BY clause,
                                       statements. See SQL           Wizard, 191-192
               345-346
                                        statements                  creating with Subreport
             outer joins, 346-347
                                       Status Bar Text property      Wizard, 192-194
             overview, 342
                                        (text boxes), 44            overview, 189-191
             parameter queries,
               350-351                 StDev function, 288, 294   Sum command
             self-joins, 347-348                                   (AutoCalc), 319
                                       StDevP function, 294
             subqueries, 354-358                                  Sum function, 288, 294
                                       StrComp function, 244
             syntax, 343-344, 351                                 sum of data field
             theta joins, creating,    StrReverse function, 245
                                                                   values (PivotTables),
               348-349                 Structured Query            displaying, 316-317
             TOP n [PERCENT]            Language. See SQL
                                                                  Summary Options
               predicate, 344           statements
                                                                   dialog box, 115
             totaling and grouping     subdatasheets (queries),
               records, 350                                       Switch function, 299
                                        284-286
             WHERE clause, 345                                    switchboard forms,
                                       Subform Wizard, 98-99
          subqueries                                               102-103
             defining field            subforms
                                                                  SYD function, 255
               criteria, 356             creating with Form
                                           Wizard, 96-98          syntax of functions, 242
             defining fields, 355
             determining whether         creating with Subform
               unit price is greater       Wizard, 98-99                      T
               than average, 355         overview, 95-96
                                       subqueries                 tab controls, 84-85
             overview, 354
             returning dynasets,         defining field           tab order (controls), 40
               356-358                    criteria, 356           Tab Order command
          UNION, 359-360                 defining fields, 355      (Arrange menu), 40
          UPDATE, 352                    determining whether      Tab Order dialog box, 41
          viewing, 341-342                unit price is greater
                                          than average, 355
                                                                      toggle buttons, binding to Yes/No fields   385


tables. See also                        temporary joins                   functions, 243-246
 PivotTables                              with Query Design               literals, 232
   adding to Relationships                window, 274                     text boxes
     window, 269                        theta joins, 279-280,                 adding to forms,
   child tables, 264                      348-349                              43-44
   Companies, 262                    modifying with update                    adding to reports,
   Contacts, 262-263                   queries, 304-306                        162-163
   creating with                     parent tables, 264                       as calculated controls,
     make-table queries,             Products, 265                             44-46, 163-165
     307-308                         in queries, 212                          overview, 43
   filtering                         referential integrity, 266           text prompts, 50
       And filter, 206               relationships                    text boxes
       applying filters,                editing, 271                     adding to forms, 43-44
         209-210                        establishing, 267-270            adding to reports,
       compared to                      many-to-many,                     162-163
         queries, 210                     265-266                        as calculated controls,
       creating filters,                one-to-many, 264                  44-46, 163-165
         208-210                        one-to-one, 265                  overview, 43
       date filters, 205             subreports, creating,
                                                                      text functions, 243-246
       Filter By Selection             193-194
         feature, 203                                                 text literals, 232
                                  tabular control
       filter criteria, 207-208    layouts, 37-38                     text prompts, 50
       Filter Excluding                                               theta joins, 268,
                                  tan function, 252
         Selection                                                     279-280, 348-349
         feature, 203             temporary joins, creating
                                   with Query Design                  time
       filtering in                                                      adding to reports, 123
         place, 204                window, 274
                                                                         business reports, 146
       number filters, 205        text
                                                                         time functions, 246-250
       Or filter, 206                formatting
                                                                         time literals, 232
       overview, 201-202                 character spacing, 86
       text filters, 205                 overview, 86-87              Time function, 249
   Invoices, 265                         proper case, 246             TimeSerial function, 249
   joins                                 with property sheets,        TimeValue function, 249
       creating, 269-270                   87, 150
                                                                      Title command (Design
       deleting, 271                     with Ribbon, 87, 150
                                                                       menu), 23
       inner joins, 268, 346             shadow effect, 92-93
                                         tips and guidelines,         titles
       join lines, 267
                                           87-88, 150                     forms, 23
       outer joins, 275-277,
                                         type sizes, 86                   reports, 122
         346-347
       self-joins, 278,                  type styles, 86              toggle buttons, binding to
         347-348                         typefaces, 86                 Yes/No fields, 58
                                     filters, 205


            How can we make this index more useful? Email us at indexes@quepublishing.com
386   Index     top N values, returning


       top N values, returning,           two-dimensional             unit price, determining
        213-214                            PivotTables                 whether greater than
       TOP n [PERCENT]                      built-in date features,    average, 355
        predicate (SELECT                    326-328                  units left in stock,
        statement), 344                     creating, 323-324          calculating, 295-296
                                            custom date fields, 328
       Top Values property                                            update queries, 304-306,
                                            customer orders,
        (queries), 229                                                 352
                                             analyzing by product
       Total row, displaying in              category, 324-326        UPDATE statement
        Design grid, 288-289                employee orders,           (SQL), 352
       totals, calculating                   viewing by custom
        with SELECT                          quarter, 330-331                       V
        statements, 350                     product orders,
       Totals list, 162                      viewing by day of        validating data
                                             week, 328-329               case study: selecting
       totals queries
                                                                           shipper (Northwind
          aggregate operations,           type sizes, 86
                                                                           2007 database), 61
            287-288                       type styles, 86                input masks
          calculating units left in       typefaces, 86                      custom input mask
            stock, 295-296
                                                                               expressions, 54-55
          combining aggregate
            functions and totals,                     U                      Input Mask
                                                                               Wizard, 53
            295-296
                                          UCase function, 245                overview, 52-53
          creating
                                          unary operator (-), 234        lists
              with aggregate
                                                                             getting list values
                functions, 294-295        unbound controls
                                                                               from current
              with calculated               converting to bound
                                                                               table, 64-65
                fields, 293                  controls, 39
                                                                             getting list values
              on multiple fields,           overview, 17, 119
                                                                               from table or query
                289-290                   Undo Record command                  fields, 64
              on single field, 289         button, 101                       List Box Wizard, 63
          custom totals, 295
                                          Ungroup command                    multi-column lists,
          filtering records before
                                           (Arrange menu), 39, 136             65-66
            calculating totals, 290
                                          ungrouping report                  overview, 62-63
          grouping on multiple
                                           controls, 136                     properties, 66-67
            fields, 291-292
                                                                             specifying custom list
          grouping totals, 291            union queries, 359-360
                                                                               values, 64
          Total row, 288-289              UNION statement                option buttons
       Trim function, 245                  (SQL), 359-360                    advantages, 59
       troubleshooting multi-             unique values queries,             creating manually, 61
         column reports, 180-181           280-282                           creating with
                                                                               Option Group
                                                                               Wizard, 59-60
                                                                                           Z-order   387


  overview, 50
  rules, 51-52
                                widowed records,
                                 preventing, 175
                                                                               X-Y-Z
  text prompts, 50              wildcard characters, 235            Xor operator, 237
  Yes/No fields, 56-57                                              XPS, publishing reports
                                wizards
     check boxes, 57                                                 to, 139-140
                                  AutoFormat Wizard,
     toggle buttons, 58
                                    19-20, 125
Validation Rule property                                            Year function, 249
                                  Combo Box Wizard, 63
 (forms), 51-52                   Command Button                    Yes/No fields, 56-57
Var function, 288, 294              Wizard, 100-102, 172              check boxes, 57
                                  Crosstab Query Wizard,              toggle buttons, 58
Variance command
 (AutoCalc), 319                    220-221
                                                                    Z-order, 39, 136
                                  Find Duplicates Query
Variance Population
                                    Wizard, 222
 command
                                  Find Unmatched Query
 (AutoCalc), 319
                                    Wizard, 222-223
VarP function, 294                Form Wizard, 12-14,
viewing                             96-98
   forms, 18-19                   Input Mask Wizard, 53
   SQL statements,                Label Wizard, 185-187
     341-342                      List Box Wizard, 63
views                             Option Group
   datasheet view                   Wizard, 59-60
      formatting, 227-228         Report Wizard, 114-116
      navigating fields              creating subreports,
        in, 224                       191-192
      overview, 223-224              grouping reports,
   Design, 15                         157-161
   Layout, 25-26, 136-137            sorting reports,
   Report Design, 116-117             157-158, 161
                                  Subform Wizard, 98-99
                                  Subreport Wizard,
           W                        192-194
Weekday function, 249           Word
WeekdayName                      Mail Merge
 function, 249                      queries, 217-219
                                    running, 219
WHERE clause                     publishing reports to,
 (SELECT                           138-139
 statement), 345
Where operation, 288




          How can we make this index more useful? Email us at indexes@quepublishing.com

				
DOCUMENT INFO