To IT240 home

Access Tutorial

Relational Database

A type of database that stores information in tables that can be related, or cross-referenced. Access is a relational database.

More specifically, this means

• The tables are highly structured. They must conform to rules governing the fields in the tables.
• Tables share common fields that enable them to be related or linked together.
• Each table contains a primary key field containing the unique identifier for the record. A specific data value in the primary key can only occur once in the table.

A. Introduction

B. Creating

C. Opening

D. Searching

E. Filtering

F. Sorting

G. Primary Keys

H. Importing

I. Exporting

J. Normalization

K. Relationships

L. Queries

M. Reports

A. Introduction

• A table is a rectangular array of data. Here is a table named StudentData.

  Table A.

  StudentData
  StudentID	Name	Year	Major	FullTime
  032901	Julie	3	Sociology	True
  090878	David	4	Art	True
  073770	Carolyn	1	Accounting	True
  010977	Bill	3	Biology	False
  065618	Arthur	2	Mathematics	True
  044541	Nancy	4	Sociology	False

By following a few basic rules, tables can be designed so that data manipulation, storage, and retrieval are more accurate and efficient. These rules are applied to a first draft of tables in a database. The rules are as follows: Each record in a table should be unique. No two records should be exactly the same. When each record is unique, it ensures that specific records are easy to locate, examine, or edit. No field in a record should have more than one possible value. When this happens it's called a repeating group, and affects data storage, integrity, and retrieval. Removing a repeating group is sometimes called flattening a table. See an example of this below in Section J, Normalization. Data should appear in only one table. Storing the same data in several places wastes disk storage and invites mistakes. There are many more rules to follow if you want to design a truly relational database. Once you are more experienced with Access, you may want to learn more about proper design, including all levels of data normalization.

• Each row of a table represents a single record of related data items. For example, if a table represents student data, each row would represent a single student.

• Each column of a table represents a particular piece of data from the rows. For example, if a table represents student data, then the columns might be StudentID, Name, Year, Major, FullTime. A field is defined as the intersection of a row and a column. Each field has a specified datatype. Typically, a field is the smallest unit of data in a database.

• Some common datatypes for fields are Text, Number, Date/Time, Currency and Yes/No.

• Students.mdb - This database contains the StudentData table.

Top   

B. Creating

• Tables are created in an existing database. Here is how to create a database:
  Invoking Access
  1. Start >> All Programs >> ...

  Creating a New Database
  1. Select File >> New >> Blank Database...>>

  2. Select the database name from the File New Database Dialog.

• After the database has been created, there are three ways to create database tables from scratch in Access: by entering data, in the design view, and using the wizard. Later we will see how to import table data into Access from text files or from Excel files.
  Creating a Table By Entering Data
  1. Select Tables from the list of objects on the left side of the Database (Access 2000 file format) Dialog >> Double click on "Create table by entering data".

  2. Enter the data into the first five fields. Change the names of the fields Field1, ... , Field5 to the desired field names by right clicking on the column name >> Rename Column.

  3. Close the data entry form. When the message box appears with the question "Do you want to save the changes to the design of table 'Table1'?", respond Yes and enter the new table name. Answer No to the question "Do you want to create a primary key now?"

  Creating a Table in Design View
  1. Select Tables >> Create table in design view.

  2. For each row that you wish to define, enter the field name and datatype. (The most common datatypes are Text, Number and Yes/No.)

  3. Change the table name as you did when creating a table by entering data.

  Creating a Table by using the Wizard
  1. Select Tables >> Create table by using wizard.

  2. In the Table Wizard Dialog, select Business or Personal >> Select one of the Sample Tables >> Transfer some of the Sample Fields to the Fields in my new table. Change the table name as you did when creating a table by entering data. Additional fields can be added in the Design View.

Top   

C. Opening

• If a database with tables has already been created and saved, it must be opened for further modifications.
  Opening an Existing Database
  1. Invoke Access >> File >> Open >> Select the name of the database to open.

  Opening an existing table
  1. Double click on the table name.

  Modifying the Rows in a Table
  1. Select View >> Datasheet View. Add or modify rows as desired.

  Modifying the Columns in a Table
  1. Select View >> Design View. Add or modify fields as desired.

Top   

D. Searching

