Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Open Other Programs Seminar   dismiss
 
 
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.

    

Order before 11/30/2014 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
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

 
 
 

Student Interaction: Microsoft Access 220

Richard on 1/1/2007:  Advanced Query Techniques, Part 1 of 4. Sales Log Query, Calculated Fields in Queries, Totals in Form Footers, Join Types (Inner & Outer), Cascade Deletes
Greg Paradise on 2/25/2009: I'm glad to see you use a different color background:0 Dark grey is cool...almost black.
Mubeezi Micah on 5/6/2010: Dear Richard,

Looking at this video at 9:33minutes, i dont understand why you opt to use the round off function in a querry instead of the "decimal places" just below the format property in the open dialog box (field properties).

Is there a specific reason? I have to admit i did not know the round off function before. I had opportunity to learn it in this 220 video however i was curious that that there could be a reason that you perhaps did not mention.

Please let me know. Thank you!

MICAH

Reply from Richard Rost:

Both work just fine. I guess my way goes back to my old Visual Basic programming days where we HAD to use the Round() function in code... we couldn't rely on the decimal property for our text boxes. I probably should have mentioned that in the video. :)

Hassan  Ali on 8/7/2010: Hi

Iis it possible to have a primary key which is automatically generated but set the way I want it. For e.g. A1023, A1024, A1-25 etc.

Reply from Richard Rost:

You can use VBA programming to construct your own counter, yes. See this tutorial. Once you have the number, you can add anything else you want to it, like the customer's last name, or first initial. It's up to you.

Doris  Taijeron on 9/2/2010: Richard,

I have a query with a field "NoOfDaysToComplete. I want an average of number of days to complete. Can you give me tips on how to acheive this? I tried the Group funtion but I'm not getting an average. Thanks

Tiffany Parker on 1/25/2011: At marker 03:28 you are able to click on the plus sign in CustomerT and see all of the sales transactions from SalesLogT for each customer, I'm unable to do that with Access 2007. When I select the plus sign a new window opens called 'Insert Subdatasheet'. Do you have suggestions on what I must do to obtain the sales transactions per customer?

Reply from Richard Rost:

You must have a GLOBAL RELATIONSHIP set up for this to work at the table level.

Estelita Oledan on 5/8/2012: 0:36 Im using Access 2007 I cannot see Queries on the available field list..can you help me out thanks
 Patrick Hoffmann on 6/17/2012: Access 220 4th video "Sales Log Form" - I just installed the button on my customer form to open the sales log form and when I saved it and tried the button I got the following error message "The expression On Current you entered as the event property setting produced the following error:
Ambiguous name detected: ShowSalesButton_Click". I have no idea how to go about fixing this would so very appreciate any and all help.
Thanks
Pat

Reply from Richard Rost:

Let's see all of your code. That could mean that you have TWO subs called ShowSalesButton_Click, or you don't have any and it can't find one. Check for a duplicate first.

Patrick H on 6/17/2012: Thanks Richard,
Here is the code that comes up when I try and open the Customer form

Dim stLinkCriteria As String

stDocName = "StudentF"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_GoToStudentButton_Click:
Exit Sub

Err_GoToStudentButton_Click:
MsgBox Err.Description
Resume Exit_GoToStudentButton_Click

End Sub
Private Sub ShowSalesButton_Click()
On Error GoTo Err_ShowSalesButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SalesLogF"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ShowSalesButton_Click:
Exit Sub

Err_ShowSalesButton_Click:
MsgBox Err.Description
Resume Exit_ShowSalesButton_Click

End Sub


Reply from Richard Rost:

OK, your button code looks fine. Where's your OnCurrent event?

 E on 8/24/2012: 6:30
FYI: Balloon has 2 ls in it.

Reply from Richard Rost:

You are absolutely correct. Sometimes I get so into the database part of the class that I forget my simple spelling. :)

E on 8/26/2012: No problem. Just thought I'd point it out in case you reshoot this clip.
Roderick Barbour on 12/2/2012: Richard, I am sure that you have answered this question somewhere along the way, but at Time Index 9:10-10:03 after you do the inner join and the left outer join all the tables have repetitive data. Why is that? Thanks in advance.

Reply from Richard Rost:

It's been several YEARS since I recorded this video... but either (a) the answer is coming, or (b) I just copied extra data for illustration purposes. If I don't answer the question soon (in the videos) let me know and I'll rewatch it.

Roderick B on 12/5/2012: Great. Thanks
Will on 12/6/2012: Hi Richard,

i forget how to do a double where condition when opening a form.

docmd.openform "CustomerT", , , "ID = " & "Forms!TutorT!ID"

how do i add on another condition like first name.

Reply from Richard Rost:

Well, if you're using an ID, then you wouldn't need a second condition because that's pretty specific. However you might sometimes want:

Docmd.Openform "CustomerF",,,"FirstName=Joe AND LastName=Smith"

And of course if those are fields or variables, you'd need to say:

Docmd.Openform "CustomerF",,,"FirstName="""& FN & """ AND LastName=""" & LN & """"


Will on 12/6/2012: it worked perfect, thanks.

i still don't understand all the """""" but it worked, haha...

Reply from Richard Rost:

You want to say:

FirstName="Rick"

But that needs to be inside of double-quotes. Well, in order to put a double-quote inside of a set of double-quotes, you need to turn it into this:

"FirstName=""Rick"""

Now since you're dealing with a variable or field name, you have to concatenate multiple strings together, so you end up with:

"FirstName=""" & FN & """"

You could use single-quotes inside of double-quotes, but I tend to avoid that because it doesn't always work:

"FirstName='" & FN & "'"

Get it now?

 

You may want to read these articles from the 599CD News:

 
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP