Home

Enhancing Queries

 

Using Functions

 

Introduction

To enhance the result produced by a query, you can use some of the built-in functions of Microsoft Access, including those we saw in Lessons 11-14.

You can use a function to control the values that would display in the query or you can include the functions in the condition set to filter the values.

To control how the values would display in the query, start a query in Design View or open a query in Design View. In the bottom section of the window, in the box of the field name, type the expression. For example, if you have a column named Gender and that display the genders as Male or as Female but you want to display only M or F respectively, you can use the Left() function in an expression as Left(Gender, 1):

If you have a column named Gender and that displays the genders as Male and Female but you want to display only M or F respectively, you can use the Left() function in an expression as LEFT(Gender, 1)

Function

In the same way, you can use any of the functions we have seen so far.

To use a function in a criterion, open the query in Design View and select the column(s) you want. In the lower section of the window, click the Criteria box that corresponds to the column that will hold the criterion and type the expression that includes the function. For example, on a list of students that includes their dates of birth in a column named DOB, to get the list of students born in 1992, you would set the condition as Year([DOB])=1992. Here is an example:

On a list of students that includes their dates of birth in a column named DOB, to get the list of students born in 1992, you would set the condition as Year([DOB])=1992

Function

The Domain-Based Functions

Besides the functions we reviewed in Lessons 11-14, there are many other functions available in Microsoft Access. For example, a domain-based function is used to get a value from another object and deliver it to the object in which it is being used or called. The general syntax of these functions is:

FunctionName(WhatValue, FromWhatObject, WhatCriteria)

To perform its operation, a domain-based function needs three pieces of information, two of which are required (the first two arguments) and one is optional (the third argument).

when calling one of these functions, you must specify the value of the column you want to retrieve. This is provided as the WhatValue in our syntax. This argument is passed as a string.

The FromWhatObject is the name of the object that holds the value. It is usually the name of a form. This argument also is passed as a string.

The third argument, WhatCriteria in our syntax, specifies the criterion that will be used to filter the WhatValue value. It follows the normal rules of setting a criterion.

Domain First: If you want to find out what was the first value entered in the cells of a certain column of an external form or report, you can call the DFirst() function.

Domain Last: The DLast() function does the opposite of the DFirst() function: It retrieves the last value entered in a column of a form or report.

Domain Sum: To get the addition of values that are stored in a column of another form or report, you can use the DSum() function.

Domain Count: The DCount() function is used to count the number of values entered in the cells of a column of a table.

Domain Average: The DAvg() function calculates the sum of values of a series and divides it by the count of cells on the same external form or report to get an average.

Domain Minimum: The DMin() function is used to retrieve the minimum value of the cells in a column of an external form or report

Domain Maximum: As opposed to the DMin() function, the DMax() function gets the highest value of a series of cells in the column of an external form or report.

Summary Queries

 

Introduction

Consider the following list of students:

Students

There are various types of statistics you may want to get from this list: you may want to know the number of students registered in the school, you may want to know the number of girls and the number boys, you may want to know how many students were born in each year, you may want to know the average age of the students, you may want to know the oldest or the youngest students, or you may want to know the number of students from each ZIP code. To assist you with getting these statistics, Microsoft Access provides a type of query called a summary query.

A query is referred to as summary if it provides one or various analytic statistics about the records.

Practical Learning: Introducing Summary Queries

  1. From the resources that accompany our lessons, open the Altair Realtors2 database
  2. On the Ribbon, click Create and, in the Other section, click Query Design
  3. In the Show Tables dialog box, click Company Assets, click Add, and click Close
  4. In the top section of the window, double-click Asset Type
  5. Double-click Asset Type again
  6. Double-click Purchase Price
  7. Double-click Purchase Price again

Creating a Summary Query

As always, when creating a query, you can use the Query Wizard or display one in Design View. If you are working in Design View, to make a query summarize its data:

  • In the Show/Hide section of the Ribbon, click the Totals button
  • Right-click the bottom section of the query window and click Totals

After applying the Totals feature, each column of the query would become equipped with a row named Total.

