Create a new field value from the data you already store in an Access table—without re-keying!

 

by Kara Hiltz

Application: Microsoft Access 2000/2002/2003/2016

 

Operating System: Microsoft Windows

 

Download: http://download.elijournals.com/excel/201808/concatenate.zip

The data you need is usually stored somewhere in your Access database, but the hard part is extracting only what you need and creating a new field from those pieces. Through a query, you can programmatically take pieces of two or more existing table fields and throw them together to create a unique value.

To automatically produce a completely new field value from portions of existing text strings, we’ll:

  • Identify the table fields that already store the values we need to create a new text field.
  • Build a query that combines unique pieces of each field in a single expression field.

Ideally, you should only type values in your database once. Re-keying data that’s already in a table wastes valuable time. It’s more efficient to automatically extract and combine portions of several fields into a new one. For instance, if you want to use characters from a contractor’s company name and individual social security number to create a unique username, you don’t need to type the new field. The data you need is already in the database. And you can create a query expression to do the work for you.

 

Pinpoint what you need from the field

Our sample database contains information about contractors hired for a company. The company IT department needs unique usernames for each contractor to give him access to company documents and networks. We’ll extract the first four letters of their company’s name and the last four numbers of their social security number from existing table fields to create a third field with unique usernames, as shown in Figure A. But, don’t get caught typing this new field by hand—especially when the pieces you need are already right under your nose. Use a query to do the work.

 

Download: You can follow along with our example by downloading and extracting the database contractors.mdb from the URL given at the beginning of this article.

 

IME18803A_annotated

A:

The ContractorUsername field combines portions of text from the CompanyName and ContractorSSN fields for a unique username.

 

To find the existing data you need:

  1.  Launch Access and open the sample database, or follow along with one of your own databases.
  2.  Click Tables in the Database window’s Objects pane to display the table objects.
  3.  Double-click on tblContractors to display the table in Datasheet view.

The information we need to create, contractor usernames, is already part of two fields in this table: the CompanyName field and the ContractorSSN field.

The CompanyName field is actually stored in the tblCompanies table, but related to the tblContractors table through the CompanyID field, as shown in Figure B. We’ll need to include the two lookup fields (CompanyID and CompanyName) in our query.

 

IME18803B_annotated

B:

You need to include any tables with the necessary information in your query.

 

Design a query to create a new field from old ones

Whenever you want to accompli[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage