Q

quantified predicate, A54-A55

queries, 347–514.

See also action queries;

in-line functions;

project queries;

stored procedures

about, 4, 99

action, 347, A33, A71

adding records in Datasheet view, 391

advantages of, 15–16

aggregate, 1505

append, 502–507

appending data with stored procedures, 1519–1522

command on Design contextual tab for, 488

creating, 503–506

defined, 485

icon for, 490

make-table vs., 502

running, 506–507

SQL overview for, A73–A75

append values

inserting parameters in, 1520–1522

syntax of, 1519–1520

assigning data types for parameters, 451

avoiding joins between, 185

Between, In, and Like comparison operators in, 360–361

building

for complex reports, 812–813

query on, 421–424

report, 757–759

subreport, 855

for table modified in Table Analyzer Wizard, 238–239

ways to create, 348

checking

field properties in Datasheet view, 354

new field validation rules, 381–382

choosing data from single table, 349–351

complex expressions for simple, 371–376

controlling output of, 452–454

criteria

dates and times in, 356–357

entering selection, 355–356

excluding requests by date, 1109

for fields in, 355–356

selecting with AND and OR, 357–360

specifying sorting, 380

crosstab, 442–449

creating, 442–447

Design view of, 442, 443

displaying multiple-value fields in, 444

filling empty cells with zero values, 446–447

function of, 442

GROUP BY clauses in, A44-A45

partitioning data in, 447–449

pivoting with PivotTable vs., 444, 469–470

Query Wizard for creating, 431

sample results desired from, 442, 443

sort order for columns in, 445–446

specifying column headings for, 445–446

unable to change data in, 468

viewing design in Datasheet view, 445

customizing properties, 452–463

data definition, 463

data selection and editing in Datasheet view, 393

defined in SQL, 417

delete, 510–511

about, 485

backing up before running, 510

command on Design contextual tab for, 488

confirming actions of, 510

deleting inactive data with, 510–511

icon for, 490

testing rows affected by, 507–510

using, 510

delivering dynamic results on Web page, 1158–1160

designating as source for embedded subform, 706–707

designing

custom query by form, 1086–1093

PivotChart form, 731–732, 860–861

ensuring data integrity in, 417–419

exporting XML, 1246–1250

expressions in, 362–370

field names in, 377–378

field property assignments in, 353–355

finding

records across date spans, 428

unmatched outer joins, 426–430

functions in, 136–137

including parameters in, 449–452

limitations updating fields in, 468–469

limiting returned records, 410

listing by object type, 110–111

looking up values with combo box, 602–604

macro actions executing, A106-A107

make-table, 495–502

append vs., 502

assigning alias to field lists in, 496, 497

command on Design contextual tab for, 488

converting from select query, 500

creating, 496–500

defined, 485

icon for, 490

limitations of, 501

running, 500–502

SELECT...INTO statements as, A75-A76

verifying data in, 496

many-to-one, 687–688

modifying, 212

opening in Design view, 112–113, 349, 351

outer joins for, 425–426

Output All Fields property, 452–453

parameter, 1565

pass-through, 463

placing all fields in many-to-one form, 688–691

preventing data modification of, 413

processing data with, 10–11

project, 1491–1545

building in query designer, 1495–1524

building with text editor, 1524–1545

viewing, 10–11, 1492–1494

record indicators in Datasheet view, 390

relationships

matching unnecessary for joined, 416

to other Access objects, 101

to tables and forms, 560

viewing those defined for, 187

restricting user datasheet, 179

returning unique records and values for, 454–458

row source, 1054–1055

saving complex results in temporary table, 496

select, 347

selecting

report fields from multiple, 778

table containing e-mail address, 332–333

setting

datasheet format for in Datasheet view, 1145

field properties for, 353–355

sorting recordsets by fields, 421

specifying

data source for embedded subforms, 693–696

fields in, 351–353

subdatasheets in, 387–390

testing new table validation rules, 382–384

totals, 435–449

defining aggregate functions in, 435–436, 437

filtering before grouping records with, 440–441

filtering groups of totals after calculating, 441–442

partitioning data in, 447

sorting sequence of, 439

unable to change data in, 468

uses for, 435

union, 463–468

building in SQL view, 463–468

illustrated, 468

updates disallowed for, 469

using column names in ORDER BY clause of, A52, A63, A69

viewing in Datasheet view, 465

update, 486–495

backing up data before using, 489

command on Design contextual tab for, 488

confirmation dialog boxes with, 489,491

converting select query to, 488

defined, 485

defining generic parameters for, 495

expressions in, 489

icon for, 490

illustrated, 488

multiple tables or queries creating, 493–495

running, 489–491

updating multiple fields, 491–493

using select query to test data updates, 486–487

working

in Datasheet view, 113–114, 384–412

with recordset data, 560

Queries object type, 48

Query Builder, 576–578

Query Design button, 348, 415

query designer, 1495–1424.

See also complex queries;

simple queries

about SQL built in, 463

adding tables, views, and functions, 1495–1496

Boolean operators in, A55

building

database and project queries in, 1491

simple queries with, 348

SQL statements from text editor, 1525–1526

defining Lookup properties for desktop database que- ries, 353

editing SQL statements in, 1526

ensuring correct table used in, 415

Group By options in, 1506

in-line functions, 1513–1519

available properties for, 1517–1519

building, 1514–1516

constructing to create updatable recordsets, 1514

declaring parameter to filter, 1515, 1516

defined, 1493

reasons for using, 1513

testing, 1516–1517

joins

adding lines when building query on query, 423

working with, 419

opening, 348

panes of

about, 1497–1498

diagram, 1497, 1499, 1500–1503

grid, 1497, 1500, 1503–1507

SQL, 1498

queries converted into SQL by, 417

reviewing query in SQL view, 464

specifying brackets around field names for database queries, 362

stored procedures, 1519–1524

aboutSQL, 1493, 1519

defining properties for columns, 1523–1524

inserting parameters in append values queries, 1520–1522

re-creating stored procedure from, 1529–1530

using append and append values queries, 1519

views

types created in, 1495

using, 1507–1513

working with columns for, 1499–1450

query parameters

assigning data types for, 451

brackets around, 449

defining generically for update queries, 495

deleting rows by date, 451

formats for date parameters, 508

including in queries, 449–452

Query Parameters dialog box, 451

query properties, 452–463

cycling through values of, 461

Max Records, 462

ODBC Timeout, 462

Output All Fields, 452–453

predefining subdatasheets with, 458–462

Record Lock, 462

Source Connect Str, 462

Source Database, 462

Subdatasheet Height, 460

Top Values, 453–454

Unique Records, 454–458

Unique Values, 454–458, 469

viewing in Query window, 452, 453

Query Type group (Design contextual tab), 488, 509

Query window

changing font size for, 467

Design view for, 351

opening query property sheet in, 452,453

Query Wizard

building crosstab queries from, 431

creating complex queries in, 431–434

Find Unmatched, 432–434

selecting, 432

Query Wizard button, 348, 1524

QueryDefs collection, 984

question mark (?) wildcard, 169, A49

Quick Access Toolbar, 27–33

adding

command from Ribbon to, 34

macros to, 30–31

separators between commands, 33

Customize category settings for, 28–29, 94

Database Tools tab, 24, 44–45

default and custom commands on, 27–30

illustrated, 28

removing items from, 28–29, 33, 34

restoring default settings for, 33

revising order of commands and macros on, 32

undo feature for, 210

quick create commands

forms

building with wizard or commands, 593

creating data entry form, 590–591

creating split form, 590–591

Report, 775–777

quick create table templates, 152

Quit method, 1328