Access 2003 in Pictures

Chia sẻ: vusuakhonghat

The guild line how to use Microsoft Access 2003. This book is provided under a Creative Commons license at: creativecommons.org/licenses/by-nc-nd/2.5/ You are free to download, copy, and share this electronic book with others. However, it is illegal to sell this book, or change it in any way....

Bạn đang xem 20 trang mẫu tài liệu này, vui lòng download file gốc để xem toàn bộ.

Nội dung Text: Access 2003 in Pictures

Access 2003
In Pictures


by Tony Fowlie




www.inpics.net
Access 2003 In Pictures

Copyright



This book is provided under a Creative Commons license at:
creativecommons.org/licenses/by-nc-nd/2.5/

You are free to download, copy, and share this electronic book with others.

However, it is illegal to sell this book, or change it in any way.

If you’d like to sell or change it, just contact us at contact@inpics.net.


Trademarks and Disclaimer
Visibooks™ is a trademark of Visibooks, LLC. All brand and product names in this book
are trademarks or registered trademarks of their respective companies.

Visibooks™ makes every effort to ensure that the information in this book is accurate.
However, Visibooks™ makes no warranty, expressed or implied, with respect to the
accuracy, quality, reliability, or freedom from error of this document or the products
described in it. Visibooks™ makes no representation or warranty with respect to this
book’s contents, and specifically disclaims any implied warranties or fitness for any
particular purpose. Visibooks™ disclaims all liability for any direct, indirect,
consequential, incidental, exemplary, or special damages resulting from the use of the
information in this document or from the use of any products described in it. Mention of
any product does not constitute an endorsement of that product by Visibooks™. Data
used in examples are intended to be fictional. Any resemblance to real companies,
people, or organizations is entirely coincidental.

ISBN 1597061018
Table of Contents
Database Basics ............................................. 1
Create a new database ..........................................................................2
Create tables ........................................................................................13
Create records......................................................................................21
Create forms .........................................................................................28
Create queries ......................................................................................40
Create reports ......................................................................................47


Working with Tables ..................................... 59
Modify tables ........................................................................................60
Create new tables.................................................................................78
Specify data types................................................................................82
Specify field properties .......................................................................97
Edit records ........................................................................................100
Find records .......................................................................................104
Sort and filter records........................................................................109
Create table relationships .................................................................115




TABLE OF CONTENTS i
Working with Forms ....................................123
Modify forms ...................................................................................... 124
Add/delete records ............................................................................ 138
Edit records........................................................................................ 142
Find records....................................................................................... 145
Filter records...................................................................................... 148


Working with Queries ..................................151
Create queries.................................................................................... 152
Sort results......................................................................................... 159
Add criteria......................................................................................... 162
Employ Boolean operators ............................................................... 165
Find duplicate records ...................................................................... 171
Create Update queries....................................................................... 179
Create Delete queries ........................................................................ 187


Working with Reports..................................195
Format reports ................................................................................... 196
Create mailing labels......................................................................... 213




ii TABLE OF CONTENTS
Database Basics
In this section, you’ll learn how to:

• Create a new database
• Create tables
• Create records
• Create forms
• Create queries
• Create reports




DATABASE BASICS 1
Create a new database
1. Start Microsoft Access 2003.

Your screen should look like this:




2 DATABASE BASICS
2. In the Getting Started pane, click Create a new file.




DATABASE BASICS 3
3. When the New File pane appears, click Blank Database.




4 DATABASE BASICS
4. When the File New Database window appears, create a new
folder in the My Documents folder called Practice Access
Files.




Tip: To create a new folder, double-click the My Documents
folder so it appears in the Save in drop-down list.

Then click the icon.




DATABASE BASICS 5
5. Double-click the Practice Access Files folder.

It should appear in the Save in box.




6 DATABASE BASICS
6. In the File name box, type:

Friends.mdb




Tip: The file extension for Access databases is .mdb.

Just like Word files are something.doc, and Web pages are
somethingelse.html, Access databases are database.mdb.

MDB stands for “Microsoft DataBase.”




DATABASE BASICS 7
7. Click the button.

The window for the Friends database should open:




8 DATABASE BASICS
Identify database elements

Elements of databases

A database stores information in an organized way, and makes it easy
to get information in and out.

Tables store data within the database.

Forms make it easy to put data into tables.

Queries pull out specific data.

Reports put data in an easily-read format.


Form Query




Table Table



Report




DATABASE BASICS 9
1. In the Objects list, click Tables.




2. Click Queries.




10 DATABASE BASICS
3. Click Forms.




4. Click Reports.




DATABASE BASICS 11
5. Click Tables.

6. Click the button.




The Friends database window should expand to fill the screen:




12 DATABASE BASICS
Create tables
1. Double-click Create table by entering data.




A blank table should open:




DATABASE BASICS 13
Name fields

1. Double-click the Field 1 column header.




2. Type:

First Name

3. Press the ENTER key on your keyboard.

The column header should look like this:




14 DATABASE BASICS
4. Double-click the Field2 column header.




5. Type:

Last Name

It should look like this:




6. Press the ENTER key on your keyboard.

7. Double-click the Field3 column header, type:

City

then press ENTER.




DATABASE BASICS 15
8. Double-click the Field4 column header, type:

Zip

then press ENTER.

9. Double-click the Field5 column header, type:

Phone Number

then press ENTER.

The table should now look like this:




16 DATABASE BASICS
Delete unused fields

1. Right-click the Field6 column header.




2. When the menu appears, click Delete Column.




