Microsoft access query codes

When you use tables to record transactions or store regularly occurring numeric data, it is useful to be able to review that data in aggregate, such as sums or averages. In Access, you can add a Totals row to a datasheet. Total row is a row at the bottom of the datasheet that can display a running total or other aggregate value. Run the Product Subtotals query you created earlier, and leave the results open in Datasheet view.

On the Home tab, click Totals. A new row appears at the bottom of the datasheet, with the word Total in the first column. Click the arrow to view the available aggregate functions. Because the column contains text data, there are only two choices: None and Count. Select Count. The content of the cell changes from Total to a count of the column values. Click the arrow, and then click Sum. The field displays a sum of the column values.

Now suppose that you want to review product subtotals, but you also want to aggregate by month, so that each row shows subtotals for a product, and each column shows product subtotals for a month. To show subtotals for a product and to show product subtotals for a month, use a crosstab query.

Note: A crosstab query cannot be displayed in an Access web app. You can modify the Product Subtotals query again so that the query returns rows of product subtotals and columns of monthly subtotals. On the Design tab, in the Query Type group, click Crosstab.

In the design grid, the Show row is hidden, and the Crosstab row is displayed. In the third column of the design grid, right-click the Field row, and then click Zoom on the shortcut menu. The Zoom box opens. In the Crosstab row, select the following values from the drop-down list: Row Heading for the first column, Value for the second column, and Column Heading for the third column.

The query runs, and then displays product subtotals, aggregated by month. For more information about crosstab queries, see Make summary data easier to read by using a crosstab query. Note: A make-table query is not available in Access web apps. For example, suppose that you want to send data for Chicago orders to a Chicago business partner who uses Access to prepare reports.

Instead of sending all your order data, you want to restrict the data that you send to data specific to Chicago orders. You can build a select query that contains Chicago order data, and then use the select query to create the new table by using the following procedure:.

Note: If you see a message beneath the Ribbon about enabling the database, click Enable content. If your database is already in a trusted location, you will not see the Message Bar.

In the Ship City column of the design grid, clear the box in the Show row. In the Criteria row, type 'Chicago' include the single quotation marks. Verify the query results before you use them to create the table.

In the confirmation dialog box, click Yes , and see the new table displayed in the Navigation Pane. Note: If there is already a table with the same name that you specified, Access deletes that table before running the query. For more information about using make table queries, see Create a make table query. You can use an append query to retrieve data from one or more tables and add that data to another table. Note: Append query is not available in Access web apps.

For example, suppose that you created a table to share with a Chicago business associate, but you realize that the associate also works with clients in the Milwaukee area. You want to add rows that contain Milwaukee area data to the table before you share the table with your associate. You can add Milwaukee area data to the Chicago Orders table by using the following procedure:. On the Design tab, in the Query Type group, click Append.

The Append dialog box opens. In the design grid, in the Criteria row of the Ship City column, delete 'Chicago', and then type 'Milwaukee'.

If an item matches with all the criteria you enter, it appears in the query results. When you want to limit the results of a query based on the values in a field, you use query criteria. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.

First open your Access database and then go to the Create tab and click on Query Design. 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.

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 databased on certain criteria, you can use the Select query. Let us now look into a simple example in which we will create a simple query which will retrieve information from tblEmployees table.

Open the database and click on the Create tab. In the Tables tab, on the Show Table dialog, double-click the tblEmployees table and then Close the dialog box. This minimizes data entry errors that cause a "Can't evaluate expression" error message to appear. One of the most powerful features of Microsoft Access queries is their support for Access functions. This is most useful in Update queries, but can also be used in Select queries.

The Advanced: Access Functions query is an example of this feature:. This query selects the Country names in descending order of name length. The second field renames itself to [Length], uses the LEN function to calculate the length of each country name, sorts the length in descending order, and excludes any records with 10 letters or fewer.

While this may not seem particularly useful, there are many situations where using Access functions is extremely useful and eliminates the need to program. In addition to using Microsoft Access functions, queries also support user defined functions.

Functions defined in VBA modules must return an appropriate value and can be used to manipulate each record. You can reference field values by passing the field name in brackets. Here is an example where a function StripLead is used to remove the leading word of a phrase if it starts with "The", "An", or "A".

This is useful for sorting phrases such as book titles on "real" words:. This is the code for the StripLead function. It is passed a string and returns the string without the leading word if any :.

And this is the result. Notice how the sorting of the [Adjusted] field differs from the [Original] field:. Select queries retrieve all the records that meet your criteria. There are occasions where you only want a subset: the top or bottom number of records. Similarly, you may just want the top or bottom percent of records.

This is easy to do. Just change the query's Top Values property right mouse click on the top portion of the query , you can specify the number of records to display. The example below query: Other: Top 10 Auto Companies has this set to Notice the query is retrieving records in Descending order so the Top Values option retrieves the largest values.

