A

ABS (), 128

Access database, see Microsoft Access

Access project, see Microsoft Access project

Active Server Pages, see ASP

ADD, 202-203

ADO, 281, 295, 297

aggregate functions, 106, 112-115

creating join containing, 165-166

alias, 61, 80

creating, 80-81

ALL, 175, 185-186

ALTER COLUMN, 204-206

ALTER TABLE, 31, 45-47, 201-203

AND, 90-95

ANY, 175, 183-185

arithmetic operators, 106, 108-111

AS, 61, 81

ASC, 61, 69-70, 72

ASP, 276, 282-286

building page in, 286-289

integrating SQL script in, 275-277

attribute, 1, 7-8

AVG (), 112-113

B

BETWEEN, 90, 99-100

BINARY data type, 37

BIT data type, 37

button,

creating to prompt user, 239-243

viewing on form, 244-245

C

Cartesian product, 145, 163

CCUR (), 128-130

CHAR data type, 36

CHECK constraint, 40

using, 46-47

clause, 15, 18, 61, 68, 88

client, 1, 11

client/server database, 11

client/server database system, 1, 11

column, 1, 4-5

adding to table, 202-203

changing, 204-205

deleting from table, 206-207

setting default value for, 205-206

comparison operators, 87, 89-90

concatenation, 61, 82-84, 117-118

constraints, 31, 40

using, 40-47

correlated subquery, 175-176

COUNT (*), 112, 114-115

COUNT (ColumnName), 112

COUNTER data type, 37

CREATE INDEX, 208-210, 221

CREATE TABLE, 31, 34-36

CREATE VIEW, 191, 194-196

Currency data type, 37

D

DAO, 295, 297

Data Control Language, 18, 32-33

Data Definition Language, 18, 32

Data Manipulation Language, 18, 32

data modeling, 1, 7

data retrieval time, 207-208

data storage, 3

data type, 31, 36

in Microsoft Access, 36-39

database, 1, 4

naming, 22

planning, 6-11

database management system, 1, 11-12

DATE (), 122, 127-128

date and time functions, 106, 122-128

date formats, 125-126

DATEPART (), 122-123

dates, inserting into table, 124-125

DATETIME data type, 37

DAY (), 123, 127-128

DBMS, see database management system

DCL, see Data Control Language

DDL, see Data Definition Language

DECIMAL data type, 36

DEFAULT, 205

DELETE statement, 31, 58

DESC, 61, 69, 72, 210

dialog box, customizing, 233-234

DISALLOW NULL, 201, 209

DISTINCT, 62, 74-75

DISTINCTROW, 62, 74-75, 152

divide, 109

DML, see Data Manipulation Language

DROP, 206

DROP INDEX, 212-213

DROP TABLE, 207, 224

DROP VIEW, 191, 199-200

duplicate values, working with, 74-75

E

entity, 2, 7

Entity Relationship Diagram model, see ERD model

equi-join, see inner join

ERD model, 2, 7-9

EXISTS, 175, 180-183

exponentiation, 109

expression, 87, 89

F

field, 32

concatenating, 82-84

field size, 32

file, 2-4

FIRST (), 112-113

flat files, 3

FLOAT data type, 36

foreign key, 2, 5-6

FOREIGN KEY constraint, 40

using, 44

FORMAT (), 123, 125-127

FROM keyword, 62

full outer join, 314

functions,

aggregate, 106, 112-115

date and time, 106, 122-128

miscellaneous, 128-130

string, 106

G

GROUP BY clause, 133-138

with the HAVING clause, 140-141

with the ORDER BY clause, 138-139

H

HAVING clause, 133

with the GROUP BY clause, 140-141

with the WHERE clause, 142-143

HOUR (), 123

HTML, 275-276, 282-286, 289-291

hyperlink, 352

Hypertext Markup Language, see HTML

I

IGNORE NULL, 201, 209

IIS, 276-277

IMAGE data type, 37

IN, 90, 100-102, 175-180

index, 207-208

creating, 209-210

deleting, 212-213

editing, 211-212

options for creating, 208-209

sorting, 210

