Access Topics

Terminology: (1) database - collection of data that allows access, retrieval, and use; in Access, a database is contained in a single file, whereas in some other programs, multiple files are used
(2) The data in an Access database are contained in one or more tables. Each row in a table is a record; each column is a field.  If a field has entries for all records and all the entries are unique, i.e. no two records have the same value for that particular field, it can be designated the primary key of the table.  If two tables are linked by a common field, and the primary key of one table is found in the other table, it is termed a foreign key in the second table.

When you create a new Access database, you will be prompted right away to save it.  Access databases for versions up through 2003 have an extension .mdb, and the extension for Access 2007 databases is .accdb. Sometimes Access will create a small additional file, so if you send someone your database, it is important that you send them the larger file (they won't need the smaller file).

Once you save the table, you will be in datasheet view for a table and you can enter data, just like in Excel. The names of the fields will be ID, and then Field1, Field2, etc. You can rename the fields by double clicking the name of the field, just like you can rename sheets in Excel. Alternatively, when you view the table in Design View, you can alter the field names there. When you initially enter data in datasheet view, Access infers the data type for the field from the data you enter in the table.

An alternate approach to table creation is to create the table in design view by selecing Create>Design View. In this approach, , you first specify the field names and datatypes, and then once the table structure is thus specified, then you enter the data. In Design view, there is a field name, a data type, and a description; the description is optional.  To designate a field as the primary key, you select the field by clicking to the left of the field name, and then click the key symbol on the toolbar.  Hitting F6 moves you between the top and bottom windows.  You can change the structure of the table by deleting fields, adding fields, or changing the order of fields.  To add fields, place the cursor where you want the field to be added, and hit the Insert key on the keyboard.  To delete a field, select the field and hit the delete key.  To reorder fields, click to the left of the field name, and drag and drop the field to the desired location.

Saving results - As soon as move from one record to another, changes in the previous record are saved to disk, unlike other programs such as Word and Excel where saving to disk is done  when the user elects to save the file. A corollary of this Access behavior is that if you are entering data that Access considers an error, it won't let you move off the record until you fix the error.

Adding additional records is done by adding records at the bottom of a table.

Additional tables can be added to a database simply by selecting Create>Table Design (for creating a table in design view) or Create>Table (to create the table in datasheet view).

Forms - If you create a form in columnar view (the default in the query wizard), you see only one record at a time, thus you can see more fields than in other views, and thus avoid the need for scrolling to see all fields in datasheet view of the table or tabular or datasheet layout for the form. 

To open a table, doubleclick the name of the table or right click on the table and select Open.

By selecting the View drop down box at the very left of the toolbar, you can switch between views.  For a table, you switch between Design View and Datasheet View.  For a form, you choose between Design View and Form View. This shortcut is quicker than closing the table or form and then reopening it in the different view. 

Creating a Report - Select the table, then click Create>Report or Create>Report Design, or Create>Report Wizard.

Breaking a table into smaller linked tables to reduce redundancy - (1) saves space, (2) makes updating easier, and (3) avoids inconsistent information.  This is called a relational database.  Another reason to use a relational database in some cases is where the number of items to be associated with a given record is variable.

Beginning with some updates to version Outlook 2002, Outlook began restricting the receipt of Access files as attachments for security reasons. To get around this, (1) upload the file to a server and email the link, (2) Change the extension on the Access file to something that Outlook doesn't block, or (3) Have the recipient change their registry so as to permit the receipt of Access files.(see http://support.microsoft.com/default.aspx?scid=kb;en-us;829982).

Queries

There are several ways to create a query.  Select Create>Query Design or Create>Query Wizard. Under the former, the first step is to select the table(s) and/or queries that will serve as input to the query. Fields can then be added to the query by dragging them from the list of fields at the top, by double clicking them in the list of fields at the top, or by clicking the arrow in the Design Grid (the bottom half of the screen) and selecting the desired field.  To select all fields in a table, you choose the asterisk. 

Entering criteria - equality can be specified by using the = sign or simply by typing the value.  You can use comparison operators.  To use wildcards, you use the keyword LIKE.  The * wildcard represents any character for any number of character positions whereas the ? wildcard represents any character in a single character position.  Thus LIKE ra* would select any records where the field began with the letters ra where LIKE ra? would select any record where the field had exactly three characters, the first two being ra. 

To run the query, click the exclamation point on the toolbar.

A query can of course select records where the value of a given field is equal to a given value by entering this value in the criteria line of the query. If one wants to specify this value when the query is run, as opposed to when the query is created, one can create a parameter query. To do this, simply enter some text in the criteria line between square brackeets. Any text is OK, except that you must enter something different than simply a field name. When the query is run, the user will be prompted with this text and the value they enter in the textbox will used as the value in the query.

A field that is present on the design grid will show in the output of the query unless you uncheck the box (you would uncheck the box when you want to select records based on a field, but do not wish that field to appear in the output).

Within a single field, you can specify compound criteria by using AND and OR.  Across fields, you specify an AND condition by specifying the criteria on the same line; you specify an OR condition by specifying the criteria on different lines.

Sorting of records is always done in terms of the values of a particular field.  You can sort the results of a query based on a single field or on two fields by specifying the sort order within the design grid (the default is for no sort).  Where multiple fields are designated for sorting, they are sorted from the leftmost sort field (only if there are ties in that field, does one move to the next sort field).  

Tables are linked (joined) by fields which represents the same information in both tables.  If you join two tables that have a field name in common, Access will use this field to join the two tables.  If you want to join tables where the fields have different names in the two tables, in Design View of the query, click one of the field names you want to join and drag and drop it on top of the field name in the other table you wish to use for the join.

In a query, you can use a field which is calculated.  To do this, in the field name you type the name you want to give the field, then a colon, then the expression for the calculation.  You put square brackets around the name of any field you use in the calculation.  If you need to specify the table for a field name (i.e. for purposes of clarity or where two tables have the same field name), you use the name of the table followed by a period followed by the name of the field.  Only the name of the field is enclosed in square brackets.

Calculating Statistics - (1) To calculate a statistic based on the value of a field in all records, right click on the field in the query, and the Total row will appear in the Design Grid and then click the Group By entry and select the appropriate statistic (e.g. Sum, Min, Max, etc.). An alternative to right-clicking is to click the Totals button (the summation symbol).
(2) Statistics can also be calculated for groups of records.  Grouping means creating groups of records that share some common characteristic.  To indicate grouping, select Group By as the entry for the Total row of the field to be used for grouping. Any field present in the query must either be used to create a grouping or for the calculation of a statistic.
(3) To calculate a statistic only on records where a field satisfies a criteria click in the Totals line and select "Where" in the drop down box. You can then specify the criteria in the Criteria line.

Saving a query - give it a name and then it can be used in the future

Update query - An update query will change all records in a table as specified in the query.  To create it, when you are creating a query and you have the design grid, right click on the top pane and select Query Type>Update Query.  To refer to a field in an expression, enclose the field name in square brackets []. Prior to running an update query, it is best to run it as a select query to see what records will be changed, since the results of the update can't be undone.

Delete query - A delete query will delete all records satisfying a criterion.  The procedure to create one is the same as before, except that one selects Delete Query for Query Type. As with an update query, prior to running a delete query, it is best to run it as a select query so that one sees what records will be deleted since the results of the deletion can't be undone.

Make table/action query - This creates a new table based on the results of the query.  You must specifyy a different name when you save the query than the name you specified for the table you want to create.

Modifying Data

If one runs a select query and then types in different values in the datasheet view of the query (the results), these changes will be carried through to the underlying table and thus the query can be used to enter and edit data. Certain table structures do not allow you to change the data in the underlying table this way.

In Form View, the new record button is at the right of the controls at the bottom of the form. 

You can search for a record in Form View or Table DataSheet view based on the value of a particular field by clicking in the field and then selecting Home>Find or Ctrl F.  You can base the Find on the (1) whole field (which is the default), (2) the start of the field, or (3) any part of the field. 

To delete a record, click to the left of the record and hit the delete key.  You will be asked whether you want to delete the record.  If an autonumber field is present in the table, the value of the autonumber field in the deleted record will not be used in a subsequent record. To resize fields, use the same techniques as in Excel (i.e. grab the right hand side of the column at the top, or double click this area for "best fit".

Creating validation rules - You do this in the bottom pane of design view for a table.
(1) Specifying a required field - a required field means that data must be entered for the field
(2) Specifying a valid range for data - validation rule
(3) Specifying validation text - This is the prompt message that is displayed to the user if the rule is not satisfied
(4) Specifying a default value - This enters a default value in the field if no value is specified
(5) Using a format - This affects the display of the data but not the underlying data. Two predefined formats are as follows: > displays lower case as upper case, < displays upper case as lower case.
(6) Using an input mask - The input mask wizard works only with date/time and text fields. Examples of use with the latter are social security numbers, zip codes, telephone numbers, etc. For fields such as number or currency, one has to manually enter the mask. There are various codes which one can use to restrict the data entry (see Access, Section 2, p. 45 in the Rutkowsky and Seguin text).

In addition to validation rules, another way to prevent errors during data entry is to create a lookup field in cases where the values for a field can be chosen from a small set of values. In design view of the table, select "lookup wizard" as the data type for the field and then one can specify the allowable values. As a result, in the datasheet view of the table, there will be a drop-down box in that field showing these values and thus the user can easily select one of these or type in one that is not on the list. One can restrict the choices to only those on the list by clicking the Lookup tab and then clicking in the "Limit to List" property box and choosing "Yes" on the drop down menu. As an alternative to typing in the permissible values, one may choose the table and field name to be used to generate the list of possbilities for the drop down box.

Specifying relationships - If you specify a relationship between two or more tables, you join the tables and thus do not have to join the tables each time you specify a query.  In Access, you specify a relationship by choosing Database Tools>Relationships, specifying the tables, and by dragging a field from one table and dropping it onto the corresponding field in another table. 
(1) When you establish the relationship, you have an option to check a box to enforce referential integrity.   Referential integrity means that the value in a foreign key must match that of another table's primary key.  Remember that a foreign key is a field in one table which is a primary key in another table.  Access then prohibits any updates to the table that would violate referential integrity.  
(2) When you establish a relationship, it is often a one to many relationship.  For Access to recognize this, you must have defined a primary key in the table which represents the one part of the relationship.  Typically the table containing the foreign key will be the many part of the relationship.
(3) When you establish a relationship, you have an option as to how the tables are joined.  The default is that you only join records where the value of the fields that are linked is identical.  (Incidentally, if you specify fields from two tables but fail to join the tables, the number of records is the cross-product of the two tables, which is not normally what you want.).

Using subdatasheets - When a relationship is established between tables, you can view all records related to a single value of a given field.  The availability of a subdatasheet is indicated by a plus sign in the datasheet view; to display the subdatasheet, click on the plus sign. To remove the display, click the minus sign.

Ordering records - By default, records on sorted by the primary key field. To sort by another field, click on the field name and click the Sort Ascending or Sort Descending buttons.  If you want to sort on two fields, you select both of them and then click the Sort Ascending or Sort Descending button.  The field on the left will be the field that is sorted on first. If the fields are not in the correct order, you can drag the fields into the correct position or use a query that has the data fields displayed in the correct order.

Backing up a database - File>Save As. In Access 2007, you have the option of saving the entire database or the specific database object you have selected (such as a table). In previous versions of Access, File>Save As only saved a database object and not the entire database. Alternatively, one can save the entire database by selecting the Office Button>Manage> Back Up Database. To save the entire database in early versions of Access, one could choose File>Backup.

Compacting a database - If one has been making a number of changes to a database, the file size for the database will likely have grown to be larger than necessary. To compact it, select Office Button>Manage>Compact and Repair Database.

To bring an Excel spreadsheet into Access, choose Office Button>External Data>then select the file type of the import (Excel, Access, etc) in the Import group. You will be asked whether you want to import the data or link to the data in the original source.

To export a table, click on the application you wish to export to, under the Export group. If you want to export to another Access database, select More on the Export Group and then choose the application. .

----------------------

Revised: January 3, 2010. Comments to William Pegram, bill@billpegram.com