ISTM 210 Access

Databases

Are large collections of data that are optimized for data storage

4 types of objects in Access

- Tables

- Queries

- Forms

- Reports

Tables

Are used to store the data in Access. It consists of columns, which store data fields, and rows, which store database records

Queries

Are used to find the specific data you need from the database. They can also be used to perform some basic calculations on numeric data

Forms

Present the data from a table in a separate window one record at a time. They are useful when entering new data into a table and when navigating through table records

Reports

Allow you to organize and present the data in your database. They are used to present the data in a meaningful way to aid in managerial decision-making generated from tables and queries

The object view displays...

The finished object while also adding new records to the table

The design view is used to...

Modify the fields of the table and modify the properties and attributes

The compact and repair database icon

Will analyze the database and reduce the size of the database file because when manipulating database objects, the size of your database can grow considerably

Another option instead of compact and repair database...

Compact on close, when saving you can use this in options which automatically analyzes a database file and reduces the size of the file every time the file is closed

The technical term for these tables is a...

Relation

Columns (fields)

Contain the attributes or the characteristics of what you are tracking

Records (rows)

Contain all the information you have about the individual thing you are tracking (entity)

Attribute

Data stored for each database record. Stored in the columns

How to create a table

- Create

- Table design

- The 1st column is the identifier

The 2nd column is the data type (that allows you to configure access to store specific types of Information or rules)

Primary key

Is used to keep each of the rows in a database table unique

Different types of data types

- Short text (limited 255 characters)

- Long text (unlimited)

- Number (for storing number calculations, if you have a zip code or phone number that would go in short text)

- Date/time

- Currency (formatting money)

- Autonumber (access makes it unique)

- Yes/no

- Ole objects (attaching a file, not as good as attachment)

- Hyperlink

- Calculated (calculation on other fields in the database)

- Lookup wizard (Links other tables to pull information from)

Different properties in a table

- Field size (goes more detailed than short or long text)

- Format (how it appears)

- Input masks (enforces a specific format like phone number)

- Caption (a note to the user)

- Default data (automatic starting value for a field)

- Validation rule/text (specific guideline like <5)

Foreign key

Is always a primary key from another table and it used to link 2 tables together

Normalization

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

We normalize databases to accomplish two important objectives

- To ensure that each table contains data about very specific things

- To limit duplicate or redundant information as much as possible

A one-to-one relationship occurs when...

A record from one table can only be associated with only one record from the other table

A one-to-many relationship occurs when...

A single record from one table could be associated with more than one record from the other table, but a record from the second table can only be associated with one record from the first table (most common)

Many-to-many relationships occur when...

A record from one table may be associated with more than one record from another table and a record from the second table may also be associated with more than one record from the first table

Before adding fields to a table....

- First create meaningful and descriptive names

- Avoid using spaces

- Only use letters and numbers

Access is different than other applications because...

Other application you save it all at once, and in access you have your work along the way

How to modify simply queries in the design view

- Sort from ascending or descending order (when doing multiple fields access will always start with the first one)

2 ways to create queries

- Query wizard: ask you questions and creates the query based on your answers. Simplifies the process but gives you less control

- Query design: Also known as query by example (what we do)

Action queries

Allows you to create new database tables or make changes to the records in existing ones

Select query

Is used to view the records selected by the query

Queries don't create a copy of the information from the tables, rather it is just...

Another "view" of the data in the table

Ways to use criteria when modifying queries

- Single values (you can search for numbers, text, or dates)

- Range of values (the valid operators are >,<,=,>=,<=)

- More than 1 criteria (using and which is when both criteria is met, or using or which only requires 1 or the other criteria to be met)

Exclusion query

To remove results (Not <100) by using the keyword Not

Empty value query

Using the keyword Null to return records that are blank

Wild card query

To search for specific words or characters (you use word to find all the records with that word, word for records that begin with that word, and word for records that end with that word)

The keyword Like tells access that...

We want to search within a field to find a specific value

Parameter query

allows you to specify a criterion for the query result at the time that the query is executed. You will use brackets in criteria along with what you want to ask [What province do you wish to display?]

Multi-table queries

Are used to display results from fields on different tables

Aggregate query

Is used when you wish to calculate summary statistics. They are used to group query results into categories based on the values from a field and then perform calculations for each category

Aggregate queries have 2 parts

- Categorical variable to group or classify the data

- Value fields used to perform summary statistic like calculations

Group by in aggregate query is...

Reserved for defining a field as a category variable

Where in a aggregate query is used to...

Specify criteria that will allow you to select specific records to be included in your calculations

Calculated fields allow us to...

Perform a calculation for every record in a query result

Calculated fields use...

Specific syntax. First you name the field, then let access know the calculation for that field (extended price:[quantity]*[price])

Crosstab queries

Are roughly analogous to a pivot table. The idea is extending an aggregate query that allows you to group the data in 1 field together according to categories and then do summary statistics on those categories in a 2 dimensional tabular format

In a crosstab query you can only have...

1 column heading and multiple row headings

Using value is a crosstab query is to...

Designate a field that will be used for our calculation

In a crosstab query the field with the...

Least amount of headings would be the column heading

Is not Null will...

Not display any blank records

When first generating a report it will show it in the...

Print preview screen

Other types of view in reports

- Design view (gives you lots of flexibility to modify data)

- Report view (is an electronic version of print view)

- Layout view (is a compromise between design and report view, you can modify spacing)

There are always 2 parts to a field in a report

The field itself and the label for the field. You want to modify both parts at the same time

Grouping in reports

Organizes information together to only display it once

When adding a group it will...

Automatically sort itself

When having groups you are always going to use the...

Stepped layout

Header section in reports

Are used to place things that you only want to display once or a few times

Report header and report footer in reports

Is at the top or bottom and is only displayed once

Page header and footer in reports

Only displays once on each page

Group headers in reports

Will only show once for groups (ex. only showing a customer once)

Detail in reports

Will display everything you have for those fields

Why do we export records from access?

Access is really designed for storing data and making queries but can not do data analysis as well as excel

Enforce Referential Integrity

A user will only be able to add a number that already exists in the relating table (creating a relationship)

Join property function in a relationship

Can choose 1,2, or 3. It's like a venn diagram to help show what isn't showing and what is

A set of all related records forms a...

File

Update anomaly

Is a data inconsistency that results from data redundancy and a partial update

Insert anomaly

Is partially empty records. Occurs when certain attributes cannot be inserted into a database without the presence of other attributes

Delete anomaly

Is the opposite of insert anomaly. It means you cannot delete data from a table without having to delete the entire record

What makes a good database?

- Each entity has their own table

- Normalized database

- Consistent data

- Redundancy is minimized or controlled

- Attributes appear multiple times with foreign keys

- Referential integrity (in a good database, you aren't allowed to refer to something that doesn't exists)

- Only shows information once unless it's a foreign key

Append

Means adding information at the end of something that already exists

Return function

For example, if you select return "1" it will give you the top one made for sorting from highest to lowest or in other way. Is normally on return "all" unless you change it