MIS final exam

Computer Concepts Criteria used to separate the generations of computers

1st generation-vacuum tubes 2nd generation-transistors 3rd generation-integrated circuits 4th generation-microprocessor

Computer Concepts ASCII

2^8 - 256 different combinations

Computer Concepts Byte

8 bits = 1 byte. 1 byte represents one character

Excel Average function

Calculates the average value of the range

Excel XIRR function

Calculates the internal rate of return for a series of cash flows made at specified dates

Excel IRR function

Calculates the internal rate of return for an investment

Excel XNPV function

Calculates the net present value of a series of cash flows at specified dates

Computer Concepts Criteria used to categorize computers

Category Physical size Number of simultaneously connected users General price range

Charts Category names

Category names become axis labels

Access When to choose Access vs Excel

Choose Access: relational database is required(multiple tables), large amounts of data, relying on external databases, maintain constant connectivity to external databases, more than 1 person working on database at same time Choose Excel: flat database is all that's needed, run primarily calculations

OR logical operator

EX. A record is selected if the BirthDate field value is less than or equal to 12/31/1956 or if the reason field value is influenza or if both conditions are met

AND logical operator

EX. Shows only the records for patients who were born on or before 12/31/1956 and who have visited the clinic because of influenza

Access Using Autofilter

Enables you to quickly sort and display field values in various ways. Click the arrow on a column heading to display the Autofilter menu.

Excel How excel handles dates

Even though you enter a date as text, Excel stores the date as a number equal to the number of days between the specified date and January 0, 1900.

Excel Building formulas

Every formula begins with an equal sign followed by an expression describing the operation that returns the value.

Access Creating a one-to-many relationship

Exists between two tables when one record in the first table matches zero, one, or many records in the second table, and one record in the second table matches at most one record in the first table.

Computer Concepts Parity bit

Extra bit added to ensure that systems are odd or even schemes (used for error checking)

Pivot Tables Fields and records

Fields are in columns Records are in rows

Pivot Tables Field

Fields that contain summary data are Values fields, and fields in that group the values in the PivotTable are Category fields.

Access Referential integrity

Set of rules that ensures that data stored in related tables remain consistent as the data are updated. Also called validation

Access Datasheet view

Shows the tables contents as a datasheet

Word Purpose of and creating columns

*Columns give greater flexibility to align articles, graphics, and advertisements in newspapers and magazines. *A reader's eye can scan narrow columns more quickly than lines the width of a full page. -Optimum range is 5-10 words across *Use columns when you want the reader to read top to bottom; use tables when you want the reader to read left to right.

Computer Concepts Unicode

16 bits; currently assigns 260,319 characters. Americans still use this

Office and Windows Explorer Files, folders, and paths

*File - set of instructions or data *Folder - used to organize files and subfolders *Path - the notation that indicates a file's location

Word Ways to create a table

*Specify a size -Using tool on Ribbon under Insert tab -Using dialog box *Drawing a table *Creating a table from existing text *Using Quick Tables

Pivot Tables Steps in creating a pivot table

-Familiarize yourself with the database; know what the fields and field names are -Visualize what you want the pivot table to look like -Place your active cell somewhere in the data and click on the Pivot Table icon on the Insert Ribbon -Under Pivot Table Options/Display, click on Classic Pivot Table Layout -Click and drag field names to the page, row, column or data areas

Access Query types

-Select - basic -Nested - building a query from a query rather than a table -Advanced - using several comparison operators to expand the types of criteria -Crosstab - used for evaluating trends over a period of time by placing data into a 2-dimensional table

Excel Relative reference

A cell reference that is interpreted in relation to the location of the cell containing the formula.

Excel Absolute reference

A cell reference that remains fixed when the cell formula is copied to a new location. It includes a $ in front of the column letter and row number.

Access Table

A collection of fields that describes a person, place, object, event, or idea

Access Definition of database

A collection of related tables

Computer Concepts Von Neumann's definition

A computer is a data processing device that allows for input of data, processes that data, stores data and information

Charts Data series

A data series contains the actual values that are plotted or displayed on the chart

Access Definition of DBMS

A database management system (DBMS) is a software program that lets you create databases and then manipulate the data they contain

Access Form

A database object you use to enter, edit, and view records in a database

A calculated field

A field that displays the results of an expression

Access Primary key

A field, or a collection of fields, whose values uniquely identify each record in a table. No two records can contain the same value for the primary key field

Access Report

A formatted printout (or screen display) of the contents of one or more tables (or queries) in a database

Excel Ranges

A group of cells in a rectangular block is called a cell range. If the blocks are not connected (two separate ranges selected), it is a nonadjacent range

Pivot Tables Pivot chart