Although you can create a summary query with all the fields or any field(s) of a query, the purpose of the query is to summarize data, not to review the records, which you would do with a normal select query. For a good summary query, you should select a column where the records hold categories of data. This means that the records in the resulting list have to be grouped by categories. To support this, the SQL provides the GROUP BY clause. It means where the records display, they would be grouped by their categories. For example, if you want to get the number of students by gender from a list of students, you would select the column that holds that information, but you can select other columns also:

I you to get the number of students by gender from a list of students, it is normal that you be interested only in the column that holds that information

When the results come up, they would be grouped by their categories:

When the results come up, they would be grouped by their categories

As stated already, the purpose of a summary query is to provide some statistics. Therefore, it is normal that you be interested only in the column(s) that hold(s) the desired statistics and avoid the columns that are irrelevant:

The purpose of a summary query is to provide some statistic. Therefore, it is normal that you be interested only in the column(s) that hold(s) the desired statistics and avoid the columns that are irrelevant

If you select (only) the one column that holds the information you want, in the resulting list, each of its categories would display only once:

If you select (only) the one column that holds the information you want, in the resulting list, each of its categories would display only once

Practical Learning: Creating a Summary Query

  • In the bottom section of the window, right-click a cell and click Totals

Summarizing the Values

To get the types of statistics you want, in the Design View of the query, add the same column one more time, and click the Total box that corresponds to the column:

To get the types of statistics you want, in the Design View of the query, add the same column one more time, and click the Total box that corresponds to the column

In reality, a summary query uses some of the functions that ship with Microsoft Access:

  • Count: Microsoft Access uses the Count() function to count the number of occurrences of the category in the column and produces the total
  • First: Microsoft Access uses the First() function to get the first occurrence of the value in the category
  • Last: Microsoft Access uses the Last() function to get the last occurrence of the value in the category
  • If the column holds numeric values:
    • Sum: The Sum() function is used to sum up the values in the category
    • Avg: The sum of value in a category would be divided by the number of occurrences in that category to get the average
    • Min: The lowest value of the category would be produced from the Min() function
    • Max: The highest value of the category would be produced using the Max() function
    • StdDev: The standard deviation of the values of the category would be calculated using the StdDev() function
    • Var: The statistical variance of the values in the category would be calculated

If none of these functions is suited for the type of statistic you want to get, you can write your own expression or condition. To do this, select the Expression or the Where item. Then, in the Criteria box of the column, type the desired expression. If you select the Where option, type a valid Boolean expression that can evaluate to true or false.

Practical Learning: Summarizing

  1. In the bottom section of the window, change the header of the second column to Qty: Asset Type
  2. Click its Total combo box and select Count
  3. Change the header of the third column to Total Spent: Purchase Price
  4. Click its Total combo box and select Sum
  5. Change the header of the fourth column to Average: Purchase Price
  6. Click its Total combo box and select Avg
     
  7. Right-click the query and click SQL View
     
    SELECT 	[Company Assets].[Asset Type], 
    	Count([Company Assets].[Asset Type]) AS Qty, 
    	Sum([Company Assets].[Purchase Price]) AS [Total Spent], 
    	Avg([Company Assets].[Purchase Price]) AS Average
    FROM [Company Assets]
    GROUP BY [Company Assets].[Asset Type];
  8. Right-click the title bar and click Datasheet View
     
  9. Close the query
  10. When asked whether you want to save, click No
  11. Open the Altair Realtors2 database
  12. On the Ribbon, click Create and, in the Other section, click Query Design
  13. In the Show Tables dialog box, double-click Properties and click Close
  14. On the Ribbon, click the Totals button Totals
  15. In the list of fields, double-click Property Type, Market Value, Market Value, and Property Type
  16. In the bottom section of the window, change the header of the second column to Cheapest: Market Value
  17. Click its Total combo box and select Min
  18. Change the header of the third column to Most Expensive: Market Value
  19. Click its Total combo box and select Max
  20. For the fourth column, set its Total to Where
  21. Clear its Show check box
  22. Set its Criteria to NOT NULL and press Enter
     
    Summary
  23. Right-click the query and click SQL View
     
    SELECT 	Properties1.[Property Type], 
    	Min(Properties1.[Market Value]) AS Cheapest, 
    	Max(Properties1.[Market Value]) AS [Most Expensive]
    FROM 	Properties1
    WHERE 	(((Properties1.[Property Type]) Is Not Null))
    	GROUP BY Properties1.[Property Type];
  24. Right-click the title bar and click Datasheet View
     
    Summary
  25. Close the query
  26. When asked whether you want to save, click No

