 |
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.
|
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
|
|
 |
| |
| Do you have
questions about
Word, Excel, Access, Web Design, or computers in general? Just
ask us anything you'd like.
Click here
for assistance. |
|
|
|
|
|
