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

[A M I R]

[h=2]Designing a Query

[h=4]Locating the Tables that Contain the Information We Need

In order to write a query, you need to be pretty familiar with the different tables in your database. From working

extensively with our own database, we know that the customer information we need is located in fields in the

Customers table. Our Order ID numbers are in a field in the Orders table. We only need to include these two tables to find all of the information we need.

​

 

[A M I R]

[h=2]Designing a Query

[h=4]Determining the Criteria our Query Should Search For



When you set criteria for a field in a query, you are basically applying a ****** to it that tells the query to retrieve only

information that matches your criteria. Review the list of fields we are including in this query. How and where can

we set criteria that will best help us answer our question?




We don't want customers who live in our town, Raleigh, so we want a criteria that will return all records except those

with "Raleigh" in the city field. We don't want customers who live too far away, either. All the phone numbers in the area

start with the area code "919," so we'll also include a criteria that will only return records whose entries from the phone

number field begin with "919." This should guarantee that we'll only send coupons to customers who live close enough

to actually come back and use them.




We won't set a criteria for the order ID field or any other fields, since we want to see all the

orders made by people who meet the two criteria we just set.

To write queries, you'll need to be able to set criteria in a language that Access understands. As

you can see in the image above, our criteria requiring phone numbers to begin with "919" must

be typed like this: Like ("919*"). To learn how to write additional criteria,
​

 

[A M I R]

Designing a Query

Joining Tables in Queries



The final thing you need to consider when designing a query is the way you link, or join, the tables you're

working with. When you add two tables to an Access query, this is what you'll see in the Object Relationship Pane:

The line connecting the two tables is called the join line. See how the join line is actually an arrow? This is because

it indicates the order in which the query looks at data from the two tables. In the image above, the arrow is pointing

from left to right, which means that the query will look at data in the left table first, then look at only the data in the

right table that relates to the records it's already seen in the left table.



Your tables won't always be joined this way-- sometimes Access will join them right to left. In either case, you might

need to change the direction of the join to make sure your query includes the correct information. The join direction

can affect which information your query retrieves.



To understand what this means, consider the query we're designing. For our query, we need to see customers who have

placed orders, so we've included the Customers table and the Orders table. Let's take a look at some of the data

contained in those tables.

What do you notice when you look at these lists? Well, first of all, every single order in the Orders table is linked to someone

in the Customers table-- the customer who placed that order. However, when you look at the Customers table, you'll see

that the customers who've placed multiple orders are linked to more than one order, and those who've never placed an order

are linked to no orders at all. As you can see, even when two tables are linked, it's possible to have records in one table that

have no relationship to any record in the other table.


So what happens when Access tries to run our query with the current join, left to

right? Well, first it pulls every record from the table to the left, our Customers table.

​

It then retrieves every record from the right table that has a relationship with a record Access has already taken from the left table.

Since our join began with the Customers table, our query will include records for all of our customers, including

those who never placed orders. This is way more information than we want! We only want to see records for

customers who have placed orders.




Fortunately, we can fix this problem by changing the direction of the join line. If we join the tables from

right to left instead, Access will first retrieve all the orders from the right table, our Orders table:

Then, Access will look at the left table and retrieve only the records of customers who are linked to an order on the right.

We now have exactly the information we want: all of the customers who have placed an order, and only

those customers. As you can see, we had to join our tables in the correct direction to obtain the information we wanted.




Now that we understand which join direction we need to use, we're ready to build our query!



In our query, we needed to use the right to left join, but the correct join direction for the tables in

your queries will depend on what information you want to see and where that information is stored. When

you add tables to a query, Access will automatically join the tables for you, but it often doesn't join them in

the correct direction. This is why it's important to always review the joins between your tables before

you build a query.

​

 

[A M I R]

[h=2]Designing a Query

[h=3]Creating a Multi-Table Query

Now that we've planned our query, we're ready to design and run it. If you have created written

plans for your query, be sure to reference them often throughout the query design process.

​

 

[A M I R]

[h=2]Designing a Query

[h=4]To Create a Multi-Table Query:

Select the Query Design Command from the Create tab on the Ribbon.

In the Show Table dialog box that appears, select each table you would like to include in your query and click Add. After

you have added all of the tables you wish, click Close. When we planned our query, we decided we needed

information from the Customers and Orders table, so we'll add those.

The tables will appear in the Object Relationship Pane, linked by a join line. Double-click the thin section of the join line between two tables to edit its join direction.

The Join Properties dialog box will appear. Select an option to choose the direction of your join.

• Choose option 2: for a Left to Right join. In our query, the left table is the Customers table, so choosing this
• would mean that all of the customers who met our location criteria, whether or not they had placed an order, would be
• included in our results. We don't want to choose this option for our query.

