4_Queries_method_l1_v2

A query is a request for data results, and for action on data. You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data.

·        As tables grow in size they can have hundreds of thousands of records, which makes it impossible for the user to pick out specific records from that table.

·        With a query you can apply a filter to the table's data, so that you only get the information that you want.

·        Queries that you use to retrieve data from a table or to make calculations are called select queries.

·        Queries that add, change, or delete data are called action queries.

·        You can also use a query to supply data for a form or report.

·        In a well-designed database, the data that you want to present by using a form or report is often located in several different tables.

·        The tricky part of queries is that you must understand how to construct one before you can actually use them.

Queries help you find and work with your data

In a well-designed database, the data that you want to present through a form or report is usually located in multiple tables. A query can pull the information from various tables and assemble it for display in the form or report. A query can either be a request for data results from your database or for action on the data, or for both. A query can give you an answer to a simple question, perform calculations, combine data from different tables, add, change, or delete data from a database. Since queries are so versatile, there are many types of queries and you would create a type of query based on the task.

Create a select query

If you want to review data from only certain fields in a table, or review data from multiple tables simultaneously or maybe just see the data based on certain criteria, a select query type would be your choice. For more information, see create a simple select query.

Review data from select fields

For example, if your database has a table with a lot of information about products and you want to review a list of products and their prices, here’s how you’d create a select query to return just the product names and the respective price:

1.      Open the database and on the Create tab, click Query Design.

2.      In the Show Table box, on the Tables tab, double-click the Products table and then close the dialog box.

3.      In the Products table, let’s say that you have Product Name and List Price fields. Double-click the Product Name and List Price to add these fields to the query design grid.

4.      On the Design tab, click Run. The query runs, and displays a list of products and their prices.

Top of Page

Review data from multiple related tables simultaneously

For example, if you have a database for a store that sells food items and you want to review orders for customers who live in a particular city. Say that the data about orders and data about customers are stored in two tables named Customers and Orders respectively. If each table has a Customer ID field, which forms the basis of a one-to-many relationship between the two tables. You can create a query that returns orders for customers in a particular city, for example, Las Vegas, by using the following procedure:

1.      Open the database. On the Create tab, in the Query group, click Query Design.

2.      In the Show Table dialog box, on the Tables tab, double-click Customers and Orders.

3.      Close the Show Table dialog box. Note the line (called a join) that connects the ID field in the Customers table and the Customer ID field in the Orders table. This line shows the relationship between the two tables.

4.      In the Customers table, double-click Company and City to add these fields to the query design grid.

5.      In the query design grid, in the City column, clear the check box in the Show row.

6.      In the Criteria row of the City column, type Las Vegas.

Clearing the Show check box prevents the query from displaying the city in its results, and typing Las Vegas in the Criteria row specifies that you want to see only records where the value of the City field is Las Vegas. In this case, the query returns only the customers that are located in Las Vegas. You don’t need to display a field to use it with a criterion.

7.      In the Orders table, double-click Order ID and Order Date to add these fields to the next two columns of the query design grid.

8.      On the Design tab, in the Results group, click Run. The query runs, and then displays a list of orders for customers in Las Vegas.

9.      Press CTRL+S to save the query.

Top of Page

Create a parameter query

If you frequently want to run variations of a particular query, consider using a parameter query. When you run a parameter query, the query prompts you for field values, and then uses the values that you supply to create criteria for your query.

Note:  You cannot create a parameter query in an Access web app.

Continuing from the previous example where you learnt to create a select query that returns orders for customers located in Las Vegas, you can modify the select query to prompt you to specify the city each time that you run the query. To follow along, open the database that you created in the previous example:

1.      In the Navigation Pane, right-click the query named Orders by City (that you created in the previous section), and then click Design View on the shortcut menu.

2.      In the query design grid, in the Criteria row of the City column, delete Las Vegas, and then type [For what city?].

The string [For what city?] is your parameter prompt. The square brackets indicate that you want the query to ask for input, and the text (in this case, For what city?) is the question that the parameter prompt displays.

Note: Neither a period (.) nor an exclamation point (!) can be used as text in a parameter prompt.

3.      Select the check box in the Show row of the City column, so that the query results will display the city.

4.      On the Design tab, in the Results group, click Run. The query prompts you to enter a value for City.

5.      Type New York, and then press ENTER to see orders for customers in New York.

What if you don't know what values you can specify? You can use wildcard characters as part of the prompt:

6.      On the Home tab, in the Views group, click View, and then click Design View.

7.      In the query design grid, in the Criteria row of the City column, type Like [For what city?]&"*".

In this parameter prompt, the Like keyword, the ampersand (&), and the asterisk (*) enclosed in quotation marks allow the user to type a combination of characters, including wildcard characters, to return a variety of results. For example, if the user types *, the query returns all cities; if the user types L, the query returns all cities that start with the letter "L;" and if the user types *s*, the query returns all cities that contain the letter "s."

8.      On the Design tab, in the Results group, click Run, and at the query prompt, type New, and press ENTER.

The query runs, and then displays orders for customers in New York.

Specify parameter data types

You can also specify what type of data a parameter should accept. You can set the data type for any parameter, but it is especially important to set the data type for numeric, currency, or date/time data. When you specify the data type that a parameter should accept, users see a more helpful error message if they enter the wrong type of data, such as entering text when currency is expected.

If a parameter is set to accept text data, any input is interpreted as text, and no error message is displayed.

To specify the data type for parameters in a query, use the following procedure:

1.      With the query open in Design view, on the Design tab, in the Show/Hide group, click Parameters.

2.      In the Query Parameters dialog box, in the Parameter column, type the prompt for each parameter for which you want to specify the data type. Make sure that each parameter matches the prompt that you use in the Criteria row of the query design grid.

3.      In the Data Type column, select the data type for each parameter.

For more information, see using parameters to ask for input when running a query.

Top of Page

Create a totals query

The Total row in a datasheet is very useful, but for more complex questions, you use a totals query. A totals query is a select query that allows you to group and summarize data, like when you want to see total sales per product. In a totals query, you can use the Sum function (an aggregate function), to see total sales per product.

Note:  You cannot use aggregate functions in an Access web app.

Use the following procedure to modify the Product Subtotals query that you created in the previous example so that it summarizes product subtotals by product.

1.      On the Home tab, click View > Design View.

The Product Subtotals query opens in Design view.

2.      On the Design tab, in the Show/Hide group, click Totals.

The Totals row is displayed in the query design grid.

Note:  Although they have similar names, the Totals row in the design grid and the Total row in a datasheet are not the same:

·         You can group by field values by using the Totals row in the design grid.

·         You can add a datasheet Total row to the results of a totals query.

·         When you use the Totals row in the design grid, you must choose an aggregate function for each field. If you do not want to perform a calculation on a field, you can group by the field.

·         In the second column of the design grid, in the Total row, select Sum from the drop-down list.

·         On the Design tab, in the Results group, click Run. The query runs, and then displays a list of products with subtotals.

·         Press CTRL+S to save the query. Leave the query open.

https://support.office.com/en-ie/article/introduction-to-queries-a9739a09-d3ff-4f36-8ac3-5760249fb65c

https://www.tutorialspoint.com/ms_access/ms_access_query_data.htm

Скачано с www.znanio.ru