Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Archive for the ‘Access Queries’ Category

Action Queries-Delete Queries

QueryDelete

Delete queries delete all records from the specified table that meet criteria specified in the query.

 

 

Scenario: The table of country names that you have inherited contains some invalid names. You have already updated the records that used to point to these names so they now point to the correct form of the relevant country name. Now you want to remove the invalid names from the table.

 

Before and After

With just a few entries, you could just open the table and delete the bad records. However, if you have many records to delete, a delete query can take care of all of them at once.

IMPORTANT: Before running a query that will modify your data, it is very important to first back up the data. You can do this either by copying the table’s structure and data or by backing up the entire database.

Setting up the query is quite straight forward. Use the table you want to clean up as the query’s record source and include the field that will determine whether or not a record should be deleted.

qdelDeleteCountriesTypos

 

This query will delete records that contain “United States” in the Country field. All other records will be untouched.

If you include additional criteria on the rows below the first criteria, then the  query will delete records that match any one of the criteria.

qdelDeleteCountriesTyposAll

 

 

 

 

 

Just to be safe, however you should not run a delete query before you view it. Notice the distinction between viewing and running the query. ViewDatasheet

 

Viewing a query will display the selected records in the query’s datasheet view.

QueryRun

Running the query, on the other hand, actually deletes the selected records.

 

Note also that double-clicking a query icon in the database window or Navigation Pane, runs the query. If you are not absolutely sure you should be running a query, open it in design view and view it in datasheet view.

Sample files for Action Queries (Access 2000 and 2007/2010 format) can be downloaded from:

 

Action Queries-Update Queries

QueryUpdate

Update queries are the means to modify record data in one or more fields in all the records that match the criteria you specify. For this article, I have modified the sample data so that it reflects an all too possible scenario.

Scenario: You have inherited a customer database where customer data was stored in a single table. You have normalized the table. When you created and populated the Countries table, you didn’t recognize that the country data inserted into the original table was inconsistent. Some country names were mis-spelled (Mxecio, United Staes, for example) and several forms of the name were used for others (US, USA, and United States, for example). The problem came to light with the Regional Manager for Mexico reported that one of more of his customers appeared to be missing when he search for customers in Mexico.

Solution: When you looked at the Countries table you recognized that the data had spelling variations for some countries. You have produced a new table with the correct spelling for each country’s name and there are no variations in the form of the name.

imageNow you want to clean up the data so that your normalized customers table will refer only to the new Countries table. You can’t simply delete the bad spellings because some of the records in your new customers table probably refer to these imagemisspelled country name records.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The solution requires first identifying which customer records refer to bad country name and then updating those records so that they refer to the correct spelling. The query results at the right is a comparative list showing the current list of country names together with a list of only valid names. In design view the query looks like this:

image 

Notice that the tables are joined by the country names, not by keys. An empty cell in the valid country column indicates that the name in the cell in the left column is invalid. This query gives us the information we need to know which records to update and what CountryID should be used for the update.

In the Customers table, these changes need to be made to the CountryID field

  • Mxeico 13 update to 12
  • United Staes 21 update to 24
  • United States 22 update to 24
  • US 23 update to 24

Because each of these updates has different criteria, four update queries will be required. (As you will see, you use a single query and simply modify the criteria after each update.) After we execute these updates, the customer table will correctly refer to Mexico and USA, respectively in the Countries table. Then the invalid names can be deleted from the Countries table.

A simple unmatched records query will help identify just the customer records that need attention. It is similar to the last query but this is what the results will look like:

image

The Is Null Criteria limits the output to just the unmatched records:

image

Now we are ready to create the actual update queries. This query will need the valid country id with which we want to update the affected Customer records:

image

This query includes the Company name field simply for “comfort.” The table requiring update is joined in this case to its related Countriesimage table so that we can restrict the record by the actual. Before running an action query it is usually a good idea to view the record selection you have specified. However, the datasheet view of an update query only shows you the field to be updated with its current value. To see more about the records that will be updated, you can temporarily convert the query to a select query and the view its datasheet.

