IS 141-Access

Data Sheet

A simple view of data arranged in rows and column.

Index- Concept & How it's implemented

- It's similar to the index in the back of a book. - It's used on fields that are heavily used for searches. - They can be unique (prevent duplicate values) - Can be on single fields (within the field properties) - Can be based on a combination of fields.

6 Principles of Database Design

1) Choose good field names. 2) Break down your information. 3) Include all the details in one place. 4) Avoid duplicating information (normalizing). 5) Avoid redundant information. 6) Include an ID field (but don't name it "ID")

Calculated Field

1) Open the table by double-clicking it in the Navigation Pane. 2) Scroll horizontally to the rightmost column in the table, and click the Click to Add column heading. 3) In the list that appears, click Calculated Field, and then click the data type that you want for the result. 4) Access displays the Expression Builder. 5) Begin typing the calculation that you want for this field, for example: [Quantity] * [Unit Price]

Table

A database object that you use to store data about a particular subject, such as employees or products. A table consists of records and fields.

Relational Database

A database that uses more than one table. Tables are linked together by common data items, such as ID number, known as keys.

Filter

A feature that conditionally restricts the view of a data in a table. It is temporary and cannot be saved.

Primary Key

A field (or a combination of fields) that's unique for each record.

Foreign Key

A field in a relational table that matches the primary key column of another table.

Outer Joins

A join in which each matching record from two tables is combined into one record in the query's results, and at least one table contributes all of its records, even if the values in the joined field don't match those in the other table.

Column

A location within a database table that stores a particular type of data. It is also the visual representation of a field in a datasheet and, in an Access database, the query design grid or the filter design grid.

OR

A logical operator that returns true, false, or null. Example: Expr1 Or Expr2 Will return true if either expression is true or if both are false then false..

NOT

A logical operator that returns true, false, or null. Returns True when Expr is not true (b/c a double negative) Not Expr

And

A logical operator that returns true, false, or null. Returns True when Expr1 and Expr2 are true.; Expr1 And Expr2

Multi-table Query

A query made up of more than one table

Function

A query that takes input parameters and returns a result like a stored procedure. Types: scalar (multistatement; returns one value), inline (one statement; an updateable table value), and table (multistatement; table value).

Action Query

Action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries: delete, update, append, and make-table. Identified by "!"

Aggregate Function

Aggregate functions perform a calculation on a column of data and return a single value. Access provides a variety of aggregate functions, including Sum, Count, Avg (for computing averages), Min and Max

Form

An access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.

Report

An access database object that you can print containing information that is formatted and organized according to your specifications. Examples of reports are sales summaries, phone lists, and mailing labels.

Expression

Anything that returns a value. Can be a constant, field, function, or a combination of these with operators etc.

Orphaned Record

As to orphan records, those are records on a TableA that have no corresponding entry on a related TableB. Suppose you have an Orders table and a Customers table. The primary key on Customers is CustomerID, and the Orders table includes CustomerID as a foreign key. Suppose a customer made several orders, you then deleted that customer from the Customers table, but you do not have cascading deletes turned on. That customers orders would still be in the Orders table, but they are "orphaned", because there is now no corresponding record in Customers.

Group by Operator

Combines records with identical values in the specified field list into a single record. "Total: Group By"

Data Redundancy

Data in a database which is needlessly duplicated

AutoNumber as Datatype- Advantages & Disadvantages

Disadvantages: Sometimes Access skips a number. This skipping could happen when several people are using a database at once, or if you start adding a new record, in which case Access never reuses that AutoNumber value of 401, then you can't safely assume that there are already 400 records in the table, the actual number is probably less. Additionally, they give a clue about the number of records in a table. You may not want a customer to know that your brand new food and crafts company doesn't have very many customers. You can fix this by fooling access into generating AutoNumber values starting at a specific minimum. This

Ampersand as a an Operator

If you have text information, then you obviously can't use addition, subtraction, and other mathematical operations. However, you can join text together. You can, for instance, link several fields of address information together and show them all in one field, conserving space (and possibly making it easier to export the information to another program). To join text, you use the ampersand (&) operator. For example, here's how to create a FullName field that draws information from the FirstName and LastName fields: FullName: [FirstName] & [LastName] This expression looks reasonable enough, but it's actually got a flaw. Since you haven't added any spaces, the first and last name end up crammed together, like this: BenJenks. A better approach is to join together three pieces of text: the first name, a space, and the last name. Here's the revised expression: FullName: [FirstName] & " " & [LastName] This produces values like Ben Jenks. You can also swap the order and add a comma, if you prefer to have the last name first (like Jenks, Ben) for better sorting: FullName: [LastName] & ", " & [FirstName]