Parameterized Queries

 

Introduction

A parameterized query is a query that is missing a value in a criteria. The word "missing" here seems pejorative to indicate something bad in the query but it is not so. The query is purposely created like that so that a value would be provided to its filter. At the time the query is created, the filter is setup in a certain way. When the query is run, the missing value must be provided to complete the criterion, then the criterion is complete, and the result of the query is produced. Most of the time, a parameterized query is created so the user must specify the specific record to display.

Although we specified that the query is parameterized, it is in fact the SQL statement that is parameterized. This means that you can create a SQL expression used as the Record Source of a form or report, but oblige the user to provide the missing information so the form or report would display only the record that uses that value.

Practical Learning: Introducing Parameterized Queries

  1. The Altair Realtors2 database should still be opened.
    In the Navigation Pane, under Properties1: Table, right-click Properties1 (the form) and click Copy
  2. Right-click an empty area of the Navigation Pane and click Paste
  3. Set the Name to Property Review and click OK
  4. In the Navigation Pane, right-click Property Review and click Design View
  5. Double-click the button at the intersection of the rulers to access the Properties window

Creating a Parameterized Query

When creating a parameterized query, you must provide a parameter to it. To proceed, open the query in Design View and select the necessary columns. In the Criteria box of the field that will hold the criteria, type [] and, inside the [], enter anything you want. Here is an example:

Most of the time, you will enter a question. The question would be presented to the user from a message box:

Once the user provides the value and clicks OK or presses Enter, the query would run:

Practical Learning: Creating a Parameterized SQL Expression

  1. In the Properties window, click the Data tab, click Record Source and click its ellipsis button .
    If you receive a message box, read it and click Yes
  2. In the list of fields, double-click Property #, PropertyID, Date Listed, Address, City, State, ZIP Code, Property Type, Condition, Bedrooms, Bathrooms, Finished Basement, Indoor Garage, Stories, Market Value, Picture, and Description
  3. In the bottom section of the window, click the Criteria box for Property # and type
     
    [Enter the property number you want to see]
  4. Close the Query Builder
  5. When asked whether you want to save, click Yes.
    Notice that the Record Source now contains a SQL statement
  6. Close the form
  7. When asked whether you want to save, click Yes
  8. In the Navigation Name, double-click Property Review
  9. When asked to enter a property number, type a property number such as 749562 and click OK.
    Notice that the form shows a property and the Current Record display 1 of 1
     
    Notice that the form shows a property and the Current Record display 1 of 1
  10. Right-click the title bar of the form and click Design View
  11. Right-click the title bar of the form again and click Form View
  12. When asked to enter a property number, type a property number such as 247472 and press Enter
  13. Close the form

Action Queries: Table Creation

 

Introduction

We have used queries so far only to create a list of fields that would be considered for a set of records. We also mentioned that a query in Microsoft Access is simply a means of graphically representing data. Indeed, a query is based on a SQL statement. As SQL is its own, fully functional language, we can use it to perform far more operations than to only select columns for filtering. The SQL can be used to create tables, perform data entry, modify records, etc. Some of these operations can be performed visually in the query Design View. Some others can be performed using SQL statements. To know what is going on behind the scenes, you can write your own code. You can also use the Design View to start a query, and then open the code to customize the SQL statement.

Creating a Table in SQL

As a computer language, the SQL is equipped to perform all basic and necessary operations of a database. As such, it can be used to create a table. Although you will usually use the visual means of Microsoft Access, you can still use a SQL statement to create a table. In Microsoft Access, to create a table using SQL code, start a query in Design View but do not select any table for it. Then, display the SQL View and write your code. To execute the statement, you can run it.

In the SQL, to create a table, you start your statement with the CREATE TABLE expression followed by the desired name of the table as follows:

CREATE TABLE TableName

As you have probably seen by now, every table must have at least one field (or column). The list of columns of a table starts with an opening parenthesis "(" ends with a closing parenthesis and an optional semi-colon ");". If the table will be made of more than one column, you can separate them with a comma. The formula to use is:

CREATE TABLE TableName (Column1, Column2, Column_n);