It simply runs the query and displays the specified number of records in the query output's order. Sometimes, you want a percentage of records and not a fixed number. Similarly but sorting in the opposite direction, you get the bottom percentage of records. Up to now, we have only retrieved records. With lots of data, it is important to calculate summaries for groups of records or totals on the entire table.

Microsoft Access and Later. This performs calculations across all the records and creates a summary result. For instance, you can Sum on a numeric field to determine the total for the entire table.

Additionally, you can group on fields to calculate totals for each unique combination of values across the group fields. When Totals is selected, a new "Total" row appears in the query design. You can specify the type of calculation you want in that section:.

For this query, the result shows average Age, Weight and Cholesterol for patients by State and Gender. Crosstabs are a powerful analysis tool that lets you quickly see the relationship of data between two fields. The view is a spreadsheet like display with unique values of one field as rows, unique values of another field as columns, and the summary of another field as the cells in the matrix. For instance, with the previous example, a crosstab can clearly show the average Cholesterol between State rows and Sex columns :.

The easiest way to create a crosstab is to use the Crosstab Wizard. When creating a new query, select Query Wizard and follow the Crosstab Query steps:.

Crosstab queries can also be manually created by selecting Crosstab from the Query menu and specifying the Row and Column Headings:. To this point, all the queries shown were for one table only. Microsoft Access queries allow very sophisticated multi-table queries. Criteria and field selections can be from any of the query's tables. Linking tables on fields is done visually by dragging a line between the fields to link. For our previous example, we may want to show the full name of each state rather than its abbreviation.

With a State table that contains the abbreviation and full names, this can be easily performed:. Notice the link on the [State] fields and the [Name] field from the States table in the query.

To create multi-table queries, the Table row should be displayed. This can be activated from the View Table Names menu. Even better, the default query options should set Show Table Names to Yes. There are several ways to join tables in a query. Another join includes retrieving all records from one table regardless of whether there are matches in the second table.

These options can be selected by double clicking on the linking line and choose among the three options. Queries with multiple tables do not even require a line between the tables.

If no lines are specified, a record by record link is assumed. That is every record in the first table is linked to every record in the second table. This is most useful when one of the tables only has one record. Finally, tables may be linked through an expression that establishes a partial match or match based on a range of values. Examples are shown later. So far, the queries presented are only based on tables. However, Microsoft Access lets you also base queries on other queries. This ability to filter and analyze data across several levels is extremely powerful.

The queries themselves behave identically whether the data comes from tables or queries. Basing queries on other queries can also simplify maintenance of your database by letting you create standard queries that other queries can use.

This can be particularly useful in reports. Of course, you need to be careful modifying the "core" queries. Additionally, when generating reports in multi-user databases, make sure you don't use queries when you should be using temporary tables generated by Make Table and Append queries. Queries always retrieve the most current data. If you are printing many reports while others are modifying the data, and consistency between reports is important the numbers need to tie , you must create temporary tables with the data you need prior to printing.

You can then base your queries on those "static" tables. For a field, calculating the percent of each record to the total for an entire table is useful for determining the relative size of each group.

This can be achieved by creating a summary query and using that in another query with the original table.

In this example, we use the Fortune table containing sales and profits data for large corporations; and two queries "Fortune Totals" and "Fortune PercentOfTotals". Here's how they work:. This is a simple query that sums the values in the two fields: [Sales] and [Profits]. For clarity, the resulting fields are named [TotalSales] and [TotalProfits]. This is a simple select query that retrieves fields from the Fortune table and creates new fields for the Percent of Total calculations.

Notice the two tables are not linked with lines between them. They only interact in the Percent of Total calculations where the values in the calculated fields using TotalSales and TotalProfits as divisors:. Calculating percent of total in Microsoft Access reports is even simpler because queries don't need to be created. The first step is to create a control with the summary total. The value can be the total for the entire report or just the group.

Give the text box control a name e. You can then reference that control for the value of the total. Frequency distributions reveal the number of records that contain values within numeric ranges. By using the VBA function, Partition, you can group on that for intervals of identical widths. In this example, we show how you can define your own groupings of different size ranges, give them your own label and sort the results in the order you expect. All without writing any code! A simple two table query calculates these results even when the size of the numeric ranges are not identical.

Just follow these simple steps:. For each record, define the groups and its low and high values:. Notice how the [Maximum] value of one record is smaller than the [Minimum] value of the next record. They cannot be identical or else such values would fall into two groups. In our example, the Age data are integers so using integers in this table is okay. Otherwise, you can use numbers very close to each other e. You can name and specify as many groups as you like.

Notice that the two tables boxes are not linked to each other. The first and third fields in the query come from the group table: the [Group Name] description and the [Group ID] field controlling the sort order.