Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

13.4 Maintaining Records in a Database

Workplace Software and Skills13.4 Maintaining Records in a Database

Learning Objectives

By the end of this section, you will be able to:

  • Examine existing records in a database
  • Construct new records to add to a database
  • Update existing records in a database
  • Demonstrate the process to delete records from a database

Most of the time, when employees maintain and work with a database, they access data that has been provided to them from another data storage area, such as the company’s data warehouse. The tasks in the analysis are to import the data, review the data to ensure that it is brought into the database correctly, and then use the data. Sometimes, however, users need to complete other tasks to ensure that the database processes the data correctly.

Reviewing Records in a Database

It is possible to view records in database tables but be sure to not change the records.

In a table, records are shown on a datasheet, with each column equaling a field name (a value that all records have) and each row representing an individual record. On the datasheet, there are commands that let you move between records. You can also use the scroll bars to look at different parts of the table. Although using the datasheet to view records can be useful in limited situations, databases typically hold a great deal of data, so in general, querying the table will be a much more efficient way of finding the records you need (Figure 13.30).

Table displays columns for ProductID (number/letter combinations), ProductCategoryID (N05B listed in the entire column), Status (with active, on hold, and retired listed as options), and Click to Add (all blank cells).
Figure 13.30 Reviewing records in a datasheet provides a broad overview of the data, while querying allows you to filter and retrieve specific information that answers particular questions or meets specific criteria. Queries are used to extract precise and targeted data from a database, whereas a datasheet presents a more comprehensive view of the data. (Used with permission from Microsoft)

Real-World Application

Data Flow Processes

Date Flow chart shows Data collected by production systems; Production databases: OLTP; Production systems copy data to data warehouse; Data storage: OLAP; Data copied/sent to work groups/in Microsoft Access/Excel; Microsoft Access.
Figure 13.31 Data flows through an organization’s information systems from collection to analysis.

Data moves across the data landscape from transactional production databases, including sales, customer service, human resources, and other systems, to storage databases such as the data warehouse, which can hold historical information (online transaction processing (OLTP)). Once housed in a data store, datasets can be made available to databases like Access and spreadsheets like Excel for local use and analysis (online analytical processing (OLAP)). Why would it be important to keep in mind how and when data was collected from a production system when performing analysis on that data in Access?

If you need to look at records in Datasheet View, there are commands at the bottom of the screen that will help you move between them. When a datasheet view is open, these commands appear automatically in Access, on the bottom left side of the screen.

The record navigation buttons open any time that records are shown. As you work in the database, you will see these buttons in Datasheet View and in certain form views. The command buttons include jumping to the first or last record in the series, navigating one record ahead or back, and adding a new record, as you can see in Figure 13.32. You can also add a filter or use the search feature to find a specific field value.

Lines with arrows pointing to buttons left/ right labeled Move to first/last record. Arrows pointing left/right labeled Move one record forward/back. Arrow pointing right with sun icon labeled Add a new record.
Figure 13.32 Using the record navigation buttons to progress through a datasheet can save you a lot of time. (Used with permission from Microsoft)

Constructing New Records

Databases are historical in that they record past details about an organization, but they also need to be updated as time goes on. There are a variety of ways to update a database, including both manual and automated updates. Most of the time, when you use Access, you will import a dataset or file from another system, such as the organization’s data warehouse.

Adding a Record Manually

If you need to add a record into a database, you can either add the record to the table manually or import it from another database. To add a record manually:

  1. Open the table in Datasheet View.
  2. On the Home tab, click the command New in the Records group.
  3. Fill in the information, using Tab or the left and right arrow keys to move through the record.
  4. Once you are done, use Tab or the up and down arrows to move to the next record, or press Shift+Enter (or any version of Save that saves a record).

Importing a Record

Creating files that can be imported to other systems is a hallmark of a digital organization. When working with Access, we are likely to import files from other, larger systems. Data is collected in business units across an organization, and using that data is key to an effective business. Business units routinely transfer files between systems, which in turn lets units analyze and create actionable information.

Most systems can create comma-delimited (CSV) files, which use commas as delimiters or breakpoints to tell Access (or any other program) where data should be separated. CSV files can be read into most Microsoft products and other popular software packages. There are also other formats designed for importing and exporting files, which use another type of formatting to delimit, or separate, data.

Access can import a variety of file types, including Excel, CSV, and XML, as well as files specific to databases and other systems or software products. Even so, CSV is one of the most common formats for data importing (Figure 13.33).

Before importing a file, be familiar with the file and its contents. To make the import process easier, you may want to rename fields so that they match in the file and the database.

Once your file is ready, you will likely want to import it to a new table so you can catch and correct any errors without corrupting the data in your other data tables. Depending on the specific type of work you are doing, you can either set up a table ahead of time or import the data into a new table.

