Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/97

Click to flip

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