Is Null

IsNull function returns TRUE if the expression is a null value. Otherwise, it returns FALSE. Field: Expr1: IsNull([Description])

Entity Integrity

It ensures that there are no duplicate records within the table and that the field that identifies each record within the table is unique and never null. Entity Integrity ensures two properties for primary keys: 1) The primary key for a row is unique; it does not match the primary key of any other row in the table. 2) The primary key is not null, no component of the primary key may be set to null.

Validation Rule

It limits or controls what a user can enter in a table field or control. A data type is a good example of this as it won't let you enter letters in a currency text box.

Query

It's similar to a filter, but it provides more advanced tools to ask questions of the database. It provides the results in table format and can be saved for later use. "A request for the database to display selected records and generally selects a limited number of data fields, then constrains the records to a set of criteria" Things it can do: *Can be used over and over again - Calculations - Summarize information such as averages, totals, general counts - Combine multiple tables to look as one - Can be used to apply changes such a updates and deletes. - Can create new tables.

Calculated Column

Like a calculated field, but the whole column.

Comparison Operators

List of Operators: = : Equal to > : Greater than < : Less than Between : Inclusive range <= : Less than or Equal >= : Greater than or Equal <> : Not Equal LIKE : Pattern matching ie. LIKE "Cr*"

String Literal

Represents a sequence of characters that together form a null-terminated string. The characters must be enclosed between double quotation marks.

Is Null()

Returns "TRUE" if the expression is a null value, otherwise it returns FALSE.

Cascade Delete

Selecting this option allows you delete a parent data but this will then delete any children data that come from this source.

Cascade Update

Selecting this option forces every change you make in the parent data to flow through to the "child" data.

Data Type

Text: Printable Characters up to 255 Long Text (Memo): Large amounts of unformatted text. Up to 65,536 characters Number: Variety of numbers to include integers, decimal and negative values Currency: A "number" type specifically designed to support money values. Includes formatting Date/Time: A calendar date or time (or both) Yes/No: It has only one of two values: "Yes" and "No"

Null Value

The value that results from not entering a value for a field; a null value is not permitted for a primary key.

Query Parameter

This has the database ask for the criteria each time you run the query. For example in the "criteria" input you put [Enter state:] and when you run the query it will ask for a state you put "CA" and all enters that the state matches "CA" will show up.

Design View

View used to create a table or to modify the structure of the table

Referential Integrity

When creating a relationship between two or more tables it's an option you can select. It ensures that the related data is always consistent. It ensures that everything you refer to actually exists elsewhere. Make information consistent.

How to use LIKE Operator

You can use the like operator to find values in a field that match the pattern you specify. For pattern, you can specify the complete value (EX: Like "Smith"), or you can use the wildcard characters to find a range of values (EX: Like "Sm*) In an expression, you can use the like operator to compare a field value to a string expression. For example, if you enter Like "C*" in an SQL query, the query returns all field values beginning with the letter C.

IIF()

You use IIf to determine if another expression is true or false. If the expression is true, IIf returns one value; if it is false, IIf returns another. You specify the values IIf returns. IIf ( expr , truepart , falsepart ) The IIf function syntax has these arguments: expr: Required. Expression you want to evaluate. true part: Required. Value or expression returned if expr is True. false part: Required. Value or expression returned if expr is False. ie. =IIf([CountryRegion]="Italy", "Italian", "Some other language")

Logical Operators

You use the logical operators to combine two Boolean values and return a true, false, or null result. Logical operators are also referred to as Boolean operators. Examples And; Returns True when Expr1 and Expr2 are true.; Expr1 And Expr2 Or; Returns True when either Expr1 or Expr2 is true.; Expr1 Or Expr2 Eqv; Returns True when both Expr1 and Expr2 are true, or when both Expr1 and Expr2 are false.; Expr1 Eqv Expr2 Xor; Returns True when either Expr1 is true or Expr2 is true, but not both.; Expr1 Xor Expr2

Meta Data

data about data