A pivot chart is a graphical representation of the data in a pivot table. The pivot chart is linked to the PivotTable

Pivot Tables Purpose of a pivot table

A pivot table is an interactive table used to group or summarize either a range of data or an Excel table into a concise, tabular format for reporting and analysis

Access Importing data

A process that allows you to copy the data from a source without having to open the file source. External data tab, Import and link group, click the button that is where you will be importing from

Access Query

A question you ask about the data stored in a database. In response to a query, Access displays the specific records and fields that answer your question

Excel Worksheet

A worksheet contains a grid of rows and columns into which you can enter text, numbers, dates, and formulas, and display charts

Excel Sum function

Adds the values in the range

Excel Fill handle

After you select a range, a fill handle appears at the bottom right of the selection. Drag the fill handle to adjacent cells or ranges, Autofill copies the content and formats from the original cell or range into the others

Word Alt shortcut key

Alt key also used to access shortcuts When pressed puts shortcut letters over commands

Charts Embedded chart

An embedded chart is an object in a worksheet

Excel Financial functions

Analyze information for business and finance. Includes FV, PV, NPV, PMT, IPMT, PPMT, SLN, SYD, DDB, RATE, NPER, IRR, CUMIPMT, CUMPRINC, XNPV, XIRR

Excel Date & Time functions

Analyze or create date and time values and time intervals. Includes today (displays the current date in a worksheet), and now (returns the current date and time).

Excel Auditing tools

Auditing tools are found: formulas tab, formula auditing group, error checking button arrow, then your options will appear. Auditing tools include tracing an error, evaluating a formula, and using the watch window

Excel Autofill

Autofill provides a quick way to enter content and formatting in cells based on existing entries in adjacent cells

Excel Goal seek

Automates the trial-and-error process by allowing you to specify a value for a calculated item, which Excel uses to determine the input value needed to reach that goal.

Office 2013 and Windows Explorer Advantages of XML

Better security Reduces chance of file corruption Reduces file size (up to 75%) Facilitates data sharing across data storage and retrieval systems Supports Unicode

Excel Purposes of excel

Business documents - Financial statements, Invoices, Expense reports, Earnings statements • Personal documents - budgets, Catalogs of collections, Exercise logs, Checkbook reconciliations • Scientific data - Experimental observations, Models, Medical charts

Access Creating a report

Click the create tab, in the reports group, click the report button. The report tool creates a simple report

Access Creating a new table (in datasheet view)

Click the create tab. In the tables view, click the table button. Rename or accept the default ID primary key field. In the add & delete group on the fields tab, click the button for the type of field you want to add or click the click to add column heading and fill out the necessary fields. Click the X or save button, name the table an click ok

Charts Best uses for different chart types

Column-compares values from different categories. values are indicated by the height of the columns Line-compares values from different categories. Values are indicated by the height if the lines. Often used to show trends and changes over time. Pie-compares relative values of different categories to the whole. values are indicated by the areas of the pie slices Bar-compares values from different categories. values are indicated by the length of the bars Area-compares values from different categories. similar to the line chart except that areas under the lines contain a fill color X Y (Scatter)-shows the patterns or relationship between two or more sets of values. often used in scientific studies and statistical analyses Stock-displays stock market data, including the high, low, opening, and closing prices of a stock Surface-compares three sets of values in a three-dimensional chart Radar-compares a collection of values from several different data sets Combo-combines two or more chart types to make the data easy to visualize, especially when the data is widely varied

Access Compact and repair a database

Compacting a database rearranges the data and objects in a database to decrease its file size, thereby making more storage space available and enhancing the performance of the database. Make sure the database is open, click the file tab to display the info screen in the backstage view, click the compact & repair database button

Excel Counta function

Counts how many cells are not empty in ranges value1, value2, and so forth, or how many numbers are listed within value1, value2, etc

Excel Count function

Counts how many in a range contain numbers, where value1, value2, and so forth are text, numbers, or cell refrences; only value1 is required

Access Fields

Custom fields - those that a created in the query, but are not in the table Calculated field - custom fields that contain the results of a formula or function

Access Data dictionary

Data dictionary - document that contains a list of tables that are designed and maintained for the business

Charts Data point

Data point: each value in the worksheet

Access Deleting a field

Datasheet view: Click anywhere in the column for the field you want to delete. In the add & delete group on the fields tab click the delete button Design view: click the field name box for the field you want to delete, in the tools group on the design tab, click the delete rows button

Charts Date series names

Date series names become the legend

Access Design view

Design view allows you to define or modify a table structure or the properties of the fields in a table

Excel Median function

Determines the middle value in the range

Excel Max function

Displays the maximum value in the range

Excel Min function

Displays the minimum value in the range

Pivot Tables Filtering within a PivotTable

Filtering a field lets you focus on a subset of items in that field. You can drag one or more fields to the Filter area of the PivotTable field pane to change what values are displayed in the PivotTable. You can also use the Filter menu, which you open by clicking the Row Labels filter button or the Column Labels filter button. You then check or uncheck items to show or hide them, respectively, in the PivotTable.

Excel Lookup functions

Find values in tables of data and insert them in another location in the worksheet such as cells or in formulas. Includes VLOOKUP (retrieves the return value from a vertical lookup table).

Word Moving around a table

Forward - Tab key Backward - Shift + Tab First cell in table - Alt + Home Last cell in table - Alt + End First cell in column - Alt + PageDn Last cell in column - Alt + PageUp To create new paragraph in cell - Enter To move to a set tab stop in a cell - Ctrl + Tab

Access FV function

Future value (FV) Calculates the future value of an annuity Rate, nper, pmt, pv, type

Excel Moving around

Home- to column A of current worksheet Ctrl + Home- cell A1 Enter- down one row Shift + Enter- up one row Tab- one column to the right Shift + Tab- one column to the left

Excel NPV function

If the future payments are not equal, you must use the NPV function to determine what would be a fair exchange. Calculates the value of those payments in today's dollars based on your chosen rate of return

Excel #####; scientific notation

If the number/outcome of a formula exceeds its cell size, you will see #### rather than a number. This can be changed by increasing the column width

Access IIF function

Immediate IF (works like IF function in Excel) expression = logical test Usually a field name followed by a comparison operator or text enclosed in quotation marks Truepart = defines the output of the function if logical test is true Falsepart = defines the output of the function if the logical test is false

Access Adding new fields

In datasheet view, you can use the options in the add & delete group on the fields tab to add fields to your table. You can also use the click to add column in the table datasheet to add new fields

Access Difference between label and text box

Labels - used to add descriptive information, like column headings Text boxes - used to display data or calculations, usually connected to a field or calculated field

Charts Tips for creating effective charts

Limit the details Use descriptive titles Chart consistent categories Use special effects Use gridlines in moderation Limit the use of different text styles to no more than 2

Access Navigation pane

Lists all the objects (tables, reports, and so on) in the database, and it is the main control center for opening and working with database objects

Excel Freezing panes

Make sure your active cell is in the row you want to freeze. On the view tab, click the freeze panes button. This will cause that row to not move as you scroll the worksheet

Excel What you can do with excel

Numeric and text data recorded in rows and columns to: Make calculations - Formulas, functions Analyze data - Pivot tables, filters Track ,store, and query information - Database Run what-if scenarios - Goal seek Create charts - Pie, Line, and Column

Access Data redundancy

Occurs when unnecessary duplicate information exists

Access PMT function

Payment (pmt) Calculates the payment given a constant interest rate and constant payments Rate, nper, pv, fv, type

Excel Statistical functions

Provide statistical analyses of data sets. Includes average, median, mode, min, max, count, counta

Access Record set

Record set (data set) - the records that satisfy the query

Pivot Tables Database hierarchy

Related characters=fields Related fields=record Related records=file Related files=database

Access 5 sections of a report

Report Header - used to create a title for a report Not a default and must be added if needed Appears on the first page only Page Header - used to add column headings and appears on each page Details - used for adding, positioning, and formatting data from the table or query Page footer - used to show labels at the bottom of each page (often an inserted field code like page numbers) Report footer - used to display text or labels; displayed on the last page of the report

Charts Data marker

Represents the datapoint in the chart

Excel IF function

Returns one value if a condition is true, and returns a different value if that condition is false.

Excel Paste special

Select and copy a range, select the range where you want to paste the clipboard contents, click the paste button arrow then click paste special. From the paste special dialog box, you can control exactly how to paste the copied range

Excel Referencing another worksheet in a function

Select the cell where you want to enter the formula. Type = and begin entering the formula. To insert a reference from another worksheet, click the sheet tab for the worksheet, and then click or select the range you want to reference. When the formula is complete, press the Enter key.

Excel Protecting a worksheet

Select the cells and ranges to unlock so that users can enter data in them. On the home tab, in the cells group, click the format button, then click format cells. In the format cells dialog box, click the protection tab. Click the locked check box to remove the checkmark, then click the ok button. On the review tab, in the changes group, click the protect sheet button. Enter a password (optional). Select all of the actions you want to allow users to take when the worksheet is protected. Click the ok button.

Excel Inserting a column or row

Select the column or row where you want to insert the new column or row. On the home tab, in the cells group, click the insert button

Excel Deleting a column or row

Select the column or row you want to delete. On the home tab, in the cells group, click the delete button

Computer Concepts Systems software

Set of programs that enable your computers hardware devices and the application software to work together

