Microsoft Access : Create a select query by using the Simple Query wizard and Design View

How to Create tab in Microsoft Access includes two commands in its Queries group:

1. Query Wizard
2. Query Design

To run a query :

1. In the Navigation pane, double-click the query.
2. In Design view, click Runon the Query Tools Design tool tab.

To save a query :

1. On the Quick Access Toolbar, click Save.
2. In the Save As dialog box, enter a name for the query, and then click OK.

To delete a query :

    In the Navigation pane, right-click the query, and then click Delete.

To create a select query by using the Simple Query wizard :

Microsoft Access : How to change a field caption, field size, set the default value and update for a field

How to change a field caption :

In Datasheet view, click Name & Caption in the Properties group (on the Table Tools Fields tool tab). In Design view, enter the caption in the Caption property box..

How to change the field size :

In Datasheet view, use the Field Size box in the Properties group (on the Table Tools Fields tool tab) to specify the field size for a text field. In Design view, select the field size setting for a Number field; enter the value for a text field.

How to set the default value for a field :

In Datasheet view, click Default Value in the properties group to open the Expression Builder. In Design view, you can enter the default value, or click the ellipsis button (…) in the Default Value property box to open the Expression

Builder and enter the value or expression there

How to add a field validation rule and specify an input mask

How  to add a field validation rule :

1. Open the table in Datasheet view.
2. On the Table Tools Fields tool tab, click Validation, Field Validation Rule.
3. In the Expression Builder, enter the expression that defines the rule, and then click OK.
4. On the Fields tool tab, click Validation, Field Validation Message.
5. In the Enter Validation Message dialog box, enter the message that Access displays if users enter invalid data.

How to specify an input mask :

1. Open a table in Design view.
2. Select the field you want to work with.
3. In the Input Mask property box, click the ellipsis to run the Input Mask wizard.
4. Select the mask you want to use, or click Edit List to modify a built-in mask or create one of your own.

How to add,rename,delete a field in datasheet view and insert, define custom data type application part

How to add a field in Datasheet view

1. On the Table Tools Fields tool tab, in the Add & Delete group, click the data type for the field you want to insert. Click More Fields to display an extended list of data types.
2. In the column heading row, enter a name for the field.

How to insert a Quick Start data type

1. On the Fields tool tab, in the Add & Delete group, click More Fields, and then scroll down to display the Quick Start group.
2. Select the Quick Start data type you want to insert

How to define a custom data type application part

1. In the datasheet, select the field or fields you want to include in the custom data type.
2. On the Fields tool tab, in the Add & Delete group, click More Fields, and then click Save Selection as New Data Type.
3. In the Create New Data Type from Fields dialog box, enter a name and description for the custom data type. Select an entry in the Category list, or enter the name for a new category.

Materi Microsoft Access : How use a query as a filter and save a filter as a query

 How to use a query as a filter

1. On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort.
2. Click Advanced again, and then choose Load from Query.
3. In the Applicable Filter dialog box, select the query you want to use as a filter, and then click OK.
4. In the Sort & Filter group, click Toggle Filter to apply the filter. Click Toggle Filter again to remove the filter.

How to save a filter as a query

1. On the Home tab, in the Sort & Filter group, click Advanced, and then click Advanced Filter/Sort.
2. In the Advanced Filter/Sort window, in the Field row in the grid, select the fields you want to use in the filter.
3. In the Criteria row, specify the expression to use in the filter.
4. Click Advanced, and then choose Save As Query.
5. Enter a name for the query in the Save As Query dialog box, and then click OK

Materi Microsoft Access : How to create filter and an advanced filter to field

How to filter by a field in Datasheet view

1. Select the field you want to filter by, and then click Filter in the Sort & Filter group on the Home tab.
2. In the menu that Access displays, clear the check box for Select All.
3. Select the check box for each field value you want to view, and then click OK

How to filter by selection in Datasheet view

1. Select the value or the portion of a value you want to use as the filter.
2. In the Sort & Filter group on the Home tab, click Selection.
3. In the menu Access displays, select the expression for the filter (Equals, Does Not Equal, or another filtering option).
4. Click Toggle Filter to remove the filter from the table.

How to filter by form

1. In the Sort & Filter group on the Home tab, click Advanced and then click Filter By Form.
2. In the Filter by Form window, on the Look for tab, enter the value in the field or fields you want to use as the filter. 
3. Click the Or tab to set up OR conditions. 
4. In the Sort & Filter group, click Toggle Filter to apply the filter. Click Toggle Filter again to remove the filter.

How To create an advanced filter

1. On the Home tab, in the Sort & Filter group, click Advanced,and then click Advanced Filter/Sort.
2. In the Advanced Filter/Sort window, in the Field row in the grid, select the fields you want to use in the filter.
3. In the Criteria row, specify the expression to use in the filter.
4. In the Sort & Filter group, click Toggle Filter to apply the filter. Click Toggle Filter again to remove the filter.