DATABASE BASICS 17
3. When the alert window appears, click the button.




4. Right-click the Field7 column header.

When the menu appears, click Delete Column.

When the alert window appears, click the button.

5. Delete the Field8, Field9, and Field10 columns the same way.

The table should now look like this:




18 DATABASE BASICS
6. On the Menu Bar, click File, then Save.




7. When the Save As window appears, type:

Friends of Mine

in the Table Name box.




8. Click the button.




DATABASE BASICS 19
9. When the alert window that reads There is no primary key
defined appears, click the button.




Access will insert an ID field—the Key field—in the table:




The key field
10.
When the alert window popped up, and you clicked the Yes button,
Access added the ID field to the table.

The ID field is now the table’s primary key, or key field. That means it
can’t contain any duplicates.

Every table should have a key field.

For example, if a hospital keeps a database, each patient can have a
unique ID number in the key field.

That way, if it has more than one patient named John Baker, it can
easily distinguish John Baker, ID #326 in for a checkup, from John
Baker, ID #298 who needs his gall bladder removed.




20 DATABASE BASICS
Create records
1. Click in the box under the First Name column header.




2. Type:

Elvis

3. Press the TAB key on your keyboard.

The table should now look like this:




4. Type:

Presley

then press the TAB key.

5. Type:

Baltimore

then press TAB.




DATABASE BASICS 21
6. Type:

21212

then press TAB.

7. Type:

4105551212

then press TAB.

The table should now look like this:




Tip: Notice how the cursor in the row selector has moved down
to the second (new) record. When you move on to a new record,
Access automatically saves the previous record.




22 DATABASE BASICS
Add new fields

1. Right-click the Zip column heading.




2. When the menu appears, click Insert Column.




The table should now look like this, with a new blank field:




3. Double-click the column heading and type:

State

4. Press the ENTER key.



DATABASE BASICS 23
5. Click inside the new State field for the first record.




6. Type:

MD

7. Press TAB until the cursor moves down to a new record.




Record number 1 is saved and complete.




24 DATABASE BASICS
Move fields

1. Click the Phone Number column heading.




The entire column should be selected.




2. Place the cursor on the Phone Number column heading.

Then drag the column so the cursor rests between the Last
Name and City columns.




3. When you see a thick black line between the two columns,
release the mouse button.




The Phone Number column should now rest between the Last
Name and City columns:




DATABASE BASICS 25
4. On the Menu Bar, click File, then Close.




5. When prompted to save the changes to the table layout, click the
button.




26 DATABASE BASICS
The Friends database window should now look like this:




DATABASE BASICS 27
Create forms
1. In the Objects list, click Forms.




2. Double-click Create form by using wizard.




28 DATABASE BASICS
3. When the Form Wizard window appears, click the button.




DATABASE BASICS 29
All the table fields should be added to the form:




4. Click the button.




30 DATABASE BASICS
5. When the next screen appears, leave Columnar selected, then
click the button.




DATABASE BASICS 31
6. When the next screen appears, make sure Standard is selected,
then click the button.




32 DATABASE BASICS
7. When the last screen appears, type:

Friends of Mine Data Input Form

in the box.




DATABASE BASICS 33
8. Click the button.

The form should open and look like this:




34 DATABASE BASICS
Add a new record

1. In the form window, click the button.




A blank record should appear:




DATABASE BASICS 35
2. Press TAB to advance to the First Name box.




3. Type:

Bo

then press the TAB key.

4. Type:

Diddley

then press the TAB key.

5. Type:

Richmond

then press the TAB key.

6. Type:

VA

then press the TAB key.




36 DATABASE BASICS
7. Type:

23220

then press the TAB key.

8. Type:

8005557890

The form should now look like this:




DATABASE BASICS 37
9. Press the TAB key again.

The form should progress to a new, blank record:




The old record has been saved.




38 DATABASE BASICS
10. On the Menu Bar, click File, then Close to return to the database
window.




DATABASE BASICS 39
Create queries
What’s a query?

A query is a way to get specific information from the database.

Essentially, it’s a question. You use queries to ask the database things
like, “Who are my customers in Montana?”, or “How many pipe fittings
have I sold this month?”



1. In the Objects list, click Queries.




40 DATABASE BASICS
2. Double-click Create query by using wizard.




3. When the Simple Query Wizard opens, double-click First
Name in the Available Fields list.




DATABASE BASICS 41
First Name should appear in the Selected Fields column:




42 DATABASE BASICS
4. Click the button.

This should add the Last Name field to the Selected Fields list:




DATABASE BASICS 43
5. Double-click Phone Number.




44 DATABASE BASICS
This should add the Phone Number field to the Selected Fields
list:




6. Click the button.




DATABASE BASICS 45
7. When the final screen appears, type:

Names and Numbers




8. Click the button.

The query is automatically saved and executed.

It should look like this:




9. On the Menu Bar, click File, then Close to return to the database
window.




46 DATABASE BASICS
Create reports
1. In the Objects list, click Reports.




2. Double-click Create report by using wizard.




DATABASE BASICS 47
3. When the Report Wizard window appears, click the
Tables/Queries drop-down arrow.

When the list appears, click Table: Friends of Mine.




48 DATABASE BASICS
4. Click the button to move all the fields into the Selected
Fields list.




DATABASE BASICS 49
5. In the Selected Fields list box, click ID, then click the
button.

The ID field should be removed.

The report wizard should now look like this:




6. Click the button.




50 DATABASE BASICS
7. When the next screen appears, click the button.




DATABASE BASICS 51
8. When the next screen appears, click the first drop-down arrow,
then click State in the list.




9. Click the button.




52 DATABASE BASICS
Choose layout and style

1. When the next screen appears, make sure Tabular is checked in
the Layout section.




Then click the button.




DATABASE BASICS 53
2. When the next screen appears, click Soft Gray in the list of
available styles.




Then click the button.




54 DATABASE BASICS
View the report

1. When the final screen appears, type:

My Friends

in the box.




Then click the Preview the report radio button.




DATABASE BASICS 55
2. Click the button.

The report is automatically created, saved and opened.

It should look like this:




3. On the Menu Bar, click File, and then Close to return to the
Friends database window.




56 DATABASE BASICS
4. On the Menu Bar, click File, then Exit to close Access.




DATABASE BASICS 57
58 DATABASE BASICS
Working with Tables
In this section, you’ll learn how to:

• Modify tables
• Create new tables
• Specify data types
• Specify field properties
• Edit records
• Find records
• Sort and filter records
• Create table relationships




WORKING WITH TABLES 59
Modify tables
1. Open a web browser and go to:

www.inpics.net/books/acc2003

2. Right-click the FoodStore1.mdb link.

When the menu appears, click Save Target As.

3. When the Save As window appears, open the Practice Access
Files folder on your hard drive.

Then click the button.




60 WORKING WITH TABLES
4. Wait for the database to download completely, then close the
web browser.




WORKING WITH TABLES 61
Open an existing database

1. Start Microsoft Access.

2. In the Getting Started pane, click More.




62 WORKING WITH TABLES
3. Open the Practice Access Files folder, then double-click
FoodStore1.mdb.




WORKING WITH TABLES 63
4. When the database opens, click the button in its window.

The FoodStore1 database window should now look like this:




64 WORKING WITH TABLES
Employ an input mask

1. Make sure Tables is selected, then right-click the Customers
table.




WORKING WITH TABLES 65
2. When the menu appears, click Design View.




66 WORKING WITH TABLES
The Customers table should open in Design View.




3. Click in the Phone Number row.




WORKING WITH TABLES 67
4. In the Field Properties area of the window, click in the Input
Mask property.




5. Click the button beside the Input Mask property.




68 WORKING WITH TABLES
6. When the Input Mask Wizard window appears…




…make sure the Phone Number input mask is selected.

Then click in the Try It: box.




WORKING WITH TABLES 69
7. In the Try It box, type:

2125551515




8. Click the button.




70 WORKING WITH TABLES
9. When the next screen appears, click the button.




WORKING WITH TABLES 71
10. When the next screen appears, click the radio button in front of
With the symbols in the mask, like this.




Then click the button.




72 WORKING WITH TABLES
11. When the final screen appears, click the button.




The Input Mask for the Phone Number field should now look like
this:




WORKING WITH TABLES 73
12. On the Menu Bar, click View, then Datasheet View.




13. When the alert window appears, click the button to save
the Customers table.




14. Use the TAB key to move across the first record until you are in
the Phone Number field.




74 WORKING WITH TABLES
15. Type:

3015551212




16. Press the TAB key four times to move to the next record.

The new phone number has been saved.

17. On the Menu Bar, click File, then Close.

You should return to the FoodStore1 database window.




WORKING WITH TABLES 75
Adjust table layout

1. Double-click the Customers table to open it.




2. Place your cursor on the divider between the Company Name
and Address 1 column headings.




76 WORKING WITH TABLES
The cursor should turn into a double-headed arrow:




3. Double-click.

The Company Name column should resize to fit the widest
piece of data in that field:




Tip: Instead of double-clicking, you can also drag column
heading dividers to set column widths manually.

4. Double-click the divider between the Address 1 and Address 2
column headings.

The Address 1 field should resize to fit the data in it.

5. Click the window’s button to close the Customers table.

6. When the alert window appears, click the button to save
changes.




WORKING WITH TABLES 77
Create new tables
1. Double-click Create table in Design view.




78 WORKING WITH TABLES
A blank table will open in Design View:




WORKING WITH TABLES 79
2. In the first box in the Field Name column, type:

Order ID

then press the TAB key.

It should look like this:




3. On the Toolbar, click the icon.




80 WORKING WITH TABLES
4. When the Save As window appears, type:

Orders

in the Table Name box.




5. Click the button.

6. When the alert window appears, click the button.




Tip: You’ll assign a Primary Key later.




WORKING WITH TABLES 81
Specify data types
Assign the AutoNumber data type

1. In the Data Type column beside the Order ID field, click the
drop-down arrow.

When the list appears, click AutoNumber.




2. In the Description column, type:

This is the Generic Order ID Number

then press TAB.




Tip: Filling in a Description is optional, but it helps you to
remember what sort of information is supposed to be stored in a
field.


82 WORKING WITH TABLES
Assign the Date/Time data type

1. In the Field Name column, in the second row, type:

Order Date

then press TAB.




2. In the Data Type column, click the drop-down arrow.




3. When the menu appears, click Date/Time, then press TAB.




WORKING WITH TABLES 83
4. In the Description column, type:

Date the order was placed

then press TAB.




84 WORKING WITH TABLES
Employ the Lookup Wizard

What does the Lookup Wizard do?

The Lookup Wizard allows you to create a field where database
users can choose from a list of things.

This list can come from a table or query, or list items can be specified
manually.



1. In the Field Name column, type:

Customer

then press TAB.

2. In the Data Type column, click the down arrow, then Lookup
Wizard …




