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 Advanced Level 2    dismiss
 
 

< Previous: Access Expert 16

Next: Access Expert 18 >

Access Expert Level 17

Expert Microsoft Access Tutorial - 1 Hour, 37 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 16 left off. In this class we will continue working with Action Queries. We will also do more with Union Queries, and we will begin working with Crosstab Queries, which are very similar to Excel PivotTables. We will create a project to tie all of the Action Query techniques we've learned together by archiving old unpaid orders from our system to a backup archive. Topics include:
 
  - Archive Old Unpaid Orders
  - Select & Mark Orders w Update Query
  - Create a Form to Review Orders
  - Append to Archive Table w Macro
  - Backup Customers, Orders, Details
  - Add "Write-Off" Notice to Customer Rec
  - UNION Query for Reporting on Both
  - More with Embedded Button Macros
  - CrossTab Queries (like PivotTables)

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access Expert Level 17
Description: Access Expert Level 17
Versions: Recorded with Access 2013. Also use with 2007 and 2010. The lessons on embedded macros are valid for Access 2007 and later only. Access 2003 users should get Access 223.
Pre-Requisites: Access Expert Level 16 strongly recommended
Running Time: 1 Hour, 37 Minutes
Cost: $24.99


This class picks up where Expert Level 16 left off. We are continuing our work with Action Queries and UNION Queries. We will also learn about Crosstab Queries, which are just like PivotTables in Excel. We will be pulling together all of the skills we've developed with Action Queries to create a system to archive old, unpaid orders. We'll move them from the order table to an order archive table.

Today we will begin by creating a macro that will automatically mark orders that are unpaid and older than 1 year as "To Be Archived."

to be archived

 

As a quick tangent, I will teach you about the differences between embedded button macros and database system-level macros. We'll use this to create a button on our Quick Access Toolbar that we can use to open up / jump to our Main Menu any time we want. It's a pain to always have to find the Main Menu in the navigation pane in order to open it - either if you've closed it, or if you have 10 windows open on top of it.

main menu

 

Back to business, we will customize the Order List form we created in a previous class to show just the orders that are marked "To Be Archived." This way we can quickly and easily review the list and uncheck any order we don't want being archived.

order list

 

We will then create two tables to hold our archived records: one for the order information, and another for the order details. We'll learn about the "duplicate output destination" error that sometimes comes up with append queries, and how to deal with it.

duplicate output destination

 

Next we will create queries to make backups of our Customers, Orders, and Order Details. It's a good idea to run these BEFORE running queries that will change your tables. We'll include these in the macro.

We want our sales reps to know if they pull up a customer's record and he has any previous orders that were written off (unpaid). So we'll create more queries to add a "Write-Off" notice to the customer's notes and we'll add this as an event in his contact history as well. This will involve an update query (to append text to a single field) and an actual append query.

customer write offs

 

Now that we've backed everything up, given the user the opportunity to review the changes, and appended the records to the archive table, we're ready to actually delete the records from the order and order details table. We'll review the delete query FROM and WHERE clauses, create a button on the order list form to launch the macro, and we'll learn about the new CloseWindow command to close the form after the macro is finished.

delete query macro

 

There may come a time when you want to see ALL of the customer's orders - both the current ones and the archived "write off" orders. In that case we'll need a UNION query to bring them all back together again into one recordset.

union query

 

Now that we're all finished with the Archive Old Unpaid Orders project, we'll start on a new topic: Crosstab queries. These are very much like PivotTables in Microsoft Excel. They allow you to take a whole bunch of data and summarize it based on one or more fields. For example, you can say, "show me all of my sales totals broken up by date on the vertical axis and state on the horizontal axis," and you'll end up with something like this:

crosstab query

 

This is the 17th class in the Access Expert series. There's a lot of great material in this class. Learning Action, Union, and Crosstab Queries will add tremendous power to your databases. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 17

00. Intro (9:05)

01. Archive Old Unpaid Orders 1 (13:30)
Add ToBeArchived y/n Field
Create ToBeArchivedUpdateQ Query
Button to Archive Old Orders on Main Menu
Database Level Macro Primer
Macro to Open Main Menu
Putting Macros on Quick Access Toolbar

02. Archive Old Unpaid Orders 2 (21:22)
Make OrderListF Show All Order
Add ToBeArchived to Order List Form
TBA Tool Tip
To Be Archived checkbox on Order Form
Macro WHERE Condition
ToBeArchived=TRUE
Create OrderArchiveT
Create OrderDetailArchiveT
Set Autonumbers to Long Int
Append Orders
Create AppendOrderDetailArchiveQ
Create AppendOrderArchiveQ
Duplicate output destination
Create OrderToBeArchivedQ to avoid error

03. Archive Old Unpaid Orders 3 (21:27)
Backup tables with MakeTable query
MakeCustomerBackupQ
MakeOrderBackupQ
MakeOrderDetailBackupQ
Calculated columns are not allowed in SELECT INTO statements
Another reason I don't like calculated table fields
Remove DaysToShip from OrderT
Update customer record "Order Writeoff"
UpdateCustomerOrderWriteoffQ
Append "Order Writeoff" to ContactT
Put writeoff notice in contact table
Add OrderID to writeoff notice
AppendOrderWriteoffQ
Update the order description with "WRITEOFF!"
UpdateOrderWriteoffQ
04. Archive Old Unpaid Orders 4 (13:02)
Delete orders and details from main tables
DeleteArchivedOrderDetailQ
DeleteArchivedOrderQ
Copy and modify append queries
Delete FROM and WHERE
Make button on TBA form to perform archive
Copy button with macro in it
CloseWindow macro command
Macro commmand to Close Form
UNION query to show orders and archive together
SQL View
Fields must match exact with UNION and * all fields

