ISTM 210 Phinney Access Terms

object view

displays finished object

design view

edit the object

layout view

easier to use

what is a technical term for a table?

relation

database tables store information for specific things, called _______

entities

record

collection of information you have for an entity, stored in the rows of tables

attribute

AKA fields, data stored for each database record, stored in the columns of tables

database table

collection of records that have all the same attributes

every field must contain what?

name and data type

short text (data type)

used to store most data that won't be treated like a number i.e. text, phone numbers, zip codes

limited to 255 characters

long text (data type)

used to store larger amounts of text

limited to 65536 characters

number (data type)

used to store positive and negative numbers

may contain decimal places

the number or currency data type must be used when...

you expect to perform calculations on the data in a field

date/time (data type)

used to store date or time information

can be formatted to display dates/times in different ways

currency (data type)

number data type that places $ in front of the number

auto number (data type)

number data type

access automatically inserts that value for the auto number field when a new record is added to a table

auto number can be set to add 1 to the last number used for the field, or it can assign a randomly generated number

in either case, autonumber will insert a unique value for each record added

yes/no (data type)

used to store data that has only two possible values such as yes or no, true/false, on/off, etc.

OLE object

used to store files created by other programs such as word or excel docs, picture/sound files

however, generally better to store this type of data as an attachment data type

hyperlink

used to store links to websites or other folders/files on your computer

attachment (data type)

used to store files for a database record; attachments can be virtually any type of file including pics, docs, sounds, and vids

calculated (data type)

used to create a field that is generated by a formula

often, calculated fields will refer to values in other fields for a record

lookup wizard

used to simplify data entry

a lookup-field will present the use a list of items to choose from when entering a new record

these values can come from another table in the database

input mask

used to set rules for how data will be entered for the field

e.g. users must enter 10 digits for a phone number

default value

used to have Access automatically set a starting value for a field when a new record is added to the table

validation rule/validation text

work together to have Access check values when they are entered into a table to make sure they fit a specific rule

e.g. use a validation rule to make sure all values entered into a field are greater than 10

((what is the difference between this and input mask??))

primary key

first field in a table that is used to uniquely identify each of the records on that table

foreign key

what a field is called when it is used to link two tables

normalization

the process of organizing tables and relationships between tables in a database

do this

1. to ensure each table contains data about very specific things

2. to limit redundant information

most common type of relationship

one-to-many

example of many-to-many relationship

products and orders

form

a window that displays the data in a table to the user; can also be used to edit and add records

query wizard v query design

wizard is easier, but gives you less control than design does

to create a criteria that returns those records with empty values, use what phrase?

IS NULL

why should you avoid spaces in Access?

although Access can understand supports spaces, many other databases don't, so transferring data becomes problematic

keyword LIKE in access

tells Access that we want to search within a field to find a specific value

to find only records with the word stove at the beginning, what do you type into criteria?

Like "stove*"

To find records that end with the word stove, use what criteria?

Like "*stove"

parameter queries

allow you to specify a criterion for the query result at the time that the query is executed

e.g. you want to create a query that displace a list of customers for a specific province, but you want query to be flexible enough that you can display a dif province each time you run it

On Criteria, type {What Province do you want to display?]

multi-tab queries

used to display results from fields on different tables

e.g. can be used to display all of the orders place by one customer

aggregate queries

used when you wish to calculate summary statistics

used to group query results into categories based on the values for a field and then perform calculations for each category

use totals icon!!

three types of options available in the Total drop-down menus

Group By: used when you wish to use the values for that field to categorize the records in the query results

Where: used to add criteria for the field

other options are used to perform a calculation on that field

why construct a calculated field?

to perform arithmetic or apply prebuilt formula on the values in our database tables

two parts to a calculated field

1. provide Access a name for the new calculated field

2. define the calculation to be performed

Crosstab queries

similar to PivotTables in Excel

used to create multi-dimensional view of data

we used a cross tab query to summarize the data in a query on two dimensions at the same time

e.g. we may want to create a query to list the amount of each product type that has been shipped by each of the shipping companies

(click crosstab in the design menu tab after making a normal query)

what are the two Group By fields in a crosstab query used for?

1. to create the column labels for the query results

2. one or more fields will be used to create row labels for the query results

also contains value field that will be used for the summary calculations

(???)

skipped chapter 14

lol

putting "yes" next to unique values in the property sheet ensures what?

there will not be any redundancies

criteria: is not null

no blank data will be shown in the query