Sunday, October 3, 2010

Microsoft access tutorial 4

Unit 7. Queries (I)


In this unit we will learn how to create queries and how to use them to edit records in tables created in Access2003.

Types of queries.

Queries are those objects in a database that allow us to view, change, and arrange data stored in tables.

We can also use them as the source of records for forms and reports.

Various types of queries exist:

Select queries.

These are the queries that extract or show us data. They will show data that complies with specific criteria.

Once we have the result we can consult the data and edit it (this can or cannot be done, depending on the query). A select query generates a logical table (named this way because it is not actually in the hard drive but in the memory of the computer, and every time we open it is recalculated).

This is the most common type of query.

Action queries.

These are the queries that carry out changes to the records. Various types of action queries exist to delete, update, insert data, and to create a new table from one existing table. These queries are named delete queries, update queries, append queries and Make-Table queries. We will study them in unit 10.

Crosstab queries.

We use these queries to calculate and restructure data for easier analysis. Crosstab queries calculate a count, average, sum, or other type of total for data that is grouped by two types of information (two fields), one down the left side of the datasheet and another across the top.

SQL queries.

When we want some action to be made on the data, we must tell Microsoft Jet engine to do it. SQL is the language that Microsoft jet engine understands and permits us to comunicate to it.

When you create a query in Query Design View, Access constructs the equivalent SQL statement behind the scenes for you. If you want, you can view and edit the SQL statement in SQL view.

After you make changes to the query in SQL view, the Query Design view will change and adapt to the new SQL sentence. However, sometimes, query might not be displayed in Query Design view because of the SQL sentence.

There is some statements that can not be defined from the Query Design View but rather directly in SQL, these are SQL-specific queries.

These queries will not be studies in this course as in order to define them knowledge of SQL is necessary, which is not part of the objective of this course.

Creating a query.

To create a query, follow the next steps:

Open the database where the query will be created.

Click on the Queries object found on the left lateral of the Database window.

This is the screen that will appear:

Later we have 3 alternatives:

Double click on the Create query by using wizard option.

In this case the wizard window will open in which we are asked from which table we choose to extract the data, the fields we wish to visualize and the title of the query, next it will automatically generate the corresponding query.

Double click on Create query in Design view.

In this case the Query design window will open on which we will elaborate further on, and within which we can define our query in more detail.

Click on the button in the Database window.

The following dialogue box will appear:

The Simple Query Wizard is the same as Create a query using the wizard mentioned above.

The other wizards permit us to generate special types of queries. In Unit 9 we will study the Crosstab Query Wizard.

The Design view option has the same effect as the Create a query in Design view. This is the option we will explain next.

On entering the Query design we are firstly asked for which tables the query should extract the data from:


Select the table from which we wish to extract the data and click on the Add button (or double-clik on its name).

If we wish to extract data from another query, click on Queries tab an select it.

If we wish to extract data from various tables we should continue in the same manner.

Finally click on the Close button.

The query Design view window will appear.

Unit 8. Summary queries (I)


Definition

In Access we can define a special query to calculate totals on the records of a table (or various tables related). In order to obtain those totals we use summary functions so it is why we call them summary queries.

It is important to remember that the resulting rows of a summary query have a distinct nature to the rest of the rows resulting from queries as each row corresponds to various rows in the source table.

A 'non summary' query row corresponds to a row in the source table and contains data found in just one row of the source, whilst a row from a summary query corresponds to the summary of various rows from the source table, this difference is the origin of a series of restrictions that the summary queries suffer as we will see later. For example, this type of query does not permit us to edit the data.

In the next image we have an example of a normal query in which one visualizes the rows of a table of offices sorted by area, in this case every row of the result corresponds to just one row in the offices table, while the second query is a summary query, every row of the result corresponds to one or various rows in the offices table.


We create a summary query by selecting the Totals option in the View menu ,

or by clicking on the button on the toolbar.

In either case a row is added to the QBE grid, the Total: row.

All the columns that we include in the grid should have a value in this row, this value indicates to Access what to do with the values contained in the field written in the Field: row.

The values that we can indicate in the Total: row are those that appear in the drop down list associated with this cell as we see in the image to the right.

The summary functions

The summary functions are functions that allow us to obtain a result based on the values contained in one column of a table and they can only be used in a summary query.

To use these functions we can write them directly in the Field: row of the grid as we will see ahead, but we can also use a simpler method which is selecting from Total: row in the grid the option corresponding the function.

Next we will describe these options.

The Sum function calculates the sum of the values indicated in this field. The data being added up need to be numeric type (integer, decimal, or currency...). The result will be the same type though might have a higher accuracy.

The Avg function calculates the average of the values contained in the field being summarized. It also applies to numeric data, and in this case the type of data in the result can change depending on the systems necessities in represent the value of the result.

The StDev function calculates the Standard deviation of the values contained in the column, assuming the values are a sample of a larger population. If the query source has less than two records, the result is null.


The Var function calculates the variance of the values contained in the column. If the query source has less than two records, the result is null.

It is interesting to distinguish that the null value is not the equivalent of 0, the summary functions do not consider null values while they consider the 0 value as a value, therefore in the average and the standard deviation the results will not be the same with 0 values as with null values.

The Min and Max functions determine the smallest and the largest value of the column. The values in the column can be numeric, text or date. the result of the function will have the same type of data as the column. If the column is numeric, Min will return the smallest value contained in the column, if the column is text type, Min will return the first value in alphabetical order, if the column is date type, Min will return the oldest date and Max the latest.

The First and Last functions are used to obtain the first and last record of the group that is being calculated. Sorting the records does not have any effect on the result of these functions, Access will allways consider the cronological order in the records were created.

The Count function counts the number of values in a column, the data can be of any kind, and the function always returns a integer number. If the column contains null values these values are not counted, if a value is repeated in a column, it is counted various times.

In order for the number of records to be counted the Count(*) function needs to be used, it returns the number of rows and therefore also counts the null values. In this case we need to select the Expression option and write it like this:

No comments:

Post a Comment