Access Beginner 3

0. Intro (6:53) 1. Field Properties 1 (14:40) Design View Field Size AutoNumber Field Size What is a Replication ID Text Field Size Number Field Size Byte Integer Long Integer Single Double Decimal Warning: data may be lost 2. Field Properties 2 (5:13) Caption Changes default form label Changes column header in datasheet Don't confuse with Description 3. Field Properties 3 (16:02) Format Property Number Formats General Currency Euro Fixed Standard Percent Scientific With percent 1 = 100% Decimal Places Rounded values displayed Actual values stored Currency Formats Format might hide data mm dd yyyy hh nn ss F1 for Access Help List of Date Format Codes Text Format Phone Numbers @@@-@@@-@@@@ Yes/No Format 4. Field Properties 4 (5:25) Input Mask Phone Number Placeholder characters Store symbols in the mask? Enter data Warning message on invalid data Date/Time Input Masks No Wizard for numbers/currency Help for Input Masks 5. Field Properties 5 (9:47) Default Value State of NY NumEmployees 1 DiscountRate .05 Automatically set current date Date() Function Now() Function includes time Yes/No Default Values 6. Field Properties 6 (16:16) Validation Rule Inequality Symbols NULL Values not the same as ZERO Existing data violates rule Validation Text ESC escape to cancel editing Range of values AND OR BETWEEN keyword Date Values #1/1/1980# Date() Function in Validation Rule Functions in Validation Text 7. Review (4:47)

Access Beginner 4

00. Introduction (6:07) 01. Field Properties 7 (10:42) Required No data is better than bad data Data integrity rules Existing data violates the new setting Null Values vs. Empty String Zero Length Text Allow Zero Length Access automatically trims spaces 02. Field Properties 8 (9:18) Text Align Left, Right, General, Distribute Text Format Plain Text Rich Text HTML Encoding Change table, change form Append Only Misleading Show Column History 03. Field Properties 9 (14:44) SmartTags - brief discussion Unicode Compression - brief discussion IME Mode - brief discussion IME Sentence Mode - brief discussion Show Date Picker AutoNumber Increment or Random Artificially inflating AutoNumbers Indexing Duplicates OK No Duplicates Which fields to index When and when not to index 04. Database Maintenance (7:57) Compact & Repair 2 GB Database Size Limit Backing Up Your Database 05. Find & Replace (15:18) Find, Replace Look In Current Field Current Document Match Whole Field Any Part of Field Start of Field Search: All, Up, Down Match Case Search Fields as Formatted Search Box at Bottom of Table/Form Replace v. Replace All Backup Your Table First How to Copy/Paste Table Rename Table Goto Button First, Previous, Next, Last, New Navigation Buttons Select & Select All CTRL-A 06. Questions From Students (9:59) Can I get an AutoNumber back? Compact & Repair trick Can I create a custom AutoNumber? Attach Label to Text Box Associate Label with a Control Add a Field to Table Must add field to form manually Renamed table fields usually rename in form Deleted fields in table cause error 07. Review (4:44)

Access Beginner 5

00. Introduction (6:49) 01. Query Criteria 1 (16:30) Multiple OR Conditions Delete a query Rename a query "NY" OR "PA" "NY" OR "PA" OR "TX" IN ("NY","PA","TX","CA") Criteria OR Rows From NY and are Active AND condition across columns AND across, OR down (State=NY) OR (State=PA AND Active=TRUE) Customers with Credit Limit over $1000 From NY with $1000 or anybody with $1500 AND OR inside of a single critera BETWEEN keyword 02. Query Criteria 2 (15:40) NOT Keyword NOT "NY" Inequalitites Review NOT "NY" AND NOT "OH" "NY" AND "PA" AND limits query results OR expands query results Show box Hide fields from query Prevent odd names like "Field1" Move a query column Dates in pound symbols <#1/1/2005# Recent customers BETWEEN two dates d/m/y or m/d/y regional settings Whether to include endpoints BETWEEN #1/1/05# AND #12/31/05# Dates with times can be tricky >=#1/1/05# AND <#1/1/06# Customers before 30 days ago Became customers yesterday >=Date()-1 AND 03. Query Criteria 3 (8:47) Is Null Is Not Null ZipCode LIKE "142*" State LIKE "N*" Email LIKE "*amicron*" Date LIKE "*/2012" Date LIKE "5/*/2012" 04. Parameter Queries (11:45) Separate query for each state [Enter the state] Missing field names from tables Parameters with other criteria Multiple parameters Between [Start Date] and [End Date] Like [Company Name] Teach your users about wildcards XYZ* Like "*" & [Company Name] & "*" Will cover String Concatenation Later Build report based on this query 05. Questions from Students (7:55) Top X Values Top X% Values Bottom X Values Drop-Down List in Query Columns Change Field Why the Run Button for Queries Format Query Field Output Property Sheet for Query Fields 06. Review (6:40)

Access Beginner 6

00. Intro (6:49) 01. Address Info (16:25) Customers with full address query Is Not Null AND Report Property Sheet Change mailing label report RecordSource Unexpected Enter Parameter Value Report Too Wide Some data may not be displayed Not enough horizontal space on the page Query of customers missing any address info Is Null OR Create Quick Report Report Layout View Resizing Columns Moving Fields Moving Page Number Block 02. Employee Table (13:05) Distinct Separate Data Means New Table "EmployeeID" vs. just "ID" Copy Fields from Customer Table Insert Rows in Table Design View Social Security Number Input Mask Picture Attachment - Brief Discussion Of OLE Object - What it is Editing a hyperlink 03. Employee Form (13:53) Create Form in Design View Bring over fields for Employee Form Copy Address Fields from CustomerF CTRL-A To Select All Objects Align to Grid Size to Grid Set Form Background Color Format Painter Double-Click to Keep Active Resize a Group of Fields 04. Inserting Pictures (9:59) Copy Image from Web Site to Desktop JPG and GIF may show icon only Convert to BMP using Microsoft Paint Click and Drag to OLE Object Stretch, Clip, Zoom 05. Employee ID Report (11:44) Label Wizard Manually Resizing Labels Adding Employee Picture Transparent Border Bound Object Frame Unbound Object Frame Image Control Insert a Static Company Logo Editing Pictures in Place 06. Review (5:53)