• A small database can be searched visually without computer aid. However, searching a large database may require the aid of software.
  Searching for a Field Value
  1. Select the column containing the field value to find. Select Edit >> Find.

  2. Enter the desired value in the "Find What" box. The first value of the desired value will be displayed.

Top   

E. Filtering

• Use filtering to extract some rows or columns from a database table. There are four types of filtering: by form, by selection, by excluding selection and by advanced filtering.
  By Form
  1. Select Records >> Filter >> Filter by Form.

  2. Enter the desired field value or values.

  3. Click Or at the bottom of the Filter Form to apply more than one criterion.

  4. Select Records >> Filter >> Apply Filter/Sort.

  By Selection
  1. Select the desired field value in the table.

  2. Select Records >> Filter >> Filter By Selection.

     Filter by Selection may be repeated to produce compound filters.

  By Excluding Selection
  1. Select the field value in the table to exclude from the selection.

  2. Select Records >> Filter >> Filter By Excluding Selection.

     Filter by Excluding Selection may be repeated or combined with other types of filtering.

  By Advanced Filter
  1. Select Records >> Filter >> Advanced Filter/Sort.

  2. Enter the fields on which to filter (double click on the column name) and the filter criteria.

     Here are some filter criteria examples: >1, <2, ="M", <>"M", >=#1/1/2002# (Dates are delimited by #.)

     When multiple filter criteria are supplied, the filtered records must match all of the criteria. This is equivalent to a logical and operation.

  Removing a Filter
  1. Select Records >> Remove Filter/Sort.

Top   

F. Sorting

• The rows of a table can be sorted by one or more fields.

• If the rows are sorted by two fields, say Field1 and Field2, the rows are sorted first by Field1. Then all the rows having a fixed value of Field1 are sorted by Field2. Here is the StudentData table sorted by Year and Major.

  Table B.

  StudentData
  Name	Year	Major	FullTime	Email
  Carolyn	1	Accounting	True	carolynw@supermail.com
  Arthur	2	Mathematics	True	arthurj@supermail.com
  Bill	3	Biology	False	billk@supermail.com
  Julie	3	Sociology	True	julieh@supermail.com
  David	4	Art	True	davidr@supermail.com
  Julie	4	Sociology	False	nancya@supermail.com

  
   

Sorting on a Single Column
1. Select a column headers.

2. Then select Records >> Sort Ascending (or Sort Descending).

Sorting on Multiple Columns
1. Select Records >> Advanced Filter/Sort.

2. Enter the columns on which to sort with Sort Ascending or Sort Descending for each column.

Removing a Sort
1. Select Records >> Remove Filter/Sort.

Top   

G. Primary Keys

• A primary key is a field that uniquely identifies a row in a table.

• Occasionally, a primary key consists of a combination of two or more fields.
  Setting a Key as the Primary Key in a Table
  1. Open the table.

  2. Right click on the table title bar >> Select Table Design.

  3. Right click on the desired field name >> Primary Key. A small key icon should appear to the left of the field.

• If the primary key consists of more than one field, repeat the preceding step for each field in the primary key.

Top   

H. Importing

• Data for a table can be imported from existing files of various formats, for example, text or Excel.
  Importing Table Data
  1. Create a new database or open an existing database that is to contain the imported table.

  2. Select File >> Get External Data >> Import...

  3. Select the type of file to import in the "Files of type" box in the Import Dialog.

  4. Then enter the name of the file to import and click Import.

  5. Follow the directions in the Import Spreadsheet Wizard.

Top   

I. Exporting

• Access tables (including tables produced from queries) can be exported in various file formats, including fixed width text, comma separated value and Excel.
  Exporting Table Data
  1. Open the database and table that contains the data to export.

  2. Select File >> Export.

  3. Select the type of file ("Save as type" box) and the name of the file (Filename box) of the file to receive the exported data, and click Export All.

  4. Follow the directions of the Export Text wizard if one of the file types .txt or .csv is chosen.

Top   

J. Normalization