• Choose option 3: for a Right to Left query. Since our right table is our Orders table, selecting this option will let
• us work with records for all of the orders and only the customers who've placed orders. We'll choose this option for
• our query, since this is exactly the data we want to see.

In the table windows, 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'll include most of the fields from the Customers

table: First Name, Last Name, Address, City, State, Zip Code, and Phone Number. We'll also include the ID number from the Orders table.

Set field criteria by entering the desired criteria in the criteria row of each field. We want to set two criteria:

• First, to find customers who do not live in Raleigh, we'll type Not like ("Raleigh") in the City field.
• Second, to find customers who have a phone number beginning with the area code 919, we'll type Like ("919*") in the Phone Number 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]Challenge!
If you haven't already, download our sample database and open it.

Create a new query.

Select the Customers and Orders tables to include in your query.

Change the join direction to Right to Left.

Add the following fields from the Customers table to your query :

• First Name
• Last Name
• City

Add the following fields from the Orders table to your query:

• Notes
• ID

Set the following criteria:

• In the Last Name field, type Like "Go*" to return only records with last names beginning with "Go."
• In the City field, type "Raleigh" to return only records with "Raleigh" in the City field.
• In the ID field, type >=60 to return only records with an ID number greater than or equal to 60.

Run the query. If you entered the query correctly, your results will include one record for a

customer named "Will Good." If not, click the View drop-down arrow on the ribbon to return to Design View and check your work.


Save the query with the name Will Query.
​

 

[A M I R]

[h=2]More Query Design Options

[h=3]Introduction

Access 2010 offers many options that let you design and run queries that return exactly the information you're looking for. For instance, what

if you need to find how many of something exists within your database? Or what if you would like your query results to automatically be

sorted a certain way? If you know how to use Access's query options, you can design almost any query you want.


In this lesson, you'll learn how to modify and sort your queries within Query Design view. You'll also learn how to

use the Totals function to create a query that can perform calculations with your data. You'll also learn about

additional query-building options offered in Access.

​

 

[A M I R]

[h=2]More Query Design Options
[h=3]Modifying Queries

Access offers many options for making your queries work better for you. In addition to modifying your query

criteria and joins after you build your queries, you can also choose to sort or hide fields in your query results.


​

 

[A M I R]

[h=2]More Query Design Options

[h=4]To Modify your Query:




When you open an existing query in Access, it is displayed in Datasheet View, meaning that you will see

your query results in a table. To modify your query, you must enter Design View, the view you used when

creating it. There are two ways to switch to Design View:




On the Home tab of the Ribbon, click the View command. Select Design View from the drop-down menu that appears.

In the bottom-right corner of your Access window, locate the small view icons. Click the Design View icon, which is the icon farthest to the right.

Once in Design View, make the desired changes, then select the Run command to view your updated results.



You may notice that Access offers other query views, like Pivot Table View, Pivot Chart View, and SQL View. You

can ignore these-- these views permit advanced functions that you will not need to use for this tutorial or for most Access functions.

​

 

[A M I R]

[h=2]More Query Design Options

[h=3]Sorting Queries
Access allows you to apply multiple sorts at once while you're designing your query. This allows you

to view your data exactly the way you want, every single time you view it.




A sort that includes more than one sorted field is called a multi-level sort. A multi-level sort allows you to apply an

initial sort, then further organize that data with additional sorts. For instance, if you had a table full of customers and

their addresses, you might choose to first sort the records by city, then further sort them alphabetically by last name.




When more than one sort is included in a query, Access reads the sorts from left to right. This means that the

leftmost sort will be applied first. So for instance, in the below example, the customers will be sorted first by the

City they live in and then by the Zip Code within that city.

​

 

[A M I R]

[h=2]More Query Design Options

[h=4]To Apply a Multi-Level Sort:
Open the query, and switch to Design View.

Locate the field you would like to sort first. In the Sort: row, click the drop-down arrow to select either an ascending or descending sort.

Repeat the process in the other fields to add additional sorts. Remember, the sorts are applied from left to

right, so any additional sorts must be applied to fields located to the right of your primary sort. If necessary, you

can rearrange the fields by clicking a field and dragging it to a new location.

To apply the sort, click the Run command.

Your query results will appear with the desired sort.

You can also apply multi-level sorts to tables that don't have queries applied to them. On the Home tab on the Ribbon, select

the Advanced drop-down command in the Sort & ****** group. Select Advanced ******/Sort, and create the multi-level sort

as you normally would. When you're finished, click the Toggle ****** command to apply your sort.

​

 

[A M I R]

[h=2]More Query Design Options

[h=3]Hiding Fields within Queries
Sometimes you might have fields that contain important criteria, but you might not need to actually see

the information from that field in the final results. For example, take one of the queries we built in our

last lesson-- a query to find the names and contact information of customers who had placed orders. We