image

Again you are only seeing the current data but you can verify that you have in fact set the criteria correctly. Now all that remains for the update is to convert the query back to its update form and then run it.

After you run the query for the first incorrect country name, you can change the query to the next misspelling, verify and run it. You simply repeat this process until you have updated the Country Id for all of the misspelled country names, one at a time.

It’s a good idea at this point to create a query to verify that the invalid country names are no longer referenced from the Customers table.

Remember: never run any data manipulation queries before backup up the affected tables and or the entire database.

Final cleanup involves deleting the records with the bad spelling. The next article in this series will discuss Delete Queries

Action Queries–Make Table (Part II)

Creating the new customer table is just a little different from the three make table queries in part one.

image

The query includes the original customers table and the three new tables. Note that the customers table is joined to each of the other tables by the field that contains the data pasted into the new tables by the respective make table queries. Some of the customer records may not have data in one or more of the Address, State/Prov and Country fields. The join types have each been modified so that all customer records will be included.

If we did not change the join type from the default equal join, then the only records the query would produce would be those that had data in the Address, State/Prov and Country fields in the original table. With the join type changed, the query will produce nulls whenever there is no corresponding value to the customer’s address, state or province, or country.

We can use the query grid to modify the field names and data that will be inserted into the new table.

image

In the query grid, each field cell begins with the name we would like to use for the field in the new table. Since not all customers are not companies and the table is the Customers table, CustomerName is probably a better field name for this data. So the expression

CustomerName:Company

is in the field cell of the first column. So the data that is in the company field in tblCustomers will be pasted into the CustomerName field in the new table.

For the Address, State/Prov, and Country fields, we want the new table’s data to be the Primary Key field of the respective tables that now contain list of unique values for those fields. We can’t use and current names in the source tables for these fields in the query and for each of them a calculation is required to deal with possible null values. So we will use the names Address, State, and Country. After the table is created we will manually edit it so that these field names indicate that they are key values.

The three calculations are

Address:IIF(IsNull([AddressID]),Null,clng([AddressID]))

State:IIF(IsNull([State_ProvID]),Null,clng([State_ProvID]))

Country:IIF(IsNull([CountryID]),Null,clng([CountryID]))

Each of these calculations serves the same purposes

  • Name the corresponding field in the new table appropriately
  • If there is a null value in the query results populate the corresponding field in the new record with null.
  • Convert non-null values to the long datatype  because the three source fields are autonumbers

Set the Make Table up using the name tblCustomers_. Once the new table is created, we will change the name of tblCustomers to ztblCustomers and the name of the new table to tblCustomers so that we can retain the original table as a backup and continue to use the name tblCustomers for the ‘real’ data.

Executing (running) the query produces a new table in the database:

image

All that remains to be done is to modify the table’s design so that it has a primary key and appropriate field name and then create the relationships.

The modified and renamed new table looks like this:

image

Creating the relationships finishes the process:

image

Using a series of Make Table queries in this tutorial, we have taken the data from an unnormalized table are produced a normalized table of customers with three related tables.

Action Queries–Make Table

QueryMakeTable

As the name implies, a Make Table Query creates a new table and populates the table with data selected by the query. In this article we will use an example that extracts data from an un-normalized table and creates a normalized structure with the data distributed into appropriate tables.

In this scenario, we have inherited a database that someone else designed. One of the tables, tblCustomers, includes customer name and address information. The table imagestructure looks straightforward enough but a look at the design reveals that the Address, State/Prov, and Country fields are all text fields.

What’s the problem? From a normalization standpoint, this table has repeated data. From a practical standpoint it means that for every record the name of the address, state or province, and country, must be typed. There is an inherent risk of data entry errors in which one of more of these values may be mis-spelled. There are also possible inconsistencies where a person may enter the abbreviation for the state or province in one record and the full name or an incorrect abbreviation in another record. Such errors increase the complexity of querying the data can often result in inaccurate query results.

If this data is properly normalized, then it is possible to use combo-boxes to restrict what data can be entered in a field to a list of valid values.

