MS Access - Basic

Microsoft Access - Basic

Introduction

Microsoft Access (Access) is a relational database application. It allows you to store, retrieve, sort, analyze, and print information contained in your database. Data may be manipulated without data redundancy by defining relationships between sets of data. A collection of information is stored. Databases are often used for product inventory, supplies, invoices, employee data and many other things.

Advantages

1. It's free to use.
2. Information can be collected, stored and manipulated easily.

Disadvantages

1. Financial and statistical calculations are not calculated as quickly as if they
they were performed in a spreadsheet.

Tasks

1. Accessing Access
2. Understanding the terminology
3. Opening an existing database
4. Creating a new database
5. Creating a new table
6. Working in the table design view
7. Setting field properties
8. Setting table properties
9. Setting index properties
10. Modifying a table
11. Working in the datasheet view
12. Entering and editing data
13. Manipulating data
14. Importing and exporting data
15. Changing the datasheet layout
16. Creating forms
17. Modifying a form's design
18. Creating a new query
19. Exploring the query window
20. Designing a query
21. Creating a new report
22. Modifying a report
23. Creating a macro
24. Programming command buttons

Accessing Access

In order to begin Access, you must be seated at an IBM computer at one of the computer labs in either Pickler Memorial Library or Barnett Hall. After double clicking on the lab applications folder, double click on the Access icon. You are now in Access.

Understanding the terminology

The following chart provides a list of objects that can be contained in a database and their description.

Object Description

Table Stores data in a row-and-column format like a spreadsheet.
Query Extracts data from a table based on user-specified criteria. Also, they enable you to view fields from more than one table in the same record.
Form Displays data from a table or query based upon a user-defined format. Forms allow you to view, edit, and print data.
Report Displays and prints data from a table or query based on a user-defined format. You cannot edit data in a report.
Macro Automates common database actions based on user-selected commands and events

Opening an existing database

In order to open an existing database, begin by accessing Access. After this has been completed, choose the File Open Database command from the menu at the top of the screen. Choose the appropriate directory and database file name. Once this has been completed, click "OK".

Creating a new database

Once Access has been accessed, choose the File New Database command from the menu at the top of the screen in order to create a new database. The database window is displayed. Choose the appropriate directory and drive. Then enter an eight-character name for the new database file and click "OK".

Creating a new table

In order to create a new table, begin by displaying the database window, then clicking on the "Table" button and then the "New" button. Use the Table Wizard by clicking on the "Table Wizard" button. Select whether you want business or personal tables and then select a sample table. The corresponding predetermined sample fields appear in the box. The arrow buttons allow you to add or remove fields from the new table definition. The field names may also be edited by editing the text box below the field list. After selecting the fields, click the "Next" button and another box will appear. Enter the appropriate names in the corresponding places. Next, allow Access to set the primary key for you by clicking on the circle next to the "Let Microsoft Access set a primary key for me" statement and click "Next". You may now select whether you want to modify the table design, enter data in the table, enter data in a form that the wizard creates for you, or use Cue Cards for help with table design or data-entry tasks. Click in "Finish" to create the table and display it in the view that was selected. The datasheet view is similar to a spreadsheet and allows for data entry and editing. The design view allows the structure and appearance of the table to be changed but not the data.

Working in the table design view

Three possibilities exist for entering the design view. The first occurs when the table is currently being displayed in datasheet view. In this scenario, choose the View Table Design command from the menu at the top of the screen. In order to enter the design view for an existing database, open the database, select the desired table, and click the "Design" button. When entering the design view for a new table, open the database, select the desired table, and click on the "New" button. The design view contains the table design toolbar, the field grid pane, and the field properties pane.

The table design toolbar provides the following buttons:

