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

Posts Tagged ‘Data Management’

dBase Support Returns to Access

Long before Access came on the personal computer scene, there was dBase. Ultimately, several other data management systems used the same .dbf file format, including FoxPro and Paradox.

From time to time, Access developers may be working with clients who want to incorporate legacy date currently stored in dBase (.dbf) format into an Access project. Some time ago, Access dropped .dbf support. Now we have good news, .dbf support has returned to the Access platform.

Build a Data List in Excel

imageI am among the first to point out that Excel is not a data manager. Yet, at the same time, Excel does have some useful features for working with data, especially small data collections. Such lists can be used directly for data analysis, for example, or indirectly as the data source for mail merges in Word.

Regardless of how the list will be used there are four fundamental rules for setting up an Excel data list:

  • The list should have a header row of unique names for each column in the list
  • There should be no blank columns in the list
  • There should be no blank rows in the list
  • Similar data should appear in one column

The last rule may be misunderstood because there is one other guideline for setting up any list. Data in any column should not be grouped. For example, in a name and address list, newcomers are tempted to list a person’s full name in a single cell. This can become problematic very soon.

For example, if you use a single cell for people’s full names, you can then sort the list only into ascending or descending order by the full name. Depending on how you actually enter the name information, a meaningful sort may prove to be impossible.

 

So, while first name, middle name and last name are similar types of information –they are all names, after all – they are really different and deserve their own cells and therefor their own columns. Separating names into Full Name, Middle Name and Last Name, allows you to then sort the list by Last Name; where several people have the same last name, those groups can be further sorted by First and Middle Names.

Separate columns for First, Last, and Middle names, makes your data far more flexible to work with in other ways. For example, having the first name separate from the last name makes it possible to address a letter to John Smith, but have a salutation in the letter like, “Dear John.”

The video illustrates just how easy it is to create a simple data list. In coming articles, I will discuss how to enhance the list to provide additional columns (fields) for mail merging and how to set up the list as mail merge data source.

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–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.