Computer Concepts Application software

Set of programs used to carry out specific tasks like typing a letter or creating a spreadsheet

Excel RATE function

The RATE function is used primarily to evaluate the return from investments (because borrowers rarely set the interest rates of their loans)

Excel Active cell

The cell that is currently selected in the worksheet is referred to as the active cell. The active cell is highlighted with a thick green border and its cell reference appears in the Name Box

Access Common data types

The data type determines what field values you can enter for the field. -Long text-used for long messages or descriptions; can be combinations of numbers or words -Short Text-used for descriptive information -Date/Time-used for date and time data -Currency-used for monetary data; can be used in calculations -Number-used for any number; can be used in calculations

Excel Formula bar

The formula bar, located under the ribbon, displays the value or formula entered in the active cell. You can also edit the formula or text here

Charts Category axis

The horizontal axis, or category axis, displays the category values from each data series

Excel Merge and center

The merge and center button, located on the Home tab of the ribbon, will merge and center text between multiple rows

Excel Name box

The name box, located under the ribbon to the left of the formula bar, displays the cell reference of the active cell. You can type a cell reference in the name box and Microsoft will take you to that cell

Excel Setting a print area

The region of the worksheet that is sent to the printer. Select the range you want to print, click the page layout tab, in the page setup group click the print area button and then click set print area.

Charts Value axis

The value axis, or vertical axis, displays the values from the data series

Excel Cell

The worksheet is organized into individual cells; identified by a cell reference, which is based on the cell's column and row location

Excel Wrap text

The wrap text button, located on the Home tab of the ribbon, is used when the columns are very long making the worksheet hard to read. Wrapping the text takes the text that currently occupies one long line and wraps the text to a new line increasing the row height

Excel Grouping worksheets

To select an adjacent group, click the sheet tab of the first worksheet in the group, press and hold the Shift key, click the sheet tab of the last worksheet in the group and then release the Shift key.

Access Expression builder

Tool to create formulas using a point and click method <<Expr>>serves as a placeholder and needs to be deleted

Excel Truncated text

Truncated text is when the text only occupies one line and does not allow you to see everything because the remaining text is being covered by another cell. To fix this, increase the column width or do best fit

Access Creating a custom field

Type field name Type a colon : after the field name Type in what will appear in the field Example: Target Week of Supply:8 You can identify a custom field by the colon or by a blank table cell in the query design grid

Excel PMT function

Used to calculate the payment schedule required to completely repay a mortgage or other type of loan

Excel Formatting numbers

When inserting numbers, use general, currency, accounting, long date, or short date format depending on what kind of context the number is in. There is also the comma style to insert commas where they belong in the numbers, or the percent style to change the numbers to percents

Access Orphaned records

When the record does not have a matching record, and the data is inconstient

Access Foreign key

When you include the primary key from one table as a field in a second table to form a relationship between the two tables, its called a foreign key in the second table

Excel Workbook

Workbooks are organized into separate pages called sheets

Excel Show formulas

You can display formulas in a worksheet by clicking the Show Formulas button in the formula auditing group on the formulas tab

Excel Changing row heights

You can drag the bottom border of the row heading to a new row height, specify a row height using the Format command, or autofit the row's height to match its content

Excel Changing column widths

You can drag the edge of the column to make it wider or use best fit so the column perfectly fits the data inside

Charts Moving charts to a different location

You can move a chart from one worksheet to another, or you can place the chart in its own chart sheet. On the chart tools design tab, click the move chart button. The move chart dialog box will open, click the object in arrow to display a list of the worksheets in the active workbook, and then click overview. Click ok and the chart will move from one worksheet to another.

Excel Splitting into panes

You can split the worksheet into two or four separate panes. Click the view tab, in the window group, click the split button. The worksheet window splits into two panes. Each pane has its own set of scroll bars.

Pivot Tables Purpose of refresh icon

You cannot change data directly in a PivotTable and PivotTables are not automatically updated when the source data for the PivotTable is updated. After you edit the underlying data, you must refresh, or update, the PivotTable report to reflect the revised calculations

Pivot Tables Record

group of related fields about one object or event

Access Guidelines for good table design

• Include necessary data • Determine what fields you need to create the necessary reports and set up those fields in the table • Store data in smallest parts and set up data types and properties as accurately as possible • Remember that Access stores dates as serial numbers (like Excel) and can perform date arithmetic • Field names can have up to 64 characters -CamelCase notation for naming

Pivot Tables Areas of the screen

• Pivot Table Field List - list of the column headings • Pivot Table Report - area used to construct and analyze the results • Pivot Table Tools - commands found on the Ribbon under the Options and Design tabs used to adjust the settings and appearance of a Pivot Table