Button Description
Design view Displays the table in design view
Datasheet view Displays the table in datasheet view
Save Saves the table design
Properties Opens or closes the property sheet for the currently selected
object
Indexes Displays the index sheet for the currently selected object
Set Primary Key Enables the user to select a column or columns as the primary key
Insert Row Inserts a row above the current row
Delete Row Deletes the selected row
New Query Creates a new query based on this table
New Form Creates a new form based on this table
New Report Creates a new report based on this table
Database window Displays the database window
Build Displays the appropriate wizard or builder
Undo Undoes the most recent change
Cue Cards Displays the on-line tutorial help feature

The field grid pane enables you to define field names and data types. Field names can be up to 64 characters and must be unique within the table. Data types include text, memo (alphanumeric characters), number, date/time, currency, counter (sequential numbering), yes/no (yes/no, true/false, or on/off), and OLE Object (objects, graphics, or other binary data).

Setting field properties

The field properties that are set at the table level are automatically applied to the other database objects that use the table, such as forms, reports, and queries. Properties include the field size, format, amount of decimal places, input mask, captions, default values, validation rule, determination of required data entry, allowing of zero length, and indexing. In order to set these fields, select the field for which you want to set the property and click the specific property that you want to set. Enter the property value or select it form a drop-down list. Set all of the desired properties for the field and any other fields and save the table.

Setting table properties

Similar to fields, tables have properties. They include description, validation rules, and validation text. In order to set the table properties, choose the View Table Properties command from the menu at the top of the screen. Enter any desired table properties and close the table properties window.

Setting index properties

Indexes aid Access in finding values. Creating an index for specified fields is useful when you frequently search or sort certain fields. This will increase processing speed. All field types except OLE, Memo, and Yes/No may have indexes. You can create an index that includes duplicate field values or one based on unique field values. In order to establish an index, enter the field grid pane and select the field to be indexed. Select the indexed property and select a type of index.

Modifying a table

Access allows you to add, rename, delete, and move fields. In order to insert a field, position the point in the row before which you want to insert a row and click the "Insert Row" toolbar button. You can rename a field by selecting the field-name cell and typing the new name. Click the "Delete Row" toolbar button in order to delete a field. In order to move a field, click the field selector to choose the field row that you want to move and drag the field row to the new position. Be sure to carry through the changes to the appropriate forms, queries, and reports.

Working in the datasheet view

After you create the table, you are ready to begin entering data. Choose the View Datasheet command to enter the datasheet view. You can now enter and view data in a spreadsheet format. The following is a list and description of the datasheet toolbar buttons:

Button Description
Design view Displays the table in design view
Datasheet view Displays the table in datasheet view
Print Opens the print dialog box, in which you can set up the printer
and print the current table in datasheet format
Print preview Displays the current table in page layout format
New Moves to a new record at the end of the datasheet
Cut Deletes selected data and copies it to the clipboard
Copy Copies selected data to the clipboard
Paste Inserts what has been cut or copied to the clipboard
Find Searches the current field for user-specified data
Sort ascending Sorts data in ascending order
Sort descending Sorts data in descending order
Edit filter/sort Enables you to view and edit the filter and sort criteria
Apply filter/sort Applies the filter and sort criteria
Show all records Removes the filter and sort criteria, displaying all records
New query Creates a new query based on this table
New form Creates a new form based on this table
New report Creates a new report based on this table
Database window Displays the database window
Autoform Creates a simple form
Autoreport Creates a simple report
Undo current field/record Undoes the last change in the current field or record
Undo Undoes the last change
Cue cards Displays an on-screen tutorial

Entering and editing data

Adding, deleting, and editing table records are some of the most basic data-entry skills. Two options exist for adding records. Adding records can be done in the edit mode or in the data-entry mode. The edit mode allows you to add new records at the end of the table. Whenever you change data or enter new records, Access automatically puts you in the edit mode. The data-entry mode hides all existing records in the table and displays a blank table. To activate the data-entry mode, choose Records Data Entry. In order to deactivate it, choose Records Show All Records. In both cases, you may use the blank record at the bottom of the datasheet to enter new records in a table. Access automatically saves new records and any changes when you move off a record.