Notepad displays tabs: File, Edit, Format, View, Help. First line reads: ProductID, WidgetColorSales, UnitsSold, OrderDate, ShipDate, Phone. Lines of information follow for the rest of the page (example of one line: 1,Red,10,04/04/2022,07/04/2022).
Figure 13.33 A CSV (comma-separated values) file is a plaintext file format commonly used for storing tabular data, such as spreadsheets or databases. (Used with permission from Microsoft)

Note: Access has the ability to import data directly into existing tables manually through the Import command and by connecting automatically with a database or system.

Follow these steps in importing a file:

  1. With your database open, select the External Data tab in Access.
  2. Choose the New Data Source button.
  3. Choose the file type you want to work with—for example, text. A dialog box will open.
  4. Select the file you want to import.
  5. Choose the import type that fits best with your project. If you are concerned about an import, try importing the data into a new table to be sure you have all of the fields and parameters set correctly.
  6. Click on OK; the import screen will open (see Figure 13.34).
    Import Text Wizard pane displays Delimited – Characters such as comma or tab separate each field selected. Sample data from file: displays in pane (first row: datum,M01AB,M01AE,N02BA,N02BE,N05B,N05C,R03,R06; other rows include: 1/5/2014,14,11.67,21.3,185.95,41,0,32,7).
    Figure 13.34 Importing a file using the Import Text Wizard allows the user to select the correct format for their file. (Used with permission from Microsoft)
  7. The Import Wizard, which is a part of the import process, will open and ask you to choose how your text file is structured. (Hint: Most text files are delimited, and CSV files use commas as the delimiting or separating character.) Once you choose whether the file is delimited or fixed width (broken up based on a certain number of characters), click Next to continue.
  8. On the next screen, set your delimiter (comma for a CSV file) and click the first row that contains field names (assuming your first row is titles or labels). Then preview the data and, when you are sure it is correct, click Next to move to the next screen. See Figure 13.35.
Import Text Wizard pane displays Comma as delimiter that separates fields. First Row Contains Field Names is selected. Columns include datum, M01AB, M01AE, N02BA, N02BE, N05B, N05C, R03, R06. Other information follows.
Figure 13.35 For files that use a delimiter, the Import Text Wizard requires that the user define which delimiting character is used. (Used with permission from Microsoft)

In the screen that appears, you can change field names and data types, if necessary (Figure 13.36). Once you are ready, click Next to continue.

Import Text Wizard displays Field Options: Field Name (datum), Data Type (Date With Time), Indexed (No). Do not import field (Skip) (not selected). Columns listed in pane (datum, M01AB, M01AE, etc.).
Figure 13.36 The Import Text Wizard allows you to specify various field properties, such as name and data type, before importing the file to Access. (Used with permission from Microsoft)

The Import Wizard will ask if you would like to set a primary key or have Access set it for you. Make your choice, as seen in Figure 13.37, and click Next to continue.

Import Text Wizard selects: Let Access add primary key. ID column visible before other columns: datum, M01AB, M01AE, N02BA, N02BE, N05B, N05C, R03, and R06.
Figure 13.37 You should select a primary key for any imported tables. This primary key can serve as a unique identifier for each record in your database. (Used with permission from Microsoft)

The final Import Wizard screen will allow you to name your new table. Be sure to use a descriptive name that distinguishes it from the tables you already have in the database.

When you are done, click Finish to view your new table. If your table has errors (e.g., if data cannot be imported because of mismatches in the field type), a new table containing the errors will appear in your list, as Figure 13.38 shows. If you find any errors, you may want to delete your imported tables and walk through these steps again.

All Access sidebar visible with Staff_2014_ImportErrors selected in Tables. File open at right lists columns of information detailing errors.
Figure 13.38 A table with errors will still be imported, but the errors will be identified in a new table with the phrase “ImportErrors” in the title. (Used with permission from Microsoft)

Editing or Deleting an Existing Record

Once you locate the record you want to update, you can edit it. To edit a record:

  1. Open the table in Datasheet View (or Form View).
  2. Click on the field you would like to edit.
  3. Place your cursor where you want the information to appear.
  4. Enter your text.
  5. Use Tab to move across your record.

As you work, Access will save your changes whenever you move your cursor from one field to another. If you want to save the record manually to be sure your data is saved, you can either click the Home tab then click Save in the Records group, or press Shift+Enter on the keyboard.

When you are updating a record, you may see symbols appear that share the status of that record, as Figure 13.39 shows. As you manually edit records, pay attention to these data entry symbols.

Gold square: Record being edited/saved. Pencil: Record being edited/not saved. Asterisk: New record ready for information. Key/triangle: Primary key field. Crossed out circle: Locked record/cannot be edited.
Figure 13.39 These are some of the common icons you will use in data entry. (Used with permission from Microsoft)

Deleting a Record

If you need to delete a record or set of records for any reason, you can do so through either the datasheet or the form.

  1. Open the table in Datasheet View or Form View.
  2. Select the record by clicking on the record selector. To select multiple records, either click and drag across record selectors or use Ctrl+Click.
  3. Press Delete or select Home>Records>Delete.

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.