WORKING WITH TABLES 85
3. When the Lookup Wizard appears, click the I want the lookup
column to look up the values in a table or query radio button.




Then click the button.




86 WORKING WITH TABLES
4. Click the Customers table, then click the button.




5. When next screen appears, double-click First Name.




WORKING WITH TABLES 87
6. Double-click Last Name.




7. Click the button.




88 WORKING WITH TABLES
8. Click the first drop-down arrow, then Last Name.




WORKING WITH TABLES 89
9. Click the button.

The Lookup Wizard should show how the field will look with data
in it:




10. Click the button.




90 WORKING WITH TABLES
11. In the label box, type:

Customer Name




Then click the button.

12. When the alert window appears, click the button.




13. In the Description column, type:

Name of the customer who placed the order

then press TAB.




WORKING WITH TABLES 91
14. On the Toolbar, click the icon.

The Orders table should look like this:




15. On the Toolbar, click the icon.




The table should change to Datasheet View:




92 WORKING WITH TABLES
16. Click in the Customer Name field, then click the drop-down
arrow.




A list of customers should appear:




17. On the Toolbar, click the icon.




WORKING WITH TABLES 93
The table should change back to Design View:




94 WORKING WITH TABLES
Assign a Primary Key

1. Right-click the Order ID field.




2. When the menu appears, click Primary Key.




The field should now show a key beside it:




WORKING WITH TABLES 95
3. On the Menu Bar, click File, then Save.




96 WORKING WITH TABLES
Specify field properties
1. Click in the Order Date field.




2. In the Field Properties section of the window, click in the
Format box.




3. Click the drop-down arrow at the end of the box.




WORKING WITH TABLES 97
4. In the menu of formats that appears, click Short Date.




The Order Date field should now look like this:




98 WORKING WITH TABLES
5. On the Toolbar, click the icon.

The table design window should now look like this:




6. On the Toolbar, click the icon.

The table should look like this:




7. On the Menu Bar, click File, then Close to return to the
FoodStore1 database window.



WORKING WITH TABLES 99
Edit records
1. Open the Customers table.

Make sure it’s displayed in Datasheet View.

2. Click inside the Address 1 field for record number 3, between
the two fives in 35546 York Ave.




3. Press the BACKSPACE key on your keyboard.

The first 5 in the address should be gone:




4. Press TAB to move to the next field.




100 WORKING WITH TABLES
5. Type:

Unit L

It should replace the previous text:




6. Click in any other record.

Tip: Remember—changed data in a record is saved when you
move off that record.

The Customers table should now look like this:




WORKING WITH TABLES 101
Delete records
1. Right-click the row heading for Chris Happ’s record.




2. In the menu that appears, click Delete Record.




102 WORKING WITH TABLES
3. When the alert window appears, click the button.




Tip: Once a record has been deleted from the database, it
cannot be recovered. There is no Undo feature in Access.




WORKING WITH TABLES 103
Find records
Navigate records
1. In the Customers table window, click the button.




The last record in the database should be highlighted:




104 WORKING WITH TABLES
2. Click the button.

The sixth record should be highlighted.

3. Click the button again.

The fifth record should be highlighted.

4. Click the button.

The first record should be highlighted.




5. Click the button.

The second record should be highlighted.

6. Click the button.

The Customers table should look like this:




WORKING WITH TABLES 105
Search for records

1. Click the Company Name column header.




The table should look like this:




2. On the Toolbar, click the icon.




106 WORKING WITH TABLES
3. When the Find and Replace window appears, type:

BBC

in the Find What box.




4. In the Match list, click the drop-down arrow, then Any Part of
Field.




WORKING WITH TABLES 107
5. Click the button.

The Customers table should now look like this, with BBC
highlighted:




6. In the Find and Replace window, click the button.




108 WORKING WITH TABLES
Sort and filter records
Sort in descending order

1. Click the State column header.




The table should look like this:




2. On the Toolbar, click the icon.




WORKING WITH TABLES 109
The Customers table should now look like this, with the States
arranged in descending alphabetical order:




110 WORKING WITH TABLES
Sort in ascending order

1. Click the Customer ID column header to highlight the column.




2. On the Toolbar, click the icon.




The Customers table should now look like this, with the
customer ID numbers arranged in ascending order:




WORKING WITH TABLES 111
Filter records

1. Click in the State field for customer number 1.




Notice that the State is Florida.

2. On the Toolbar, click the icon.




The Customers table should now look like this, showing all
customers whose State is Florida:




112 WORKING WITH TABLES
3. On the Toolbar, click the icon.

The Customers table should show its original contents:




4. On the Menu Bar, click File, then Close to return to the
FoodStore1 database window.

5. When the alert window appears, click the button.




WORKING WITH TABLES 113
When to filter

Filtering records works like a query, bringing up specific information
from a table.

But unlike a query, your filtering doesn’t remain part of the database.

When you perform a query and save it, the query remains with the
database, even after you close it. Filtering does not.

Since you can’t save filtering like a query, filter records when you need
quick results. Think of filtering as a temporary, quick, “on the spot”
query.




114 WORKING WITH TABLES
Create table relationships
What are “relationships?”

Relationships are links that associate a field in one table with a field in
another.

An example is a school database with two tables:

• Students
• Classes

The Students table holds students’ names and addresses:

Students
Student Name Address
John Elm St.
Jane Oak St.

The Math Class table holds information about the students in math
class:

Math Class
Student Name Grade
John A
Jane B

Both tables have a Student Name field.

By linking the Student Name fields, you make sure that John in the
Students table is the same John listed in the Math Class table:

