Microsoft Access & Excel MIS Exam 2

Object (element)

Include tables, queries, forms, and reports & is found in navigation pane

Navigation pane

organizes and lists the objects in an access database, left side of screen and displays all objects

Table

group of fields related to one topic & organized in rows & columns

Field

Category of info we store in a table

Record

Complete set of all fields about one person, place, event or concept

Primary key

a field that uniquely identifies each record in a table

Query

A question you ask abut the data in your database. Allows you to extract specific data

Form

This allows simplified entry and modification of data

Report

contains professional-looking formatted information from underlying tables or queries

Datasheet view

This view displays all records in a database. It is a grid containing fields & records. You can view, edit, and delete records

Design view

Used to create and modify a tables design by specifying the fields it will contain, the fields data types, and their associated properties

Field property

The detailed characteristics of a field

Relationship

link btwn two tables using common field. Required primary key and foreign key

Foreign key

A field in a table that is related to the primary key of another table. It is used to create a relationship

VLOOKUP formula for getting data from another sheet

=VLOOKUP(lookup_value, Sheet!range, column number in range, true/false)

ensures exact match

False in vlookup

ensures approximate match

True in vlookup

pivot table, pivot chart, and slicers

What excel elements are in this screenshot

Slicers

interactive filters used in PivotTables, PivotCharts, and tables. They allow users to quickly filter and analyze data visually instead of using drop-down menus.

Adding table categories to your query results

Open table in query design > double click on fields

Criteria

______ should be placed in single quotation marks because they serve as delimiters marking the beginning and end of the text. This does not apply when entering numerical values

Sorting results

Identify desired field in the lower table area > Use drop-down corresponding to the “sort” clause > choose ascending/descending

#

delimiters for dates in access

Grouping & aggregating data

Query design > totals button > add fields you want to group by > Add fields to aggregate > choose btwn sum/count/avg/min/max

Appending data

Design view > Drag the two fields on top of each other > select append on the ribbon (changing from select) > Add data to existing table when pop-up shows > press run

Creating a table from a query

Create a query with two tables and ensure they are joined > select “make table” from ribbon (changing from select) > Run

Key

a field (or set of fields) used to uniquely identify each record in a table. It ensures data integrity and helps establish relationships between tables.

Primary key

A field that uniquely identifies each record in a table

Changing column headings

Open your query in design view > in the field row type NewName: (OriginalFieldName)

changing format of data

Open report in Design View > Select field > In the property sheet select format > set to Currency

Specifying tables & columns to display

Create tab > Query design > add tables > In the design grid select the columns you want to display by double-clicking field from the table list

Updating info using a query

Click update query (on query design toolbar) > in field row enter the column update > in the update to row enter your calculation ( EX: XYZ*!.5) > Run