This example will use a series of queries to create normalized data from single unnormalized tblCustomers. The procedure for creating a make table query is similar to that for creating an append query. Start by creating a new query in design view. In Access 2007/2010 look for the Make Table shortcut on the Query Tools/Design tab of the ribbon:

 

image

QuerySelectQueryTypeIn Access 2003 and earlier, look for the Query Type icon on the Query Design toolbar. If this is the first action query you create the image will represent a select query. If you have previously created one of the other query types, the image will represent the type of the last query you created.

With the query open in design view, add the table from which you wish to extract data for the new table to be created.

When you click the Make Table Icon, the Make Table dialogue will pop up. The Table Name: text box will be empty. Type the name that you want to use for the new table in the textbox and click OK.

image

The address field is obviously the field that I want to send to the new table so I have added it to the grid. However, before we can actually run the query we have to set it up so that the query will return only unique values and will not return any nulls (addresses,for example) that have no data at this time). Notice that I have clicked the Totals shortcut and left the value in the Totals Row as group by. I have also specified Is Not Null in the first Criteria row.

image

When you click the Run shortcut, a dialogue appears, advising how many rows are about to be pasted into the new table.

image

There are 91 records in the customers table and it appears that there are no duplicate addresses. When you click yes the table is created and the selected data pasted into it. (In the sample database, I have saved the query as qmakAddresses.)image

Now is a good time to modify the new table so that it has an appropriate primary key. We will use the primary key later to create a relationship between each of the new tables and the modified customer we will create.

In the sample database, I have repeated this process to create a new State/Province table and a new Country table. When I reviewed the State/Province data, I noticed some inconsistencies. In some cases the field was the full name of the state or province. In other, it was the abbreviation. So, when I modified the table to include a primary key, I added a new field for abbreviation. When the revision of the structure is complete, some data maintenance on the State/Province table will be required to ensure the name is spelled correctly and that each record has both a full name and abbreviation.

The original table also had a special character in the state/province field name. So when I was modifying the table’s design, I changed that field name to remove the slash.

Two tasks remain. First we have to create a new customers table with fields that link to the new address, state/province, and country tables and its own primary key. Then we have to create appropriate relationships for the four new tables we have just created.

We will take a look at creating the normalized customers table in the next part of this tutorial (Update Queries)

Action Queries–Append

QueryAppendAn append query, copies records from one data source (table or query) and appends them to another table. Only data in fields whose names are the same in the source and target is inserted into the destination table. If a field in the source does not exist in the destination, the data in that field simply goes nowhere. If a field in the destination does not exist in the source that field in the appended records will have a null value. As you will see in the example, however, the append query definition does let you specify a target field whose name is not an exact match to one of the source fields.

Scenario: You have received some data in an Excel worksheet that you want to import into your database. You have created a link to the Excel worksheet and named the link tblPeople. In your database, the table where you plan to maintain this type of information about people is tblHumanResources.

The first step is to create a query in design view using the source table:

image

In Access 2007 and 2010, look for the Append command in the Query Type group on the Ribbon Design tab.

image

QuerySelectQueryTypeIn Access 2003 and earlier, look for the Query Type icon on the Query Design toolbar. If this is the first action query you create the image will represent a select query. If you have previously created one of the other query types, the image will represent the type of the last query you created.

When you click the Append shortcut, the Append dialogue will open. Use the Table Name: dropdown to select the table to which you want to append data.

image

The query grid will change to include a new row, Append To:

image

Cells on the append row will be populated when there are matches in the field names. In this case the Human resources table has fields names Lastname, Firstname, Middlename that correspond to the differently named fields Lname, Fname, and MName in tblPeople. In order to ensure that all needed data is inserted into tblHumanResources, pick the relevant field names from the dropdowns in the Append To: row.

If you have specific requirements to include or exclude certain records, you would enter criteria into the appropriate grid columns.

The finished query to append records from tblPeople into tblHumanResources looks like this:

image

The SQL for this query is:

