MIS TEST 3
Refreshing a Pivot Table
-You CANNOT change data directly in a pivot table, you must edit the data in the source on which the Pivot Table was created
Creating Pivot Tables
2 Musts: --Specify where to find the data for the Pivot table: Table, range (in existing workbook), or an external source. --Specify where the Pivot Table is to be located: In a new or existing worksheet (If in an existing worksheet also specify what cell the upper left hand corner of the table should be)
Creating a Pivot Chart
A Pivot Chart and its associated Pivot Tables are linked; when you modify one, the other also changes.
Field
A characteristic or attribute of interest (specific content)
Database
A collection of data centrally organized in storage to serve many applications efficiently
Table
A collection of related records
Primary Key
A field that uniquely identifies each record in a table
Reports
A formatted printout or screen display of the contents of one or more tables or queries in a database.
Record
A group of related records
AND OR
And: SAME LINE Or: DIFF LINE
Queries (Dates)
Appropriate format is #mm/dd/yyyy#
Operators
BETWEEN: includes the beginning and the end value EX: Between #1/1/1965# and #12/31/1972#
Report View
CANT change anything, how the report looks in entirety
Design View
Can change what gets displayed/entered on a form Can add controls to a form
Design View (Property Sheet)
Can further describe, validate fields. For example, can limit values entered into fields, text box width, calculated fields
Form View
Cannot make design changes CAN add, change, delete records but CANNOT make design changes
Whats the difference between database and spreadsheet?
Content in a database saves automatically
Forms
Graphical representation of tables Friendlier user interface Can add/delete/update records
Pivot Tables
Has the ability to "Pivot" the tables, or arrange, hide, and display different category fields.
Operators
IN: List of value match EX: IN("Baylor","texas christian")
Operators
IS NULL: empty
Subtotals
If data is in a table, you must convert it to a range.
Creating Pivot Tables (Formatting)
If wishing to format a pivot table, use the PivotTable Styles gallery
Nested Subtotals
If you're creating a nested subtotal, you must sort by what you're totaling. EX: "Show how much was spend on cars and maintenance by YEAR and MAKE. --Sort by YEAR and then MAKE in ascending order
Design View
More detailed structure of report
WILDCARDS
Must use "Like" and location *B would mean 'ends with B' B* starts with b *B* b anywhere in the text ? 1 Character L? = L0 # 1 number
Q: Can there be quotes around number
NO
Operators
NOT: to specify not to return a certain value or name
Grouping
Pulling data from another worksheet: sheetName!CellRange Worksheet Range: 'worksheetRange'!CellRange [Workbook Name] 'WorksheetName'!CellRange
ACCESS (RDBMS)
Relational Database Management System
Crosstab
Row heading (can have multiple) Column heading (can only have 1) Value (can only have one)
Forms
Some form of design changes require you be in Design View, which gives you a more detailed view of the form's structure
Pivot Table
Source --> Pivot Table (effected by source) --> Pivot Chart (not effected by source, only table)
Query
Start in design view --> Tables --> Fields --> Records --> Sort --> Hide --> Run --> Save
Datasheet View
The ONLY place you can enter data
Queries (Text)
Use single or double quotes
ACCESS
When data is given units it becomes information
Grouping (Source File)
Where you're getting the information from
Grouping (Destination File)
Where you're placing the data
Boolean Field
Yes/no, T/F
Filtering a Pivot Table
You can filter a pivot table by: --Row Label Filter --Column Label Filter --Slicer
Operators
AND: when you have two conditions --SAME LINE
Grouping (Hyperlink)
Linking out to something external
Layout View
Looks like preview but YOU CAN change formatting
Q: We can enter data from either view
FALSE
Creating Pivot Tables (Adding Fields)
Fields that contain GROUP VALUE are typically CATEGORY FIELDS.
Creating Pivot Tables (Adding Fields)
Fields that contain SUMMARY DATA are VALUE FIELDS.
Operators
LIKE: uses pattern matching and wildcards EX: Like ("*Texas*")
Subtotals
Level 1: grand total only Level 2: grand total + yearly total Level 3: Grand total + yearly total + individual records In other words, it gets more specific the higher the level.
Operators
OR: either one or two conditions must be satisfied --DIFFERENT LINES
Creating Pivot Tables (Adding Fields)
Pivot Table Fields pane is divided into two sections: --Upper section (fields) --Lower section (layout) which contains: Filters, Rows [contain text or nonnumeric data], Columns, and Values [typically contain numeric data and by default are summarized using the SUM function]
Refreshing a Pivot Table
Pivot Tables are NOT updated automatically when the source data for the Pivot Table is updated, you must REFRESH the report to reflect the revised calculations
Pivot Table
Pivot tables help organize data by summarizing data into categories using functions.