Access Beginner 7

00. Introduction (7:09) 01. Navigation Forms (16:46) Getting around in your database Switchboards, discussion of Navigation Form Pros and Cons Create Simple Horizontal Nav Form Add Forms to Nav Form Add Reports to Nav Form Rename Tabs Change Form Caption Rearrange / Move Tabs Delete Tab Horizontal Tabs, 2 Levels Multi-Level Menu Tabs Tabs by Object Type Tabs by Data Type Changing Colors Display Navigation Form on Startup Hide Navigation Pane on Startup Bypassing Startup Options Nesting Navigation Forms, discussion of 02. Main Menu Form 1 (12:01) Create a Blank Unbound Form Command Button Wizard Form Operations > Open Form Button Text (Caption) Open Form Show All Records Make Forms Remember Startup Position Command Button Wizard not starting Use Control Wizards Option Why Use Command Buttons Button to Close Form 03. Main Menu Form 2 (18:07) Change Form Background Color Form Properties Form Caption Property Form Property Sheet Turn Off Record Selectors Turn Off Navigation Buttons Turn Off Scroll Bars Main Menu Label Formatting Label Options Shape Outline Special Effect Shadowed Labels Use Labels to Group Buttons Position > Send to Back Report Operations > Preview Report Copy Logo to Main Menu Form 04. Customer List Form 1 (11:54) Sorted List of Customers NOT Using a Multiple Items Form Create a Sorted Query for Customers Form Design View with Blank Form Setting a Form's Record Source Property Query as a Record Source Show Only Fields from Current Record Source Form Properties Default View > Continuous Forms 05. Customer List Form 2 (18:59) Rearrange Fields into Columns Put Labels in a Row Over Text Boxes Form Header & Footer Page Header & Footer, discussion of Move Labels to Form Header Access Should Save Form Position and Size Command Button to Open Customer Form Open the Form and Find Specific Data to Display Which Fields Contain Matching Data Making a Button Transparent Turn off Navigation Buttons Turn off Record Selectors Set Scroll Bars to Vertical Only Allow Additions Allow Deletions Allow Edits 06. Review (5:46)

Access Beginner 8

00. Intro (6:51) 01. Value List Combo Box (15:02) Combo Boxes provide for uniform data entry Select an option from a list Add LeadSource to Customer Table Make sure LeadSource is in Customer Form Change RecordSource to CustomerT Add Combo Box to Customer Form I will type in the values that I want Store that value in this field Selecting values Why is it called a Combo Box? Limit to List Edit List Items Adding Items to the List Default Value Allow Value List Edits 02. Table-Based Combo Box (12:27) Why you should NOT use a Value List Combo Create a LeadSource Table Combo Box get the values from another table Which fields do you want in combo box What sort order do you want Save in the LeadSource field Row Source Type: Table/Query List Items Edit Form Create a LeadSource Multiple Items Form 03. Multi-Column Combo Box (8:58) Combo Box to pick States Create a State Table State Abbreviation and Name Hide Key Column Resize Column Widths Which column to store in CustomerT List Width Column Widths List Rows 04. Tab Order (10:58) Show the Tab Order on CustomerListF Rearrange Controls Tab Order button Detail Section Auto Order Manually Edit Tab Order Tab Stop Property Cycle Property All Records Current Record Fix the Customer Form Tab Order 05. List Boxes (8:55) Preferred Shipping Method Create ShippingT Shipping field in CustomerT Change To > Combo Box Don't use Table Lookup Wizards 06. Search Combo Box (2:39) Turn on Form Header/Footer Combo Box Find Record 07. Review (5:54)

Access Beginner 9

00. Intro (6:26) 01. Contact Table & Form (12:48) What is a Contact Why not use a Memo field? Create Contact Table Review of Now() Function Create Contact Form Enter Data into Contact Form Notice Poor Data Integrity 02. Contact Query (11:39) Bring in All Fields "ContactT.*" Duplicate Fields Show Box to Hide Column Records from a Single Date =#11/24/12# doesn't work Between Keyword doesn't work Inequality works Today's Contacts with Date() Date()+1 Yesterdays Contacts Date()-1 [Enter Date] in multiple places Explicit Query Parameters 03. Contact Report 1 (14:54) Building a Report from Scratch Page Header, Footer Detail Section Print Preview Problems with Layout View Remove Box Borders Transparent Shape Outline Portrait vs. Landscape View Margins Horizontal Line SHIFT to keep line strait Printing Reports 04. Contact Report 2 (13:44) Switch back to Portrait Can Grow, Can Shrink Force exact size for reports String Concatenation FirstName & LastName in Query Trim Function Concatenation in Report Fields LastName, FirstName in Report 05. Miscellaneous (17:11) Alternate Back Color HTML Color Codes Light Gray Line Line Thickness Line Type Section Background Colors Page Header vs. Report Header Page Numbering Page [Page] [Pages] Report Footer Confidentiality Notice Add Buttons to Main Menu 06. Review (6:50)