05. Crosstab Queries (13:16)
What is a Crosstab Query?
Similar to an Excel PivotTable
Show example of a PivotTable
Add some new orders
Edit OrderListQ Add State Field
Create Crosstab Query
Crosstab Query Wizard
Built Crosstab Query Manually
Set up Helper Query
Orders by Month by State
OrderByMonthByStateCrosstabQ

06. Review (5:38)

 


 
Keywords: Multi Query Macro, Make Table Query, Union Query, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, archive old unpaid orders, macro, customize quick access toolbar, make table query, where condition, append query, update query, write-off notices, delete query, union query, crosstab query
 
 

Student Interaction: Microsoft Access Expert 17

Richard on 12/10/2013:  Microsoft Access Expert Level 17 is 1 hour, 37 minutes long. In this class we will continue working with Action Queries. We will also do more with Union Queries, and we will begin working with Crosstab Queries, which are very similar to Excel PivotTables. We will create a project to tie all of the Action Query techniques we've learned together by archiving old unpaid orders from our system to a backup archive. Topics include: - Archive Old Unpaid Orders - Select & Mark Orders w Update Query - Create a Form to Review Orders - Append to Archive Table w Macro - Backup Customers, Orders, Details - Add "Write-Off" Notice to Customer Rec - UNION Query for Reporting on Both - More with Embedded Button Macros - CrossTab Queries (like PivotTables) Click here for more information on Access Expert Level 17, 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 16. The next class in the series is Access Expert 18.
Brian Merrick on 1/19/2014: Do you cover how to create a multi user login in Access? And to track the user's transactions in Access?
Brian Merrick on 1/19/2014: Never mind my last question? I found the answer.
John M on 2/4/2014: Microsoft Access Expert 17 Video:2 at the 15:19 time index you ran into the Duplicate Out Put Destination Error for ToBeArchived field. you later explain that in the Append Query each field needs to be listed separately if you are using one of the fields as Criteria. I have found in my working with Access that by removing the Append To reference to the duplicate field (The one used for Criteria) it works just fine with no error
John Miller on 2/5/2014: Access 2013 Expert 17 Video:2 at 15:19 time forward you ran into the Duplicate Out Put Destination Error for ToBeArchived field. you later explain that in the Append Query each field needs to be listed separately if you are using one of the fields as Criteria. I have found in my working with Access that by removing the Append To reference to the duplicate field it works just fine with no error
CHARLES FULGHAM on 1/24/2015: Mr. Rost, I have taken every class starting from Beginner-1 through here Expert-17. I must say, Expert-17 is hands-down my favorite so far. I feel a major breakthrough has occurred for me personally. Just thought I'd say Thank You! I am excited about the remainder of the expert series and eventually the advanced/developer series' as well. Again, just wanted to say THANKS!

Reply from Alex Hedley:

Glad you're enjoying it Charles.
I love when it hits you that things are sinking in and your understanding is sticking.
Hope you enjoy the rest of the series and I too am looking forward to the A/D Series, that's when the real fun starts with Programming.

Michael on 3/27/2015: Richard,

Since you are always encouraging us to send you ideas for future courses, here are a couple of ideas for your consideration. First, you might consider putting together a seminar on queries with particular emphasis on multiple table queries and all the things that can go wrong. We've used multiple table queries throughout the Expert series, but I think that it would be really good to take a deep dive into what is happening behind the scenes inside of Access - having this insight would make users much more confident I think. I would include an entire session on bad queries - for example, ambiguous outer joins and Cartesian products. I think there is just so much you can do with queries to make a solid class or seminar.

Along the same lines, I think you could make a class covering validation. It's one thing to build a database, but as you know, a database without good validation rules in place (for example, the ship date can't be before the order date; the invoice date can't be before the order date;) is just a recipe for disaster. You could easily use your order table for some of these things.

Food for thought. As always, thanks to you and Alex and the entire team working behind the scenes to bring us these great videos and fantastic instruction.

Reply from Alex Hedley:

Have you seen the Outline for the SQL Seminars?
I can't remember if all of those are covered but they would be a very addition if they aren't.

Validation Rules are covered in Expert 28.

Thanks for the kind words and suggestions, it's a pleasure being able to help out.

vicki Hudson on 5/22/2015: When I try to run my MakeCustomerBackupQ I get the "You are about to Modify...", click Yes, and I get error message "Multi-valued fields are not allowed in SELECT INTO statements." I notice it did not create my CustomerBACKUPT. Help

Reply from Alex Hedley:

Is there an Attachment Field in your Query?
Have you created a Lookup Field in your Table?

vicki H on 5/23/2015: The [Picture], bitmap image/OLE Object or the [Website] & [Email] hyperlinks in the CustomerT. Are those attachments? No lookups in the Table.

Reply from Alex Hedley:

Did you add the Picture one yourself?
It's advised to have a path of the Picture you use in the DB and not an image stored as this bloats the DB. Open Other Programs Seminar and Imaging Seminar.

Try removing the Picture Field from the values you want to back up.


Wayne Markel on 7/7/2015: John Miller is correct
 

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