When working with databases, the user needs to receive information in a convenient form for displaying with a summary of various parameters, using calculations and expressions. Access databases use data retrieval queries and reports for this. The article discusses another way to display and select the required values - a cross-query.
Concept and use
In a database containing many related tables, the user uses query queries in order to get only the information that he needs at a particular moment. Such queries allow you to select values from several objects, group and sort them by criteria, select records by certain conditions.
The query results are displayed in table form. Columns are the fields of objects, from which selection is made. And the rows contain the cell values of these objects. The user displays the obtained data on the monitor using Access reports, getting a visual representation of the information contained in the database.
But often it is necessary to obtain data not in its pure form, but using functions and expressions. One column of the table is grouped with another to combine information. All this helps to cross-query.
Expressions, sorting, query conditions
Queries use functions to process individual values and results. Access database provides a choice of built-in functions for working with various types of data. Calculation of the day, month or year for the date, finding the substring input in the text field, trigonometric and arithmetic operations with numbers, type conversion among themselves are available.
In cross-queries, the functions of finding the minimum, maximum or average value, amount or amount of data are used to derive the totals.
Query results are sorted by fields in ascending or descending order. For the selection of values, conditions are set that allow obtaining only the necessary data in the results. In terms, expressions are also widely used.
Using the wizard to create
Suppose a small store selling home furnishings keeps records of vases. They are made of various materials and have certain sizes. The customer wants to buy goods from only one material of the same size in the amount of 25 pieces. To find out exactly which vases are enough in the warehouse, a request is created, the results of which will show the number of items that meet the criteria of the buyer.
Creating a cross-query for this example is conveniently done using the Query Wizard. Its advantage is simplicity, but this is also a disadvantage. It does not allow you to make complex selections from multiple tables. But for our case, the Query Wizard is perfect.
On the "Creation" tab, click "Query Wizard" and in the window that opens, select the type "Cross", and then the table for the selection - "Vases".
Suppose in the final table we want to see the material from which the vases are made in rows and their size in columns. Choose this.
The values in the cells represent the number of vases from each material and each size. They are calculated as a sum.
We get the result. It states that the store can offer the customer 25 medium-sized vases of glass or porcelain.
Creation in the "Designer"
For more complex cases, creating a cross-query in Access is possible using the "Constructor". Let's take a more complex example of a store selling decor items. Vases are in different warehouses. This time, the buyer needs 45 identical vases, and he wants to pick them up on his own right from the warehouse.
The database contains tables "Vases" with information about the material and size of items, "Warehouses" with the addresses of the storages and "Goods by warehouses" with the type of product and its quantity in each of the storages.
In the "Query Designer" select all these tables. The rows contain data on warehouses with numbers and addresses, in the columns - the names of vases, in the cells - the number of goods.
Judging by the results table, we can offer the buyer 3 warehouses in which there is the required number of identical vases - No. 2, 4 and "Central". In the first two there is only one product name in the right quantity, in the last - three.
Using SQL
You can also create a crosstab query in an Access database using the SQL programming language. To do this, use the TRANSFORM operation. In the body of the query is a function that we use to process the values in the cells of the resulting table. In our case, this is the sum of the numbers in the "Quantity" field of the "Warehouse Products" table.
Data can be obtained by a regular SELECT query. The selected fields indicate those columns that make up the row headers in the final query. In our example, these are the "Warehouse" and "Address" fields from the "Warehouses" table. To add other tables, use the INNER JOIN statement with the fields for the join.
And finally, the PIVOT command includes the field whose values as a result are column headings - the names of the vases.
SQL query takes the form:
TRANSFORM Sum([ ].) AS [Sum-]
SELECT ., .
FROM INNER JOIN ( INNER JOIN [ ] ON . = [ ].) ON . = [ ].
GROUP BY ., .
PIVOT .;
As we have seen, cross-querying in the Access database is a convenient function, which is easy to use. It helps simplify the presentation of data and facilitate work on summary reports.