Students Math Class
Student Name Address Student Name Grade
John John
Jane Jane




WORKING WITH TABLES 115
Examine current table relationships

1. On the Toolbar, click the icon.




2. On the Toolbar, click the icon.

3. When the Show Table window appears, double-click
Customers.




116 WORKING WITH TABLES
The Customers table should appear in the Relationships field:




4. In the Show Table window, double-click Employees.

The Employees table should appear in the Relationships field:




5. Double-click Order Details.

6. Double-click Orders.




WORKING WITH TABLES 117
7. Double-click Products.

8. In the Show Tables window, click the button.




The relationships window should now look like this:




118 WORKING WITH TABLES
Add relationships

1. Place the cursor on the Title Bar at the top of the Employees
table.




2. Click and drag the Employees table to place it under the
Customers table.




WORKING WITH TABLES 119
3. Click and drag the Orders table to place it between the
Customers table and Order Details table.




4. Click the Order ID field in the Order Details table, then drag it
and rest it on top of the Order ID field in the Orders table.




The Orders table should look like this:




120 WORKING WITH TABLES
5. Release the mouse button.

When the Edit Relationships window appears, click the
button.




The relationships window should now look like this:




The Order ID field in the Orders table is linked to the Order ID
field in the Order Details table.

The same Order ID number in both tables refers to the same
order.



WORKING WITH TABLES 121
6. On the Menu Bar, click File, then Close to return to the
FoodStore1 database window.

7. When the alert window appears, click the button.




8. On the Menu Bar, click File, then Exit.




122 WORKING WITH TABLES
Working with Forms
In this section, you’ll learn how to:

• Modify forms
• Add/delete records
• Edit records
• Find records
• Filter records




WORKING WITH FORMS 123
Modify forms
1. Start Access, then open the FoodStore1 database.

2. In the Objects list, click Forms.




3. Double-click Customer Form.




124 WORKING WITH FORMS
4. When Customer Form opens, click the icon on the
Toolbar.




Tip: The icon changes to Form View:

The form should look like this in Design View:




WORKING WITH FORMS 125
Add a field

1. On the Toolbar, click the icon.




A list of fields in the Customers table should appear:




126 WORKING WITH FORMS
2. Click and drag the Address 2 field from the Customers field list
to the form.

Place it between the Address 1 box and the City box:




WORKING WITH FORMS 127
3. Release the mouse button.

There should be a new box on the form:




4. Close the Field List window.




128 WORKING WITH FORMS
Change field position

1. Click the Address 2 box.




2. Position the cursor over the move handle of the Address 2 box.

It should turn into a black hand with a finger:




WORKING WITH FORMS 129
3. Click-and-drag the box to line it up with the other boxes.




Then release the mouse button.

The form should now look like this:




4. Position the cursor over the move handle of the Address 2 label.




130 WORKING WITH FORMS
5. Drag the label to line it up with the other labels.




Then release the mouse button.

The form should now look like this:




WORKING WITH FORMS 131
6. On the Toolbar, click the icon.




The form should now look like this:




7. On the Toolbar, click the icon to return to Design View.




132 WORKING WITH FORMS
Adjust size of fields

1. Click the Address 1 box.




2. Press and hold down the SHIFT key, then click the Address 2
text box.

Both objects should be selected:




WORKING WITH FORMS 133
3. On the Menu Bar, click Format, then Size, then To Widest.




The form should now look like this:




134 WORKING WITH FORMS
Change caption

1. Double-click the Address 1 label.




The Properties window should appear:




Tip: Access automatically creates names when something is
added to a form. Don’t worry if your computer has given the label
a name different from the one above.




WORKING WITH FORMS 135
2. Click the Format tab.




3. Click the Caption item to highlight it.




4. Type:

Street Address

then press ENTER.




136 WORKING WITH FORMS
5. On the Toolbar, click the icon.

The label should look like this:




6. On the Toolbar, click the icon to return to Form View.




WORKING WITH FORMS 137
Add/delete records
Add a new record

1. Click the button.




138 WORKING WITH FORMS
2. Fill in the following information:

Amy Preston
Preston Publishing
1214 North Ave.
Suite 118
Redmond, WA 98049
4255556113
4255557441
amy@prestonpub.com

Tip: Using the TAB key to move through the form doesn’t
access the field in the right sequence.

To correct this, click the icon to enter Design View.

Then on the Menu Bar, click View, then Tab Order.

3. When the Tab Order window appears, click and drag the row
headers to place the fields in correct order.




WORKING WITH FORMS 139
The record should look like this:




4. Click the button to save the record and advance to a new
one.




140 WORKING WITH FORMS
Delete records

1. Click the button to go back to record number 9, the one you
just added.

2. On the Toolbar, click the icon.

3. When the alert window appears, click the button.




WORKING WITH FORMS 141
Edit records
Navigate to a specific record number

1. Click in the Current Record box.




2. Highlight the current record number, then type:

2

3. Press the ENTER key.

The form should now show record number 2:




142 WORKING WITH FORMS
Edit data in a record

1. Delete the contents of the Street Address field.




2. Type:

476 New York Ave.




WORKING WITH FORMS 143
3. Press the TAB key.

The record should look like this:




144 WORKING WITH FORMS
Find records
1. Click the button.




2. Click in the Company Name box to place the cursor there.




3. On the Toolbar, click the icon.




WORKING WITH FORMS 145
4. When the Find and Replace window appears, type:

Metro-Corp

in the Find What box.




5. Click the button.

The window should look like this:




146 WORKING WITH FORMS
6. Click the button.




