Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
 
 
Courses - Microsoft Access 220
Description: Advanced Access Queries, Part 1
Running Time: 65 minutes
Pre-Requisites: Access 207 very strongly recommended
Previous Lesson: Access 207
Next Lesson: Access 221
Main Topics: Advanced Queries, Round Values, Form Total, Inner Outer Joins, Cascade Delete
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    
 
Microsoft Access 220
Advanced Access Queries 1

Advanced Query Techniques, Round Values, Form Footer Totals, Inner & Outer Joins, Cascade Deletes. 65 Minutes.
 

AC220 Major Topics

  • Advanced Query Techniques
  • Round Values
  • Form Footer Totals
  • Inner & Outer Joins
  • Cascade Deletes

This class begins our look at Advanced Query Techniques. We will start by creating a Sales Log system. We'll build a sales log table where we will store basic order information, like unit price, description, quantity, etc.

 

Next, we'll create a Sales Log Query which will use Calculated Fields to figured out the extended price by multiplying the unit price and quantity. We will also learn how to calculate sales tax based on whether or not each sale is taxable - cool stuff, using the IIF() function we learned about earlier. We'll also learn how to round off values inside queries.

 

Next, we'll make a Sales Log Form where you can enter in your sales information, pick your customer from a combo box, and so on. There's a real cool trick on this form - one that people have been emailing me to show them for a while... how to put a Total in the form footer!

 

We'll learn about different types of Query Joins, like Inner and Outer Joins. This is necessary if you want to, for example, get a list of all of your customers with contact history - whether or not they have contacts; or all sales whether or not there is an associated customer record. This is very powerful stuff.

 

We'll also talk a lot more about Global Relationships, Referential Integrity, Cascade Deleting records, and more. This is a great class, and it's just the first in our series of Advanced Query lessons.

 

 

Access 220 Outline
 

1. Sales Log Table
Create SalesLogT
Default SaleDate to =Date()
Setup PaymentMethodT

2. Sales Log Query
Calculated Query Fields
Multiply Two Fields Together
Calculate Extended Price
Mathematical Operators
Add, Subtract, Multiply, Divide
Integer Division
Exponents
Modulo (Modulus)
IIF Function
Determine If Taxable
Calculate Order Total
Query Field Properties
Round Function
Rounding Field Values

3. Sales Log Form, Part 1
Copy Customer Combo from ContactF
Bring in Calculated Fields from Query
Continuous Forms
Form Header and Footer
Move Labels to Header
Horizontal Line

4. Sales Log Form, Part 2
Create an Order Total in the Form Footer
SUM Function in Footer Field
Totals with Filters On
Show Sales for Specific Customer
Open a Form and Show Specific Records

5. Query Join Types
Only include rows where the joined fields from both tables are equal
Inner Join
Include ALL records from Table1 and matching fields from Table2
Outer Join
Show ALL Customers and Sales if they have them
Show ALL Sales and Customers if listed

6. Global Relationships
Enforce Referential Integrity
Cascade Delete
Tools > Relationships
Why I don't often use Global Relationships

 


Try a FREE Demo Lesson

 
 
 

 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2023 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 12/8/2023 9:05:12 PM.