To make the statement easier to read, and because some columns can be long, you can create each on its own line. The syntax would become:

CREATE TABLE TableName
(
Column1,
Column2,
Column_n
)

To create a column, you specify its name, followed by its data type, and some possible options. Therefore, the syntax of creating a column is:

ColumnName DataType, Options

The name of a column can be in one or many words. If you put space after the first word, the SQL engine would treat the next word as a data type. For example, the following statement would produce an error:

CREATE TABLE Employees
(
Last Name
)

If you want to use space in a column name, include it between an opening square bracket “[“ and a closing square bracket “]”. The above statement would be changed to:

CREATE TABLE Employees
(
[Last Name]
)

The Data Type of a Field

Here are the SQL data types supported in Microsoft Access (remember that the names of data types are not case sensitive):

Data Type Description
Char or Text The char or the text data types can be used for columns whose fields would receive (or present) text. The field must not contain more than 255 characters.
This is the same as Microsoft Access’ Text data type.
Varchar This is one of the most regularly used data types of a SQL column. It can used for strings (text) of any kind.
Memo This is for a field that should hold up to 65656 characters.
This the same as Microsoft Access’ Memo.
Bit This is used for a Boolean field that accepts only a True or False, a Yes or No, and Off or On, or a 0 or –1 as a value.
This is equivalent to the Yes/No data type in Microsoft Access.
Smallint The smallint data type can be used for a field that would hold numbers that can range from –32,768 to 32767.
Int or Integer Each of these data types can be used to represent a natural number.
This is the same as the Integer in Microsoft Access.
Long This is used for fields that would hold small to very large natural numbers.
This is the same as the Long Integer option in Microsoft Access.
Real Real is a relatively small data type in the world of double-precision representation but can be used on a column whose fields would hold numbers that can range from – 3.402823E38 to – 1.401298E-45 for negative values or from 1.401298E-45 to 3.402823E38 for positive values.
This data type is close to Microsoft Access’ Single.
Float The float data type can be used on a column whose fields would hold numbers with a decimal portion. Like the Single data type in Microsoft Access, the float is mainly used on a column where number precision is not a big issue.
Numeric The numeric data type can be used on a column whose fields would hold numbers with a decimal portion. It is close to the Microsoft Access’ Double data type and can be used when numeric precision is needed.
Double This is the same as Microsoft Access’ Double data type.
Money or Currency This data type is appropriate for fields that would hold numbers that represent monetary values.
Datetime Equivalent to Microsoft Access Date/Time data type, the DATETIME data type can be applied to a column whose fields would display either date, time or both date and time values.
Binary The binary data type can let a field accept any type of data but it is equipped to interpret the value. For example, it can be used to receive hexadecimal numbers.
Image This can be used for fields that would hold OLE Object equivalents

Here is an example that creates a table named Persons:

CREATE TABLE Employees
(
    EmployeeNumber char,
    [Date Hired] DateTime,
    FirstName VARCHAR,
    [Last Name] Text,
    Gender char,
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image,
    [Employee Review] Memo
);

Table Creation With SQL

The Field Size of a Field

When studying the techniques of creating fields in a table’s Design View, we saw that a property called Field Size could be used to specify the size of the value used on a field. In the SQL also, this property is fixed for most fields expect those that are text-based. Therefore, when creating a field whose data type is char, text or varchar, you can optionally specify the desired number of characters that the field should allow.

To specify the maximum number of characters of a text-based field, include it in parentheses just to the right of the data type. Here are examples:

CREATE TABLE Employees
(
    EmployeeNumber char(10),
    [Date Hired] DateTime,
    FirstName VARCHAR(40),
    [Last Name] Text(50),
    Gender char(1),
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image,
    [Employee Review] Memo
);

The Nullity of a Field



By default, the user is not required to provide a value for each field when performing data entry. This is because, by default, each field is set to NULL. This is equivalent to setting the Required property of a field to No (which is the default) in a table’s Design View. As this property is to No in the Design View, you can also specify it by typing NULL on the right side of any column you want. Here are examples:

CREATE TABLE Employees
(
    EmployeeNumber char(10),
    [Date Hired] DateTime,
    FirstName VARCHAR(40) NULL,
    [Last Name] Text(50),
    Gender char(1) NULL,
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image NULL,
    [Employee Review] Memo
);