WORKING WITH FORMS 147
Filter records
1. Click in the State box.




148 WORKING WITH FORMS
2. On the Toolbar, click the icon.

The form should now look like this:




Tip: Notice the number of records now reads 1 of 1 (Filtered).

3. On the Toolbar, click the icon.

4. On the menu bar, click File, then Close.

When the Alert window appears, click the button.




WORKING WITH FORMS 149
The database window should now look like this:




5. On the Menu Bar, click File, then Exit to close Access.




150 WORKING WITH FORMS
Working with Queries
In this section, you’ll learn how to:

• Create queries
• Sort results
• Add criteria
• Employ Boolean operators
• Find duplicate records
• Create Update queries
• Create Delete queries




WORKING WITH QUERIES 151
Create queries
1. Start Access, and open the FoodStore1 database.

2. In the Objects list, click Queries.




3. Double-click Create query in Design view.




152 WORKING WITH QUERIES
The query Design View window should appear:




4. In the Show Table window, click Customers, then click the
button.




WORKING WITH QUERIES 153
5. Click the button.




This adds the Customers table to the query. The query window
should look like this:




154 WORKING WITH QUERIES
Add fields to query

1. Drag the Customer ID field from the Customers table to the first
column of the query design grid.




Release the mouse button.

The query design grid should look like this:




WORKING WITH QUERIES 155
2. Drag the First Name field and Last Name field to the design grid
the same way.

The design grid should look like this:




The query is now set up to show the Customer ID, First Name,
and Last Name of each customer in the Customers table.




156 WORKING WITH QUERIES
Run the query

1. On the Toolbar, click the icon.




The query window should now look like this:




WORKING WITH QUERIES 157
The query shows the Customer ID, First Name, and Last Name
of each customer in the Customers table:




158 WORKING WITH QUERIES
Sort results
1. On the Toolbar, click the icon to return to the query Design
View.




2. In the Last Name field, click in the Sort row.

When the drop-down arrow appears, click it, then click
Ascending.




WORKING WITH QUERIES 159
It should look like this:




3. On the Toolbar, click the icon.

The query should run, and sort the records alphabetically by
Last Name:




4. On the Toolbar, click the icon to return to the query Design
View.




160 WORKING WITH QUERIES
5. In the Customer Last Name field, click in the Sort row, then
click (not sorted).




It should look like this:




WORKING WITH QUERIES 161
Add criteria
1. In the query’s Customer table, scroll down to the State field.

2. Add the State field to the query design grid.




3. In the State field, click in the Criteria row.




162 WORKING WITH QUERIES
4. Type:

FL




5. Press the ENTER key.

It should look like this:




WORKING WITH QUERIES 163
Tip: Notice that Access automatically surrounded the FL
criterion with double quotes. That’s because the State field
stores text data. In database query language, double quotes
signify a string of text.

