Tables are probably the most important part of a database. Tables are made up of sets of fields (columns) that are defined as specific data types. Tables store data in these fields in the form of individual records (rows) in the table. Access makes it very easy to create new tables and edit existing ones, and a number of methods exist for doing so in Access 2010, such as Datasheet or Design View modes. This lesson discusses the basics of building tables for a database application using Datasheet View mode in Access 2010.
In this lesson, you learn how to create a table using the Datasheet View designer in Access 2010. Datasheet View allows you to quickly add fields, set the data type of the field, and enter data into the table, all in a tabular layout that is similar to an Excel worksheet.
The Create Ribbon offers three basic methods for creating tables. The Table button creates and opens a new table in Datasheet View mode. The Table Design button opens the table in Design View mode and is the traditional method of creating tables. Design View also provides easy access to all the properties of the fields in the table. This allows you greater control when designing the table. The SharePoint Lists button allows the user to create linked tables to a new SharePoint list. This lesson discusses creating Access table objects in an ACCDB database file using the Table Design button to create the table in Datasheet ...
In Datasheet view you see all the data that can be displayed in the form in a datasheet which allows you to see many records at once in a tabular format. If you are creating a form from a query or a filtered table, you may find this view useful. It allows you to see that the correct records are included. To see a form in datasheet view, display the form, click the down arrow next to the View button and choose Datasheet View.
The Datasheet View shows the records in an Access table. In addition, each row is one record and the columns are the fields from the table's definition. This table contains the characteristics of a spreadsheet, but it does not function like an Excel spreadsheet.
Now that you know how to work with List Details views in your web browser, let us explore how to use Datasheet views within your web browser. Datasheet views displayed in your web browser function very much like table and query preview datasheets displayed in Access.
If you have cleared the Read-Only view property for a Datasheet view (cleared by default), you can create new records, make changes to the data, and delete records within the browser window. To create a new record in a Datasheet view, you can click the Add Action Bar button or scroll to the bottom of the Datasheet view window until you see the new record line, and then enter your data on the new datasheet record.
To delete a record, you can click the Delete Action Bar button or right-click the row selector on the left side of the Datasheet view, and then click Delete on the shortcut menu to remove the record. You can also use Ctrl+Delete
to delete the current or selected record in a datasheet. If you attempt to delete a record in a Datasheet view, Access Services first prompts you with a message to confirm the deletion.
Learning Outcomes
- Define datasheets
- Discuss datasheet windows within Microsoft Access
A datasheet is a useful way of viewing data in Access. Most importantly, datasheet view allows a user to view many table records at the same time. In datasheet view, information is displayed in rows and columns—similar to a spreadsheet. Records are displayed as rows, so each row is an individual record. Within each row, columns represent fields within the record.
Datasheet Windows
At the top of the datasheet window are the title bar, the Quick Access toolbar, and the Ribbon. The bottom of the window has a status bar and on the right side of the window a scroll bar as you would expect.
Many of the features that are common to spreadsheets are present in Access datasheet windows. A user can modify row height, column width, font size to change the amount of data that fits without scrolling. A user can also change the order of the rows or columns displayed in the window.
Datasheet views are a common display tool for tables, queries, and forms in Access. There are some irregularities in the functionality of the datasheet windows for these depending on the underlying data sources.
Contribute!
Did you have an idea for improving this content? We’d love your input.
Improve this pageLearn More
When you open a table or view the results of a query, Access displays the table or query result in Datasheet view. Table data or query results that are shown in Datasheet view are commonly referred to as a datasheet. You can customize the appearance of a datasheet to display specific data for use as a simple report.
A datasheet is the visual representation of the data contained in a table, or of the results returned by a query. It displays the fields for each record from a table, form, or query result in a tabular (row and column) format, as shown here.
By default, tables and queries open in Datasheet view. In the Navigation Pane, right-click a table or query, and then click Open on the shortcut menu to open the table or query as a datasheet.
When you apply specific formats to rows and columns or add a Total row, a datasheet can also serve as a simple report.
You can organize data to make the datasheet easier to view or to display only the required data at a particular time. This section shows you several ways in which you can accomplish this.
Sometimes, not all of the columns in a datasheet will fit on your screen or printout, or individual columns may occupy more space than they need for their contents. In addition, you may want to change the height of the rows so that text that does not fit into a column can continue on a new line.
After you open a table, query, or form in Datasheet view, you can either manually resize the individual columns or automatically resize a column to fit its contents.
-
Position the pointer on the edge of the column that you want to resize.
-
When the pointer becomes a double-headed arrow, drag the edge of the column until it is the size that you want.
-
To resize a column to best fit its contents, position the pointer on the edge of the column that you want to resize.
-
When the pointer becomes a double-headed arrow, double-click the edge of the column.
-
To resize multiple columns at the same time, hold down the SHIFT key, select multiple adjacent columns, and then resize the selected columns. You can also select the entire datasheet and resize all the columns.
-
To resize rows, position the pointer between any two record selectors in the datasheet, and drag until the rows are the size that you want.
You cannot resize each row individually — when you resize a row, all of the rows are resized.
-
To resize rows to the default height, right-click a record selector, and then click Row Height on the shortcut menu.
-
In the Row Height dialog box, select the Standard Height check box, and then click OK.
Note: You cannot undo changes to the width of a column or the height of rows by clicking the Undo button on the Quick Access Toolbar. To undo changes, close the datasheet, and then click No when you are prompted to save your changes to the layout of the datasheet. Clicking No will also undo any other layout changes that you made.
You can quickly change the order of the columns in a datasheet by dragging the columns to different locations within the datasheet. For example, you might do this to ensure that a specific column always remains in view.
-
Click the column header to select a column, or hold down the SHIFT key to select multiple adjacent columns, and then drag the column or columns to a new location.
There might be occasions when you want to rename a column so that it better describes the data it contains. For example, if a column containing e-mail addresses is named EMAdd, you may want to make the column heading easier to understand by changing it to E-Mail Address. To do this, right-click the heading for the column, click Rename Column on the shortcut menu, and then type the new name. For example, type E-Mail Address.
If you want to display or print only certain columns for your datasheet, you can hide the columns that you don't want displayed. For example, if you are working with a database of contact information, you may want to see only the full name and e-mail address for each contact. You can create this view by hiding all other columns.
-
Click the heading for the column that you want to hide.
To select adjacent columns, hold down SHIFT and click additional column headers.
Note: You cannot select nonadjacent columns. Select any additional columns and hide those separately.
-
Right-click the column header, and then click Hide Fields on the shortcut menu.
-
Right-click any column header, and then click Unhide Fields on the shortcut menu.
-
In the Unhide Columns dialog box, select the check box next to each column that you want to show, and then click Close.
When using a datasheet, you can enhance the appearance of the data by changing the formats, such as the gridlines style or the background color, or by creating different colors for alternating rows.
-
On the Home tab, in the Text Formatting group, click the arrow next to the Gridlines button.
-
Click the gridlines style that you want.
-
On the Home tab, in the Text Formatting group, click the arrow next to the Background Color button.
-
Click the background color that you want.
You can set the background color of alternating rows in a datasheet independent of the default background color. By setting an alternating background color, you can make it easier to distinguish between adjacent rows.
-
On the Home tab, in the Text Formatting group, click the arrow next to the Alternate Row Color button.
-
Click the background color that you want to apply.
After you open a table, query, or form in Datasheet view, you can change the appearance of the text, including the formatting. Note that your changes apply to the entire datasheet. If you want your changes to appear automatically the next time that you open the datasheet, remember to click Yes when you close the datasheet and are prompted to save the changes to the layout.
You use the additional commands in the Text Formatting group on the Home tab to make changes to font characteristics in the datasheet.
-
Click any cell in the datasheet.
-
On the Home tab, in the Text Formatting group, do one or more of the following:
-
To change the font, type or click a font in the Font box.
-
To change the font size, type or click a font size in the Font Size box.
-
To change the font style, click the Bold, Italic, or Underline button (or any combination of the three).
-
To change the font color, click the arrow next to the Font Color button, and then select a color from the palette.
-
By adding a Total row to a datasheet, you can quickly see the calculated total for a column. In addition to summing a column of data, you can use the Total row to perform other calculations, such as finding averages, counting the number of items in a column, and finding the minimum or maximum value in a column of data.
To display the Total row, do the following:
-
On the Home tab, in the Records group, click Totals.
A new row appears at the bottom of the datasheet, with the word Total in the first column.
-
Click any cell that is on the same row as the cell that contains the word Total.
-
Click the arrow that appears, and then click the type of calculation that you want to display in that cell.
The list of functions or types of calculations that are available depends on the data type of the column. For example, if the column contains currency values, you see a list of functions that apply to currency calculations, such as Minimum and Maximum.
For more information about using the Total row feature, see the article Display column totals in a datasheet using a Totals row.
There are some additional quick changes that can make your datasheet easier to view or print. For example, in the following illustration, the company names are sorted alphabetically.
-
To apply sorting, right-click the column (or click the arrow in the column header), and then click the sorting option you want.
-
To apply a filter, click the arrow in the column header and select the check boxes for the values you want to display. Alternatively, you can click Text Filters or Number Filters to create more detailed filters.
After you change the layout and appearance of a datasheet, you need to save the changes if you want to keep the changes the next time that you open the datasheet. When you close a datasheet after you make changes to its layout, you are prompted to save those changes. If you click Yes, the changes are saved and applied the next time that you open the datasheet. If you click No, the changes are discarded, and the datasheet opens with the default layout settings (or the last saved settings) the next time that you open it.
Top of Page