If you do not specify the NULL option, the nullity is implied. On the other hand, if you want to require the values of a field so the user cannot move to the next record unless the field is filled, type NOT NULL to its right. This would indicate to Microsoft Access that the field does not allow a null value. Here is an example:

CREATE TABLE Employees
(
    EmployeeNumber char(10),
    [Date Hired] DateTime,
    FirstName VARCHAR(40) NULL,
    [Last Name] Text(50) NOT NULL,
    Gender char(1) NULL,
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image NULL,
    [Employee Review] Memo
);

The Uniqueness of Values

During data entry, the user is allowed to type the same value in the same fields of two different records. For example, it is not unusual for two people to have the same last name. In some other cases, you may want each record to have a different value for a particular field. For example, a company may not allow two employees to have the same employee number or the same security numbers, just like two cars should not have the same tag number in the same state. To communicate this, on the right side, type the UNIQUE keyword. Here is an example:

CREATE TABLE Employees
(
    EmployeeNumber char(10) UNIQUE,
    [Date Hired] DateTime,
    FirstName VARCHAR(40) NULL,
    [Last Name] Text(50) NOT NULL,
    Gender char(1) NULL,
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image NULL,
    [Employee Review] Memo
);

Practical Learning: Creating a Table With SQL

  1. To create a new database, press Ctrl + N
  2. Set the name to Clarksville Ice Cream2 and click Create
  3. Close the default table without saving it
  4. On the Ribbon, click Create and, in the Other section, click Query Design
  5. In the Show Table dialog box, click Close
  6. Right-click the middle of the window and click SQL View
  7. Change the statement in the Query1 window as follows:
     
    CREATE TABLE Employees
    (
        EmployeeNumber char(10) UNIQUE,
        [Date Hired] DateTime,
        FirstName VARCHAR(40) NULL,
        [Last Name] Text(50) NOT NULL,
        Gender char(1) NULL,
        MaritalStatus Integer,
        HourlySalary Money,
        [Employee Picture] Image NULL,
        [Employee Review] Memo
    );
      Table Creation
  8. To save the query, click the Save button Save
  9. Type Table Creation as the name of the query and press Enter
  10. To execute the query, on the Ribbon, click the Run button.
    If nothing happens, on the Message Bar, click the Options button. Click the Enable This Content radio button, click OK. Then, in the Navigation Pane, double-click Table Creation
  11. Read the message on the message box:
     

     
    Then click Yes 

SQL and Data Entry

After creating a table, you should populate it with the necessary values. You can open the table from the Navigation Pane and enter the values as we have done so far. The SQL provides the actual means of performing data entry on a table. In the language’s standard, you can create a table and fill it with values. Alternatively, you can add various new records to the  fields of an existing table. This is also referred to as inserting records. In the SQL, data entry is performed using the INSERT INTO expression, followed by the table name, followed by the VALUES keyword, and followed by the values in parentheses. If the table is made of only one column, you can include the desired value in the parentheses. If the table is made of more than one column, you can separate the values with commas. The syntax to use is:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName attribute must be the name of an existing table of the current database. If the name is wrong, the SQL would consider that the table you are referring to does not exist. Consequently, you would receive an error. The VALUES keyword indicates that you are ready to list the values of each field of the record. The values of the columns must be included in parentheses.

In the above syntax, the value of each field of the column must be entered in the exact order of the columns as they were created in the table. Fortunately, the SQL allows you to perform data entry in the order of your choice. To do this, when creating the statement, after specifying the name of the table, open the parentheses and type the order of the columns in the order of your choice but make sure you type valid names of existing columns. Then, in the parentheses of the VALUES attribute, type the values in the order specified in the parentheses of the table name. This random order of fields presents another advantage: it allows you to specify only the fields whose values you want to enter.

If the column if a BIT data type, you must specify one of its values as 0, 1 (or –1), True, False, On, Off, Yes, or No.

If the column is a numeric type, you should pay attention to the number you type. If the column was configured to receive an integer (Int, Smallint, Integer, or Long), you should provide a valid natural number without the decimal separator.

If the column is for a decimal number (real, float, single, double, or numeric), you can type the value with its character separator (the period for US English).