• Sometimes you would like to create a table like this with more than one value in a field:

  Table C.

  StudentData
  StudentID	Name	Year	Major	FullTime	Course
  032901	Julie	3	Sociology	True	ENG202 SOC210 ISP121
  090878	David	4	Art	True	MAT111
  073770	Carolyn	1	Accounting	True	BUS215 ISP121 IT130 REL300
  010977	Bill	3	Biology	False	BIO200 CSC211 ENG202
  065618	Arthur	2	Mathematics	True	MAT215 CSC212
  044541	Nancy	4	Sociology	False	SOC301

  This is our previous (see Introducton) StudentData table (Table A) with a Course field added. However, now our second rule No field in a record should have more than one possible value.is violated. There are multiple values in the Course field because one student can take several courses. The data should be split into two tables, as shown below (Tables D and E). The repeating group, Course, has been moved into its own table, CourseData, to eliminate the repeating data group. The StudentData table is as it was originally (Table A).

   

   

   

   

   

   

   

   

  Table D.

  StudentData
  StudentID	Name	Year	Major	FullTime
  032901	Julie	3	Sociology	True
  090878	David	4	Art	True
  073770	Carolyn	1	Accounting	True
  010977	Bill	3	Biology	False
  065618	Arthur	2	Mathematics	True
  044541	Nancy	4	Sociology	False

  Table E.

  CourseData
  StudentID	Course
  032901	ENG202
  032901	SOC210
  032901	ISP121
  090878	MAT111
  073770	BUS215
  073770	ISP121
  073770	IT130
  073770	REL300
  010977	BIO200
  010977	CSC211
  010977	ENF202
  065618	MAT215
  065618	CSC212
  044541	SOC301

• Note that simply adding additional rows, one for each course, is a poor solution to the repeating groups problem.
  • StudentID could no longer be used as a primary key, and
  • there would be a lot of repitition of data.

• The solution given above is part of a process known as normalization.

• Now StudentID can still be used as a primary key for the StudentData table.

• A foreign key is a field of a table that contains the primary key of another table.

• StudentID is a foreign key in the CourseData table.

• So, for example, to determine the name and year of all students taking ISP121 we could first find all records in the CourseData table that match the Course = ISP121. Then, using the foreign key, look them up in the StudentData table.

Top   

K. Relationships

• A foreign key is a field that contains the primary key of another table.

• Example: DoctorID in the Patients table is a foreign key that contains the primary key of the Doctors table.
  

  Table F. Patients PatientID Name DoctorID 11111 Sally 4444 33333 Winston 4444 55555 Chloe 6666 77777 Jason 4444 99999 Mary 6666

  Table G. Doctors DoctorID DoctorName 4444 Jones 6666 Smith

• A foreign key defines a one-to-many relationship (one doctor to many patients in the previous example).

• The joins between tables are established by having the various tables share a common field. Typically, a primary key in one table is linked to the same field (the foreign key) in another table. Fields used in a join should be identical in design, that is, the same data type and size, and they should contain the same set of values. They don't have to have the same name nor do they have to be primary keys. It's easier to remember which fields are the common fields, however, if the same field names are used.

• A join of two tables, T₁ and T₂, is the composite table, T, defined by a one-to-many relationship. A row in table T is defined for each pair of rows where the primary key in T₁ matches the foreign key in T₂.

• Example: The join of the tables Doctors (Table G) and Patients (Table F) is Table H. Notice that in each row of Table H, the join variables Doctors.DoctorID and Patients.DoctorID match.

  Table H.

  Join of Doctors and Patients
  Doctors.DoctorID	DoctorName	PatientID	Name	Patients.DoctorID
  4444	Jones	11111	Sally	4444
  4444	Jones	33333	Winston	4444
  6666	Smith	55555	Chloe	6666
  4444	Jones	77777	Jason	4444
  6666	Smith	99999	Mary	6666

• Question: What is the join of Table D with Table E? Here the join variables are StudentData.StudentID and CourseData.StudentID.
  Creating a New Relationship
  1. Under Objects, select Tables.

  2. Click on Tools >> Relationships...

  3. Click on Show Tables... >> Select the tables you wish to add >> Click the Add Table Button.

  4. Select main menu Relationships >> Edit Relationships... >> Click Create New.

  5. Enter the following information:

     Table I.

     Drop Down Menu Caption	Information to Select
     Left Table Name	Name of Table Containing Foreign Key
     Left Column Name	Name of Foreign Key
     Right Table Name	Name of Table Containing Associated Primary Key
     Right Column Name	Name of Primary Key

  6. Check the box Inforce Referencial Integrity.
  7. Click on Create. A relationship line that connects the two tables will appear in the Relationships Window.

     The symbol 1 marks the primary key; the symbol ∞ marks the associated foreign key.

  8. Now select Tables in the list of objects and look at the Doctors table. Each primary key (DoctorID column) contains a drop down table that contains all patients for that doctor; each table contains all of the foreign keys that match the given primary key.

  Editing a Relationship
  1. Click on Relationship Icon

  2. Right click on relationship arrow

  3. Select Edit Relationship...

  4. Change the information in the Edit Relationship Dialog.

