Access Flashcards
Design View
Greater access to properties available for each type
Change name of existing table
Add relationships
Relationships
Created in Design view using join lines
Create queries, forms & reports
Add Description to Table
Right click table and select table properties
Type description
Working in Storage v. RAM
Word, Excel & Powerpoint work in RAM
Access works in Storage
View Existing Relationships
Click Relationships in Relationships group in the Database Tools tab
Re-position tables by dragging the table’s title bar so all relationships are visible
Close
Prompted to Save
Change design of table such as font or background color.
Deleting Records
Use alternative such as adding yes/no field for active status
Selection Filters
Find exact matches
Find records containing a value
Filter by Form
Database Object Types
Standard MS Office applications interface:
*Ribbon
*Backstage View
*Home Tab
*Scroll Bars
Data objects - each has its own tab on Navigation Pane & open objects have a tab displayed beneath the ribbon.
Datasheet View
Double click table from Navigation pane
Fields/categories shown as columns
Records complete set of fields shown as rows
Navigation Bar
Bottom of Datasheet view
Shows total number of records & current record
Pencil shows record is being edited
Arrows for first, last, next, and previous
Yellow asterisk adds a blank record
Find command for single field or specific records
Design View
Detailed look at the table’s structure
Specify fields contained in the table
Data types & associated properties
Click View to toggle between data and design
Tables
Databases rely on tables to provide the framework for all the tasks and activities performed in a database.
Design a Table
Consider all the specific fields you’ll need and the type of data each field will store
Next add necessary fields.
Guidelines for Adding Fields to a Table
Include the necessary data Design for now and the future Store data in their smallest parts Determine the primary keys Link tables using common fields Design to accommodate calculations
Methods of Creating a Table
Use a template
Import data from another database
Import data from Excel
Create field names in Design then enter data in Datasheet view
Directly enter field names and table data in Datasheet view
Deleting Fields
In either Design or Datasheet views.
Primary Key
Field that uniquely identifies each record in a table.
Use a customer ID or an Autonumber.
Field Properties
Determine how the field looks and behaves.
Often set in Design view but some can be set in Datasheet view in the Table Tools Fields tab.
Adding Fields
Data sheet View add arrow at the top of an empty column.
Design view offers more field property choices.
Import Wizard
Tables, queries, forms, reports, pages, macros, and modules from other databases.
External Data tab in Import & Link group.
Why Import
Saves typing
Prevents data entry errors
Imported tables may need editing to data types, field sizes, captions, input masks, other properties-change in design view.
Benefits of Relationships
Efficiently combine data from related tables for creating queries, forms, and reports.
Database Tools tabs, Relationships Group, Relationships
Drag the Primary Key field name from one table to the Foreign Key field of the other table.
Referential Integrity
Enforces the rules in a database that are used to preserve relationships between tables when records are changed.
You cannot enter a foreign key value in a related table unless the primary key value exists in the primary table.
Single Table Query
Simple query wizard or query design tool.
The design tool offers more flexibility.
Query Criteria
Delimeters - " or # Wildcards - text value Comparison operators - =,<> Null AND, OR, NOT conditions
Query Sort Order
Designview
Determined from left to right.
Fixing Query Errors
Correcting immediately saves time.
Changes the original data table.
Multitable Queries
Contain 2 or more tables
Takes advantage of relationships in the database.
Easiest is to add tables to an existing query or add to a copy of an existing query.
Create a Multitable Query
Use only related tables or those with a common field.
Create Tab, query design in the queries group, then add the tables. Close the show table dialog box and drag fields to query design grid, add criteria and sort options.
Modify a Multitable Query
To add tables, use Show Table dialog box in query setup, to remove, click table and press delete.
To add fields, double click the fields to include.
To remove fields, click column selector and press delete.
Order of Operations
Rule that controls sequence arithmetic operations are performed. Parentheses Exponentiation Multiplication Division Addition Subtraction
(OOO) 2+3*3
Multiply Add 2+3*3 2+9 11
(OOO) (2+3)*3
Add values in parentheses then multiply
(2+3)3
(5)3
15
(OOO) 2+2^3
Evaluate Exponent Add 2+2^3 2+(2*2*2) 2+(8) 10
(OOO) 10/2+3
Divide then add 10/2+3 (10/2)+3 (5)+3 8
(OOO) 10/(2+3)
Simplify parenthetical expression then divide
10/(2+3)
10/(5)
2
(OOO) 102-32
Multiply then subtract 10*2-3*2 (10*2)-(3*2) (20)-(6) 14
To Create a Calculated Field in a Query
Open query in Design view
Click the Field row of blank column
Type expression
Click Run in the Results group to display in Datasheet view.
Formatting Results of Calculated Query
Go to Property Sheet
Recover from Common Errors in Calculated Fields
Forgetting colon, spelling, order of operations.
An error message or prompt will appear.
Verify Calculated Results
Use critical thinking skills or verify by copying to excel or using a calculator.
Expression Builder Tool
Helps create more complicated expressions.
Size enables you to see complex formulas and functions in their entirety.
Provides easy access to objects, operators, and functions.
Built in Functions (Access)
Function is a predefined computation that performs a complex calculation.
Each input is known as an argument.
Access has about 150 functions.
Aggregate Function (Access)
Performs a calculation on an entire column of data and returns a single value.
Avg, Count, Max, Min, Sum
Referred to as totals
Two Methods of Aggregate Functions (Access)
The total row displays as the last row in Datasheet view.
Use a totals query.
Totals Query (Access)
Additional row in the query design grid.
Used to display only aggregate data when the query is run.
Enables you to see statistics by category.
Grouping a Query
Enables you to summarize data by the values of a field.
Add the field you want to group by to the query in Design view.
Verify the Total row displays Group By for the added field.
A condition can be added to this type query.
Conditions in Totals Query
Perform calculations on values that meet the conditions.
Calculated Fields
Can have aggregate functions applied to them.
Create the calculation to summarize the data
Select the appropriate aggregate function from the menu in the Total Row
Run the query
Create Forms Using Form Tools
Automatically creates a basic form saving tedious design work.
Create a form using the form tool
Create a split form
Create a multiple items form
Modify Forms
Edit Data in Form View Delete a Field Add a Field Format Controls Add a Theme
Work with a Form Layout
Tools on the Arrange tab
Add gridlines, change from stacked to tabular layout, or remove the layout.
Rows & Columns group to insert rows or columns.
Sort Records in a Form
Use the Sort & Filter Group
Create Reports using Report Tools
Report Tool
Report Wizard
Use Report Views
Report Layout Design Print Preview Click the View arrow in the Views Group to switch between the 4 views
Modify a Report
Add a field
Remove a field
Change orientation
Apply a theme
Sort Records in a Report
Defaults to record source primary key.
Open report in Layout or Design view.
Click Group & Sort in Groupings & Totals group on Design tab.
Click Add a sort and select the sort field.
Form
A user friendly database object used to add data to or edit data in a table
Form group on the Create tab
Cannot use to change layout and design.
Navigation bar at bottom to move between records.
Tab key to move between fields.
Form Tool
Create customized, professional looking forms.
Improve data entry and editing.
Used to create data entry forms for customers, employees, products, and other types of tables.
Create a Multiple Items Form
Select a table or query in Navigation Pane.
Click More Forms.
Click Multiple Items in the Forms Group of Create Tab.
Delete a Field in a Form
Display the form in Stacked/Layout View
Click the text box control of the field for text.
Click Select Row in the Rows & Columns group on the Arrange tab to delete both the text box and its label.
Press Delete
Multiple Items Form
Displays multiple records in a tabular layout similar to tables Datasheet view, containing more options.
More customization options than a data sheet such as graphical elements, buttons, and other controls.
Format Controls
Font size, emphasis, alignment, background color, font color, and number format.
Edit Data in Form View
Form layout view allows you to adjust all controls from first field.
Sort Order within Forms
Tables sort on primary key.
Queries sorted in a variety of ways.
Change using the Sort & Filter Group.
Report Tool
Easiest way to create a report.
Reports Group on the Create tab.
Create a tabular report based on the selected table or query.
Report Wizard
Prompts you for input in a series of steps to generate a customized report. Make certain customizations quickly. * Determine sorting and summary data. * Specify preferred layout. In the Reports Group on the Create Tab.
Reports
A document that displays information in a format that outputs meaningful information to its readers.
Easiest is to use the Report tool in the Reports group on the Create tab.
Report View
Cannot make modifications
Report Print Preview
Cannot make modifications.
See exactly what the printed report will look like.
Options to export and save to a different file type.
Portable Document Files (PDF)
A file type that was created for exchanging documents independently of software applications or operating system environments.
Add a Field to a Report
After adding fields into a table.
Open report in Layout View
Click Add Existing Fields in the Tools group on Design tab.
Click and drag the desired field to the report.
Remove a Field from a Report
Open the report in Report View.
Click the Text box control of the field.
Click Select Row in the Rows & Columns group on the Arrange Tab.
Press Delete.
Change Orientation of a Report
Page Setup tab
Page Size group
Page Layout group for orientation.
Apply a theme to a Report
To improve appearance and readability.
Open the report in Layout or Design view.
Select Themes in the Themes Group on the Design tab.
Live Preview, click the theme to select then Apply.
Database Objects
Forms
Reports
Subform
To view and edit data in tables that contain related records.
Subform
Access automatically creates when you have a form with related data.
To view and edit data in tables that contain related records.
When you create a form based on a table that has a related table, access automatically creates a subform that displays the related records.
Establish Data Validation
Require a field.
Add a default field value.
Add a validation rule with validation text.
Control the Format of Data Entry
Use an Input Mask to specify the exact format of the data entry.
Phone and SSN numbers
Data Validation
A set of constraints or rules that help control data entered into a field.
Access provides data validation when the type of data is different than the field’s properties.
Can use comparison operators (=,>,,>=,<=,between, in, like.
Control Input with a Lookup Field
Created using the Lookup Wizard.
Open the table in Design view.
Click the field you want to change to a lookup and select Wizard from the list.
Choose a table that contains the lookup values.
Specify the sort order, and adjust column width.
Customize Output Based on User Input
Create a parameter query
Create a parameter report
Use Advanced Functions
Date function Round function If function IsNull function DatePart function
Create an Input Mask - Input Mask Wizard
Open the table in Design view.
Click the field to which you will ad an input mask.
Click Input Mask in the Field Properties pane.
Click the ellipses at the end of the Input Mask Row.
Select one of the built-in masks from the menu.
Decide upon whether to store dashes (phone/ssn).
Click next and finish.
Lookup Fields
You can add, delete, or edit values in the lookup field to adjust to changing data by changing the table containing the field options.
Data Validation Rule
Open the table in Design view.
Click the field to which you will add a validation rule.
Click Validation Rule in the Field Properties pane.
Enter the validation rule.
Enter a meaningful error message in the Validation text.
Use to minimize data entry errors.
Create an Input Mask - Input Mask Wizard
Open the table in Design view.
Click the field to which you will ad an input mask.
Click Input Mask in the Field Properties pane.
Click the ellipses at the end of the Input Mask Row.
Select one of the built-in masks from the menu.
Decide upon whether to store dashes (phone/ssn/zip).
Click next and finish.
The input mask now appears in Input Mask property box.
Create a Parameter Query
Select Parameter query in the Navigation pane.
Click Report in the Reports group of the Create tab.
The Enter Parameter Value dialog box appears asking for the criterion.
Enter a value and click OK.
The report opes in Layout view, then save the report.
Date Function
Calculates the current date.
Doesn’t require any parameters.
Type it in field row of an existing query or use the Expression builder.
Expression Builder
Open a query in Design view or create a new query.
Click the Field row of a blank column.
Click Builder in the Query Setup group of the Design tab
Double click Functions in the Expression Elements section,
Double click Date in the Expression Values section, OK
Round Function
Displays a number rounded to a specific number of decimal places, and requires a number and optional parameter specifying the number of decimal places.
Use same procedure as entering Date function.
IIf(Round([age])>24,”Approved”,”Not Approved”))
Create If Function
In the Expression Builder, click Program Flow in the Expression Categories section.
Double click If in the Expression Values section.
Replace the placeholder text for the expression, true part, and false part with the desired values you want.
Create IsNull Function
Checks whether a field has no value.
In the Expression Builder, click Inspection in the Expression Categories section, double click IsNull in the Expression Values section.
Replace the placeholder text for the expression you want to check, then OK.
Create the DatePart Function
Examines a date and displays a portion of the date. Used to determine the year, month, or day and a number of other intervals as well.
In Expression Builder, click Date/Time in the Expression Categories section.
Double click DatePart in the Expression Values section and replace the placeholder text for the interval, date, first day of week, and first week of year, then OK.
Create the DatePart Function
Examines a date and displays a portion of the date. Used to determine the year, month, or day and a number of other intervals as well.
In Expression Builder, click Date/Time in the Expression Categories section.
Double click DatePart in the Expression Values section and replace the placeholder text for the interval, date, first day of week, and first week of year, then OK.
DatePart(“yyyy”,FieldName)
