MIS TEST 3

Ace your homework & exams now with Quizwiz!

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.


Related study sets

Chapter 1: Nurse's Role in Health Assessment: Collecting and Analyzing Data PrepU

View Set

Respiratory Failure and Acute Respiratory Distress Syndrome

View Set

Prep U-Chap 51-Assessment and Management of Patients with Diabetes

View Set

Principles of Marketing Chapter 12 - Services and Nonprofit Organization Marketing

View Set

Chapter 5: Vulnerabilities and Impacts

View Set