INSERT INTO tblHumanResources ( Lastname, Firstname, Middlename, Honorific, BirthDate )
SELECT tblPeople.Lname, tblPeople.Fname, tblPeople.Mname, tblPeople.Honorific, tblPeople.BirthDate
FROM tblPeople;

Caution: ViewDatasheetBefore running the query (and performing its action, appending data to tblHumanResources in this case) your should back up the database or table that will be modified. You should also view the query in datasheet view to ensure that it is selecting all the data, and only the data, that you want to append to the destination table.

 

Running the query: Once you have backed up the data and reviewed theQueryRun data to be appended, you can run the query. All selected records from tblPeople will be appended to tblHumanresources. In Access 2007 and 2010, the View and Run buttons are in the Results group of the Ribbon’s Design tab. In Access 2003 and early you will find these shortcuts on the Query Design tool bar.

 

image

After you run the query, the selected records will be appended to the destination table. If there are data validation rules set in the destination tables, any record whose data violates those rules will not be appended.

Action Queries–Introduction

The name query implies that you are asking a question. Indeed, that is what selection queries do. However, action queries perform actions that change data in some way or other.

Action queries come on four ‘flavours.’

  • Append
  • Make Table
  • Delete
  • Update

It is important to keep in mind that an action query will perform its action on all records that meet its criteria. The same action is applied to all records so an action query is not an appropriate way to modify the data of individual records.

Append queries select data from the query source and append them to a specified table.

Make Table queries select data from the query source and create a new table containing just the selected records.

Delete queries remove all records that match the query criteria from the data source.

Update queries modify the contents of one or more fields in the query’s data source.

You follow the same general rules and procedures for creating action query criteria as you do for creating criteria in simple selection queries. Before executing an action query is very important to view the records selected. Otherwise you may end up making unwanted changes to your data. As an added precaution against unwanted data changes, you should also back up the database or at least the table(s) that will be affected by the query.

To create an action query start by creating a selection query in design view. In Access 2003 look for the Select Query Type Icon on the Query Design toolbar:

A2003SelectQueryType

In Access 2007 and 2010 look for the Query Type group on the Query Tools Design Tab.

QueryDesignRibbon_QueryType

Depending on the query type you choose, the design grid will be modified. If you choose a Make Table or Append query type, a dialogue will appear to allow you to enter the name of the target table.

In the next four articles in this series, we will look at the specifics of each of the action query types in detail.

Parameter Queries

Parameters simplify query creation by allowing single query to produce different results depending on values you enter when you view the query. This lesson is intended to introduce the theory of parameters and the basics of using them in queries. The examples we are using are not forms driven. In a ‘real’ application all user interaction with the data is done through forms and the use of parameters is much more streamlined than the following examples might lead you to think.

image

In this query definition, the year, 2011, is entered as a literal value in the calculation of the review date. The only thing this query can calculate is a review date in 2011. Without the use of parameters, every year we would have to create a new query or re-design this one in order to calculate a new set of review dates.

Now, let’s look at a similar query, this time with a parameter in the calculation instead of a literal year value. Only one small modification is required. In place of 2011 for  the imagefirst argument of the DateSerial function, we put in [year]. Parameters are always enclosed in square brackets. Whatever text you type between the brackets will be used to prompt the user when the query is run. This text should not be the same as any of the field names in the query’s data source.

When you run the query, Access displays an input box with the text you placed between the square brackets as its prompt:

image

 

If you type 2011 in the input box and press enter or click OK, Access will substitute that value for the parameter in the calculation and produce a list of review dates in the year 2011.

When it is time for 2012 reviews, you can run the query again, this time entering 2012 into the input box instead of 2011.

 

Parameters are most frequently used to specify query criteria. Here’s an example that uses a parameter to specify the month in which increases come into effect. So, when

image

the query runs, an input box requesting the month appears. The payroll department could use a query like this to determine which employees to update on the first run of any given month. The last column of the grid handles cases when the user does not enter a month and causes the full list to be displayed.

image

The Parameters Dialogue

While most parameter queries do not require its use, the parameters dialogue serves two important purposes. First it lets you control the order in which the parameters are requested. Second it lets you specify a data type for the value to be entered.

