Week 1 - Thought of the Day II - 04/27/2010

Class – Please explore MS Access and define each one of these components, to help us prepare for the upcoming Microsoft Access assignment. Provide examples of usage of each listed component.

You will thank me later!

* Tables

• Field specifications

• Keys

• Relationships between tables

• Forms

• Sorting and filtering

• Reports


Tables - This is where the database stores the records. For example: A database could contain all of the information for students at a specific school.

Field Specifications - I think the field specifications are the parameters, characteristics and constraints for a specific field. For example: a field in the above example could contain a student id. That student id could be made to only allow numbers six digits long.

Keys - Primary keys are a unique identifier in a table to distinguish each record. Foreign keys are keys that help link tables. For example: a student id could be a primary key, that is linked to the student in a class

Relationships between tables - Common fields between tables can be linked. For example: the student id and a student in a class from the example above.

Forms - You can place controls on a form to make accessing the data in the database a lot easier. For example: Instead of having to directly typing in the students info into the database, you could create a form that is a lot more user friendly

Sorting and Filtering - Sorting orders the data, and filtering returns only a subset of the data. For example: Searching a school and only filtering out the 7th graders

Reports - Reports can be customized to print out specific data from the database. For example: you could set a report to produce a students grades from all of their classes.

There are 2 types of keys that you want to make sure you understand very well: Primary keys, and Foreign keys.

Primary keys are unique identifiers in a table. Like I mentioned in my example, you could have a table of the entire school with all of their information, and you could set the Student ID as the primary key because the student ID should always be unique:
Student ID First name Last name address phone
9875 Mike Jones fake street 123-555-8954
8465 Nancy Magana made up 555-555-5555

A foreign key ties tables together to show a relationship. This makes it easier for table management. For example: If you had another table that was for a particular class, you could tie the student IDs together so you don't have to have all of the students information in the class table:

Student ID Grade
9875 A
8465 A

Obviously this 2nd table is a lot smaller, and since you tied the student IDs together, you could be looking at the 2nd table, and pull the student information in the first table because you tied them together with a foreign key. If the two tables weren't tied together, you would have no idea which student the student ID in the 2nd table was referencing.

-Mike Jones

MS Access Tables- are grid tables that can define things any in quantity such as location, cost, personnel, amount, etc. The table consists of columns, rows and cells. The columns are the vertical part of the table. The column on the first segment is the definition of the sub items directly beneath it throughout the table. This row can be frozen as you scroll through the table. The rows are the horizontal segments of the table which is a sub item of the column. Each cell in the rows are definition of the items in the column. The cells are the points in the table where the rows and columns intersect. These cells can be formatted into dollar amount, calendar date, time, and quantity.

Field Specification The field specification determines the size of the element, if the field can be indexed, and what format to use. According to Microsoft website there are ten different types of information that can be entered in this section. This information can be attachments, auto-number, currency, date/time, hyperlink, memo, number, Object linking and embedding (OLE), text, and yes/no Boolean values.

Keys – All databases have primary keys. Each table has only one primary key. These keys are used to define specific records. These keys are unique and can be used from one table to another to create a relationship creating a relation database. Keys from one table to another are called a foreign key. Foreign keys do not have unique value like primary keys.

Relationships between tables –Relationships between databases reduces the event of duplicate information in multiple tables. There are three types of tables utilized in MS Access: One to one, one to many, and many to many. One to one relationships have one matching row in table A and in table B. Both entries must have primary keys. The primary key side of a one-to-one relationship is denoted by a key symbol. The foreign key side is also denoted by a key symbol. In the Many to Many relationships, table A matches multiple entries in table B. A third table, junction table, primary key is made from the foreign keys of both tables. The most common table relationship used is the one to many relationships. Table A has several matching entries, but table B has only one.

Forms There are three types of forms in Microsoft access to include Data entry forms, switchboards, and custom dialog boxes. Data entry forms are used to large volumes of records with a top to bottom format. These forms can be sorted by such characteristics as date, time, amount, alphabetical, and numerical. Switchboard forms are forms that use hyperlinks, images, labels or buttons to point to an alternative database or an Object link or embedded file. This switch is usually in the form of an application menu.

Sorting and Filtering Sorting records can be accomplished through the fields associated with the cells. Sorting can be done by one field or multiple fields. Sorting can be combined with filtering and grouping. Grouping is combining elements with similar characteristics such as location, age, amount and yes/no Boolean

Reports You can query MS Access database for varies reports based on unique information within your database. Reports can be generated based on the types of forms, overdue reports, pending request reports, date range, and various other expressions.

Ricky "Bo" Pierce
PST time zone

Donna M. Hoffman, OCP
University Of Phoenix
(512) 517-1060

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License