• The example database Patients.mdb contains the tables Patients and Doctors with a relationship connecting the DoctorID columns in the two tables.

• For practice, create a relationship using CruiseID, in the database Cruises.mdb.

Top   

L. Queries

• A query is information extracted from parts of one or more tables in a database and displayed in table format.

• Select Queries The input to a query is data from tables or other queries. The data is turned into information by applying criteria that mask out unwanted data and produce the result. The entire query can be saved so that the result can be re-created at at any time.

  Creating and Running Query

  1. Click on the Queries tab from the Database window.
  2. Choose the Create query in Design Viewnoption or click the New button and choose Design view.
  3. The Show Table dialog box appears.
  4. Choose the table(s) to be included in the query.
  5. Click the Add button to add the table(s) to the Query design. Click the Close button to remove this dialog box from the screen.
  6. Now add (double-click) the desired fields to the grid.
  7. If you want to restrict the rows in the output query, add expressions in the Criteria boxes.
     Criteria are written like they were for filtering. Here are some examples: >1, <2, ="M", <>"M", >=#1/1/2002# (Dates are delimited by #.), Not "Baja"
  8. To run the query, click on the Run button on the toolbar or Click on the Datasheet View button.
  9. If you need to return to Design View, just click the Design View button on the toolbar.

  Example

  Using the database, Cruises.mdb, answer the queries:

  1. Find all passengers sailing to Aruba. Show persons name, ship, destination, departure location, and state.
  2. Find all passengers sailing First Class. Show passenger name, class, and city and state. Sort by last name.
  3. Make up some queries of your own (and answer them).
   

  Queries with Summaries or Groupings

  • Total Queries A Total query is a special type of query that performs summary calculations on groups of data. All Total queries must include at least two fields - one field that's used to group the data, and another to provide values for calculating the summary information.

    Total queries can be run on any data that is organized into categories or groups. Usually fields having only a few different values are selected as grouping fields. For example, in the Cruises.mdb database, the table Cruises could be grouped by Company name, Ship, or Destination. For instance, if we wanted to know the average first class price for each company we would choose as our Group by field Company name and the other field would be FC price.

    How to Display the Total Row

    • Click the Total button (Sigma icon) on the toolbar from Design view, or
    • Choose the Totals command from the View menu.
    You can select a variety of grouping options by clicking on the drop-down button on the right side of each cell in the Total row. This row has a default value of Group By in each column. Although there are 12 options available from the drop-down list box that's displayed, we will work with only a few of these options: Group By, Sum, Avg, Min, Max, and Count.

    Using the Group By Option

    The Group By option allows the user to specify a field as a grouping field. The data will be organized and displayed into groups based on the values in that field. For example, you can group together all records that come from a specific cruise company, so that all "Gateway Cruises" are grouped together and all "Velo Cruise Line" cruises are grouped together, etc. Subtotals can be calculated in each group for any numeric fields. For example, we could calculate the average price of a first class cabin. So we would have the average price subtotal for "Gateway Cruises," for "Velo Cruise Line," and so forth.

    The Group By option should be specified for only one field, unless subgroupings are needed. For example, you could group by Company name and within that grouping it's possible to group all ships. Be careful that only those columns that are part of the grouping scheme contain the value Group By in the Group row. Group By is the default value.

    How to Calculate Subtotals

    1. Identify the field that contains the different categories for grouping and the field that contains the numeric data to be totaled. Set up a query containing only those two fields.
    2. Press the Total button on the toolbar, or
       Choose the Totals command from the View menu.
    3. Choose the total option (e.g., Sum, Avg, Count, etc.) from the Total drop-down list for the field that contains the numeric data to be totaled.
    4. Run the query.

  • Crosstab Queries A Crosstab query displays summarized data in a spreadsheet like format (in rows and columns). You can display sums, averages, counts, and other statistical information about data in a table. The records are grouped in two ways by values in two different fields, and then statistics are calculated where the two groups meet or cross - hence the name Crosstab query. For example, you can group student records by major and gender so that students' average ages can be calculated. Table J shows how this Crosstab query looks.

    Table J.

    	Male	Female
    Business	21.3	20.7
    Math	19.8	21.0
    Communications	20.9	20.8
    Psychology	20.2	19.5

    Row headings = major Column headings = gender Values = average age

    As you can see, there are three parts to a Crosstab query: Row Headings, Column Headings, and Values. A Crosstab query must have an entry for each of these. The Row Headings and Column Headings are descriptive labels that identify how the Values are grouped, and Values are summarized numeric data. In Table J, the Row Heading field is Major, the Column Heading field is Gender, and the Value field is Age. Several Row Headings can be used in a Crosstab, but only one Value and one Column are used. Also, criteria cannot be entered in Value fields. You can create a Crosstab query on mutiple tables if they can be joined.

    There are three examples of Crosstab queries in the Cruises1.mdb database. The one called qryAverage, computes the average first class price for each cruise company grouped by departure location. The qryAverage2 is the same as qryAverage except that it just gives the results for Departure Locations Miami and Los Angeles. The qryCount query counts the number of entries in each of the company-departure location groups. When doing a count, it doesn't matter what field you use in the Values column.

    Creating a Crosstab Query in Design View

    Creating a Crosstab query in Design view gives you more flexibility than the wizard. The most important thing to remember is to specify one or more Row Headings, only one Column Heading, and only one Value field. Here is what to do:

    1. Create a new query in Design view.
    2. Select the table(s) for the data source.
    3. Select Crosstab Query from the Query menu, or
       Click the drop-down arrow on the Query button and select Crosstab Query.
    4. Add the field for the Row Heading to the grid.
    5. Select Row Heading from the drop-down list in the Crosstab row, and keep the Group By selection entered in the Total row.
    6. Add the field for the Column Heading to the grid.
    7. Select Column Heading from the drop-down list in the Crosstab row, and keep the Group By selection entered in the Total row.
    8. Add the field for the Value to the grid.
    9. Select Value from the drop-down list in the Crosstab row.
    10. Select the type of function desired for the value (sum, count, and so forth) in the Total row.
    11. Specify a criteria if you want, on any Heading field (but not on the Value field).
    12. Run the query using the Run button on the toolbar, or
        Choose Run from the Query menu.

  • Parameter Queries Sometimes you want to select certain values from a table, but you don't want to choose the value ahead of time. You want the user of the database to enter the selected value interactively.

    To do this, enter the expression [Text of Prompt] in the Criteria row for the field for which you are selecting a value interactively. For example, suppose, in the Cruises database, we wanted to be able to enter a destination and get all cruise infromation associated with that destination. Someone else may want information for a different destination. The query qryDestination has [Enter a destination:] entered under Criteria in the Destination column.

  Top   

  M. Reports

  • The easiest way to design a report is to use the Report Wizard.

  • An access report can show detailed records and also summary information.
    Using the Report Wizard
    • Select Reports from the list of Objects on the Database Dialog.

    • Double click on Create report by using wizard.

    • This brings up Page 1 of the Report Wizard.
      1. Select the table or query that you want to use for the report.

      2. Move the fields that you want in the report to the right.

      3. Select Next.

      4. Move the fields that you wish to appear from Available Fields on the left to Selected Fields on the right.

      5. Click Next to move to Page 2.

    • Page 2 asks "Do you want to add any grouping levels?"
      1. Move any fields you want to use for grouping levels to the left.

      2. Click Next to move to Page 3.

    • Page 3 asks "What summary and sort order information do you want for detail records?"
      1. Enter sort information. You can enter sort information for up to four fields.

      2. Click the Summary Options button.

      3. Indicate what summary information you would like calculated.

      4. Choose to show detail and summary or summary only.

      5. Click Next to go to Page 4.

    • Page 4 asks "How would you like to layout your report?"
      1. Choose the layout and orientation. Stepped is a good layout, unless you have reason to choose another one.

      2. Click Next to move to Page 5.

    • Page 5 asks "What style would you like?".
      1. Choose the style.

      2. Click Next to move to Page 6.

    • Page 6 asks "What title do you want for your report?"
      1. Enter the title.

      2. Choose whether you want to preview the report or modify the design.

      3. Click the Finish button.

  • Right click on the report and select Report Design to modify the report.

  • Modify the fields on the Report Design until the report looks good to you.

  Top