If this field held date data, Access would have surrounded it with
hash marks (#).

If the field stored number data, Access wouldn’t have added
anything.

6. On the Toolbar, click the icon.

The query results should look like this:




It has returned all customers who live in the State of FL.




164 WORKING WITH QUERIES
Employ Boolean operators
What are Boolean operators?

Boolean operators are expressions such as AND, OR, NEITHER, and
NOR that allow you to add multiple criteria to a query. They take their
name from George Boole, the mathematician who first used them.

If you had a T-shirt store with an Access database, for instance, and
you wanted to find out how many of your California customers had
ordered blue T-shirts, you’d employ the AND operator in your query:

customers from California
AND
who also bought blue T-shirts

If you wanted to see how many customers were from California (these
California customers could have bought T-shirts of any color),

and how many customers bought blue T-shirts (these blue T-shirt
customers could be from anywhere),

you’d employ the OR operator:

customers from California
OR
customers who bought blue T-shirts




WORKING WITH QUERIES 165
Employ the OR operator

1. Click the icon to return to the query Design View.

2. In the State field, click in the or row under the criterion “FL”.




3. Type:

MD

then press the ENTER key.

The design grid should look like this:




166 WORKING WITH QUERIES
Tip: The two common Boolean operators are AND and OR.
They’re easily confused. If you don’t know which to use, ask
yourself the purpose of the query:

Do I want to find customers with a state of both Florida AND
Maryland?

No—a customer can’t be in two places at once.

Do I want to find customers with a state of either Florida OR
Maryland?

That makes sense, so this query would use the OR operator.

4. Click the icon.

The query results should look like this:




WORKING WITH QUERIES 167
Employ the AND operator

1. Click the icon.

2. Remove the query criteria from the State field.

(Highlight them, then press the DELETE key.)

The Criteria row in the design grid should be blank:




3. Add the Company Name field to the query:

Drag it from the field list in the Customers table and drop it in
the blank field to the right of the State field in the design grid.




168 WORKING WITH QUERIES
4. In the Company Name field, click in the Criteria row.

5. Type:

A*

then press the ENTER key.

Tip: An asterisk (*) stands for any character or combination of
characters. For instance, Ap* would match Ape, Aptitude,
Apparent, etc.

6. Click the icon.

The query results should look like this:




The query shows all companies whose names begin with A.

7. On the Menu Bar, click File, then Save.




WORKING WITH QUERIES 169
8. When the Save As window appears, type:

Companies that begin with A

in the Query Name box.




9. Click the button.

10. On the Menu Bar, click File, then Close.

You should return to the FoodStore1 database window.




170 WORKING WITH QUERIES
Find duplicate records
1. Make sure Queries is selected in the Objects list.

2. On the Toolbar, click the icon.




3. When the New Query window appears, click Find Duplicates
Query Wizard.




Then click the button.




WORKING WITH QUERIES 171
4. When the next screen appears, click Table: Customers, then
click the button.




172 WORKING WITH QUERIES
5. When the next screen appears, double-click First Name, then
Last Name to add the fields to the query:




Then click the button.




WORKING WITH QUERIES 173
6. Click the button.

The screen should look like this:




Then click the button.




174 WORKING WITH QUERIES
7. When the final screen appears, type:

Find Duplicate Customers

in the name box.




8. Click the button.

The query should run, and look like this:




There are two identical entries for Jane Doe in the database.




WORKING WITH QUERIES 175
Remove duplicates

1. Click the row selector button for the Jane Doe record with the
Customer ID of 7.




2. On the Toolbar, click the icon.




176 WORKING WITH QUERIES
3. When the alert window appears, click the button.




The query results should now look like this:




Record number 7 is removed from the database.

Tip: Deleting a record from a query also deletes it from the table
it was stored in.




WORKING WITH QUERIES 177
Verify results

1. On the Menu Bar, click File, then Close.

2. In the Objects list, click Tables.




3. Double-click the Customers table.

It should now look like this, with only one record for Jane Doe:




4. Close the Customers table.




178 WORKING WITH QUERIES
Create Update queries
1. Double-click the Products table to open it.

2. Note the price (to customers) of French Truffles ($8.99) and Blue
Mountain Coffee ($9.99).




3. Note the cost (to the store) of French Truffles ($6.15) and Blue
Mountain Coffee ($8.00).




4. On the Menu Bar, click File, then Close to return to the
FoodStore1 database window.




WORKING WITH QUERIES 179
5. In the Objects list, click Queries.




6. Double-click Create query in Design view.




180 WORKING WITH QUERIES
7. When the Show Table window appears, click Products.




Then click the button.




WORKING WITH QUERIES 181
8. Click the button.

The query window should look like this:




9. On the Menu Bar, click Query, then Update Query.




182 WORKING WITH QUERIES
10. In the field list of the Products table, scroll down and double-
click the Price field.




The query design grid should now look like this:




11. In the Price column, click in the Update To row.




WORKING WITH QUERIES 183
12. Type:

[Cost]*1.25




Then press the ENTER Key.

This will raise prices for all products by 25%.

Tip: The square brackets [ ] tell Access that Cost is a field.

13. On the Toolbar, click the icon.

14. When the alert window appears, click the button.




Tip: No records will appear.

This is a type of Action query, which affects records directly,
rather than a Select query, which retrieves records.



184 WORKING WITH QUERIES
15. On the Menu Bar, click File, then Save.




16. When the Save As window appears, type:

Update Product Prices

in the Query Name box.




Then click the button.

17. On the Menu Bar, click File, then Close to return to the
FoodStore1 database window.

WORKING WITH QUERIES 185
Verify results

1. In the Objects list, click Tables.

2. Double-click the Products table.

It should open and look like this:




Note the updated price of French Truffles ($7.69) and Blue
Mountain Coffee ($10.00).

3. Click File, then Close to return to the FoodStore1 database
window.




186 WORKING WITH QUERIES
Create Delete queries
1. Double-click the Employees table.

It should open and look like this:




Note that Tom Gumman and Tina Gumman are currently
employees.

2. Click File, then Close to return to the FoodStore1 database
window.

3. In the Objects list, click Queries.

4. Double-click Create query in Design view.




WORKING WITH QUERIES 187
5. When the Show Table window appears, click Employees.




Then click the button.

6. Click the button.

7. On the Menu Bar, click Query, then Delete Query.




188 WORKING WITH QUERIES
The query window should now look like this:




WORKING WITH QUERIES 189
Add fields

1. In the field list of the Employees table, click-and-drag the * field
to the first column of the design grid.




2. Release the mouse button.

The query design grid should look like this:




190 WORKING WITH QUERIES
3. In the field list of the Employees table, double-click the Last
Name field.




The query design grid should now look like this:




WORKING WITH QUERIES 191
Add criteria

1. In the Last Name column of the design grid, click in the Criteria
row.




2. Type:

Gumman

then press the ENTER key.

It should look like this:




192 WORKING WITH QUERIES
3. On the Menu Bar, click the icon.

When the alert window appears, click the button.

Tip: Be careful when designing Delete queries. Once a record is
deleted, the deletion cannot be undone.

You may want to run the query as a regular query first, to verify
it’s choosing the correct records, and then switch the type to a
Delete query.

4. Click File, then Save.

5. When the Save As window appears, type:

Delete Old Employees

in the Query Name box.




6. Click the button.

7. Click File, then Close to return to the database window.




WORKING WITH QUERIES 193
Verify results

1. In the Objects list, click Tables.

2. Double-click the Employees table.

It should look like this:




Note that Tom Gumman and Tina Gumman are no longer listed
as employees.

3. Exit Microsoft Access.




194 WORKING WITH QUERIES
Working with Reports
In this section, you’ll learn how to:

• Format reports
• Create mailing labels




WORKING WITH REPORTS 195
Format reports
1. Open a web browser and go to:

www.inpics.net/books/acc2003

2. Right-click the FoodStore2.mdb link and save that database in
the Practice Access Files folder.




3. Close the web browser and start Access.

4. On the Toolbar, click the icon.




196 WORKING WITH REPORTS
5. Navigate to the Practice Access Files folder, then click
FoodStore2.mdb.




6. Click the button.




WORKING WITH REPORTS 197
7. Click the button in the database window.

The FoodStore2 database should look like this:




8. In the Objects list, click Reports.




198 WORKING WITH REPORTS
9. Double-click Customer Orders.




The Customer Orders report should look like this:




WORKING WITH REPORTS 199
10. Click the icon.

The report’s Design View should look like this:




200 WORKING WITH REPORTS
Edit label captions

1. Click the label with the caption Orders.




2. Click the label again.




Now the label can be edited.




WORKING WITH REPORTS 201
3. Edit the label to read Customer Orders.




Then press ENTER.

4. Click the CustomerID label in the Customer ID Header section.




5. Click it again.




202 WORKING WITH REPORTS
6. Edit the label to read Customer Name:




Then press ENTER.

It should look like this:




WORKING WITH REPORTS 203
7. Click the icon.

The report should look like this:




204 WORKING WITH REPORTS
8. Click on the report.

The view should zoom in:




WORKING WITH REPORTS 205
Change font size

1. Click the icon.

2. Place the cursor in the vertical ruler bar beside the Customer
Name label.




206 WORKING WITH REPORTS
3. Click the mouse button, then drag the cursor to just above the
Order ID Header section.

Release the mouse button.

Everything in the Customer ID Header section should be
selected:




WORKING WITH REPORTS 207
4. On the Formatting toolbar, click the down arrow next to the Font
Size box.

When the list appears, click 10.




The report should now look like this:




208 WORKING WITH REPORTS
5. Click the icon, then click on the report to zoom out.

The report should now look like this:




WORKING WITH REPORTS 209
Set page margins

1. On the Menu Bar, click File, then Page Setup.




210 WORKING WITH REPORTS
2. When the Page Setup window appears, type:

0.5

in the Top box, and

0.5

in the Bottom box.




Then click the button.




WORKING WITH REPORTS 211
The report should now look like this:




3. On the Menu Bar, click File, then Close.

4. When the alert window appears, click the button.




212 WORKING WITH REPORTS
Create mailing labels
1. On the toolbar, click the icon.




2. When the New Report window appears, click Label Wizard.




WORKING WITH REPORTS 213
3. In the drop down list, click the Customers table.




Then click the button.




214 WORKING WITH REPORTS
4. In the Filter by manufacturer box, click Avery.




5. In the list of labels, under Product number, click Avery USA
5160.




WORKING WITH REPORTS 215
6. Click the button.

7. Click the button again.




216 WORKING WITH REPORTS
8. In the Available Fields list, double-click First Name.




9. Press the SPACE bar on the keyboard.

10. Double-click Last Name, then press the ENTER key.

The label window should look like this:




WORKING WITH REPORTS 217
11. Double-click Company Name, then press ENTER.

It should look like this:




12. Double-click Street Address, then press the ENTER key.

13. Double-click City, then type a comma and a space on the
keyboard.

14. Double-click State, then type two spaces.

15. Double-click Zip, then press ENTER.

The label window should look like this:




16. Click the button.




218 WORKING WITH REPORTS
17. When the next screen appears, double-click Last Name.




Then click the button.




WORKING WITH REPORTS 219
18. When the next screen appears, type:

Shipping Labels

in the box.




220 WORKING WITH REPORTS
19. Click the button.

The shipping labels should look like this:




Tip: If you receive a warning box saying “Some data may not be
displayed,” you can click the OK button and ignore it. Your labels
should still look like the picture above.

20. Exit Microsoft Access.




WORKING WITH REPORTS 221
222 WORKING WITH REPORTS
Glossary
Boolean Operators
Boolean logic is a system of logical thought developed by George Boole.
Boolean operators allow you to construct complex queries which can be
understood by computers. The most common Boolean operators are “AND”
and “OR”.

Data
A series of facts. When data is organized, it becomes useful information,
which can be processed and used to draw conclusions.

Database
A collection of information organized into tables of data.

Field
A single unit of data stored as part of a database record.

Form
A database object primarily used to enter or display data.

Key Field
A field in a table that can contain no duplicates. The key field is each
record’s unique identifier.

Query
A set of questions presented to a database to retrieve specific information.

Record
One row in a given table. In a relational database, records correspond to
rows in each table.

Relationship
An association established between common fields in two tables.




GLOSSARY 223
Report
Presents information retrieved from a table or query in a preformatted,
easy-to-read way.

Select Query
A query that asks a question about the data stored in tables, and provides a
result in the form of a datasheet.

Table
A collection of rows and columns that organize data.

Update Query
A query that changes a set of records according to specified criteria.




224 GLOSSARY
Where To Get In Pictures Books

If you liked this book, and would like to buy more like it, visit:

www.inpics.net


In Pictures offers more than 20 titles on subjects such as:

• Computer Basics
• Microsoft Office
• Desktop Linux
• OpenOffice.org
• Web Site Layout
• Web Graphics
• Web Programming




In Pictures: computer books based on pictures, not text.




www.inpics.net
Đề thi vào lớp 10 môn Toán |  Đáp án đề thi tốt nghiệp |  Đề thi Đại học |  Đề thi thử đại học môn Hóa |  Mẫu đơn xin việc |  Bài tiểu luận mẫu |  Ôn thi cao học 2014 |  Nghiên cứu khoa học |  Lập kế hoạch kinh doanh |  Bảng cân đối kế toán |  Đề thi chứng chỉ Tin học |  Tư tưởng Hồ Chí Minh |  Đề thi chứng chỉ Tiếng anh
Theo dõi chúng tôi
Đồng bộ tài khoản