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