Access Review

When to use Access

1.) Anticipate many people working in the database and you want robust options that safely handle updates to your data, such as record locking and conflict resolution. 2.) Anticipate the need to add more tables to a data set that originated as a flat or non relational table. 3.) Want to run complex queries. 4.) Want to produce a variety of reports or mailing labels.

When to use Excel

1.) Require a flat or non relational view of your data instead of a relational database that uses multiple tables, and when your data is mostly numeric. 2.) Frequently run calculations and statistical comparisons on your data. 3.) Want to use Pivot Table reports to view hierarchical data in a compact and flexible layout. 4.) Plan to create charts regularly and want to use the new charting formats that are available in Office Excel 2007. 5.) Want to emphasize your data by using conditional formatting icons, data bars, and color scales. 6.)Want to perform sophisticated what-if analysis operations on your data, such as statistical, engineering, and regression analysis. 7.) Want to keep track of items in a simple list, either for personal use or for limited collaboration purposes.

Calculated Field

A numeric or date field that derives its data from calculation of other fields. The data are not entered into a calculated field by the user.

Database schema

The database schema of a database system is its structure described in a formal language supported by the database management system (DBMS). The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases).

Relationship

in the context of databases, is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationships allow relational databases to split and store data in different tables, while linking disparate data items.

Foreign Key

is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.

Referential integrity

is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key.

Primary Key

is a special relational database table column (or combination of columns) designated to uniquely identify all table records. A primary key's main features are: It must contain a unique value for each row of data. It cannot contain null values.

One to many relationship

is created if only one of the related fields is a primary key or has a. A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.