If the column was created for a date data type, make sure you provide a valid date or a valid time.

If the data type of a column is a string type, you can include its value between either single or double quotes. For example, a shelf number can be specified as 'HHR-604' and a middle initial can be given as 'D'.

Practical Learning: Performing Data Entry With SQL

  1. The Clarksville Ice Cream2 database should still be opened.
    On the Ribbon, click Create and, in the Other section, click Query Design
  2. On the Show Table dialog box, click Close
  3. Right-click the query window and click SQL View
  4. Change the SQL statement as follows:
      
    INSERT INTO Employees(EmployeeNumber, [Date Hired], FirstName, [Last Name])
    VALUES('287495', 12/08/1998, 'Alex', 'Cozart');

    Insert

  5. To execute the query, click the Run button Run
    You will receive a message box
  6. Read it and click Yes
  7. To add another record, change the statement as follows:
     
    INSERT INTO Employees(EmployeeNumber, [Last Name], Gender, HourlySalary)
    VALUES('227947', 'Jameson', 'M', 18.85);
  8. Execute the statement and click Yes
  9. Close the query
  10. When asked whether you want to save, click No
  11. In the Navigation Pane, double-click the Employees table to see its records

The Make Table Action Query

Instead of first creating a table then filling it with values, if you already have values stored in a table, Microsoft Access allows you to create a new table filled with values from that table. This is done through an action query. Creating the table is just one of the actions that Microsoft Access provides through a query. It is important to know that, when using an action query to perform a specify action such as creating a table, you are not creating a regular query in the strict sense. Most of these queries are usually useful only once. For this reason, you will hardly need to save them, as the action may be needed only once. If you perform the action of the query, you do not need to save it but once you apply its intended action, the related action is executed and stays with the database even if either you do not save the query or save it but later on delete the query.

Using queries, there are two main techniques you can use to create a new table for your database. Microsoft Access provides a technique called Make Table Query. This type of query is used to retrieve all or some fields of an existing table and, instead of creating a new query that depends on an existing table, you would get a brand new table, independent of any existing table. This type of action can let you create a new table based on a rule applied to an existing table. For example, suppose the Cars table of your car rental database contains cars that should not be rented to customers perhaps because of their age. You can create a query that would make a list of cars that follow this rule, apply it, and then create a new table of only the necessary cars.

To create a table using a Microsoft Access query, start a new query in Design View. Then, in the Query Type section of the ribbon, click the Make Table button . This would present you with a Make Table dialog box that allows you to specify a new name for the query or to select an existing table that would receive the new data.

Practical Learning: Using Make-Table Queries

  1. Open the Bethesda Car Rental1 database
  2. In the Navigation Pane, double-click the Cars table to open it. Notice that some cars have the year set to 2000, 2002, 2003, 2005
  3. Close the table
  4. On the ribbon, click Create and, in the Other section, click Query Design
  5. On the Show Tables dialog box, click Cars, click Add, and click Close
  6. In the Query Type section of the Ribbon, click the Make Table button
  7. In the Table Name combo box, type Cars to Consider Retiring as the name of the table
     
    Make Table
  8. Make sure the Current Database radio button is selected and click OK
  9. In the Cars list, double-click Tag Number, Make, Model, Car Year, and Condition
  10. To set the criteria for the cars that need to go, click the Criteria field of the Car Year column and type <= 2005
     
    Make Table Query
  11. To preview the list of cars that will be considered, on the ribbon, click the View button
  12. Close the query
  13. When asked whether you want to save it, click Yes
  14. Set the name to Create A List Of Retiring Cars as the name of the query and press Enter
  15. To execute the action, in the Navigation Pane, double-click Create A List Of Retiring Cars.
    If nothing happens, on the Message Bar, click the Options button. Click the Enable This Content radio button
     
    Microsoft Office Security Options
     
    Click OK. Double-click Create A List Of Retiring Cars again
  16. You will receive a message box
     
    Make Table
  17. Read it and click Yes
  18. You will receive a second message box

    Make Table

    Read it and click Yes
  19. In the Navigation Pane, double-click Cars to Consider Retiring: Table to review it
  20. Close the table

Action Queries: Appending Records

 

Introduction

