آموزش کامل Access 2007-EN

[A M I R]

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

To ****** the new field:

• Click in the Criteria cell for the field.

• Enter your criteria by directly typing it into the cell. Access will put quotation marks around your criteria, showing that it is searching for that exact value.

Important: Access will perform the sorts left to right, according to the way they are displayed on the

Advanced ****** design screen. Once all the sorts are completed, Access will perform the ****** functions. Like

the sorts, Access ******s from left to right, based on the order displayed on the Advanced ****** design screen.

To view the results, click on the Toggle ****** button on the Ribbon.


Your ******ed results will appear in a new table. You can save the advanced ******, in the event that you may want to run it again later.





​

 

[A M I R]

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

• Right click on the Advanced ****** design tab.
• Select Save.
  • If the Save as Query dialog box comes up, you must give the advanced ****** a name.
    This dialog box will only appear the first time you save.
  
  Access 2007 saves Advanced ******s as queries, because they are very simple queries run on only one table.
  
  

​

 

[A M I R]

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

[h=3]Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.

Use the ****** by Field method to ****** the Customers table to find all customers from NC.



Use the ****** by Selection method to ****** the Customers table, looking for all NC customers NOT in Raleigh.

Hint: Use the Does Not Equal option.



Practice toggling between the ******ed results and the un******ed tables.



Apply an Advanced ****** on the Books table to find all the Kids books, sorted on price with the highest price first.



Save your Advanced ****** with whatever name you choose

​

​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=3]Introduction The real power of an Access 2007 database is in the ability to pull data for quick analysis, which is what happens when you run

a query. Queries allow you to retrieve information from one or more tables based on a set of search conditions you define. Access 2007

will display your results in their very own table that you can analyze and manipulate further. This lesson will explain how to plan a

query using a three-question planning process. You will learn how to use the Query Design command to run the query, as well

as how to modify the query to hide fields or other information in your query results. Finally, it will show you how to save the query for later use.


​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=3]Using Queries - Part 1

[h=5]Using Queries
Queries retrieve information from one or more tables based on a set of search conditions that you set up and then

combine that information in a way that is easy for you to analyze. If you have used an Advanced ****** in

Access 2007, then you have already run a very basic query on only one table. If you want to pull data from

more than one table, though, you will need to use either the Query Design command or the Query Wizard.


Before using the Access 2007 query tools, it is important to plan out the query using a

logical process. Otherwise, you may not get the results you expect.



​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=3]Planning a Query
There are three questions you need to answer when you are planning a query:

• What do you want the results to look like? Identify every field or bit of information that you want included in the results.

• Where is the information stored in the database? List which tables (and/or queries) hold the information that you want to see.

• What conditions do you want the data to meet? This helps determine how to set the criteria so Access can search the records properly.

​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=5]Planning: Which Customers Ordered Technology Books?

Let's think about this process for our bookstore database scenario. We have new technology series

coming out soon, and we want to send coupons to customers that have ordered technology books from

us in the past. A query can help us answer the question: which customers have ordered technology

books from us already? Let's use the three-question process to plan this query.


What fields do we want to see in the results? We need a list of customer names and addresses

in order to mail the coupons to our customers, so we'll need the results to show:


In which tables is the information stored? Well, for this query we'll need:

• Customers table - to get the customers' names and addresses

• Books table - to know which books are technology books

• Orders table - to know which customers ordered those books.

• What is the condition we want the data to meet? We want Access to look for only the books where the book's category is technology.

​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=3]Using the Query Design Command
Once you have planned out your query, you can build and run it using Access 2007's query tools.





​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=5]To Build a Query using the Query Design Command
Select the Query Design command from the Create tab on the Ribbon.


Use the Show Table dialog box to select which tables (and/or queries) to include in the query. Our plan called for all three tables.


Drag and drop the fields you want to see in your results to the bottom portion of the query design screen.


Enter the condition in the Criteria row for the condition field. For our query, we typed Technology in the cell

