| A query is information extracted from parts of one or more tables in a database and displayed in table format. Select Queries The input to a query is data from tables or other queries. The data is turned into information by applying criteria that mask out unwanted data and produce the result. The entire query can be saved so that the result can be re-created at at any time. Creating and Running Query - Click
on the Queries tab from the Database window.
- Choose the Create query in Design Viewnoption or click the New button and choose Design view.
- The Show Table dialog box appears.
- Choose the table(s) to be included in the query.
- Click the Add button to add the table(s) to the Query design. Click the Close button to remove this dialog box from the screen.
- Now add (double-click) the desired fields to the grid.
- If you want to restrict the rows in the
output query, add expressions in the Criteria boxes.
Criteria are written like they were for filtering. Here are some examples: >1, <2, ="M", <>"M", >=#1/1/2002# (Dates are delimited by #.), Not "Baja" - To run the query, click on the Run button on the toolbar or Click on the Datasheet View button.
- If you need to return to Design View, just click the Design View button on the toolbar.
Example Using the database,
Cruises.mdb, answer the queries: - Find all passengers sailing to Aruba. Show persons name, ship, destination, departure location, and state.
- Find all passengers sailing First Class. Show passenger name, class, and city and state. Sort by last name.
- Make up some queries of your own (and answer them).
Queries with Summaries
or Groupings - Total Queries A Total query is a special type of query that performs summary calculations on groups of data. All Total queries must include at least two fields - one field that's used to group the data, and another to provide values for calculating the summary information.
Total queries can be run on any data that is organized into categories or groups. Usually fields having only a few different values are selected as grouping
fields. For example, in the Cruises.mdb database, the table Cruises could be grouped by Company name, Ship, or Destination. For instance, if we wanted to know the average first class price for each company we would choose as our Group by field Company name and the other field would be FC price. How to Display the Total Row - Click the Total button (Sigma icon) on the toolbar from Design view, or
- Choose the Totals command
from the View menu.
You can select a variety of grouping options by clicking on the drop-down button on the right side of each cell in the Total row. This row has a default value of Group By in each column. Although there are 12 options available from the drop-down list box that's displayed, we will work with only a few of these options: Group By, Sum, Avg, Min, Max, and Count. Using the Group By Option The Group By option allows the user to specify a field as a
grouping field. The data will be organized and displayed into groups based on the values in that field. For example, you can group together all records that come from a specific cruise company, so that all "Gateway Cruises" are grouped together and all "Velo Cruise Line" cruises are grouped together, etc. Subtotals can be calculated in each group for any numeric fields. For example, we could calculate the average price of a first class cabin. So we would have the average price subtotal for
"Gateway Cruises," for "Velo Cruise Line," and so forth. The Group By option should be specified for only one field, unless subgroupings are needed. For example, you could group by Company name and within that grouping it's possible to group all ships. Be careful that only those columns that are part of the grouping scheme contain the value Group By in the Group row. Group By is the default value. How to Calculate Subtotals - Identify the field that contains
the different categories for grouping and the field that contains the numeric data to be totaled. Set up a query containing only those two fields.
- Press the Total button on the toolbar, or
Choose the Totals command from the View menu. - Choose the total option (e.g., Sum, Avg, Count, etc.) from the Total drop-down list for the field that contains the numeric data to be totaled.
- Run the query.
- Crosstab Queries A Crosstab
query displays summarized data in a spreadsheet like format (in rows and columns). You can display sums, averages, counts, and other statistical information about data in a table. The records are grouped in two ways by values in two different fields, and then statistics are calculated where the two groups meet or cross - hence the name Crosstab query. For example, you can group student records by major and gender so that students' average ages can be calculated. Table J shows how this Crosstab
query looks.
Table J.
|
| Male | Female |
|---|
| Business
| 21.3
| 20.7
| | Math
| 19.8
| 21.0
| | Communications
| 20.9
| 20.8
| | Psychology
| 20.2
| 19.5
|
Row headings = major Column headings = gender Values = average age As you can see, there are three parts to a Crosstab query: Row Headings, Column Headings, and Values. A Crosstab query must have an entry for each of these. The Row Headings and Column Headings are descriptive labels that identify how the Values are grouped, and Values are summarized numeric data. In Table J, the Row Heading field is Major, the Column Heading field is
Gender, and the Value field is Age. Several Row Headings can be used in a Crosstab, but only one Value and one Column are used. Also, criteria cannot be entered in Value fields. You can create a Crosstab query on mutiple tables if they can be joined. There are three examples of Crosstab queries in the Cruises1.mdb database. The one called qryAverage, computes the
average first class price for each cruise company grouped by departure location. The qryAverage2 is the same as qryAverage except that it just gives the results for Departure Locations Miami and Los Angeles. The qryCount query counts the number of entries in each of the company-departure location groups. When doing a count, it doesn't matter what field you use in the Values column. Creating a Crosstab Query in Design View Creating a Crosstab query in Design view gives you
more flexibility than the wizard. The most important thing to remember is to specify one or more Row Headings, only one Column Heading, and only one Value field. Here is what to do: - Create a new query in Design view.
- Select the table(s) for the data source.
- Select Crosstab Query from the Query menu, or
Click the drop-down arrow on the Query button and select Crosstab Query. - Add the field for the Row Heading to the grid.
- Select Row Heading from
the drop-down list in the Crosstab row, and keep the Group By selection entered in the Total row.
- Add the field for the Column Heading to the grid.
- Select Column Heading from the drop-down list in the Crosstab row, and keep the Group By selection entered in the Total row.
- Add the field for the Value to the grid.
- Select Value from the drop-down list in the Crosstab row.
- Select the type of function desired for the value (sum, count, and so forth) in the
Total row.
- Specify a criteria if you want, on any Heading field (but not on the Value field).
- Run the query using the Run button on the toolbar, or
Choose Run from the Query menu.
- Parameter Queries Sometimes you want to select certain values from a table, but you don't want to choose the value ahead of time. You want the user of the database to enter the selected value interactively.
To do this, enter the expression [Text of
Prompt] in the Criteria row for the field for which you are selecting a value interactively. For example, suppose, in the Cruises database, we wanted to be able to enter a destination and get all cruise infromation associated with that destination. Someone else may want information for a different destination. The query qryDestination has [Enter a destination:] entered under Criteria in the Destination column.
 M. Reports- The easiest way to design a report is to use the Report Wizard.