viewing, 211-212

INDEX, 201

inner join, 147-151

INNER JOIN, 145-152

INSERT INTO keywords, 48

INSERT INTO SELECT, 51-52

INSERT statement, 32, 48-52

INSTR (), 117, 120-121

INT (), 128

INTEGER data type, 36

Internet Information Services, see IIS

IS NOT NULL, 90, 102-103

IS NULL, 90, 102-103

J

joins, 146

full outer join, 314

inner join (equi-join), 147-151

left outer join, 163-165, 314

nested join, 156-159

outer join, 159-165

right outer join, 160-163, 314

self join, 153-156

with an aggregate function, 165-166

K

keys, 2, 5

foreign, see foreign key

primary, see primary key

keywords, 15, 18

L

LAST (), 112-113

LCASE (), 116

LEFT (), 116, 118-119

LEFT JOIN, 146, 163-165

left outer join, 163-165, 314

LEN (), 116, 118-119

LIKE, 90, 95-99, 237-238

logical operators, 87, 90

LTRIM (), 116

M

many-to-many relationship, 8

many-to-one relationship, 8

MAX (), 112-113

Memo data type, 37

Microsoft Access, 15, 19, 293-297

data types, 36-39

database, 293, 298-300

date formats, 125-126

disabled mode in, 33-34

enabling blocked query in, 33-34

opening, 21-22

SQL view, 20-21, 63

switching to version SQL-92 in, 17

Microsoft Access project, 293, 298-300

data types, 304-306

database diagrams, 307-310

functions, 318-321

joins, 313-316

Project window, 301-302

queries, 310-311

stored procedures, 316-317

tables, 302-304

views, 311-312

Microsoft Access Query Wizard, 19-20

MID (), 116, 120-121

MIN (), 112-113

minus, 109

MINUTE (), 123

modulus, 109

MONEY data type, 37

MONTH (), 123, 127-128

multiply, 109

N

negation, 109

nested joins, 156-159

nested subquery, 186-189

non-correlated subquery, 175-176

non-parameter parameter query, 248-250

normalization, 2, 10-11

NOT, 90, 100-102, 176, 179, 181

NOT NULL constraint, 40

using, 41

NOW (), 123

NULL constraint, 40

using, 41

NULL statement, 32

NULL values, inserting into table, 51

Number data type, 37

numeric data types, 36

Nz (), 128

O

ODBC connection, setting up, 277-281

OLE Object data type, 37

ON, 146

one-to-many relationship, 8

one-to-one relationship, 8

operators,

arithmetic, 106, 108-111

comparison, 87, 89-90

logical, 87, 90

precedence of, 91, 93, 109

string, 116

using, 91-103

OR, 90-95

ORDER BY clause, 62, 68-74

with the GROUP BY clause, 138-139

outer join, 159-165

P

parameter query, 229-230

creating, 231-233, 246-248

non-parameter, 248-250

syntax, 246

using to create button, 239-243

using to prompt user, 234-239

plus, 109

PRIMARY, 202, 209

primary key, 2, 5-6

PRIMARY KEY constraint, 40

using, 42-43

prompts, creating, 234-243

Q

qualification, 64, 146-147

query, 15, 18, 62

see also sub-query

creating, 230-231

enabling blocked, 33-34

fixed, 254-259

in Access projects, 310-311

on the fly, 253-259

parameter, see parameter query

union, 167

viewing as SQL statement, 257-258

Query Design view, 20-21, 62-63

Query Wizard, see Microsoft Access

Query Wizard

R

REAL data type, 36

records,

copying, 51-53

deleting, 58

filtering through view, 197-198

inserting into table, 48-51

updating, 54-57

updating through view, 198-199

recordset, 253

filtered, 259-265

for subforms, 265-270

referential integrity, 2, 6

relational database, 2, 4

relationship, 2, 7

types of, 8-9

report filters, 270-273

result set, 62

sorting, 68-74

RIGHT (), 116

RIGHT JOIN, 146, 160-163

right outer join, 160-163, 314

ROUND (), 128

row, 2, 4

RTRIM (), 116

S