labeled Criteria for the Category field. As seen above, Access 2007 puts quotation marks around the term

to show it is looking for exactly that term within the designated field.


Once the condition is set, click Run! in the Results group on the Ribbon.


Finally, view your results to determine if they match your desired results.


​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=3]Hiding Fields or Other Information in the Results

Sometimes the results of a query will include information that is seemingly unnecessary

to you. Access 2007 allows you to easily hide these fields.



​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=5]To Hide Part of the Query Result
In the query design window, de-select the Show option by clicking on it.


Now, when you run your results, that field will be hidden, as seen below.


​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=3]Saving the Query

Sometimes you will not need to save your results or your query design. Other times, you may want to

keep it to run again later or to modify it slightly. Saving a query is very easy to do.



​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=5]To Save a Query

• Right click on the query tab.

• When the Save As dialog box opens, give your query a meaningful name.
• 
  
  
  • Click OK.
  
  
  • The query will now be listed in the object list on the left side of the Access window.
  
  

​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 1

[h=3]Challenge!
If you haven't already done so, save the sample Ready2Read database on your own computer.

Plan a query to find out which customers order a certain category of books.



Use the Query Design command to set up the query.


Run the query and view your results.



Save the query.



Modify the query to hide a field.

​

​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 2

[h=3]Introduction You already know how to plan and run a basic query. This lesson will show you how to run a query that includes a Totals function to

group and count the records in the results. It will also deal with ways to further sort and ****** via your query

design to refine the results even more.


​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 2

[h=3]Using Queries - Part 2
[h=3]Using Totals in a Query
Sometimes, you may want to see your query results grouped or counted in some way. Access 2007 offers several

options that make these functions possible. Perhaps the easiest of these is the Totals command, whose optional

functions are very similar to the functions used in Microsoft Excel. These functions include:

• Sum -- used to add a column of numbers.

• Average -- used to find the average of a column of numbers.

• Maximum -- returns the highest value in a field.

• Minimum -- returns the lowest value in a field.

• Count -- used to count the number of same values in a query.

One of the most useful Totals functions to use in queries is the Count function.

​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 2

[h=3]Using Count and Group By in a Query
When you use the Totals command in a query, Access will automatically group every field by

the values in each field. This just means that it will look for repeating values and group the like

values together, so they appear as one record, rather than as many records. This is called the Group By function.


Take our bookstore database for example. If we run a query to see the information for every

book that has been ordered, we'd get a list that looks like this:


Notice that we get a record back for every order of each book that has been ordered.


In our bookstore example query, we want to see these titles grouped together, so that we see each

ordered titles only one time. To do this, we use the Count and Group By options.


​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 2

[h=5]To Use the Count and Group By Options in a Query
To include the Group By and the Count functions in a query:
Click on Totals in the Show/Hide group on the Ribbon.


The Total row will instantly appear in the bottom portion of the query design screen.

• Next, click in the Totals row for the field you wish to count. We want to count the number
• of times the same Book ID appears in the Orders table.

From the list of optional Totals functions, select Count.


Click Run! to see your results. Notice that each title, author, price and category is

now listed only one time for each book, with an extra column that indicates the number of times this Book ID appeared in the Orders table.


​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 2

[h=3]Sorting and ******ing Query Results
Once you have the results looking how you want them, you can sort and ****** them to narrow your

results down even further. This can be done using the methods of sorting and ******ing covered in

Lesson 10 and Lesson 11, or by applying the sort and ****** in the query design itself.





​

 

[A M I R]

[h=2]Using Queries to Make Data Meaningful - Part 2

[h=5]To Sort via the Query Design
To add a Sort to any field in the query design:
Click on the Sort row for the field you wish to sort. A drop down list will appear:

• Choose one of the options:
• • Ascending will show your results sorted with the lowest numerical value or the text value closest to A first.
• • Descending will show the results with the highest numerical value or the text value furthest from A first.
• • (not sorted) will keep your records grouped, but not sort them.
• Click Run! to see the results.

​