Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
97 Cards in this Set
- Front
- Back
|
Microsoft Access
|
A relational database management application that is used to create and analyze a database
|
|
|
Database
|
A collection of related data
|
|
|
Field
|
SSN, Fname, Iname, Phone#
|
|
|
Record
|
Individual employees
|
|
|
Table
|
A collection of related records. Every record in a table contains the same fields in the same order.
|
|
|
Database
|
Employees, sales, stores, products, etc
|
|
|
Objects
|
Contain the basic elements of a database. 6 types; Tables, forms, queries, reports, macros, modules
|
|
|
Table Views:
|
Datasheet view- used to add,modify, delete, and view records. Design view: used to create and modify the fields in a table. Two planes, upper and lower, use F6 to switch between planes. Set field properties in the lower plane.
|
|
|
Primary Key
|
Uniquely id's each record
|
|
|
Field Size Propers
|
Set in Design view, anticipate the future.
|
|
|
Validation Rules
|
Used to avoid data entry errors by restricting what can be entered.
|
|
|
Sarbanes Oxley Act (SOX)
|
Protects public and shareholders agains fraudulent practices and accounting errors. Requires that all business records be saved for five years and made available to the SEC upon request
|
|
|
PNPI
|
Personal non public information
|
|
|
Other Table Design Considerations
|
Store data in the smallest part for greater flexibility (ex Name Fields). Avoid calculation fields, use in queriers or reports instead. Plan for Data Arithmetic, use date/time. Design Multiple Tables, reduces redundancy (Normalization).
|
|
|
CamelCase
|
Notation to use for field names- access does not like space!
|
|
|
Form
|
An interface that enables you to enter or modify record data. Field names and data values match the table it draws from. The same can be accomplished through the datasheet view of a table. but the form interface is easier for non technical users to use and understand
|
|
|
Query
|
Provides info that answers a question. the answer to a query is a dataset. Question asked is formed using criteria
|
|
|
Criteria***
|
Are the rules used to formulate queriers. Examples: ="John", >2000, <2000AND="Homer"
|
|
|
Two planes in design view:
|
Table and Design. F6 toggles between, Most basic is the Select Query, which searches tables and returns a dataset that matches the query parameters. Changes made to the dataset will reflect in the tables! Can specify Criteria to narrow the dataset.
|
|
|
Query Design - Criteria Specification: Currency
|
Dollar sign, decimal point
|
|
|
Query Design - Criteria Specification: Operands
|
Less than, greater than, equal to, not equal to
|
|
|
Query Design - Criteria Specification: Wildcards (asterisks)
|
searches for pattern that includes any number of characters in the position of the asterisk.
|
|
|
Query Design - Criteria Specification: Wildcards (question mark)
|
Searches for pattern that includes a single character in the positions of the question mark
|
|
|
Query Design - Criteria Specification:Null Values (IS NULL)
|
Finds only records that have no value
|
|
|
Query Design - Criteria Specification: Null Values (IS NOT NULL)
|
Excludes records that have no value
|
|
|
Query Design - Criteria Specification: OR
|
Finds records that can match one or more conditions
|
|
|
Report
|
Present and summarize database
|
|
|
Access file management
|
Access data is stored on the hard drive (or CD, DVD, flash memorts) while you are working on it - not in memory like word, excel and powerpoints. You cant always undo changes.
|
|
|
A filter
|
A filter lets you find a subset of meaningful data
|
|
|
Filter by selection
|
Selects only the records that match the pre selected criteria.
|
|
|
Filter by form
|
Permits selecting criteria from a drop down list, or applying multiple criteria. Toggle filter on/off
|
|
|
Inequity
|
equals, not equals, greater than, less than, greater than or equal to, lesser than or equal to.
|
|
|
Sort List
|
A sort lists those records in a specific sequence, such as alphabetically by last name (ascending vs descending). It does not matter if you sort first or filter first.
|
|
|
Excel
|
manageable data size, no need for relationships between data
|
|
|
Access
|
Large amounts of data, Need to create relationships between data, rely on external data-basses to analyze your data
|
|
|
Relational databases
|
The strength of access is the fact that it is a relational database. In a relational database, data is organized in related tables. The tables are related or linked to one another by a common field FK. Relational database tables, since you can combine and extract data between, tables. Helps eliminate redundant data. Excel tables are not relational.
|
|
|
Relationship Window
|
Primary key, foreign key
|
|
|
A foreign Key (relationship window)
|
is the primary key from one table that becomes stored in another table so that tables can be linked.
|
|
|
A primary key (relationship Key)
|
is the field ( or combo of fields) that makes each cord in a table unique
|
|
|
Referential Integrity
|
RI ensures that the data in a relational database maintains consistency when the data change. It is established when creating the relationship between two tables
|
|
|
Cardinality (three types)
|
Cardinality indicates the number of instances of an entity, described through relationships
|
|
|
Cardinality; One to One
|
One driver one race car
|
|
|
cardinality: One to Many
|
one car, many mechanics
|
|
|
Cardinality: Many to Many
|
Many mechanics work on many cars. Need associative table because databases cannot represent many to many relation
|
|
|
Most large companies separate their databases...
|
into front and back ends to protect data integrity. Front end objects needed for the user to interact with the data but not the tables where the record values reside.
|
|
|
Expressions are formulas based on.......
|
existing fields. Results in a new, calculated field. Used mostly in queries reports and forms. Can include..... Names of fields, controls of properties, or operators like (), *,etc.
|
|
|
Parts of Expressions
|
Constant, Value, (Tax Calculation)
|
|
|
Parts of Expressions: Constant
|
A named item whose value remains constant
|
|
|
Part of Expressions: Value
|
Literal values, like a number or word. Tax calculation.. [Price]*[QuantityOnHand]*.08. Use correct syntax! Use good name for new field so you remember what you name it. Use brackets around fields so access knows you're talking about field.
|
|
|
Expression Builder
|
Makes it easier for you
|
|
|
IIF Function
|
evaluates a condition, one if its true, one if its false. If [QuantityOnHand] used if you have things in stock (displays 'in stock' if quantity is greater than or equal to 1, and displays 'out of stock' if quantity is anything other than above).
|
|
|
Data Arithmetic
|
You can use calculated fields for dates. This is because access stores dates as a serial number so calculations is allowed no matter what format is entered.
|
|
|
Report Design Considerations
|
Information, look, grouping, calculations, distribution
|
|
|
Report Design Considerations: Information
|
What to include or exclude. (Identity Sources- tables, queries).
|
|
|
Report Design Considerations: Look
|
Overall how it looks
|
|
|
Report Design Considerations: Grouping
|
If any levels are needed
|
|
|
Report Design Considerations: calculations
|
If and what kind will be needed
|
|
|
Report Design Considerations: Distribution
|
Who is the report for? Who will see it?
|
|
|
Report Wizard
|
Allows use of multiple tables and queries. Do not have to include all fields. Can summarize or detail data. Chooses one or more tables and or queries for your reports, then chooses fields to include
|
|
|
Report Total
|
Generates a basic report based on one table or query. Shows all fields in the data source (the table or query).
|
|
|
Divisions of a report
|
Detail Section, Report Headers, Page Headers, Group Headers.
|
|
|
Divisions of a report: Detail section
|
Main report body. Data repeats once for each record
|
|
|
Divisions of a report: Report Headers
|
upper right hand corner. Printed only once
|
|
|
Divisions of a report: Page Headers
|
Upper left hand corner. Print at the top of every page
|
|
|
Divisions of a report: Group Headers
|
Appear at the start of the page.
|
|
|
Report controls
|
Allow you to position, display, format, and calculate report date. Manipulated in design view.
|
|
|
Two types of report controls
|
Bound- ties to an underlying source. Calculations made from an underlying table or query, records in the detail section.
Unbond- not tied to an underlying source. Report titles or explanatory text added to the report. |
|
|
Pivot Tables
|
Interactive table that shows how info fits together. It helps organize and compare data. It allows you to view patterns, trends, and relationships. Very important to management
|
|
|
Creating Pivot Table
|
Assemble source data in a single object. Can be based on a table, query, or form. Drag fields to the appropriate drop zone of design grid. Fields are selected and dragged from the pivot tables field list calculate
|
|
|
Column Field
|
Source data field assigned to a column
|
|
|
Row field
|
source data field assigned to a row
|
|
|
Page field
|
allows display of data for single or multiple items
|
|
|
Two ways to auto calc.
|
Click autocalc in the tools group or right click and choose autocalc
|
|
|
Pivot Charts
|
Allow a visual representation
|
|
|
Chart data
|
Two types***continuous data and discrete data. Continuous data can be broken in to smaller segments. Discrete data can not be broken into small increments
|
|
|
How to create a chart.
|
Right click pivot tables or query tab. Select pivot chart view from the view group, click views.
|
|
|
ActionQueries
|
Change underlying table data. Can be used to create new tables. Cannot be undone! Play a large role in maintaining database.
|
|
|
Types of Action Queries
|
Update queries (Change values in one or more records), Append Queries (Adds records from one table to another), make table queries (creates a new table of data), and Delete query (Deletes records from a table- if no criteria specified, deletes all).
|
|
|
Crosstab Query
|
Summarizes by two sets or unrelated facted**** Output resembles a pivottable. Crosstab is primarily for end users while PT is primary.
|
|
|
Parameter Queries
|
Info intentionally omitted by the query designer. Allows the end user to supply the criteria. Customizable data requested. More flexible than other query types. Considered a select query, doesn't change table data! Query designer can look the query design.
|
|
|
Creating Parameter Query
|
Created in design view. Prompt created for user as criteria. Prompt must be enclosed in brackets. Multiple fields can be used as a prompt. Can be used with logic and oerands.
|
|
|
Unmatched Query
|
Compares records in two related tables. Returns records found only in one table. Easiest way to create is with Query Wizard.
|
|
|
Find Duplicates Query
|
Finds duplicated data. Aids in data entry error detection. Easiest way to create is with query Wizard
|
|
|
Macros
|
Automates tasks. Two types; embedded and standalone.
Embedded- Single object or control. Standalone- more than one control |
|
|
Structured Query Language
|
Clauses can be added to statements to restrict or specify records. Defines a processes database queries. Industry standard query language.
|
|
|
Visual Basic for Applications
|
Programming Language. Allows you to create or customize applications. It is a host application. Resides in office applications
|
|
|
Visual Basic Editor****
|
workspace used for writing VBA procedures.
|
|
|
Comment***
|
Explains the purpose of the code. Does not run with code. Use apostrophe to "comment out" code
|
|
|
Standard Module
|
Stores procedures used by events.
|
|
|
What performs a action?
|
Sub procedure and another procedure (a&b)
|
|
|
Decisions Structures***
|
Performs a logical test to compare statements
|
|
|
Variables
|
Named storage location in memory. Variables scope limits accessibility.
|
|
|
Three different scopes for variables
|
Local- available toe specific procedure
Global- available to any procedure Module level- available to any procedure within the module |
|
|
You have to dimension your variables before you use them
|
DING DING DING
|
|
|
Naming and dimensioning variables
|
Dim strName as string. Dim intCost as int
|
|
|
Dim Variables before using!
|
Using appropriate data type
|
|
|
Name variables logically
|
Use a meaningful name
|
