TechiWarehouse.Com


Top 3 Products & Services

1.
2.
3.


Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /home/santanaservices/public_html/techiwarehouse.com/cms/engine.php on line 37

Dated: Aug. 12, 2004

Related Categories

Microsoft Access

Introduction

Access is a piece of software known as a database management system. At its most basic level, it can be used to record and retrieve data, present information and automate repetitive tasks. On a more advanced level, you may want to develop easy to use input forms, create macros, produce powerful reports or carry out data processing.

Microsoft Access is an entry-level database that offers a flexible environment for database developers and users. It makes use of the familiar Microsoft Office interface and allows for integration with larger-scale enterprise databases such as Microsoft's SQL Server and Oracle.

Access is a relational database management system (RDBMS). It can be used as a flat-file database (using a single table) or a relational database (using more than one table linked together). Access database applications can be created for a large number of database situations including financial and scientific data - with excellent results. The disadvantage to using Access is that it is a difficult package to learn. It is a "big" package with many capabilities, some of which require quite a high level of technical expertise and occasionally programming knowledge. Having said that, Access comes equipped with Wizards which help the novice to create tables, forms, queries and reports.Microsoft Access is a Windows Relational Database Management System (RDBMS).

An Access database is made up of a collection of tables, queries, forms, reports, macros and modules (programming code). All of the information connected with a database is stored in a single file. The file will have a name of your choice and extension MDB.

Microsoft access
Access enables its users to:
Create a database
Create tables within a database
Insert, delete and modify data
Validate data
Display wanted data (run queries)
Import data from other database systems and from ordinary files
Export data
Sort data
Produce simple graphics
Create forms
Create reports (and mailing labels)
Create macros
Create programs (to operate on the data stored)
Manage concurrent access to data
Obtain on-line help



Lets the Game Begin

Forms

Data entry using a datasheet is fairly straightforward. There are however several disadvantages in using datasheet view to enter records:

It is often difficult to navigate across a record in datasheet view - especially if the set of fields is wider than the screen width. Although you can see information from several different records at once, it is often impossible to see all of the information from a single record.

The display of information in datasheet view can become wearing on your eyes if you work on it for too long - it is difficult to keep your place on-screen. Datasheets do not present an ordered, professional view of your data. It is more difficult to use controls (such as check boxes and drop-down lists) on a datasheet to make data entry easier and more reliable. Large columns, such as those associated with memo and large text fields, are difficult to display and edit. Many of these disadvantages are overcome by using a form for data entry and display.

A form usually displays database records one at a time. Most fields are displayed as a text box with a label next to it. Forms can be created with different layouts and can use graphics and effects as enhancements.

Use AutoForm to Generate a Form

The simplest method of creating a form is to use AutoForm. To create an AutoForm, you simply select a data source, such as a table or query, then select AutoForm. Access creates a simple form using all the fields in the data source laid out in a single column.

To create a form using AutoForm