imageIt is not always possible to clearly indicate the data type that you expect the user to enter. For example, the month value in the example queries in this article is a number. That is because the effective month calculation produces a number between 1 and 12. By specifying a byte type, you are ensuring that the user will receive a more meaningful error message if they type a month name than if you did not use the parameters dialogue to explicitly state that a numeric value is expected. To open the parameters dialogue, right-click in the top portion of the query designer and select Parameters.

If the query refers to the same parameter more than once, it is important that the references have identical spelling. Otherwise, Access will treat the variations as additional parameters.

Summary Queries

Download the demonstration database for this article from

In the previous article in this series, we looked at creating calculated query fields. While they not strictly speaking doing calculations, totals queries provide the means to summarize data, in which data can be grouped and numeric fields totalled or otherwise summarized.

For example, in the example database for this article, there is a table of employee salary information. The table stores current and past salaries for each employee along with the date that the person’s salary became effective. A query can be used to produce a list showing each person’s current salary along with the date he or she started receiving payment at that rate.

image

The graphic shows a query open in design view. This is the Access 2010 designer. In Access 2003 and earlier look on the query design toolbar for the totals (S) button. When you click it, the Total row will open in the query grid. When you click a cell in the Total row, a dropdown list opens with the available choices.

image

Each time a person’s salary changes, a new record is created in the salaries table. As you can see in the field list, the fields in this table are EmployeeID, to identify the related record in the employees table, Salary, the new salary, and SalaryEffectiveDate, the date on which the salary becomes effective.

As time goes by each employee may have several records in this table, one record for each change in pay. The grouping query allows us to group all salary records for each employee together in the datasheet. If data within the group is sorted into descending order by effective date then the first record in each employee group will be latest effective date and the corresponding salary will be the employees current salary. Keep in mind that if future salary changes have been recorded, you will need to use date criteria to ensure that those future salaries are excluded.

 

Here is the result of the query in datasheet view.:

 

image

This query can now be used as the data source for a report or form to inform management of each person’s current salary level and for how long they have been receiving that salary.

Now, here is a little more involved example. It has been decided to increase every employee’s salary in 2011 by 3% effective the first of the month in which their employment anniversary falls.

Here is the query design to do the calculations.

image

And the resulting datasheet:

image

Notice that we had to include the employees table because the query needs to know the employee’s data of hire to determine the anniversary month.

This query is not quite in its final form. We have included the literal value for the year, 2011. That would mean the query could only be used for 2011 salary calculation.

In the next article we will make the query more flexible by making it a parameter query.

Query Calculations

The demonstration database has been updated to include additional fictitious data so that the examples and demonstrations are more ‘realistic.’ You can download the new file from:

 

LastDollarQuery calculations provide a powerful means to turn raw data into valuable information. Depending on what you are after, there are different ways of performing the calculations:

  • calculated fields
  • grouping and totals

Once you have designed a query to provide the information want, you can use the query as the record source for both forms and report.

Calculated Fields

Standard relational database design practice requires that tables do not include calculated fields (although Access 2010 does include a calculated field type.) Typically a table will include the factors (such as units sold and cost per unit) needed to produce a result (sale amount) the result will be calculated whenever it is needed but it won’t be stored in a table.

The general pattern for a calculated query field is to start with entering a name for the field in the field row of a blank column in the Field row of the grid. Type a colon after the name and then type the formula after the colon. It is standard practice to enclose in square brackets any field references in the formula.

Calculations using numbers are obvious examples but it is also possible to perform calculations on text. For example, people’s names are usually stored in several fields – first name, middle name or initial, and surname. That simplifies management of the names but the result is not particularly nice to look at.

Here’s a simple example. The query, qselEmployees produces a simple list of employees with their date of hire and job title. Here is the basic design view:

image

In datasheet view each field is in a separate column:

image

Incidentally, all data in these and other examples in this series is completely fictitious. These aren’t the names of real people.

With a simple calculation, it is possible to display the names in a last name, first name format (“Jones, John”, for example.)