included Order ID numbers in our query, since we wanted to make sure that we only pulled customers who had placed orders.




However, we really didn't need to see that information in our final query results. In fact, if we were just looking for

customer names and addresses, seeing the order number mixed in there too might have even been distracting. Fortunately, Access

makes it very easy to hide fields while still including any criteria they contain.





​

 

[A M I R]

[h=2]More Query Design Options

[h=4]To Hide a Field within a Query:

Open the query, and switch to Design View.

Locate the field you would like to hide.

Click the checkbox in the Show: row to uncheck it.

To see the updated query, select the Run command. The field will be hidden.



To unhide a hidden field, simply return to Design View and click the checkbox in the field's Show: row again.

​

 

[A M I R]

[h=2]More Query Design Options

[h=3]More Types of Queries

By this point, you should understand how to create a simple one- or multi-table query using multiple criteria. Additional

queries offer you the ability to perform even more complex actions with your database. One of these is the

totals query, which lets you perform calculations with your data.





​

 

[A M I R]

[h=4]Totals Queries

Sometimes, setting simple criteria won't give you the results you need, especially when

you're working with numbers. You may want to see your query results grouped or

counted in some way. Access 2010 offers several options that make these functions

possible. Perhaps the easiest of these is the Totals command.




When you use the Totals function in your query, the data in your fields will be grouped

by value, meaning that all items of one type are listed together. For instance, in a

totals query about the items sold at our bakery, each type of item sold would be listed

on a single row, no matter how many times that item had been sold.




Once your records are grouped, you can perform calculations with them. These calculations include:

• Count, which counts the number of the same items in a field
• Sum, which adds the numbers in that field
• Average, which finds the average of the numbers that occur in that field
• Maximum, which returns the highest value that has been entered in that field
• Minimum, which returns the lowest value that has been entered in that field
• First, which returns the first, or earliest, value that has been entered in that field
• Last, which returns the last, or most recent, value that has been entered in that field

These calculations will apply to the rows containing your grouped items. For example, if

you decided to use Sum to find out how many of each item on a menu has been ordered, you

would get a subtotal for each item in your query, not a grand total of all the items combined.

To add a calculation like a grand total to your query or table, review the instructions for creating a Totals row in our Modifying Tables lesson.
​

 

[A M I R]

[h=2]More Query Design Options

[h=3]To Create a Totals QueryCreate or open a query you would like to use as a totals query. For our example, we want to find the total number we've

sold of each of our menu items, so we'll use a query showing us all of the menu items we've sold. If you want to follow

along in our database, open the Menu Items Ordered query.


In the Query Design tab, locate the Show/Hide group and select the Totals command.

A row will be added to the table in the Design Grid, with all values in that row set to Group By. Select the cell in the Total: row

of the field you would like to perform a calculation on, and click the drop-down arrow that appears.

Select the calculation that you would like to be performed in that field. In our example, we want to add the quantities of products we've sold, so we'll select the Sum option.

When you are satisfied with your query design, select the Run command on the Query Tools Design tab to run the query.

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]More Query Design Options

[h=3]More Query Options

We offer mini-lessons on creating additional types of queries in our Extras section. Below is a list of the queries we currently cover.

A parameter query allows you to create a query that can be updated easily to reflect a new criterion, or search term. When

you open a parameter query, Access will prompt you for a search term, and show you query results that reflect that search.




A parameter query allows you to create a query that can be updated easily to reflect a new criterion, or search


term. When you open a parameter query, Access will prompt you for a search term, and show you query results that reflect that search.


​

 

[A M I R]

[h=2]More Query Design Options

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

Open the Customers Who've Ordered from Nearby Towns query and switch to Design View.

Add a Totals row to the query.

Set the Totals row in the Order ID field to Count. This will let us count how many orders each customer has placed.

In the Customers table in the Object Relationship Pane, double-click the word City to add another City field to the design grid below.

Click and drag the City field you just added so that it is to the left of the First Name field. It should now be the leftmost field in the design grid.

Apply the following multi-level sort:

In the leftmost City field, apply an ascending sort.

In the Last Name field, apply an ascending sort.

Hide the leftmost City field.

Run the query. If you did it correctly, the first record in your results should look like this.

​

 

[A M I R]

[h=2]Creating Reports

[h=3]Introduction

If you need to share information from your database with someone, but don't want them actually working with your

database, you should consider creating a Report. Reports allow you to organize and present your data in a

reader-friendly, visually appealing format. Access 2010 makes it easy to create and customize a report using

data from any query or table in your database.


In this lesson, you will learn how to create, modify, and print reports.

​

 

[A M I R]

[h=2]Creating Reports

[h=3]Creating Reports

Reports give you the ability to present components of your database in an easy-to-read, printable

format. Access 2010 lets you create reports from both tables and queries.





​