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 have an extension .mdb, just like Excel spreadsheets have an extension .xls and Word documents have an extension .doc.  Once you save it, the name of the database will appear at the top of the database window, and buttons for tables, queries, forms, etc. will appear down the left hand side.

To create a table in design view, you first specify the structure of the table; once the structure is specified, then you enter the data, as opposed to Excel where you typically enter data right off.  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.

Print Setup allows you to specify landscape/portrait orientation for printing and Print Preview gives you a preview.

Additional tables can be added to a database simply by clicking table in the database window and then selecting one of the Create table alternatives (we will create in design view).

Forms - If you use a form, you see only one record at a time.  Using a form permits you to see more fields than the datasheet view, and thus in many cases you will be able to see in a single window all the fields in a table where all fields are not visible without scrolling in datasheet view.  An easy way to create a standard form is to select the table and then select AutoForm from the New Object dropdown box icon on the toolbar. 

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, Datasheet View, and Form View (the last is the default). 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 Report from the New Object dropdown box icon on the toolbar, then choose 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.

Outlook 2003 and updated versions of Outlook 2002 restrict 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 the table first, and then go to New Object dropdown box icon on the toolbar, and choose Query, the Design View.  Alternatively, select Query on the Database Window, then New, then Design View, and then Add the table(s) which will be used in the query.

Fields can 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.  Edit, Clear Grid clears the Design Grid.

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. 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, you select Queries, then New, then Design View, then add the necessary tables to the query.  Then 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.)
(2) To calculate a statistic based on the value of a field in some records, right click on the field used to select the records and in the Totals Line, scroll down in the dropdown box till you find the word "Where".  You can then specify a criterion.
(3) 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.

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, and then 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 for 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 in most cases.

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

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

If you want to display all records that have the same value for a given field as does a particular record, you select the value in that field in the record, and then click the Filter by Selection icon on the toolbar.  To remove the filter, click the Remove Filter icon on the toolbar.

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 - data must be entered here
(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 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 using the Relationships command (icon on toolbar) 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>Backup. Within Access, this is the way that one saves the database to another location than its original location. Note that within Access, if you elect the Save option, you are only saving a table, not the entire database.

Replicating a database - Tools>Replication>Create Replica. The database used to create the replica is converted to a Design Master. Changes to the design of the database's objects can only be made in the Design Master. Changes in the replica can be synchronized with the Design Master.

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 Tools>Database Utilities>Compact and Repair Database. One can do this step automatically each time one closes the database by selecting Tools>Options>General>check the Compact on Close button.

To bring an Excel spreadsheet into Access, choose File>Get External Data (you will need to change the file type to include Excel files)>Import (you will be asked whether the first row should be construed as column headings and whether to create a primary key). File>Get External Data>Link Tables means that the data is maintained in the original application and any changes to that data in that application automatically show up in Access.

To export a table to Excel, click on the table name and choose File>Export.

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

Some topics left for advanced course:

1) Insertion of subdatasheets into subdatasheet
2) Creation of subforms
3) Modifying and moving controls in a form
4) Adding controls to a form
5) Creating a report in design view, resizing and moving controls in a report

Revised: July 18, 2007. Comments to William Pegram, wpegram@nvcc.edu