- An access report can show detailed records and also summary information.
Using the Report Wizard- Select Reports from the list of
Objects on the Database Dialog.
- Double click on Create report by using wizard.
- This brings up Page 1 of the Report Wizard.
- Select the table or query that you want to use for the report.
- Move the fields that you want in the report to the right.
- Select Next.
- Move the fields that you wish to appear from Available Fields on the left to Selected Fields on the right.
- Click Next to move to Page 2.
- Page
2 asks "Do you want to add any grouping levels?"
- Move any fields you want to use for grouping levels to the left.
- Click Next to move to Page 3.
- Page 3 asks "What summary and sort order information do you want for detail records?"
- Enter sort information. You can enter sort information for up to four fields.
- Click the Summary Options button.
- Indicate what summary information you would like calculated.
- Choose to show
detail and summary or summary only.
- Click Next to go to Page 4.
- Page 4 asks "How would you like to layout your report?"
- Choose the layout and orientation. Stepped is a good layout, unless you have reason to choose another one.
- Click Next to move to Page 5.
- Page 5 asks "What style would you like?".
- Choose the style.
- Click Next to move to Page 6.
- Page 6 asks "What title do you want for your report?"
- Enter the title.
- Choose whether you want to preview the report or modify the design.
- Click the Finish button.
- Right click on the report and select Report Design to modify the report.
- Modify the fields on the Report Design until the report looks good to you.
How do you create a report from a table in Access?
In the Navigation Pane, click the table or query on which you want to base the report. On the Create tab, in the Reports group, click Report. Access builds the report and displays it in Layout view. For more about viewing and printing your report, see the section View, print, or send your report as an e-mail message.
How do you add a field from a table to a report in Access?
On the Design tab, in the Tools group, click Add Existing Fields.. Double-click the field.. Drag the field from the Field List pane to the form or report.. Hold the CTRL key and click several fields, and then drag them all to the form or report at the same time.. Select Create > Report Wizard. Select a table or query, double-click each field in Available Fields you want to add it to the report, and select Next. Double-click the field you want to group by, and select Next. Complete the rest of the wizard screens, and select Finish.
How do you include a field in an Access query?
To add a field, drag the field from a data source in the upper pane of the query design window down to the Field row of the design grid, in the bottom pane of the query design window. When you add a field this way, Access automatically fills in the Table row of the design grid to reflect the data source of the field.
|
