اموزش کامل Access 2010-En

[A M I R]

[h=2]Sorting and ******ing Records

[h=4]To ****** Numbers by a Search Term:
Click the drop-down arrow next to the field you would like to ****** by. We want to ****** the

records in our menu items table by price, so we'll click the arrow in the Price field.

In the drop-down menu, hover your mouse over the words Number ******s. From the list that appears, select

the way you would like the ****** to match your search term. In this example, we want to create a ****** that will

show us inexpensive items only. We want to see items that are under a certain price, so we'll select Less Than.

The Custom ****** dialog box will appear. Type in the number or numbers you would like to use in your

******. We'll type "5" so that the ****** will show us only menu items that cost $5 or less.

Click OK. The ****** will be applied.

Specific types of numbers may include other ******ing options. For instance, dates stored in

numerical form (mm/dd/yy-- e.g., 12/01/2010) include options to ****** by periods of time.

​

 

[A M I R]

[h=2]Sorting and ******ing Records

[h=3]Challenge!
If you haven't already, download our sample database and open it.

Open the query called Cakes and Pies Sold.

Apply a ****** to the Product Types field that shows only Cakes.

In the Sum of Quantity field, apply a ****** that only shows numbers greater than five.

Apply an ascending sort to the Sum of Quantity field.

Save the database.
​

 

[A M I R]

[h=2]Designing a Query

[h=3]Introduction

The real power of a relational database is in the ability to quickly retrieve and analyze your data by running a query. Queries

allow you to pull information from one or more tables based on a set of search conditions you define.


In this lesson, you will learn how to create a simple one-table query. Then you will learn how to plan and run a slightly more complex multi-table query.

​

 

[A M I R]

[h=2]Designing a Query

[h=3]What are Queries?

Queries are a way of searching for and compiling data from one or more tables. Running a query is like asking

a detailed question of your database. When you build a query in Access, you are defining specific search

conditions to find exactly the data you want.




​

 

[A M I R]

[h=2]Designing a Query

[h=4]How are Queries Used?

Queries are far more powerful than the simple searches or ******s you might use to find data within a table. This

is because queries can draw their information from multiple tables. For example, while you could use a search

in the customers table to find the name of one customer at your business or a ****** on the orders table to view

only orders placed within the past week, neither of those would let you view both customers and orders

at once. However, you could easily run a query to find the name and phone number of every customer

who's made a purchase within the past week. A well-designed query can give information that you

might not be able to find out just by examining the data in your tables.




When you run a query, the results are presented to you in a table, but when you design one, you use a

very different view. This is called Query Design view, and it lets you see how your query is put together.

​

 

[A M I R]

[h=2]Designing a Query

[h=3]Query Views

Click the View drop-down arrow to switch to another view of your query. In most cases, you

will only need to use two main views: Datasheet View and Design View.
​

Datasheet View lets you view your query results in the form of a table. Design

View, featured here, allows you to view and modify the design of your query.
​

​

 

[A M I R]

[h=2]Designing a Query

[h=3]Run Query

After you have designed your query, click the Run Query command to view the results of the query in a table.


​

​

​

 

[A M I R]

[h=2]Designing a Query

[h=3]The Object Relationship Pane

All of the tables you choose to include in your query will appear as small windows in the

Object Relationship Pane. Each window contains a list of every available field within that table.
​

​

 

[A M I R]

[h=2]Designing a Query

[h=3]The Design Grid

The bottom part of the Query Design View is called the Design grid. It contains a table

that lists all the fields included in the query. Within this table, you can set criteria to specify which information the query should retrieve.
​

​

 

[A M I R]

[h=2]Designing a Query

[h=3]Field and Table Names

The first row of the Design Grid contains the names of the fields included in the query. Directly

beneath each field name is the name of the table that field belongs to.
​

​

 

[A M I R]

[h=2]Designing a Query

[h=3]Sorting

You can sort the data retrieved by a query. Simply click in the Sort: row of the field you wish

to sort, and select either an Ascending or Descending sort. By default, query results are not sorted.
​

​

 

[A M I R]

[h=2]Designing a Query

[h=3]Showing or Hiding Fields

You may wish to include fields in the design of your query, but hide them in the query results. To

hide a field, simply uncheck the checkbox in the Show: row of that field.
​

​

 

[A M I R]

[h=2]Designing a Query

[h=3]Query Criteria

Entering query criteria lets you specify exactly what type of information you want your

query to retrieve. Simply type the desired criteria in the Criteria: row of the field you wish to search.
​

Here, the criteria have been set so that the query will search for records with "Raleigh" in the

City field, and zip codes that are not "27610", "27615", or "27616" in the Zip Code field.
​

​

 