Here is the design view

image

Notice that the first field of the query is now a calculated field defined like this:

Fullname: [Lname] & ", " & [FName]

The definition starts with Fullname followed by a colon. ‘Fullname’ is the name of the calculated field. A form or report using this query as its record source can refer to this name as the control source for one of its controls.

The ampersand (&) is known as the concatenation operator. This calculation is concatenating (or joining) text. Field names used in the calculation are enclosed in square brackets ([ ]). And because we want to separate the first and last name with a comma followed by a space, we enclose those characters between two double quotes.

Here’s the result (qselEmployeesExtendedFullName):

image

A slightly different calculation lets us display the names in a first name last name format (qselEmployeesExtendedFirstLast):

image

A simple variation in the formula was all that was needed to make the change :

Fullname: [FName] & " " & [LName]

FName and LName have switched places and only a single space is concatenated between them. It is still possible to have the results sorted by the person’s last name, for example, simply by adding that field to the grid and unchecking the show box for that column.

Working with Joins

It is important that tables and queries that are the data source of a query be correctly joined in the query design. In some ways a query join is similar to a relationship. The most important distinction is that a query join is temporary. In fact, a query join can temporarily over-ride a permanent relationship.

In Access, there are three join types, affectionately known as Type 1, Type 2, and Type 3. In SQL these joins are more commonly referred to as Equi-or Inner Join , Left Outer Join, and Right Outer Join, respectively. Equi-joins are the default in the Access query designer; if you want a left, or right, outer join you have to modify the join type. In addition to these three join types, there is a fourth, the Cartesian join, which usually happens by mistake and yields results that, most often are far different from what you may be expecting.

Let’s put the theory in more understandable terms with some examples. In the sample database (download from here if you haven’t already done so), are two tables, tblRegions, and tjxLocations. In the database design there is already a relationship between these tables but we are going to override the relationship and set up temporary joins for this demonstration. In this particular example, we know the city and country of every location but we do not always know a city’s region.

When you first create a query using these two tables, the query diagram will include a join based on their relationship. To experiment with join types then, the first step is to right-click on the join line and edit the Join Properties.

Edit Join

 

DialogueJoinProperties

Inner Joins

An Inner Join (Type 1) returns records only when there are matching records in both tables. The diagram looks like this:

EquiJoin

The query returns four records.

EquiJoinData

Right Joins

A right join will return all records from the primary table, whether or not there is a matching record in the related (or foreign) table. So, in this example, the query returns one more record than the inner join did. The region for City 2 is not in the table.

RightOuterJoin

RightOuterData

Left Joins

A left join returns all records from the foreign table and only matching records from the primary table. In this case, the query result has the same number of records a the inner join example.

LeftOuterJoin

LeftOuterData

But what about the cities and countries, you might ask? We simply have to include two additional tables in the query source, tblCities and tblCountries. For both of these tables an inner join will be adequate because our data includes city and country data for every employee.

Including More Data in the Query

With a query open in design view additional tables and queries can be added to it by right-clicking the the field list area and clicking on Show Table. The required tables and queries can also be simply dragged for the database window or navigation pane depending on your Access version.

Here is an example that will return the city, region and country name for each employee:

qselEmployeeLocations

 

Another approach is to create an intermediate query that gathers the city, country, and region names using a query like this (qselLocations_Extended)

qselLocations_Extended

qselLocations_ExtendedData

Then this query is included in the employee locations query:

qselEmployeeLocationsExtended

This is the result (which is exactly the same result you get when you include tjxLocations and the city, region, and country tables):

qselEELocationsExtendedData

Cartesian Joins

A cartesian join is a special case where no explicit join is defined between a pair of tables. There are some special cases where a cartesian join is called for. However, most often the presence of a cartesian join in a query is simply an error. Depending on how many records are in the source tables, a cartesian join can return an extremely large number of records. Multiply the number of records in the first table by the number of records in the second table. The result will by the record count of the query output.

For example, there are two records in the Regions table and five records in the Locations table.

CartesianJoin

CartesianJoinData