Materi Microsoft Access : How to set up and apply an advanced sort

a. How to sort records from the Home tab

1. In the Navigation pane, double-click the table to open it in Datasheet view.
2. Click the field you want to sort by.
3. On the Home tab, in the Sort & Filter group, click Ascending or Descending.

b. How to sort records by using a shortcut menu

1. In the Navigation pane, double-click the table to open it in Datasheet view.
2. Right-click the field you want to sort by, and then click the command for the sort order you want to use. (The command names depend on the field’s data type.)

c. How to set up and apply an advanced sort

1. In the Navigation pane, double-click the table to open it in Datasheet view.
2. On the Hometab, in the Sort & Filtergroup, click Advanced, and then click Advanced Filter/Sort.
3. In the Advanced Filter/Sort window, drag the field or fields you want to sort by to the Field row in the grid.
4. In the Sort row, select the sort order you want to use.
5. In the Sort & Filter group, click Toggle Filter to sort the records.
6. Click Remove Sort to return the table to its default sort order.

Materi Microsoft Access : How to append and find records to a table in the current database

How To append records to a table in the current database

1. On the External Data tab, click the command for the data source you want to import.
2. In the Get External Data dialog box, click Append a copy of the records to the table, and then select the table you want to append records to.
3. Click Browse to locate the file with the source data, and then click OK.
4. Follow the steps in the import wizard (if Access provides one) to import and append the data.
5. In the Get External Data dialog box, select Save import steps if you want to save the steps in this operation.

How To find records

1. In the Navigation pane, double-click the table to open it in Datasheet view.
2. On the Home tab, click Find.
3. In the Find and Replace dialog box, enter the text you want to find in the Find What box.
4. In the Look In list, select Current field or Current document(which refers to the entire table).
5. In the Match list, select Whole Field, Any Part of Field, or Start of Field.
6. In the Search list, select All, Up, or Down.
7. Select Match Case to perform a case-sensitive search.
8. Select Search Fields As Formatted if you want to search the data as it is formatted in the datasheet.
9. Click Find Next.

Materi Microsoft Access : How to work with records in Datasheet view

 How to work with records in Datasheet view :

1. In the Records group, on the Home tab, click New.
2. Enter the values for each field that defines the record.
3. In the Records group, click Save.
4. To update the value for a field, select the field, and then enter the new value.
5. To delete a value from a specific field, select the value, and then click Delete in the Records group.
6. To delete a record, click the record selector (at the left edge of the table), and then click Delete Record in the Records group.

Microsoft Access : How to add, rename and delete a field in Datasheet view

How to add a field in Datasheet view :

1. On the Table Tools Fields tool tab, in the Add & Delete group, click the data type for the field you want to insert. Click More Fields to display an extended list of data types.
2. In the column heading row, enter a name for the field.

How to  rename a field in Datasheet  view :

1. Right-click the column heading for the field, and then click Rename Field.
2. In the highlighted area, enter the new name for the field .

How to delete a field in Datasheet view :

1. Right-click the column heading for the field, and then click Delete Field.
2. Click Yes in the prompt that Access displays to confirm that you want to delete the field.

Microsoft Access : How to use a query as a filter and save a filter as a query

How to  use a query as a filter :

1. On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort.
2. Click Advanced again, and then choose Load from Query.
3. In the Applicable Filter dialog box, select the query you want to use as a filter, and then click OK.
4. In the Sort & Filter group, click Toggle Filter to apply the filter. Click Toggle Filter again to remove the filter. 

 How to  save a filter as a query :

1. On the Home tab, in the Sort & Filter group, click Advanced, and then click Advanced Filter/Sort.
2. In the Advanced Filter/Sort window , in the Field row in the grid, select the fields you want to use in the filter.
3. In the Criteria row , specify the expression to use in the filter.
4. Click Advanced, and then choose Save As Query.
5. Enter a name for the query in the Save As Query dialog box, and then click OK 

Microsoft Access : How to filter by form and create an advanced filter

How to  filter by form  :

1. In the Sort & Filter group on the Home tab, click Advanced and then click Filter By Form.
2. In the Filter by Form window , on the Look for tab, enter the value in the field or fields you want to use as the filter. 
3. Click the Or tab to set up OR conditions. 
4. In the Sort & Filter group, click Toggle Filter to apply the filter. Click Toggle Filter again to remove the filter. 

 How to create an advanced filter :

1. On the Home tab, in the Sort & Filter group, click Advanced,and then click Advanced Filter/Sort.
2. In the Advanced Filter/Sort window , in the Field row in the grid, select the fields you want to use in the filter.
3. In the Criteria row , specify the expression to use in the filter.
4. In the Sort & Filter group, click Toggle Filter to apply the filter. Click Toggle Filter again to remove the filter.

Microsoft Access : How to filter by a field and by selection in datasheet view

How to  filter by a field in Datasheet  view :