When working in a sensitive database, if you were the one in charge of data entry, you may prefer to use a temporary table to create records to make sure they are validated before actually adding them to the system. If you had created a significant number of these records and need to add them to the main table, the work can become overwhelming. Fortunately, Microsoft Access allows you to create a special query that can be used to add many records to a table in one step.

An Append Query allows you to add records to an existing table but you do not create the records. They must be retrieved from one table and transferred to another table. For example, in our Bethesda Car Rental1 database, imagine the company had acquired many cars at once but they were created in their own table waiting to receive final approval after inspection. Now that this has been done, you can add them to the list of cars that is made available to the clerks who process data entry.

Creating an Append Query

To start an Append Query, start a query in the Design View. In the Query Type section of the Ribbon, click the Append button Append. You will be presented with a dialog box that expects you to either select the table to which you will add the records, or to specify the name of a new table that would receive the records. If you want to add the records to an existing table, you can select it from the combo box.

As you may imagine, appending a record consists of adding a record to a table. If you want to do this in SQL, simply create an INSERT statement as we saw with the Make Table Query.

Practical Learning: Appending Records

  1. The Bethesda Car Rental1 database should still opened.
    In the Navigation Pane, double-click Cars: Table to open it
  2. Notice the number of cars in the current table (39). After viewing the table, close it
  3. In the Navigation Pane, double-click the Cars Reviewed and Approved table to open it
  4. After viewing the table, close it
  5. On the ribbon, click Create and, in the Other section, click Query Design
  6. In the Show Table dialog box, click Cars Reviewed and Approved
  7. Click Add and click Close
  8. In the Query Type section of the Ribbon, click the Append button Append
  9. In the Append dialog box, click the arrow of the Table Name combo box, and select Cars
     
    Append
  10. Click OK
  11. In the Cars Reviewed and Approved list, double-click the [Tag Number], Make, Model, [Car Year], Category, Doors, Available, and Condition
  12. To save the query, click the Save button Save
  13. Type Add New Cars to the Application as the name of the query and press Enter
  14. To see the SQL statement, right-click the title bar of the Query1 window and click SQL View
     
    INSERT INTO Cars ([Tag Number], Make, Model, [Car Year], 
    		   Category, Doors, Available, Condition )
    SELECT  [Cars Reviewed and Approved].[Tag Number], 
    	[Cars Reviewed and Approved].Make, 
    	[Cars Reviewed and Approved].Model, 
    	[Cars Reviewed and Approved].[Car Year], 
    	[Cars Reviewed and Approved].Category, 
    	[Cars Reviewed and Approved].Doors, 
    	[Cars Reviewed and Approved].Available, 
    	[Cars Reviewed and Approved].Condition
    FROM 	[Cars Reviewed and Approved];
  15. Close the query
  16. To execute the query, In the Navigation Pane, double-click Add New Cars to the Application.
    If nothing happens, on the Message Bar, click the Options button. Click the Enable This Content radio button, and click OK.
    Then, in the Navigation Pane, double-click Add New Cars to the Application
  17. Read the strings on the message box:
     
    Append
     
    Then click Yes
  18. Another message box will come up:


     
    Read it and click Yes
  19. Close the query window
  20. From the Navigation Pane, open the Cars table to verify that the number of cars has increased

The Update Query

If you have a large database and there are many records that need to receive a common change, you may face an arduous task. To apply this type of impact to a set of records, you can create a special query. To assist you with updating many record at the same time, Microsoft Access provides an action query named Update Query. An Update Query allows you to change the existing values of one or more columns of a table. In other words, you can retrieve the values that are already in the fields of a table, change these values and have the table saved with them.

To create an Append Query, start a query in the Design View. In the Query Type section of the Ribbon, click the Update button Update. When creating the query, you will have to set a condition that Microsoft Access will apply on a table to find out what record(s) need(s) to be updated. To do this, you use the Criteria box of a column, just like we learned during data analysis.