SECOND (), 123

SELECT INTO FROM, 53, 223

SELECT statement, 32, 62-67, 220

self join, 153-156

SELF JOIN, 146

server, 2, 11

SET, 54-57

SMALLINT data type, 36

SOME, 176, 183

SPACE (), 128

SQL, see Structured Query Language

SQL script,

integrating in ASP code, 275-277

integrating in VBA code, 253-276

SQL Server, 294-296

SQL view, 20-21, 63

switching to in Microsoft Access 2003, 25-28

switching to in Microsoft Access 2007, 23-25

statements, 15, 18

STDEV (), 128

STDEVP (), 129

stored procedures, 310-311, 316-317

string data types, 36-37

string functions, 106, 116

string operators, 116

Structured Query Language, 15-16

components of, 17-18

syntax, 18

versions, 16-17

subquery, 176-190

see also query

correlated, 175-176

nested, 186-189

non-correlated, 175-176

using to retrieve value, 189-190

SUM (), 112-113

syntax, 16, 18

T

tables, 2, 4-5

adding column to, 202-203

adding constraints to, 45-47

creating, 34-36

deleting, 207

deleting column from, 206-207

inserting dates into, 124-125

inserting records into, 48-51

temporary, see temporary tables

tables (database examples)

Activities table, creating, 353-354

Activities table, working with, 124-125

Committee1 table, creating, 354-355

Committee1 table, working with, 167-172

Committee2 table, creating, 355-356

Committee2 table, working with, 80-83, 167-172

Computers table, creating, 356

Computers table, working with, 91-95, 118-121, 192-195, 216-219

Customers table, creating, 356-357

Customers table, working with, 148-152, 155-166, 177-180, 195-196, 256-257, 281-282

Customers2 table, creating, 357-358

Departments table, creating, 359

Employees table, creating, 359-360

Employees table, working with, 71-74, 153-155

Friends table, creating, 360-361

Friends table, working with, 102-103

Manufacturers table, creating, 43, 361-362

Manufacturers table, working with, 44-45, 76-79

Numbers table, creating, 362

Numbers table, working with, 107-108, 110-111, 113-115, 129-130, 203, 205-206

Products table, creating, 363-364

Products table, working with, 156-159, 184-186, 189-190, 209-210, 213

Sales table, creating, 364

Sales table, working with, 56-57, 177-188, 238-239

Tools table, creating, 365-366

Tools table, working with, 96-102, 230-238

Toys table, creating, 40-41, 366

Toys table, working with, 42, 44, 48-51, 54-56, 65-67, 69-70, 72, 75, 83-84

Transactions table, creating, 367

Transactions table, working with, 135-137, 139, 141-143, 148-152, 157-166, 195-196

temporary tables, 215-216

accessing, 219-220

copying records from, 223

creating, 218-219

deleting, 224

indexing, 221

querying, 220-221

updating, 222

using, 224-227

TEXT data type, 37

TIME (), 123  127-128

TIMESERIAL (), 123

TINYINT data type, 36

TOP, 62, 76-78

TOP PERCENT, 62, 78-79

TRIM (), 116, 118-119

TRUNCATE (), 129

U

UCASE (), 116, 118-119

UNION, 146, 167-171

UNION ALL, 146, 171-172

union query, 167

UNIQUE, 202, 209-210

UNIQUE constraint, 40

using, 45-46

UNIQUEIDENTIFIER data type, 37

UPDATE statement, 32, 54-57, 197, 222

V

VALUES keyword, 48

VAR (), 129

VARP (), 129

VBA, 253-254, 276

integrating SQL script in, 253-273

views, 191, 215-216

creating, 192-196, 216

deleting, 199-200

filtering record through, 195-196

querying, 199

saving, 217-218

updating record through, 198-199

virtual table, 191

Visual Basic for Applications, see VBA

W

WEEKDAY (), 123

WHERE clause, 87-89

with the HAVING clause, 142-143

wildcard characters, 87, 95-96

WITH clause,

options for, 208-209

using, 209-210

Y

YEAR (), 123, 127-128

Yes/No data type, 37