1. Select the field you want to filter by, and then click Filter in the Sort & Filter group on the Home tab.
2. In the menu that Access displays, clear the check box for Select All.
3. Select the check box for each field value you want to view, and then click OK 

How to  filter by selection in Datasheet view :

1. Select the value or the portion of a value you want to use as the filter.
2. In the Sort & Filter group on the Home tab, click Selection.
3. In the menu Access displays, select the expression for the filter (Equals, Does Not Equal, or another filtering option).
4. Click Toggle Filter to remove the filter from the table. 

Microsoft Access : How to Sort From Home tab, Shortcut Menu and Set Up, Apply an Advanced Sort

How to sort records from the Home tab :

1. In the Navigation pane, double-click the table to open it in Datasheet view.
2. Click the field you want to sort by.
3. On the Home tab, in the Sort & Filter group, click Ascending or Descending. 

How to sort records by using a shortcut menu :

1. In the Navigation pane, double-click the table to open it in Datasheet view.
2. Right-click the field you want to sort by, and then click the command for the sort order you want to use. (The command names depend on the field’s data type.)  

How to set up and apply an advanced sort :

1. In the Navigation pane, double-click the table to open it in Datasheet view.
2. On the Hometab, in the Sort & Filtergroup, click Advanced, and then click Advanced Filter/Sort.
3. In the Advanced Filter/Sort window , drag the field or fields you want to sort by to the Field row in the grid.
4. In the Sort row , select the sort order you want to use.
5. In the Sort & Filter group, click Toggle Filter to sort the records.
6. Click Remove Sort to return the table to its default sort order. 

Microsoft Access : Find and append records to a table in the current database

How to append records to a table in the current database :

1. On the External Data tab, click the command for the data source you want to import.
2. In the Get External Data dialog box, click Append a copy of the records to the table, and then select the table you want to append records to.
3. Click Browse to locate the file with the source data, and then click OK.
4. Follow the steps in the import wizard (if Access provides one) to import and append the data.
5. In the Get External Data dialog box, select Save import steps if you want to save the steps in this operation.

How to find records :

1. In the Navigation pane, double-click the table to open it in Datasheet view.
2. On the Home tab, click Find.
3. In the Find and Replace dialog box, enter the text you want to find in the Find What box.
4. In the Look In list, select Current field or Current document(which refers to the entire table).
5. In the Match list, select Whole Field, Any Part of Field, or Start of Field.
6. In the Search list, select All, Up, or Down.
7. Select Match Case to perform a case-sensitive search.
8. Select Search Fields As Formatted if you want to search the data as it is formatted in the datasheet.
9. Click Find Next.

Microsoft Access : How to work with records in Datasheet view

How to work with records in Datasheet view :

1. In the Records group, on the Home tab, click New.
2. Enter the values for each field that defines the record.
3. In the Records group, click Save.
4. To update the value for a field, select the field, and then enter the new value.
5. To delete a value from a specific field, select the value, and then click Delete in the Records group.
6. To delete a record, click the record selector (at the left edge of the table), and then click Delete Record in the Records group.

Microsoft Access:How to add a description and set other table properties

How to add a description and set other table properties :

1. Right-click the table in the Navigation pane, and then click Table Properties. 
2. Enter a description in the Description box. 
3. Open the table in Datasheet view. 
4. On the Table Tools Table tool tab, click Table Properties. 
5. In the Enter Table Properties dialog box, specify the settings you need.

Materi Microsoft Access : How to Add a Total row to a table and rename table

How to Add a Total row to a table :

1. In the Navigation pane, double-click the table (or right-click the table, and then click Open). 
2. On the Home tab, in the Records group, click Totals. 
3. For each column in the Total row where you want a total to appear, click in the column, and then select the function you want to apply. 
4. To remove the Total row , click Totals in the Records group.

How To rename a table :

1. Right-click the table in the Navigation pane. 
2. Click Rename, and then enter the name you want to use.

Materi Microsoft Access: How to change a field’s data format in Datasheet view

How to change a field’s data format in Datasheet view

1. Open the table in Datasheet view. 
2. Click the column heading for a field. 
3. In the Formatting group on the Table Tools Fields tool tab, select the format you want.

Materi Microsoft Access: How to hide and unhide fields, freeze and unfreeze fields

How to hide and unhide fields : 

1. In the Navigation pane, double-click the table to open it in Datasheet view. 
2. Right-click the column heading for the fields you want to hide, and then click Hide Fields. 
3. To show hidden fields, right-click a column heading, and then click Unhide Fields. 
4. In the Unhide Columns dialog box, select the check boxes for the fields you want to show. 

 How to freeze and unfreeze fields :

1. In the Navigation pane, double-click the table to open it in Datasheet view. 
2. Right-click the column heading of the fields you want to freeze, and then click Freeze Fields. 
3. To unfreeze fields, right-click a column heading, and then click Unfreeze All Fields.