Practical Learning: Updating Records

  1. The Bethesda Car Rental1 database should still be opened.
    In the Navigation Pane, double-click the Cars table to open it
  2. Notice that some cars have the year set to 2000, 2002, or 2005
  3. Close the table
  4. On the ribbon, click Create and, in the Other section, click Query Design
  5. On the Show Tables dialog box, click Cars, click Add, and click Close
  6. In the Query Type section of the Ribbon, click the Update button Update
  7. In the Cars list, double-click Car Year and Condition
  8. To set the criteria for the cars that need to go, click the Criteria field of the Car Year column and type <= 2005
  9. To specify the value to set on the column, click the Update To field for the Condition column and type "Must be Retired"
     
    Update Query
  10. To see the SQL code, right-click the query and click SQL View
     
    UPDATE Cars SET Cars.Condition = "Must be Retired"
    WHERE (((Cars.[Car Year])<=2005));
  11. Close the query
  12. When asked whether you want to save it, click Yes
  13. Set the name to Mark the Cars to Retire as the name of the query and click OK
  14. To execute the action, in the Navigation Pane, double- click Mark the Cars to Retire
  15. You will receive a message box
     
    Update Query
  16. Read it and click Yes
  17. You will receive a second message box

    Make Table

    Read it and click Yes
  18. In the Navigation Pane, double-click Cars: Table to review it. Notice that all cars whose years are set to before 2006 have a new value in the Condition
  19. Close the table

The Delete Query

If you have a few records that need to be removed from a table, you can delete them and Microsoft Access provides various techniques to do it. In the SQL, to delete one or more records, you use the DROP TABLE operator. The syntax is:

DROP TABLE TableName;

The DROP TABLE command is used only to delete tables (and indexes), not forms or reports. The TableName parameter must be a valid name of a table of the current database.

There are two big issues with the DROP TABLE command: it does not warn you and it is not reversible.

Besides or instead of deleting a table, you may want to delete individual records of a table. Microsoft Access provides an easy mechanism of performing such an operation. To delete a group of records in one action, you can create a special query called a Delete Query.

Like all other Action queries, a Delete Query allows you to select the necessary fields of a table. The first difference between this and the Select Query is that the Delete Query, like all other action queries, selects or considers all fields, even those you do not include in your statement. The other columns are those that would be used to specify the rules under which a record must be deleted.

You can use a criterion or many criteria to set the rule to follow in order to get rid of a record. Like most other action queries, the action of a Delete Query is irreversible.

In SQL, to delete a column, the syntax to use is:

ALTER TABLE TableName DROP COLUMN ColumnName;

The ALTER TABLE and the DROP COLUMN expressions are required. The TableName factor is the name of the table that holds the column you want to delete. The ColumnName is the name of the column you want to remove from the table.

Practical Learning: Deleting Database Records

  1. The Bethesda Car Rental1 should still be opened.
    In the Navigation Pane, double-click Cars: Table to open it
  2. Notice that a few records are set as must be retired. Close the table
  3. On the ribbon, click Create and, in the Other section, click Query Design
  4. On the Show Tables dialog box, click Cars, click Add, and click Close
  5. In the Query Type section of the Ribbon, click the Delete button Delete
  6. In the Cars list, double-click Condition
  7. To set the criteria for the cars to be deleted, click the Criteria field of the Condition column and type "Must be Retired"
     
    Delete Query
  8. To see the SQL code, right-click the query and click SQL View
     
    DELETE 	Cars.Condition
    FROM 	Cars
    WHERE 	(((Cars.Condition)="Must be Retired"));
  9. Close the query
  10. When asked whether you want to save it, click Yes
  11. Set the name to Remove Old Cars From the Application as the name of the query and click OK
  12. To execute the action, in the Navigation Pane, double- click Remove Old Cars From the Application
  13. You will receive a message box
     
    Delete Query
  14. Read it and click Yes
  15. You will receive a second message box

    Delete Query

    Read it and click Yes
  16. In the Navigation Pane, double-click Cars: Table to review it. Notice that there is no more cars set to be retired
  17. Close the table

Lesson Summary

 

Exercises

 

World Statistics

  1. Open the World Statistics1 database
  2. Open the Countries form and show only the countries that got their independence in 1960
  3. Show the countries that have their national holiday in July
  4. Do not save anything

US Senate

  1. Open the US Senate1 database
  2. Create a query that includes the name of a senator and another new column named Years in Office. The Years in Senate field would have an expression that subtracts the year the senator was elected from the current year to get the number of years the senator has been in office. Save the query as Years in Senate

Previous Copyright © 2008-2012 FunctionX Next