Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
 
Microsoft Access Expert 13
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   8 years ago

Microsoft Access Expert Level 13 is 1 hour, 22 minutes long. This tutorial will focus on using Update Queries to manipulate data in your tables. Topics include:

- Update Queries
- Types of Action Queries
- Report Where Conditions
- Datasheet View vs Run Query
- UCase, LCase, Proper Case
- Deleting Tables with Relationships
- Assign Sales Rep by State
- Add 5% to all Product Prices
- Update Paid Orders with Ship Date

Click here for more information on Access Expert Level 13, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 12. The next class in the series is Access Expert Level 14.

Calculating GST Upload Images   Link 
Dave Ingram 
4 months ago
In an earlier lesson we had state taxes, federal taxes etc. I understand perfectly. In new Zealand we have just one sales tax called G.S.T. ( Goods & Services Tax) @15%.
All goods are sold inclusive of G.S.T. To get the G.S.T. content you have to multiply by 3 then / 23 which gives you the G.S.T. content. To normalise the Data Base how can I have this in on place i.e. in the Organisational table. At the moment I have the calculations in the queries, which is not ideal. I am sure the answer is simple, it just escapes me at the moment.
Adam Schwanz
4 months ago
If I understand what you're saying, you just want an easy way to always use NZ tax, you could make a global function and send it the number in question.

Something like

Public Function NZTax (X As Double)
X * 3 / 23
End Function

Then just send it the value in your calculations

NZTax(ProductCost)
Richard Rost
4 months ago
Don't forget the Superman III Problem.

Public Function NZTax (X As Currency) As Currency
   NZTax = Round(X*3/23,2)
End Function

Add a Reply
Update Query Product Prices Upload Images   Link 
Abraham Breuer 
9 months ago
you mentioned in the lesson that an update query is usefulto add 5% to active products.
my question is since there are orders which were orderedwhen the price wasn'tadded to 5% will they also be updated? if the answer is yes, this might mess up all passedorders with a wrong price
Richard Rost
9 months ago
Very good observation. Yes, that is true. This is one of the problems with building a "teaching" database. Right now, as you figured out, your orders are directly related to your products. So if you make a change to one of the product's details (like name or price) then all of your orders are effected. Later on, in Access Expert 22, I show you how to fix this by copying product details to the order detail table as you create the order. It requires VBA or a macro, which is why I cover it later. There's nothing wrong with the current method, except you cannot make changes to products once they're used on orders.
Add a Reply
McD Upload Images   Link 
Austin Ritner 
13 months ago
I actually laughed out loud at Ronald McDonald. Perfect!
Richard Rost
13 months ago
My goals are to educate AND entertain. :)
Add a Reply
Error Parameter Value Upload Images   Link 
Austin Ritner 
13 months ago
When I try to open all of the orders for a certain customer, it asks me to enter parameter value: Forms!OrderF!OrderID.

The WHERE condition on the button is CustomerID=Forms!CustomerF!CustomerID. Read More...
Richard Rost
13 months ago
Somewhere you must be asking for that OrderID. Walk me through your steps. Are you opening a FORM or a REPORT? Are you using the Wizard?
Austin Ritner
13 months ago
Trying to open a report and yes using the wizard. Trying to make it so I can view ALL orders for a customer in a report instead of the one thats currently open.

I copy/pasted the button onto the CustomerF and thats when it asks for the OrderID.

I've gotten it so that it's filtered to JUST the one on the screen, but it's just viewing all invoices for that particular customer that I'm having an issue with.
Add a Reply
Thank you for the effort you put into these videos Upload Images   Link 
John Terhorst 
5 years ago
Thank you for the effort you put into these videos! I have been enjoying following along while I am building my birdwatching database.
Add a Reply
Order2Q not OrderQ Upload Images   Link 
Michael Larsen 
6 years ago
Dear Richard
At 17:09 you say "Order2Q", but you go on to open OrderQ and modify that Query.
Just a slight slip of the tongue.

Thanks for an excellent course.

Michael
Add a Reply
Refresh Macro Upload Images   Link 
Bonnie Staib 
6 years ago
In Access 2013 Expert 13, just as lesson one is ending (at 26:12) Richard tests his browse between dates button. The prompt comes up immediately. My macro will only work if I remove the refresh record action in the macro. (Message: The Command or action "Refresh' isn't available now.")Once I remove the RefreshRecord action, it works fine. Yet, it looks like Richard did not have to remove this. Why would he be able to leave it in? Did anyone else have this problem? Read More...
Add a Reply
Just a point of clarification Upload Images   Link 
Chris Thompson 
6 years ago
Just a point of clarification. At 18:41 you say that "...the focus of the advanced classes is going to be on Macro design; but there are some things at are so much easier to do if I can just teach you a little bit macro editing...". Read More...
Add a Reply
CustomerT Upload Images   Link 
Kenny Nelson 
7 years ago
At 3:15, you are restoring your saved copy of the CustomerT.  To avoid the relationship issue, can you copy the Copy Of CustomerT2 back to the CustomerT?  I was afraid if I deleted the CustomerT it would affect my queries that used that table. Read More...
Add a Reply
Can you use the Choose function instead of the IIf Upload Images   Link 
Bruce Reynolds 
7 years ago
Can you use the Choose function instead of the IIf function? Is there a Choose function in Access?


Reply from Alex Hedley:

Yes there is and yes you can.
The CHOOSE function is covered in Access Expert 25. Read More...
Add a Reply

Show Older Comments...
View in Table Format

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

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

9/17/2021Overlapping Windows
9/16/2021Inventory
9/10/2021Benefits of Access
9/10/2021Access on a Phone
9/5/2021Prevent Deletion
9/4/2021Booklet Template
9/4/2021Inspections Template
8/29/2021What is Microsoft Access?
8/27/2021Union Query
8/24/2021Navigation
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
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
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access expert 13 update queries  Page Tag: whatsnew  PermaLink