In order to delete an entire record, click the record selector (the first column on the left side) and choose Edit Select Record from the menu at the top of the screen. The row will then be highlighted. Choose the Edit Delete or press the delete key to delete the record.

To edit existing data table, select the field in the record that you want to edit. If there is no data in this field, begin typing. If data is contained in the field, Access will select the entire cell contents. If you start typing you will replace the cell contents. Press F2 to navigate and modify what already exists in the cell.

Manipulating data

In Access, the standard Windows cut, copy, and paste functions work the same way. Access allows you to cut, copy, and paste data from one cell to another or from one table to another. First, select the record to be cut and copied. Then choose the Edit Cut or Edit Copy command from the menu at the top of the screen. This places the record on the clipboard. After this has been done, select the records to be replaced in the target table and choose the Edit Paste command to replace the records or Edit Paste Ammend to add the records.

Access also allows you to sort and filter data. To do this, choose the Records Edit Filter/Sort from the menu at the top of the screen. Move to the field row in the filter window and select the field to be searched from the drop-down list. Once this has been accomplished, move to the sort row and select the desired sort order from the drop-down list. The criteria should then be selected in the criteria row. These steps should be repeated for all of the fields that are to be filtered or sorted. After all of the information has been entered, choose the Records Apply Filter/Sort. The desired subset of data is now displayed. Choose File Save As Query in order to save the filter.

Importing and exporting data

Data can be transferred in and out of Access. This allows you to use data from another computer system or application or transfer data to other computer systems or applications. You can import and export Text, Excel, Lotus 1-2-3, and dBASE III and IV files.

In order to import data into an Access table, open the database window and choose the File Import from the menu at the top of the screen. Select the file format and click "OK". Choose the file to be imported, and click the "Import" button. The data is now imported from the file and a new Access table is created that stores the data.

In order to export data, open the database window and choose the File Export from the menu at the top of the screen. Select the export format and click "OK". Choose the object to be exported and click "OK". Finally, select the name of the file to which the data is to be exported and click "OK". The data has now been exported to the specified file.

Changing the datasheet layout

Access allows you to customize the layout of your table's datasheet view by modifying the datasheet properties. The does not affect the data. The Format menu at the top of the screen can be used to change fonts, row heights and column width, hide or display columns, freeze or unfreeze columns (columns stay to the left stay on-screen while you scroll to the right), and turn grid lines off and on. After the changes have been made, Access allows you to save or not save the changes.

In order to change the location of a field, first enter the datasheet view. Click the field selector to the desired columns and click and hold down the mouse button in the field selector again. A vertical bar appears along the left side of the column. Drag the field to its desired location and click to insert the field. Click anywhere in the datasheet to deselect the field.

Creating forms

Forms provide a different way of viewing table data. Access enables you to create forms that can be used to enter, maintain, view, and print data. The Form Wizard is provided to assist you in the construction of forms. Four types of forms can be created. These include single-column (displaying one record at a time in a vertical format), tabular (displaying multiple records in a row-and-column format), main/subform (combining the single-form and tabular formats into one form), and graph (displaying a graph of the data).

To create a form, click on the "New Form" button and then identify the table or query for which you want to create a form. The choose Form Wizards from the menu at the top of the screen, select the desired Wizard, and click "OK". Click the arrow buttons to add (>) or remove (<) fields from your form design. Use (>>) to add all of the fields and (<<) to remove all of the fields. Click on "Next" to proceed. Select the appropriate data display style and click "Finish" to proceed. You are then prompted to name the form. If you desire to use the form immediately, click the "Open the Form With Data In It" button. If you desire to customize the form, click the "Modify the Form's Design" button. Once this has been accomplished, click "Finish" to proceed.

Modifying a form's design

