Access Chapter 2
one-to-one, one-to-many, many-to-many
3 different relationships Access provides for joining your data
Yes
Criteria for Yes/No data type. EX: enter Yes for a Discontinued filed, returns records where the check box is selected
Default Value
Property type: Enters automatically a predetermines value for a field each time a new record is added to the table. For EX: if most customers live in L.A. the default value for the City field could be set to L.A. to save data entry time and accuracy
result type
Property type: is used for calculated fields only. enters the format for the calculated field results
Allow Zero length
Property type: that allows entry of zero length text strings ("") in a Hyperlink, or Short or Long Text fields
format
Property type: that changes the way a field is displayed or printed but does not affect the stored value
Field Size
Property type: that determines the maximum characters of a text filed or the format of a number field
Caption
Property type: that enables an alternate name to be displayed other than the field name; alternate name appears in datasheets, forms, and reports
Indexed
Property type: that increases the efficiency of a search on the designated field
required
Property type: that indicated that a value for this field must be entered
expression
Property type: that is used for calculated fields only. enters the expression you want Access to evaluate and store
Validation rule
Property type: that requires data entered to conform to a specified rule
Input Mask
Property type: that simplifies data entry by providing literal characters that are typed for every entry, such as hyphens in a Social Security Number or slashes in a date. imposes data validation by ensuring that data entered conform to the mask
validation text
Property type: that specifies the error message that is displayed when the validation rule is violated
integer, Long Integer
a field with a number data type can be set to _________ to display the field contents as whole numbers from -32,768 to 32,768 or to ______ ________ for larger values
delimiter
a special character that surrounds a criterion's value that maybe needed when specifying the criteria for a query. type needed is determined by the field data type, Access will automatically enter for you for some data types, example: Access automatically adding quotations around text
freeze fields
allows you to keep a field viewable while you are scrolling through a table, do this by selectin the desired field(s) , right click, then choose option. if you want to remain save changes when you close the table, to undo right click and select proper undo function
Many-to-Many
artificially constructed relationship giving many matching records in each direction between tables. requires construction of a third table called a junction table. EX: database might have a table for employees and one for projects, several employees might be assigned to one project, but one employee might also be assigned to many different projects. find this relationship type when Access connects to databases using Oracle or other software
indexed property
automatically set to Yes (No Duplicates) when you set the primary key in Access. setting enables quick sorting in primary key order and quick retrieval based on the primary key. for non-primary key fields, may be beneficial to se the property to Yes (Duplicates OK)
constant
better to store starting dates with calculated fields so that the data can remain _________. for example, storing a birthdate in a calculated field will always keep the age accurate rather than storing the current age itself
best fit columns
can be done when a field name is cut off in Datasheet view, accomplished by adjusting the column width by positioning the pointer on the vertical border on the right side of the column, double-click border when pointer displays 2 headed arrow. can also click more in records group on home tab and adjust from there
caption property
can be set to create a label more readable than a field. displays at the top of a table or query column in datasheet view and when the field is used in a report or form. EX: field named ProductCostPerUnit could have this that makes it read Per Unit Product Cost, must use actual field name (ProductCostPerUnit) for any calculation though
datasheet view
can be used to add, edit and delete records, resembles Excel spreadsheet and displays data in a grid format. rows represent records (Data the correspond with their given fields/columns) and columns represent fields. indicates current record using gold border
date arithmetic
can be used to subtract one date from another to find out the number of days, months, or years that have lapsed between them. can also add or subtract a constant from a date
validation rule
checks that the data entered when the user exits the field. ensures that the correct type of data are entered or that the data do not violate the enforced properties
<=
comparison operator that only returns results less than or equal to the character that follows it
<
comparison operator that only returns results less than the character that follows it
=
comparison operator that returns results only equal to what follows it
>=
comparison operator that returns results only greater than or equal to the character that follows it
>
comparison operator that returns results only greater than the character that follows it
<>
comparison operator that returns results only not equal to the character that follows it
multivariable queries
contain two or more tables. enable you to take advantage of the relationships that have been set in your database. most likely needed will need to pull data from multiple tables to provide the answer you need when using queries in databases
data type
contained in every field that is a property that determines the type of data that can be entered and the operations that can be performed on that data. Access recognizes 12
show row
controls whether the field will be displayed in the query result bottom portion of the Query Design View
select queries
created through the Query Design tool. type of query that displays only the records that match criteria entered in Query Design view
# sign
criteria for date data type. EX: enter #2/2/2015# for a ShippedDate field, shows order shipped on Feb. 2, 2015
certain number (digits only)
criteria for numeric data type. EX: for a quantity field, if 5000 is entered, displays only number that match 5000 exactly
quotations
criteria for text data type. EX: "harry" will yield a display of only text that matches harry exactly for a FirstName field
Is Null
criteria that is used to find blank fields. EX: for an employee field in the customers table when the customer has not been assigned a sales representative
Yes/No
data type that assumes one of two values, such as Yes or No, True or False, or On or Off (also known as Boolean). For example, is a student on the dean's list: Yes or No EX: Yes
Number
data type that contains: a value that can be used in a calculation, such as the number of credits a course is worth. the contents are restricted to numbers, a decimal point, and a plus or minus sign. EX: 12
OLE Object
data type that contains: an object created by another application. include spreadsheets, pictures, sounds, graphics EX: JPG image
Date/Time
data type that contains: dates or times and enables the values to be used in date or time arithmetic. EX: 10/31/2016, 1:30:00 AM
long text
data type that contains: lengthy text or combinations of text and numbers such as several sentences or paragraphs; used to hold descriptive data. formerly Memo data type. EX: a description of product packaging
AutoNumber
data type that contains: special data type used to assign the next consecutive number each time you add a record. The value of this field is unique for each record in the file. EX: 1, 2, 3
short text
data type that contains: stores alphanumerical data (customer's name or address). can contain alphabetical characters, numbers, and/or special characters (apostrophe in O'Malley). social security numbers, telephone numbers, postal codes should be designated as text fields since not used in calculations and often contain special characters (hyphens/parentheses). can hold up to 255 characters. Formerly Text data type. EX: 2184 Walnut Street
Currency
data type that contains: use for fields that contain monetary values EX: $1,200
Lookup Wizard
data type that creates a field that enables you to choose a value from another table or from a list of values by using a list box or a combo box EX: Customers table with an AccountID field that looks up the Account ID from an Accounts Table
Attachment
data type that is used to store multiple images, spreadsheet files, Word documents, and other types of supported files EX: an Excel workbook
Calculated
data type that results of an expression that references one or more existing files EX: [IntRate] + 0.25
Hyperlink
data type that stores a web address (URL) or the path to a folder or file. can be clicked to retrieve a Web page or to launch a file stored locally. EX: http://www.keithmast.com
field properties
determine how the filed looks and behaves. includes field size, caption property, etc.
query sort order
determines the order of records in a query's Datasheet view. can change order of records by specifying in Design view. must have two fields sorted in the correct order in the Design field
table row
displays the data source bottom portion of the Query Design View
field row
displays the field name in bottom portion of the Query Design View
query
enables you to ask questions about the data stored in a database and then provides the answers to the questions by providing subsets or summaries of data
sort row
enables you to sort data in ascending or descending order bottom portion of the Query Design View
referential integrity
enforces rules in a database that are used to preserve relationship between tables when records are changed. Enforce_______ ____________ should be checked in most cases when the Edit Relationships box displays after creating a relationship. disables you from entering a foreign key value in a related tables unless the primary key value exists in the primary table. cannot delete a record in one table if it has related records
foreign key
field in one table that is also the primary key of another table. Customer ID is primary key in Customers table (uniquely identifies each customer) but is this aspect when it appears in a related table (Accounts table). can appear once in table where it is primary key but when it is this aspect it can appear multiple times in another table
simple query wizard
guides you through the query design process. helpful for creating basic queries that do not require criteria
store data in smallest parts
idea behind strategy is it increases flexibility and can more easily display order (alphabetizing) and can be sorted more easily. includes separating components of name (first and last) and components of address (zip, city, state, etc.)
asterisk
is wildcard that stands for any number of characters in the same position as the wildcard
linking data
keeps the data in the original Excel file and Access retrieves the data each time the database is opened
one-to-many relationship
most common type of relationship that is established when the primary key value in the primary table can match many of the foreign keys in the related table primary key table must have only one occurrence of each value. (each customer must have unique ID number in customers table) foreign key field in the second table may have repeating values (one customer may have many different account numbers)
field name
must be given when adding a new field in Design view to identify the data it holds. should be descriptive of the data and can be up to 64 characters in length, including letters numbers and spaces
cascade delete related records
option when you enforce referential integrity so that when the primary key is deleted in a primary table, Access will automatically delete all records in related tables that reference the primary key
Cascade Update Related fields
option when you enforce referential integrity that when the primary key is modified in a primary table, Access will automatically update all foreign key values in a related table
calculated field
produces a value form an expression or function that references one or more existing fields. EX: could store expression Principal X 0.01 to enter records under Monthly Interest field. frequently created with numeric data, and can create using date/time data (find person's age, days account has been open, etc.)
NOT logical operator
returns all records except the specified criteria
field size property
short text data in a field can hold up to 255 characters; however you can limit character by reducing the _____ ________ _________
wildcards
special characters that can represent one or more characters in a text value. EX: want to find a customer but not sure how to spell last name, know it starts with letters Sm, so can enter that.
importing/appending data
stores a copy of the Excel data in Access
related tables
tables that ate joined in a relationship using a common field. only tables that should be included in a multitable query
null
the term Access uses for a blank field. helps you find what is missing, or fields with blank records
data redundancy
the unnecessary storing of duplicate data in two or more tables. should be avoided so that errors do not result
name, data type
these two components should be kept the same between common fields (that are linked by join lines) appearing in varying tables. join line will not be allowed otherwise
one-to-one
two different tables use the same primary key. exactly one record exists in the second table for each record in the first table. sometimes security causes a table to be split into 2 related tables. (EX: anyone in company can look in employee table to find employee's office number, dept. assignment, telephone; but only a few people need to have access to network login password, salary, SSN, etc.) both tables would use same unique identifier to identify each employee
new blank record
use _____ ________ _________ (marked with asterisk) at end of table to add a new record in datasheet view
CamelCase notation
used by developers when typing field names. instead of spaces in multiword field names, use uppercase letters to distinguish the first letter of each new word. best to avoid spaces in field names b/c they can cause problems when creating other objects (queries, forms and reports) based on tables
query design view
used to create queries. divided into two parts: top portion displays the tables and the bottom portion displays the fields and the criteria. select only the fields you want arranged in the order that you want the resulting data displayed
BETWEEN operator
used to find data that fall inbetween a specific criteria range
Is Not Null
used to find fields with data. EX: for a ShipDate field; a value inserted indicated the order was shipped to the customer
criteria row
used to set the rule that determine which records will be selected, such as customers with account balances greater than $5,000
AND logical operator
used when queries with multiple criteria are required. when criteria are in same row of the query design grid, Access interprets the instructions using this operator. means that the query results will display only records that match all criteria (criteria appear in same row, such as balance and branch)
OR logical operator
used when you have multiple sets of criteria, but only need to match one. the query results will display records that match any of the specified criteria. enter expression in criteria row, separating criteria with this operator (criteria are entered in separate rows)
1. necessary 2. now, future 3. smallest 4. calculated fields 5. date 6. common
when creating database, after identifying tables, add necessary fields using these 6 guidelines (fill blanks): 1. Include the _____ data 2. Design for ____ and for the ____ 3. Store data in their ______ parts 4. Add ______ _______ to a table 5. Design to accommodate ______ arithmetic 6. Link tables using _____ fields
AutoNumber
when no primary key occurs, can naturally create a primary key field with this data type. it is a number that automatically increments each time a record is added
question mark
when this wild card is used it stands for single character in the same position as the wild card
exclamation mark
wild card used inside brackets to match any character not in the brackets
# sign
wild card used to match any single numeric character
brackets
wildcard that is used to match any single character within the wildcard