[A M I R]

[h=2]Designing a Query

[h=3]One-Table Queries

Let's familiarize ourselves with the query-building process by building the simplest query possible: a one-table query.


We will run a query on the Customers table of our bakery database. Imagine that our bakery is having a special

event, and we want to invite our customers who live nearby, since they are the most likely to come. This means

we need to see a list of all the customers who live close by, and only those customers.


If you think this sounds a little like applying a ******, you're right. A one-table query is actually just an advanced ****** applied to a table.

​

 

[A M I R]

[h=2]Designing a Query

[h=4]To Apply a Simple One-Table Query:
Select the Create tab on the Ribbon and locate the Queries group.

Select the Query Design command.

Access will switch to Query Design view. In the Show Table dialog box that appears, select the table you

would like to run a query on. Click Add, then click Close. We are running a query about our customers, so we will add the Customers table.

The selected table will appear as a small window in the Object Relationship Pane. In the table window, double-click

the field names you would like to include in your query. They will be added to the Design Grid in the bottom part of the screen.

In our example, we want to mail invitations to customers who live in a certain area, so we'll include the first and

last name, street address, city, state, and zip code fields. We aren't planning on calling or emailing our

customers, so we don't have to include the telephone or email fields.

Set the search criteria by clicking on the cell in the Criteria: row of each field you would like to ******. Typing

criteria into more than one field in the Criteria: row will set your query to include only results that meet all the

criteria. If you want to set multiple criteria, but don't need the records shown in your results to meet them all, type

the first criteria in the Criteria: row and additional criteria in the Or: row and the rows beneath it.

For this one-table query, we'll use very simple search criteria.

We want to find our customers who live in a city called Raleigh, so in our City field, we'll

type "Raleigh." Typing "Raleigh" in quotation marks will retrieve all records with an exact match for "Raleigh" in the City field.

• Some customers who live in the suburbs live fairly close by, and we'd like to invite them as well. We'll add

• their zip code, 27513 as another criteria. Since we want to find customers who either live in Raleigh or

• the 27513 zip code, we'll type "27513" in the or: row of the Zip Code field.

After you have set your criteria, run the query by clicking the Run command on the Query Tools Design tab.

The query results will be displayed in the query's Datasheet View, which looks like a table. If desired, save

your query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type in the desired name and click OK.

​

 

[A M I R]

[h=2]Designing a Query

[h=3]Designing a Multi-Table Query

Queries can be hard to understand and build if you don't have a good idea of what you're trying to find and how to

find it. A one-table query can be simple enough to make up as you go along, but to build anything more powerful, you'll need to plan the query in advance.





​

 

[A M I R]

[h=2]Designing a Query

[h=4]Planning a Query

When planning a query that uses more than one table, you should go through these four steps:


Pinpoint exactly what you want to know. If you could ask your database any question, what would it be? Building

a query is more complicated than just asking a question, but knowing precisely what question you want to answer is essential to building a useful query.



Identify every type of information you want included in your query results. Which fields contain this information?

Locate the fields you want to include in your query. Which tables are they contained in?

Determine the criteria the information in each field needs to meet. Think about the question you asked in the first step. Which

fields do you need to search for specific information? What information are you looking for? How will you search for it?



This process might seem abstract at first, but as we go through the process of planning our own multi-table

query, you should start to understand how planning your queries can make building them a lot easier.

​

 

[A M I R]

[h=2]Designing a Query

[h=3]Planning Our Query
Let's go through this planning process with a query we'll run on our bakery database. As you read through the planning

process step-by-step, think about how each part of the planning process could apply to other queries you might run.

​

 

[A M I R]

[h=2]Designing a Query

[h=4]Pinpointing the Question We Want to Ask

Our bakery database contains many customers, some of whom have never placed an order, but who

are in our database because they signed up for our mailing list. Most of them live within the city limits, but

others live out of town or even out of state! We want to get our out-of-town customers who've placed orders

in the past to come back and give us another try, so we're going to mail them some coupons. We don't actually

want our list to include customers who live too far away-- sending a coupon to someone who doesn't live in our

area probably won't make them come in. So really, we just want to find people who don't live in our city, but still live in our area.

In short, the question we want our query to answer is this: Which customers live in our area, are

outside the city limits, and have placed an order at our bakery?
​

 

[A M I R]

[h=2]Designing a Query

[h=4]Identifying the Information We Need



What information might we want to see in a list of information about these customers? Obviously, we'll need the

customers' names and their contact information-- their addresses, phone numbers, and email addresses. But

how are we going to know if they've placed orders? Each record of an order identifies the customer who placed that

order. If we include the order ID numbers, we should be able to narrow our list down to only customers who have previously placed orders.

​