Select the table for which you want to create the form
On the Database toolbar, click AutoForm (if the AutoForm button is not displayed, click the arrow and select it from the drop-down menu

Sub Forms

A subform is a form that is placed in a parent form, called the main form. Subforms are particularly useful to display data from tables and queries that have one-to-many relationships. For example, in the sample below, data on the main form is drawn from an item information table while the subform contains all of the orders for that item. The item record is the "one" part of this one-to-many relationship while the orders are the "many" side of the relationship since many orders can be placed for the one item.

The remainder of this page explains three methods for creating subforms and they assume that the data tables and/or queries have already been created.

Create a Form and Subform at Once

Use this method if neither form has already been created. A main form and subform can be created automatically using the form wizard if table relationships are set properly or if a query involving multiple tables is selected. For example, a relationship can be set between a table containing customer information and one listing customer orders so the orders for each customer are displayed together using a main form and subform. Follow these steps to create a subform within a form:

Double-click Create form by using wizard on the database window.

From the Tables/Queries drop-down menu, select the first table or query from which the main form will display its data. Select the fields that should appear on the form by highlighting the field names in the Available Fields list on the left and clicking the single arrow > button or click the double arrows >> to choose all of the fields.

From the same window, select another table or query from the Tables/Queries drop-down menu and choose the fields that should appear on the form. Click Next to continue after all fields have been selected.

Choose an arrangement for the forms by selecting form with subform(s) if the forms should appear on the same page or Linked forms if there are many controls on the main form and a subform will not fit. Click Next to proceed to the next page of options.

Select a tabular or datasheet layout for the form and click Next.

Select a style for the form and click Next.

Enter the names for the main form and subform. Click Finish to create the forms. New records can be added to both tables or queries at once by using the new combination form.

Subform Wizard

If the main form or both forms already exist, the Subform Wizard can be used to combine the forms. Follow these steps to use the Subform Wizard:

Open the main form in Design View and make sure the Control Wizard button on the toolbox is pressed in.

Click the Subform/Subreport icon on the toolbox and draw the outline of the subform on the main form. The Subform Wizard dialog box will appear when the mouse button is released.
If the subform has not been created yet, select "Use existing Tables and Queries". Otherwise, select the existing form that will become the subform. Click Next to continue.

The next dialog window will display table relationships assumed by Access. Select one of these relationships or define your own and click Next.

On the final dialog box, enter the name of the subform and click Finish.
Drag-and-Drop Method

Use this method to create subforms from two forms that already exist. Make sure that the table relationships have already been set before proceeding with these steps.

Open the main form in Design View and select Window |  Tile Vertically to display both the database window and the form side-by-side.

Drag the form icon beside the name of the subform onto the detail section of the main form design.

Reports in MS Access

Reports will organize and group the information in a table or query and provide a way to print the data in a database.

Using the Wizard

Create a report using Access' wizard by following these steps:

Double-click the "Create report by using wizard" option on the Reports Database Window.

Select the information source for the report by selecting a table or query from the Tables/Queries drop-down menu. Then, select the fields that should be displayed in the report by transferring them from the Available Fields menu to the Selected Fields window using the single right arrow button > to move fields one at a time or the double arrow button >> to move all of the fields at once. Click the Next > button to move to the next screen.

Select fields from the list that the records should be grouped by and click the right arrow button > to add those fields to the diagram. Use the Priority buttons to change the order of the grouped fields if more than one field is selected. Click Next > to continue.

If the records should be sorted, identify a sort order here. Select the first field that records should be sorted by and click the A-Z sort button to choose from ascending or descending order. Click Next > to continue.

Select a layout and page orientation for the report and click Next >.

Select a color and graphics style for the report and click Next >.

On the final screen, name the report and select to open it in either Print Preview or Design View mode. Click the Finish button to create the report.

Create in Design View

To create a report from scratch, select Design View from the Reports Database Window.

Click the New button on the Reports Database Window. Highlight "Design View" and choose the data source of the report from the drop-down menu and click OK.

You will be presented with a blank grid with a Field Box and form element toolbar that looks similar to the Design View for forms. Design the report in much the same way you would create a form. For example, double-click the title bar of the Field Box to add all of the fields to the report at once. Then, use the handles on the elements to resize them, move them to different locations, and modify the look of the report by using options on the formatting toolbar. Click the Print View button at the top, left corner of the screen to preview the report.

Printing Reports

Select File |  Page Setup to modify the page margins, size, orientation, and column setup. After all changes have been made, print the report by selecting File |  Print from the menu bar or click the Print button on the toolbar.

Query in MS Access

You can use simple tools such as Filter and Sort to analyze data stored in tables. However, you cannot re-use the "views" created, which are lost when you apply a different filter or sort. Also, you may not want to view all fields in the table at the same time.

A query is a more powerful and flexible way of selecting, filtering and sorting records. A query is created by specifying fields to display from a data source (a table or another query). Typically a query also specifies what data to look for in one or more fields (criteria). When you run a query, Access creates a record set, essentially a table containing the data matching the criteria you specified. However, unlike a real table, a record set is not saved as part of the database. Access creates a new record set each time the query is run. With a query you can:

Extract records according to criteria you specify. Choose the fields you want to view in the result. Sort the records in a specific order. Calculate fields and summarize data. Create a Query Using the Simple Query Wizard. The Simple Query Wizard helps you to select the fields to include in a query. To create a query using the Query Wizard. From the Database Window, select Create query by using wizard then on the Database Window toolbar, click Open

OR

From the Insert menu, select Query then in the dialogue box select Simple Query Wizard and click OK

Form Controls

List and Combo Boxes

If there are small, finite number of values for a certain field on a form, using combo or list boxes may be a quicker and easier way of entering data. These two control types differ in the number of values they display. List values are all displayed while the combo box values are not displayed until the arrow button is clicked to open it as shown in these examples:

Combo Box List Box
Academic 3Ben Hill Griffin III HallReed HallWhitaker Hall Academic 3Ben Hill Griffin III HallReed HallWhitaker Hall

By using a combo or list box, the name of the academic building does not need to be typed for every record. Instead, it simply needs to be selected from the list. Follow these steps to add a list or combo box to a form:

Open the form in Design View.
Select View| Toolbox to view the toolbox and make sure the "Control Wizards" button is pressed in.
Click the list or combo box tool button and draw the outline on the form. The combo box wizard dialog box will appear.
Select the source type for the list or combo box values and click Next >.

Depending on your choice in the first dialog box, the next options will vary. If you chose to look up values from a table or query, the following box will be displayed. Select the table or query from which the values of the combo box will come from. Click Next > and choose fields from the table or query that was selected. Click Next > to proceed.

On the next dialog box, set the width of the combo box by clicking and dragging the right edge of the column. Click Next >.

The next dialog box allows tells Access what to do with the value that is selected. Choose "Remember the value for later use" to use the value in a macro or procedure (the value is discarded when the form is closed), or select the field that the value should be stored in. Click Next > to proceed to the final screen.

Type the name that will appear on the box's label and click Finish.

Check Boxes and Option Buttons

Use check boxes and option buttons to display yes/no, true/false, or on/off values. Only one value from a group of option buttons can be selected while any or all values from a check box group can be chosen. Typically, these controls should be used when five or less options are available. Combo boxes or lists should be used for long lists of options. To add a checkbox or option group:

Click the Option Group tool on the toolbox and draw the area where the group will be placed on the form with the mouse. The option group wizard dialog box will appear.
On the first window, enter labels for the options and click the tab key to enter additional labels. Click Next > when finished typing labels.

On the next window, select a default value if there is any and click Next >.

Select values for the options and click Next >.

Choose what should be done with the value and click Next >.

Choose the type and style of the option group and click Next >.

Type the caption for the option group and click Finish.

Command Buttons

In this example, a command button beside each record is used to open another form.

Open the form in Design View and ensure that the Control Wizard button on the toolbox is pressed in.
Click the command button icon on the toolbox and draw the button on the form. The Command Button Wizard will then appear.
On the first dialog window, action categories are displayed in the left list while the right list displays the actions in each category. Select an action for the command button and click Next >.

The next few pages of options will vary based on the action you selected. Continue selecting options for the command button.
Choose the appearance of the button by entering caption text or selecting a picture. Check the Show All Pictures box to view the full list of available images. Click Next >.

Enter a name for the command button and click Finish to create the button.

Queries Forms Form Controls

To create a form using the assistance of the wizard, follow these steps:

Click the Create form by using wizard option on the database window.
From the Tables/Queries drop-down menu, select the table or query whose datasheet the form will modify. Then, select the fields that will be included on the form by highlighting each one the Available Fields window and clicking the single right arrow button > to move the field to the Selected Fields window. To move all of the fields to Select Fields, click the double right arrow button >>. If you make a mistake and would like to remove a field or all of the fields from the Selected Fields window, click the left arrow < or left double arrow << buttons. After the proper fields have been selected, click the Next > button to move on to the next screen.

On the second screen, select the layout of the form.

Columnar - A single record is displayed at one time with labels and form fields listed side-by-side in columns

Justified - A single record is displayed with labels and form fields are listed across the screen

Tabular - Multiple records are listed on the page at a time with fields in columns and records in rows

Datasheet - Multiple records are displayed in Datasheet View
Click the Next > button to move on to the next screen.

Select a visual style for the form from the next set of options and click Next >.

On the final screen, name the form in the space provided. Select "Open the form to view or enter information" to open the form in Form View or "Modify the form's design" to open it in Design View. Click Finish to create the form.

Create Form in Design View

To create a form from scratch without the wizard, follow these steps:

Click the New button on the form database window.
Select "Design View" and choose the table or query the form will be associated with the form from the drop-down menu.
Select View| Toolbox from the menu bar to view the floating toolbar with additional options.


Add controls to the form by clicking and dragging the field names from the Field List floating window. Access creates a text box for the value and label for the field name when this action is accomplished. To add controls for all of the fields in the Field List, double-click the Field List window's title bar and drag all of the highlighted fields to the form.

Adding Records Using A Form

Input data into the table by filling out the fields of the form. Press the Tab key to move from field to field and create a new record by clicking Tab after the last field of the last record. A new record can also be created at any time by clicking the New Record button at the bottom of the form window. Records are automatically saved as they are entered so no additional manual saving needs to be executed.

 

Editing Forms

The follow points may be helpful when modifying forms in Design View.

Grid lines - By default, a series of lines and dots underlay the form in Design View so form elements can be easily aligned. To toggle this feature on and off select View| Grid from the menu bar.

Snap to Grid - Select Format| Snap to Grid to align form objects with the grid to allow easy alignment of form objects or uncheck this feature to allow objects to float freely between the grid lines and dots.

Resizing Objects - Form objects can be resized by clicking and dragging the handles on the edges and corners of the element with the mouse.

Change form object type - To easily change the type of form object without having to create a new one, right click on the object with the mouse and select Change To and select an available object type from the list.

Label/object alignment - Each form object and its corresponding label are bounded and will move together when either one is moved with the mouse. However, to change the position of the object and label in relation to each other (to move the label closer to a text box, for example), click and drag the large handle at the top, left corner of the object or label.
Tab order - Alter the tab order of the objects on the form by selecting View| Tab Order... from the menu bar. Click the gray box before the row you would like to change in the tab order, drag it to a new location, and release the mouse button.

Form Appearance - Change the background color of the form by clicking the Fill/Back Color button on the formatting toolbar and click one of the color swatches on the palette. Change the color of individual form objects by highlighting one and selecting a color from the Font/Fore Color palette on the formatting toolbar. The font and size, font effect, font alignment, border around each object, the border width, and a special effect can also be modified using the formatting toolbar:

Page Header and Footer - Headers and footers added to a form will only appear when it is printed. Access these sections by selecting View| Page Header/Footer on the menu bar. Page numbers can also be added to these sections by selecting Insert| Page Numbers. A date and time can be added from Insert| Date and Time.... Select View| Page Header/Footer again to hide these sections from view in Design View.

Multiple-Page Forms Using Tabs

Tab controls allow you to easily create multi-page forms. Create a tab control by following these steps:

Click the Tab Control icon on the toolbox and draw the control on the form.
Add new controls to each tab page the same way that controls are added to regular form pages and click the tabs to change pages. Existing form controls cannot be added to the tab page by dragging and dropping. Instead, right-click on the control and select Cut from the shortcut menu. Then right-click on the tab control and select Paste. The controls can then be repositioned on the tab control.

Add new tabs or delete tabs by right-clicking in the tab area and choosing Insert Page or Delete Page from the shortcut menu.
Reorder the tabs by right-clicking on the tab control and selecting Page Order.
Rename tabs by double-clicking on a tab and changing the Name property under the Other tab.

Conditional Formatting

Special formatting that depends on the control's value can be added to text boxes, lists, and combo boxes. A default value can set along with up to three conditional formats. To add conditional formatting to a control element, follow these steps:

Select the control that the formatting should be applied to and select Format| Conditional Formatting from the menu bar.

Under Condition 1, select one of the following condition types:
Field Value Is applies formatting based upon the value of the control. Select a comparison type from the second drop-down menu and enter a value in the final text box.
Expression Is applies formatting if the expression is true. Enter a value in the text box and the formatting will be added if the value matches the expression.
Field Has Focus will apply the formatting as soon as the field has focus.
Add additional conditions by clicking the Add >> button and delete conditions by clicking Delete... and checking the conditions to erase.

Password Text Fields

To modify a text box so each character appears as an asterisk as the user types in the information, select the text field in Design View and click Properties. Under the Data tab, click in the Input Mask field and then click the button [...] that appears. Choose "Password" from the list of input masks and click Finish. Although the user will only see asterisks for each character that is typed, the actual characters will be saved in the database.

Change Control Type

If you decide the type of a control needs to be changed, this can be done without deleting the existing control and creating a new one although not every control type can be converted and those that can have a limited number of types they can be converted to. To change the control type, select the control on the form in Design View and choose Format| Change To from the menu bar. Select one of the control types that is not grayed out.

Multiple Primary Keys

To select two fields for the composite primary key, move the mouse over the gray column next to the field names and note that it becomes an arrow. Click the mouse, hold it down, and drag it over all fields that should be primary keys and release the button. With the multiple fields highlighted, click the primary key button.

 


.

Modifying a Query in Microsoft Access

Congratulations! Our company's purchasing department was very pleased with the results of the query you designed for them after last week's tutorial. However, the sheer amount of data the query produced overwhelmed their staff and they've come back asking you to provide them with more concise results.

Last week's query displayed the inventory levels for all of the products in our inventory. In order to satisfy our customer, we'd like to add several features to our previous query. First, we'd only like to display those products where our current inventory level is less than ten with no products on order. Second, we're only interested in displaying the product name along with the phone number and contact name of each product's supplier. Finally, we'd like to sort our final results alphabetically by product name.

Before we begin, please open your Northwind sample database and ensure that you completed the steps in the Creating a Simple Query in Microsoft Access tutorial. We're going to modify that query using the Design View to produce our desired results.

I. Open Query in Design View

Last week, we used Microsoft's query wizard to create a simple query. If you recall the creation process, the query wizard did not provide us with any of the advanced options (adding criteria, hiding fields, etc.) that we need to perform this week's query. Therefore, we're going to used the more advanced Design View to make these modifications. Our first step is to invoke the Design View for our query.

1. Select the appropriate query. From the Northwind database menu, single click on the query you wish to modify. In our case, choose the "Product Supplier Listing" query that we designed last week.

2. Click the Design View icon. This icon appears in the upper left portion of the window. Immediately upon clicking this icon, you'll be presented with the Design View.

II. Adding Fields

Adding a field is one of the most common query modifications. This is usually done to either display additional information in the query results or add criteria to the query from information not displayed in the query results. In our example, the purchasing department wanted the contact name of each product's supplier displayed. As this was not one of the fields in the original query, we must add it now.

1. Chose an open table entry. Look for an entry in the field row that does not contain any information. Depending upon the size of your window you may need to use the horizontal scroll bar at the bottom of the table to locate an open entry.

2. Select the desired field. Single click in the field portion of the chosen entry and a small black down arrow will appear. Click this once and you'll be presented with a list of currently available fields. Select the field of interest by single clicking on it. In our example, we want to choose the ContactName field from the Suppliers table (listed as Suppliers.ContactName).


III. Removing Fields

Often, you'll need to remove unnecessary information from a query. If the field in question is not a component of any criteria or sort orders that we wish to maintain, the best option is to simply remove it from the query altogether. This reduces the amount of overhead involved in performing the query and maintains the readability of our query design.

1. Click on the field name. Single click on the name of the field you wish to remove in the query table. In our example, we want to remove the CompanyName field from the Suppliers table.

2. Open the Edit menu and select Delete Columns. Upon completion of this step the CompanyName column will disappear from the query table.

IV. Adding Criteria

We often desire to filter the information produced by a database query based upon the value of one or more database fields. For example, recall that our purchasing department was only interested in those products with a small inventory and no products currently on order. In order to include this filtering information, we can add criteria to our query in the Design View.

1. Select the criteria field of interest. Locate the field that you would like to use as the basis for the filter and single click inside the criteria box for that field. In our example, we'd first like to limit the query based upon the UnitsInStock field of the Products table.

2. Type the selection criteria. For a complete look at the allowable criteria, see these examples of criteria expressions from Microsoft. Continuing with our example, we want to limit our results to those products with less than ten items in inventory. To accomplish this, enter the mathematical expression "< 10" in the criteria field.

3. Repeat steps 1 and 2 for additional criteria. We'd also like to limit our results to those instances where the UnitsOnOrder field is equal to zero. Repeat the steps above to include this filter as well.

V. Hiding Fields

Sometimes we'll create a filter based upon a database field but won't want to show this field as part of the query results. In our example, the purchasing department wanted to filter the query results based upon the inventory levels but did not want these levels to appear. We can't remove the fields from the query because that would also remove the criteria. To accomplish this, we need to hide the field.

1. Uncheck the appropriate Show box. It's that simple! Just locate the field in the query table and uncheck the Show box by single clicking on it. If you later decide to include that field in the results just single click on it again so that the box is checked.

VI. Sorting the Results

The human mind prefers to work with data presented in an organized fashion. For this reason, we often desire to sort the results of our queries based upon one or more of the fields in the query. In our example, we want to sort the results alphabetically based upon the product's name.

1. Click the Sort entry for the appropriate field. Single click in the Sort area of the field entry and a black down arrow will appear. Single click on this arrow and you'll be presented with a list of sort order choices. Do this for the Products.ProductName field in our example.

2. Choose the sort order. For text fields, ascending order will sort alphabetically and descending order will sort by reverse alphabetic order. We want to choose ascending order for this example.

That's it! Close the design view by clicking the "X" icon in the upper right corner. From the database menu, double click on our query name and you'll be presented with the results shown below -- exactly what our purchasing department asked for!

.

Sorting and Filtering

Sorting and filtering allow you to view records in a table in a different way either by reordering all of the records in the table or view only those records in a table that meet certain criteria that you specify.

Sorting

You may want to view the records in a table in a different order than they appear such as sorting by a date or in alphabetical order, for example. Follow these steps to execute a simple sort of records in a table based on the values of one field:

In table view, place the cursor in the column that you want to sort by.
Select Records| Sort| Sort Ascending or Records| Sort| Sort Descending from the menu bar or click the Sort Ascending or Sort Descending buttons on the toolbar.
To sort by more than one column (such as sorting by date and then sorting records with the same date alphabetically), highlight the columns by clicking and dragging the mouse over the field labels and select one of the sort methods stated above.

Filter by Selection

This feature will filter records that contain identical data values in a given field such as filtering out all of the records that have the value "Smith" in a name field. To Filter by Selection, place the cursor in the field that you want to filter the other records by and click the Filter by Selection button on the toolbar or select Records| Filter| Filter By Selection from the menu bar. In the example below, the cursor is placed in the City field of the second record that displays the value "Ft. Myers" so the filtered table will show only the records where the city is Ft. Myers.

 

Filter by Form

If the table is large, it may be difficult to find the record that contains the value you would like to filter by so using Filter by Form may be advantageous instead. This method creates a blank version of the table with drop-down menus for each field that each contain the values found in the records of that field. Under the default Look for tab of the Filter by Form window, click in the field to enter the filter criteria. To specify an alternate criteria if records may contain one of two specified values, click the Or tab at the bottom of the window and select another criteria from the drop-down menu. More Or tabs will appear after one criteria is set to allow you to add more alternate criteria for the filter. After you have selected all of the criteria you want to filter, click the Apply Filter button on the toolbar.

 


The following methods can be used to select records based on the record selected by that do not have exactly the same value. Type these formats into the field where the drop-down menu appears instead of selecting an absolute value.

Filter by Form
Format Explanation
Like "*Street" Selects all records that end with "Street"
<="G" Selects all records that begin with the letters A through G
>1/1/00 Selects all dates since 1/1/00
<> 0 Selects all records not equal to zero

Saving A Filter

The filtered contents of a table can be saved as a query by selecting File| Save As Query from the menu bar. Enter a name for the query and click OK. The query is now saved within the database.

 


 

Creating Tables
Introduction to Tables

Tables are grids that store information in a database similar to the way an Excel worksheet stores information in a workbook. Access provides three ways to create a table for which there are icons in the Database Window. Double-click on the icons to create a table.

 

Create table in Design view will allow you to create the fields of the table. This is the most common way of creating a table and is explained in detail below.

Create table using wizard will step you through the creation of a table. Create table by entering data will give you a blank datasheet with unlabelled columns that looks much like an Excel worksheet. Enter data into the cells and click the Save button. You will be prompted to add a primary key field. After the table is saved, the empty cells of the datasheet are trimmed. The fields are given generic names such as "Field1", "Field2", etc. To rename them with more descriptive titles that reflect the content of the fields, select Format| Rename Column from the menu bar or highlight the column, right-click on it with the mouse, and select Rename Column from the shortcut menu.
Create a Table in Design View

Design View will allow you to define the fields in the table before adding any data to the datasheet. The window is divided into two parts: a top pane for entering the field name, data type, and an option description of the field, and a bottom pane for specifying field properties.


Field Name - This is the name of the field and should represent the contents of the field such as "Name", "Address", "Final Grade", etc. The name can not exceed 64 characters in length and may include spaces.
Data Type is the type of value that will be entered into the fields.

Text - The default type, text type allows any combination of letters and numbers up to a maximum of 255 characters per field record.

Memo - A text type that stores up to 64,000 characters.

Number - Any number can be stored.

Date/Time - A date, time, or combination of both.

Currency - Monetary values that can be set up to automatically include a dollar sign ($) and correct decimal and comma positions.

AutoNumber - When a new record is created, Access will automatically assign a unique integer to the record in this field. From the General options, select Increment if the numbers should be assigned in order or random if any random number should be chosen. Since every record in a datasheet must include at least one field that distinguishes it from all others, this is a useful data type to use if the existing data will not produce such values.

Yes/No - Use this option for True/False, Yes/No, On/Off, or other values that must be only one of two.

OLE Object - An OLE (Object Linking and Embedding) object is a sound, picture, or other object such as a Word document or Excel spreadsheet that is created in another program. Use this data type to embed an OLE object or link to the object in the database.

Hyperlink - A hyperlink will link to an Internet or Intranet site, or another location in the database. The data consists of up to four parts each separated by the pound sign (#): DisplayText#Address#SubAddress#ScreenTip. The Address is the only required part of the string. Examples:
Internet hyperlink example: FGCU Home Page#http://www.fgcu.edu#
Database link example: #c:\My Documents\database.mdb#MyTable

Description (optional) - Enter a brief description of what the contents of the field are.
Field Properties - Select any pertinent properties for the field from the bottom pane.
Field Properties

Properties for each field are set from the bottom pane of the Design View window.

Field Size is used to set the number of characters needed in a text or number field. The default field size for the text type is 50 characters. If the records in the field will only have two or three characters, you can change the size of the field to save disk space or prevent entry errors by limiting the number of characters allowed. Likewise, if the field will require more than 50 characters, enter a number up to 255. The field size is set in exact characters for Text type, but options are give for numbers:

Byte - Positive integers between 1 and 255
Integer - Positive and negative integers between -32,768 and 32,768
Long Integer (default) - Larger positive and negative integers between -2 billion and 2 billion.
Single - Single-precision floating-point number
Double - Double-precision floating-point number
Decimal - Allows for Precision and Scale property control
Format conforms the data in the field to the same format when it is entered into the datasheet. For text and memo fields, this property has two parts that are separated by a semicolon. The first part of the property is used to apply to the field and the second applies to empty fields.

Text and memo format.

Text Format
Format Datasheet Entry Display Explanation
@@@-@@@@ 1234567 123-4567 @ indicates a required
character or space
@@@-@@@& 123456 123-456 & indicates an optional
character or space
< HELLO hello < converts characters to lowercase
> hello HELLO > converts characters to uppercase
@\! Hello Hello! \ adds characters to the end
@;"No Data Entered" Hello Hello
@;"No Data Entered" (blank) No Data Entered

Number format. Select one of the preset options from the drop down menu or construct a custom format using symbols explained below:
Number Format
Format Datasheet Entry Display Explanation
###,##0.00 123456.78 123,456.78 0 is a placeholder that displays a digit or 0 if there is none.
# is a placeholder that displays a digit or nothing if there is none.
$###,##0.00 0 $0.00
###.00% .123 12.3% % multiplies the number by 100 and added a percent sign

Currency format. This formatting consists of four parts separated by semicolons:
format for positive numbers; format for negative numbers; format for zero values; format for Null values.
Currency Format
Format Explanation
$##0.00;($##0.00)[Red];$0.00;"none" Positive values will be normal currency format, negative numbers will be red in parentheses, zero is entered for zero values, and "none" will be written for Null values.

Date format. In the table below, the value "1/1/01" is entered into the datasheet, and the following values are displayed as a result of the different assigned formats.
Date Format
Format Display Explanation
dddd","mmmm d","yyyy Monday, January 1, 2001 dddd, mmmm, and yyyy print the full day name, month name, and year
ddd","mmm ". " d", '"yy Mon, Jan. 1, '01 ddd, mmm, and yy print the first three day letters, first three month letters, and last two year digits
"Today is " dddd Today is Monday
h:n:s: AM/PM 12:00:00 AM "n" is used for minutes to
avoid confusion with months

Yes/No fields are displayed as check boxes by default on the datasheet. To change the formatting of these fields, first click the Lookup tab and change the Display Control to a text box. Go back to the General tab choices to make formatting changes. The formatting is designated in three sections separated by semicolons. The first section does not contain anything but the semicolon must be included. The second section specifies formatting for Yes values and the third for No values.
Yes/No Format
Format Explanation
;"Yes"[green];"No"[red] Prints "Yes" in green or "No" in red


Default Value - There may be cases where the value of a field will usually be the same for all records. In this case, a changeable default value can be set to prevent typing the same thing numerous times. Set the Default Value property.

Primary Key

Every record in a table must have a primary key that differentiates it from every other record in the table. In some cases, it is only necessary to designate an existing field as the primary key if you are certain that every record in the table will have a different value for that particular field. A social security number is an example of a record whose values will only appear once in a database table.

Designate the primary key field by right-clicking on the record and selection Primary Key from the shortcut menu or select Edit| Primary Key from the menu bar. The primary key field will be noted with a key image to the left. To remove a primary key, repeat one of these steps.

If none of the existing fields in the table will produce unique values for every record, a separate field must be added. Access will prompt you to create this type of field at the beginning of the table the first time you save the table and a primary key field has not been assigned. The field is named "ID" and the data type is "autonumber". Since this extra field serves no purpose to you as the user, the autonumber type automatically updates whenever a record is added so there is no extra work on your part. You may also choose to hide this column in the datasheet as explained on a later page in this tutorial.

Indexes

Creating indexes allows Access to query and sort records faster. To set an indexed field, select a field that is commonly searched and change the Indexed property to Yes (Duplicates OK) if multiple entries of the same data value are allowed or Yes (No Duplicates) to prevent duplicates.

Field Validation Rules

Validation Rules specify requirements (change word) for the data entered in the worksheet. A customized message can be displayed to the user when data that violates the rule setting is entered. Click the expression builder ("...") button at the end of the Validation Rule box to write the validation rule. Examples of field validation rules include <> 0 to not allow zero values in the record, and ??? to only all data strings three characters in length.

Input Masks

An input mask controls the value of a record and sets it in a specific format. They are similar to the Format property, but instead display the format on the datasheet before the data is entered. For example, a telephone number field can formatted with an input mask to accept ten digits that are automatically formatted as "(555) 123-4567". The blank field would look like (___) ___-____. An an input mask to a field by following these steps:

In design view, place the cursor in the field that the input mask will be applied to.
Click in the white space following Input Mask under the General tab.
Click the "..." button to use the wizard or enter the mask, (@@@) @@@-@@@@, into the field provided. The following symbols can be used to create an input mask from scratch:
Input Mask Symbols
Symbol Explanation
A Letter or digit
0 A digit 0 through 9 without a + or - sign and with blanks displayed as zeros
9 Same as 0 with blanks displayed as spaces
# Same as 9 with +/- signs
? Letter
L Letter A through Z
C or & Character or space
< Convert letters to lower case
> Convert letters to upper case


 

Data Types Supported by Access

The following data types are supported by Access:

Text - up to 255 alphanumeric characters
Memo - up to 64000 characters
Number
Byte: integers in the range 0-255
Integer: range -32768 - +32767
Long Integer: range -2147483648 - +2147483647
Single precision floating point: range +-3.4 * 1038 (7 sf)
Double precision floating point: range 1.79 * 1038 (15 sf)
Currency
Date/Time
Counter (An automatically incremented long integer)
Yes/No
OLE Object (binary data)
Restrictions imposed by Access

Many of the restrictions imposed are not restrictions in any practical sense: e.g. the maximum database size of 1GB (1000Mb) is hardly likely to be exceeded before disk space is exhausted. The only maxima likely be approached in practice are:

Number of tables in a query: 16
Number of sorted fields in a query: 10
Number of characters in a query cell: 255
Form/Report - Width: 22", Section Height: 22", Overall height: 200"
Number of levels of nested Forms/Reports: 3
Number of group headers/footers in a report: 10


Creating Database Relationships

Now let's dig in and create that relationship!

1. Open the Relationships Window from the Tools menu.

2. Add the appropriate tables. We'll add the Employees and Orders tables -- you can use the Shift-Click combination to select both at the same time. Once you've highlighted the tables click the Add button.

3. Open the Edit Relationships tool. You'll find it on the Relationships pull-down menu

4. Create a new relationship.

5. Fill in the appropriate details. The left table is the primary source of the data and the right table is the desired location of the foreign key. In this case, our original information is stored in the Employees table and the foreign key will be in the Orders table. The field name is EmployeeID in both cases. Once you've selected the appropriate items, click OK to continue.

We've now completed the preliminary steps to set up the relationship. In the next section we'll take a look at some of the more advanced relationship functionality offered by Microsoft Access.

6. Choose whether to enforce Referential Integrity. In most circumstances, you will want to select this option. This is the real power of a relationship -- it ensures that new records in the Orders table only contain the IDs of valid employees from the Employees table.

You'll also notice two other options here. The "Cascade Update Related Fields" option ensures that if an EmployeeID changes in the Employees table that change is propagated to all related records in the Orders table. Similarly, the "Cascade Delete Related Records" option removes all related Orders records when an Employee record is removed. The use of these options will depend upon the particular requirements of your database. In this example, we'll won't utilize either one.

7. Open the Join Type dialog box.

8. Select a join type. The three options are shown in the figure below. If you're familiar with SQL, you might notice that the first option corresponds to an inner join, the second to a left outer join and the final to a right outer join. We'll use an inner join for our example.

9. Create the relationship.

 

 

Naming Schemes and Conventions

When most people start using MS Access, they choose names for their tables and forms that seem obvious to them. A table of student's names might be called "Student Names" and a table full of addresses might be called "Addresses." For very simple databases this will work, but as your database becomes larger and more complex, and as more users start using your database, it becomes very important to use a standard naming convention.

There are several different naming conventions (or schemes) to choose from, but this tutorial will cover the most common one used in many Access Traning books and and online discussion groups. There are only two basic rules for following the convention.

1. Never use spaces in your names.

Even though Access can handle spaces many other databases can not. As your database becomes more complex, you may want Access to link directly with some of these other databases and spaces in the names will cause problems. If you feel like you must have a space in your name, use the underscore character ( "_" ) to signify a space.

2. Always precede every name with the 3 letter description.

It's very common to have a table, form and a report with the same name. While Access can tell the difference between the three, you won't be able to when you're creating scripts or macros. So, in order to distinguish between them you precede each object with a 3 letter description. Here's a list of the most common extensions:

 

Object
Extension

table
tbl

query
qry

form
frm

report
rpt

macro
mcr


Here's an example of some names that follow this naming convention: "tbladdresses" ; "rpt_student_names" ; "frmNewStudentInput". By following these simple rules you will save yourself a lot of time and effort later on. It will be faster and easier for you to update the design of your reports and forms and it will be easier for you to troubleshoot problems. The small effort it takes to use standard names is will worth the effort in the long run.


 

Importing, Exporting, and Linking

Importing

Importing objects from another database will create a complete copy of a table, query, or any other database object that you select. Import a database object by following these steps:

Open the destination database.
Select File| Get External| Import from the menu bar.
Choose the database the object is located in a click the Import button.
From the Import Objects window, click on the object tabs to find the object you want to import into the database. Click the Options >> button to view more options. Under Import Tables, select "Definition and Data" if the entire table should be copied or "Definition Only" if the table structure should be copied but not the data. Under Import Queries, select "As Tables" if the queries should appear as regular tables in the destination database. Highlight the object name, and click OK.

The new object will now appear with the existing objects in the database.

Exporting

The effect of importing can also be achieved using the opposite method of exporting.

Open the database containing an object that will be copied (exported) to another database.
Find the object in the Database Window and highlight it. Then, select File| Export... from the menu bar.
Select the destination database from the window and click Save.
You will be prompted to name the new object and may also be given other options, such as whether to copy the structure or data and structure of a table. Click OK to complete the export procedure.

Linking

Unlike importing, linking objects from another database will create a link to an object in another database while not copying the table to the current database. Create a link by following these steps:

Open the destination database.
Select File| Get External| Link Tables... from the menu bar.
Choose the database that the table is located in and click the Link button.
A window listing the tables in the database will then appear. Highlight the table or tables that should be linked and click OK. A link to the table will appear in the Database Window as a small table icon preceded by a small right arrow.


 

Filtering data

While sorting information in the datasheet view allows you to apply your own ordering to the display of the records in the table, it can also be useful to filter the table. This allows you to limit the number of records that are visible to a manageable sub-set of the records.


To use Filter by Selection

The simplest way of filtering the display of records in a table is use Filter by Selection. This displays only records that have the same value in that field.

Display the table in datasheet view
Select the value in a particular field that you have chosen as the filter value
Tip: If you select part of a field, then the filter will be based on that partial selection. For example, if you pick 'F' in the third position of a field and filter this by example, then the results will be exactly that - fields that have an 'F' in the third position of that field.

For instance, you might want to display items that for which you have no stock level, or examine the customers where your contact is the owner.

On the Standard toolbar, click Filter by Selection

OR

From the Records menu, select Filter, then select Filter by Selection
The datasheet is refreshed to display only records that pass the selected filter criteria- you should always get at least one record - the one that you selected for the filter.

You may further limit the records that are displayed by adding to the Filter by Selection.

If you select another field from this filter-limited list, and select Filter by Selection once more, the records displayed will be those the match both criteria
It follows that you can add successive filters by example, until the requirements match the criteria that you require.

To re-display all records

Once a filter is in place, you may want to display all records from the table. If the filter that you have created is complex, Access allows you to 'toggle' the display of records between all records and the filter that you have created.

Once you have set a filter, the Apply Filter button provides you with the ability to toggle the display between the filter that you have created, and displaying all records.

On the Standard toolbar, click Apply Filter to toggle between filtered records and all records
Tip: This button can also be used to turn off the filter when you have finished using it.

Using Advanced Filters

While Filter by Selection allows you to filter on exact values, it is often necessary to filter the information in your datasheet by a range of values. Access provides an option to Filter by Form. Filter by Form uses a blank record that you can type an example of the data by which you wish to filter into.

You may also use calculated expressions as a basis of the filter that you wish to display. As well as matching the filter value to the field, you can use different operators to create more sophisticated filters. Some of the more common operators are explained in the table below.

 

For more examples of expressions that you can create, consult the Access help screens.

It should be noted that if you fill in more than one column with a filter expression, then the expressions must all be true for the record to be displayed (sometimes known as 'AND-ing').

Access also allows you to 'OR' several fields together, using the OR tab at the bottom of the filter by example window.
When you select the OR tab, another blank record is displayed at the top of the window, and you may fill in additional criteria for your filter.


 

Mail Capabilities In Access

Using File, Send to Send Information

Perhaps the easiest way to send information from the system using MAPI is the Send command found on the default File menu When you use this standard Send method, you'll be sending the output from whatever is currently in focus. If you have a form currently in focus and select Send from the File menu, you'll be sending the output from the currently displayed form. The underlying table is not sent, unless you are reviewing a table in datasheet mode at the time you select Send.

If you are sending a RecordSet object, either by sending the output from a form or by sending the results of a query or table browse, you'll be prompted for an output type. This is the format that the information will be placed in so that it may be read at the recipient's system.

Format Options When Sending Information

Select the format for the export and select OK. If you have selected a record or records in a table view, the Output frame will be enabled, allowing you to send only the select row(s) or the entire table. Once you indicate how to format the information, the standard send note dialog box is shown, allowing you to specify typical options, addressee information, additional attachments, etc. Notice that your information is shown as an object attached to the mail message note.

Send Note with Attachment

The easiest output format for re-use by Access on the receiving end is Excel format, you should consider using this format for your exported files if possible. Once you select the Send button, the mail message will be sent and delivered to the destination you specified.

One thing to remember is that if you are currently on a form and select File, Send, Access is going to send all visible output on the form to the destination you specify. If you have information that does not come form an underlying table, Access will still send the information as you request.

Using SendObject to Send A Mail Message

As you've seen, using MAPI to send messages is quite straight-forward. You'll probably find that you'll come across instances where you require more programmatic control over the information that is sent.

In these cases, you may be able to use the Access Basic SendObject function. With SendObject, you specify all of this information from within Access Basic. If you do leave out a piece of information that is required, MAPI will step in and ask the user to clarify that item.

You may want to add the SendObject capabilities to a menu that is pertinent to your application. You can place a reference from the menu to an access basic module routine that packages up the information and sends it to the defined recipient.

As an example, the Function below, ABSendObject function, send a copy of a table, hard-coded as "Company," to the destination you set up. You'll need to provide a valid destination address in sRecipient. The name should be unique in your mail system to avoid MAPI needing to resolve the name when the message is sent.

If you do not specify a unique name, the Send Note dialog will be shown and you will have to specify the name to send the message to.

Function ABSendObject ()
'send an object, using MAPI, from Access to
'another system - this routine is hardcoded
'to send the company table.

Dim sRecipient As String
sRecipient = "Steve Wynkoop;"

'This example specifies the user to send the object to.
MsgBox "Sending message to: " & sRecipient & "..."
DoCmd SendObject A_TABLE, "Company", A_FORMATXLS, sRecipient, , , "Copy of the company database.", , False

MsgBox "Message sent."
End Function

The table below shows the parameters expected by the SendObject method. As indicated before, if you omit these, MAPI will attempt to resolve the information, either by asking the user to clarify something, or by not sending the message and providing your calling routine with an error message.

ObjectType Use the Access intrinsic constants of A_TABLE,

A_QUERY, A_FORM, A_REPORT or A_MODULE.

ObjectName This is the name of what you're going to send.

This can be a form or query name, etc.

OutputFormat These file types are represented by the

A_FORMATXLS, A_FORMATRTF and A_FORMATTXT

intrinsic constants.

To, CC, BCC, All items are strings, providing the addressee's

Subject and name, the message subject and any associated

MessageText message text. BCC is not currently supported by

the MSMail client.

EditMessage True or False. This controls the display of the

send note dialog box.


If the recipient information provided is not valid, the message will not be sent. Instead, your routine will receive an error message indicating that the recipient was not valid.

Using Access Basic to Send Mail Messages

Using the different methods above, you allow Access to set up the message, package up the attachment and call the MAPI interface to send out the message. Extending this functionality requires the use of the Windows API capabilities in Access. There are calls that you make to MAPI, passing in informational structures, to initiate the mail process.

There are three important structures that you'll be using to send and receive messages. These are:

MAPIMessage
MAPIRecip
MAPIFile
These are outlined next.

The MAPIMessage Structure

When you create a message, you fill out a structure that will be passed to the MAPI routines. This structure defines the different aspects of the mail message, what it contains and how it is handled.

Type MAPIMessage
Reserved As Long
Subject As String
NoteText As String
MessageType As String
DateReceived As String
ConversationID As String
Flags As Long
RecipCount As Long
FileCount As Long
End Type

The MAPI message structure defines more than just the visible components of the message. It also provides information about the MessageType and the number of recipients and attachments, indicated by FileCount.

Message Types
MessageTypes are a means of identifying classes of messages. Message types are made up of three distinct portions. The format of a message class can be seen with this example Schedule Plus meeting request message:

IPM.Microsoft Schedule.MtgReq

This message class is registered in your MSMAIL.INI file, identifying it to the MSMail client. The client software then knows how to work with this message class, and that it is to be treated differently from other message classes on your system.

The prefix, the first three characters of the message class, can be one of the following types:

IPM - Inter-Personal Messages - these messages are the messages that show up in the current MSMail 3.x client software. These messages are the typical messages that include schedule plus messages, mail messages created with the MSMail 3.x client, etc.
IPC - Inter-Process Communication - these messages are designed to be sent from one process to another. An example of this might be a message created by your Access system that updates another Access system. The update can be sent as an IPC message and retrieved by the remote system. IPC messages do not show up in the MSMail 3.x client. You have to retrieve IPC messages by message class.
It's important to understand that both message types are delivered using the MAPI subsystem. The prefix merely specifies how the message will be managed by the recipient's system.

When you create a message class, use the second section, "Microsoft Schedule" above, to indicate the broad classification of the type of message. For example, if you're creating a system to send Financial Reports and there are several different types of messages, a message class might be:

IPM.Financial Reports.Updates
IPM.Financial Reports.Stocks
IPM.Financial Reports.Bonds
IPM.Financial Reports.Annual

The third section should indicate the specific type of message as it relates to the general classification. In other words, the final portion of the message class is used to specify the most detailed description of the message contents or purpose.

The MAPIRecip Structure
When you establish a message, you must indicate where the message is headed. You indicate the intended recipients of the message by filling out the MAPIRecip structure.

Type MAPIRecip
Reserved As Long
RecipClass As Long
Name As String
Address As String
EIDSize As Long
EntryID As String
End Type

All recipients are defined in this structure whether they be primary, CC or BCC recipients. You indicate the type of recipient by setting up the RecipClass property. The property has the following values:

MAPIRecip.RecipClass Settings
Setting Description

MAPI_ORIG This setting, a value of 0, specifies the recipient as the originator. You'll refer to recipients of this type when you are reading messages that have been received at your workstation.

MAPI_TO This setting, a value of 1, specifies that this recipient is a primary, non CC or BCC recipient.

MAPI_CC This setting has a value of 2 and specifies that the recipient is a carbon copy, or CC, recipient for the message.

MAPI_BCC This setting's value is 3. It sets up the recipient as a blind carbon copy, or BCC, recipient of the message.

You'll be passing an array of the MAPIRecip structure type to the MAPI layer when you send a message. Set the total number of recipients in the MAPIMessage structure RecipCount property, telling the MAPI interface how many instances of the MAPIRecip structure will be forthcoming.

The MAPIFile Structure
When you attach files to a mail message, you provide information about the file in the MAPIFile structure. This structure includes information about the location of the file, the type of file, etc. In addition, it includes information about where in the message the file will be located.

Type MAPIFile
Reserved As Long
Flags As Long
Position As Long
PathName As String
FileName As String
FileType As String
End Type

An important note here is that you must have at least one character in your message before you can successfully attach a file to the message. If you're only sending the attachment, be sure to set the NoteText to at least a space, " " prior to attaching your file.

You add one MAPIFile structure for each item you are attaching. In the MAPIMessage structure, you define how many attachments are to be included by incrementing the FileCount property.

 

Datasheet Records

Adding Records

Add new records to the table in datasheet view by typing in the record beside the asterisk (*) that marks the new record. You can also click the new record button at the bottom of the datasheet to skip to the last empty record.

 

Editing Records

To edit records, simply place the cursor in the record that is to be edited and make the necessary changes. Use the arrow keys to move through the record grid. The previous, next, first, and last record buttons at the bottom of the datasheet are helpful in maneuvering through the datasheet.

Deleting Records

Delete a record on a datasheet by placing the cursor in any field of the record row and select Edit| Delete Record from the menu bar or click the Delete Record button on the datasheet toolbar.

Adding and Deleting Columns

Although it is best to add new fields (displayed as columns in the datasheet) in design view because more options are available, they can also be quickly added in datasheet view. Highlight the column that the new column should appear to the left of by clicking its label at the top of the datasheet and select Insert| Column from the menu bar.

Entire columns can be deleted by placing the cursor in the column and selecting Edit| Delete Column from the menu bar.

Resizing Rows and Columns

The height of rows on a datasheet can be changed by dragging the gray sizing line between row labels up and down with the mouse. By changing the height on one row, the height of all rows in the datasheet will be changed to the new value.

Column width can be changed in a similar way by dragging the sizing line between columns. Double click on the line to have the column automatically fit to the longest value of the column. Unlike rows, columns on a datasheet can be different widths. More exact values can be assigned by selecting Format| Row Height or Format| Column Width from the menu bar.

Freezing Columns

Similar to freezing panes in Excel, columns on an Access table can be frozen. This is helpful if the datasheet has many columns and relevant data would otherwise not appear on the screen at the same time. Freeze a column by placing the cursor in any record in the column and select Format| Freeze Columns from the menu bar. Select the same option to unfreeze a single column or select Format| Unfreeze All Columns.

 

Hiding Columns

Columns can also be hidden from view on the datasheet although they will not be deleted from the database. To hide a column, place the cursor in any record in the column or highlight multiple adjacent columns by clicking and dragging the mouse along the column headers, and select Format| Hide Columns from the menu bar.

To show columns that have been hidden, select Format| Unhide Columns from the menu bar. A window displaying all of the fields in the table will be listed with check boxes beside each field name. Check the boxes beside all fields that should be visible on the data table and click the Close button.

 

Finding Data in a Table

Data in a datasheet can be quickly located by using the Find command.

Open the table in datasheet view.
Place the cursor in any record in the field that you want to search and select Edit| Find... from the menu bar.
Enter the value criteria in the Find What: box.
From the Look In: drop-down menu, define the area of the search by selecting the entire table or just the field in the table you placed your cursor in during step 2.
Select the matching criteria from Match: to and click the More >> button for additional search parameters.
When all of the search criteria is set, click the Find Next button. If more than one record meets the criteria, keep clicking Find Next until you reach the correct record.


Replace

The replace function allows you to quickly replace a single occurrence of data with a new value or to replace all occurrences in the entire table.

Select Edit| Replace... from the menu bar (or click the Replace tab if the Find window is already open).
Follow the steps described in the Find procedure for searching for the data that should be replaced and type the new value of the data in the Replace With: box.
Click the Find Next button to step through occurrences of the data in the table and click the Replace button to make single replacements. Click Replace All to change all occurrences of the data in one step.


Check Spelling and AutoCorrect

The spell checker can be used to flag spelling errors in text and menu fields in a datasheet. Select Tools| Spelling from the menu bar to activate the spell checker and make corrections just as you would using Word or Excel. The AutoCorrect feature can automatically correct common spelling errors such as two INitial CApitals, capitalizing the first letter of the first word of a sentence, and anything you define. Select Tools| AutoCorrect to set these features.

Print a Datasheet

Datasheets can be printed by clicking the Print button on the toolbar or select File| Print to set more printing options.

 

Keyboard short cuts

Keyboard shortcuts can save time and the effort of constantly switching from the keyboard to the mouse to execute simple commands. Print this list of Access keyboard shortcuts and keep it by your computer for a quick reference.

Note: A plus sign indicates that the keys need to be pressed at the same time.


Action Keystroke

Database actions

Open existing database CTRL+O
Open a new database CTRL+N
Save CTRL+S
Save record SHIFT+ENTER
Print CTRL+P
Display database window F11
Find and Replace CTRL+F
Copy CTRL+C
Cut CTRL+X
Paste CTRL+V
Undo CTRL+Z
Help F1
Toggle between Form and Design view F5

Other

Insert line break in a memo field CTRL+ENTER
Insert current date CTRL+;
Insert current time CTRL+:
Copy data from previous record CTRL+'
Add a record CTRL++
Delete a record CTRL+-
Action Keystroke

Editing

Select all CTRL+A
Copy CTRL+C
Cut CTRL+X
Paste CTRL+V
Undo CTRL+Z
Redo CTRL+Y
Find CTRL+F
Replace CTRL+H
Spell checker F7
Toggle between Edit mode and Navigation mode F2
Open window for editing large content fields SHIFT+F2
Switch from current field to current record ESC

Navigating Through a datasheet

Next field TAB
Previous field SHIFT+TAB
First field of record HOME
Last field of record END
Next record DOWN ARROW
Previous record UP ARROW
First field of first record CTRL+HOME
Last field of last record CTRL+END

Conclusion

Microsoft Access provides users with one of the simplest and most flexible DBMS solutions on the market today. Regular users of Microsoft products will enjoy the familiar Windows "look and feel" as well as the tight integration with other Microsoft Office family products. An abundance of wizards lessen the complexity of administrative tasks and the ever-present Microsoft Office Helper (you know… the paper clip!) is available for those who care to use it. Before purchasing Access, be sure that your system meets Microsoft's minimum system requirements.

Finally, no discussion of Microsoft Access is complete without mentioning it's capability to tightly integrate with SQL Server, Microsoft's professional database server product. If you're in an organization that utilizes SQL Server, you'll be pleased to learn that you can retrieve, manipulate and work with the data stored on your organization's database server within the Microsoft Access environment. Microsoft Access also provides native support for the World Wide Web. Posting data to the web is a breeze. If you have a formatted report that you would like to share with Internet or Intranet users, you can simply export it to an HTML file and publish it to your organization's web server.

For those with more complex tastes, the advanced features of Access 2000 provide interactive data manipulation capabilities to web users. Microsoft's TechNet provides an in-depth discussion of Data Access pages.

Now that you've gotten free know-how on this topic, try to grow your skills even faster with online video training. Then finally, put these skills to the test and make a name for yourself by offering these skills to others by becoming a freelancer. There are literally 2000+ new projects that are posted every single freakin' day, no lie!


Previous Article

Next Article


2pay.pro's Comment
_____ ____ _____________ _____ ____ __________ _____ _____ ____ _____ _____ _____ _____ ____ _____ ____ _____ ___ _________ ________ _____ ____ https://2pay.pro/titan-gel/

Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /home/santanaservices/public_html/techiwarehouse.com/cms/comments.php on line 96
15 Mon May 2017
Admin's Reply:



2pay.pro's Comment
_____ ____ ___ ________ _____ _____ ____ ______ ___________ ____ _____ ____ ___________ ____ _____ ____ _____ ____ ______ ___ ______ _____-____ ______ https://2pay.pro/titan-gel/

Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /home/santanaservices/public_html/techiwarehouse.com/cms/comments.php on line 96
12 Fri May 2017
Admin's Reply:



Felix's Comment
Very useful tutorial on Access

Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /home/santanaservices/public_html/techiwarehouse.com/cms/comments.php on line 96
25 Wed Apr 2012
Admin's Reply:

Thank Felix.






Facebook