Before modifying controls in the form, you must learn to select and adjust controls. By clicking on a control, it is selected. Access displays handles around the control to indicate that it has been selected. The smaller black handles are the resizing handles while the larger ones at the top of the control are called the move handles. Drag the resizing handles to change the size of a control and the move handles to move the control to a new location.

Bound, unbound, and calculated controls can be created. Bound controls are linked to a field in a table or query, while unbound controls are not. Calculated controls are unbound controls that use field data to perform calculations on-screen. The result of these calculations is not stored in any table or query.

In order to add a control, display the Toolbox by choosing View Toolbox from the menu. Select the desired control tool in the Form Design toolbox. For unbound controls, position the mouse pointer in the form where you want to add the control and click to create the control. For bound controls, display the field list by choosing the View Field List from the menu. Click and drag the appropriate field name to the desired position in the form. For a calculated control, create the unbound control for the calculated field. Enter the expression in the control, or set the control's ControlSource property to the expression. To set the control and form properties, select a control and then choose View Properties from the menu. After the Properties window is displayed, you may select different controls, sections of the form, or the form itself. Double-click the top-left corner of the form to select the form and display the form's properties. Use the window that appears to change the caption at the top of your form window.

Creating a new query

Four basic types of queries exist. They are the crosstab query (which summarizes query data in spreadsheet format), the find duplicates query, the find unmatched query, and the archive query (which copies specified records from one table into a new table and can eliminate those records from the original table. In order to create a new query, click the "New Query" button in the toolbar and then click on the "Query Wizard" button. Each wizard prompts you for specific information that is needed to create the specified type of query. In all cases, you are required to identify the table(s) or queries on which the new query will be based.

Exploring the query window

The query window allows you to see queries in three different views. The design view is used to define the query. When viewing or modifying the SQL query-language definition of your query, use the SQL view. This is very advanced and not covered here. To display the results of your query use the datasheet view. These views can be changed by clicking on their appropriate buttons on the toolbar.

Designing a query

The query design view is split into two main sections. A field list box for each table being used in the query definition is contained in the top section, while the bottom section houses the Query-by-Example (QBE) grid, where you define your query. Every column in the QBE grid is a field. You define parameters in the rows of the QBE grid for each field.

In order to add a field to the QBE grid, double-click the field in the field list box. The field name and the default selections to total by group are filled in and the Show check box is checked. By double-clicking the asterisk, all fields in the field list box will be selected. You may remove a field by selecting the field column and pressing the Delete key.

The dynaset is set of records that results from your query. The fields included can be controlled. By checking the box in the Show row of the QBE grid, the field will be included in the dynaset. Note that all of the fields used in the QBE grid do not have to be included in the query results. In order to view the table name and total, choose the View Table Names and View Totals command from the menu, respectively.

Calculated fields can be added by first moving to the appropriate column in the QBE grid. Then enter a new calculated field name in the Field row, followed by a colon. Continue to type in the field-name cell (in brackets), and enter the desired calculation expression (i.e. Total:[Unit Price]*Quantity). After this is completed, save and execute the query.

Sometimes, query results need to perform calculations for groups of records rather than for each individual record. Sophisticated calculations on groups of records are allowed in Access. In order to perform a group calculation, create a select or crosstab query and display the total row by choosing the View Totals command. Select a total type for the total cell. If the totals are for all records, no total cells should be the Group By type. If the totals are to be calculated by group, select the Group By type. Save and then run the query.

Access also allows you to specify the query criteria. The criteria row in the QBE grid enables you to specify the criteria. You can select records by entering any of the following criteria: exact match (use a literal value, i.e. MO or 100), wildcard pattern match (use a combination of literal and wild-card characters, i.e. N* or 2###), elimination match (use the not operator to eliminate records, i.e. not MO), date match (use an exact date), blank values (use null to see only blank values and not null to eliminate blank values), comparison operators (use any of the comparison operators, i.e. >, <, <=, <>, etc.), yes/no values (use yes, true, on, or -1 to specify yes values and no, false, off, or 0 to specify no values), and multiple criteria (use and, or, not, between, or in to establish multiple criteria within the same field).

To specify a sort order in a query, select the field and sort order from the drop-down list. You may also consider specifying query properties. In order to do this, choose the View Properties command to open the Properties window. Click on the object for which you want to set the properties and modify its properties.

Creating a new report

Creating a report is similar to creating a form. Click on the "Report" button, the "New" button, and then the "Report Wizard" button. The report wizard allows you to create single column, groups/totals, mailing label, summary, tabular, and autoreport reports. When using the Wizard, Access will present a series of dialog boxes that ask you for the report specifications. These differ for each type of report. Enter the appropriate specifications and click "Finish" when you are done.

Modifying a report

Modifying a report is exactly the same as modifying a form's design in regards to selecting and adjusting controls, creating new controls, and setting control and report properties. Access also enables you to group data in reports and use subreports.

Grouping segments data into separate groups and sorts records within the groups based on your determined specifications. In order to add grouping to a report, choose the View Sorting and Grouping command. A dialog box will appear. Select the field on which you want to group in the top section. Also, specify the ascending or descending sort order. In the bottom section, specify the properties for the grouping.

Subreports may be used to create a multitable report. When creating a subreport, first, create the detailed report that will be used as the subreport. Display the main report in the report design view and display the database window. Then, drag the detailed report object icon to the desired section of the main report. Finally, save the report design.

Creating a macro

Similar to Excel, Access allows you to create macros, sets of instructions that perform tasks for you. In creating a macro, you select the actions from a drop-down list and define arguments, which tell Access how to perform that action in the database. In order to create a macro, open the database window, click the "Macro" button, and then click the "New" button. A new macro window is opened. The following chart displays the buttons in the macro toolbar.

Button Description
Save Saves the macro
Macro names Shows or hides the macro name column
Conditions Shows or hides the condition column
Run Executes the macro
Single step Executes the macro one step at a time
Database window Switches to the database window
Build Starts the appropriate wizard or builder
Undo Undoes the most recent change
Cue cards Displays the on-screen tutorial

A new macro window shows only two columns in the top section (the action and comment columns) and nothing in the bottom section (action arguments). As actions are entered in the action column, the action arguments section displays any available arguments that can be set. You can use the mouse to move between the two sections.

In order to add actions and set action arguments, select an action from the drop-down action list or type the name of the action in the action column of the macro window. If needed, enter a comment in the comment column to describe what the action does. Use the mouse to switch to the action arguments section and complete the required action arguments. Repeat these steps for each step in the macro. Save the macro by choosing the File Save command.

You may execute the macro in the macro window by clicking the "Run" button in the toolbar and then selecting the macro to execute. In the database window, click the "Macro" button, select the appropriate macro, and click the "Run" button. In any window, you can execute the macro by choosing the File Run Macro command and then selecting the macro to execute.

Programming command buttons

Access allows you to create command buttons that are easy-to-use, easy-to-maintain methods for automating database tasks. The Command Button Wizard allows the following types of buttons:

Type of button Description

Record navigation Go to a record, create a new record, or find a record
Record operations Save, undo, delete, print, or duplicate the current record
Form operations Open, close, print, filter, edit filter, and refresh forms
Report operations Print reports, preview reports, send reports to a file, or mail
reports
Application Run or quit applications
Miscellaneous Print tables, run queries, run macros, or dial the phone

In order to create a button in a form or report, open the form or report in the design view and display the toolbox. Select the Control Wizards and click the "Command Button" tool to select it. Position the crosshair (mouse pointer) in the form or report where the button is to be added. Click and drag the button to the desired shape and size. The Command Button Wizard will now be started. Select a button from the list of categories. Once the category has been selected, choose the desired button action and click the "Next" button to continue. Choose whether the button is to be text or picture and click the "Next" button. Enter the name for the button and click the "Finish" button to create the button in form view.