3 hours ago: Our file storage provider is running maintenance today. If any videos don't load, wait a minute, refresh the page, and try again. Sorry for any inconvenience.  Dismiss
Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  

< 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: This class was recorded with Microsoft Access 2013. The material is valid for Access 2007 up to 2021. Access has not changed that much over the years.
Pre-Requisites: Access Expert Level 16 strongly recommended
Running Time: 1 Hour, 37 Minutes
Cost: $26.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
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
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"
Append "Order Writeoff" to ContactT
Put writeoff notice in contact table
Add OrderID to writeoff notice
Update the order description with "WRITEOFF!"
04. Archive Old Unpaid Orders 4 (13:02)
Delete orders and details from main tables
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

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


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

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

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/14/2024 9:04:03 AM. PLT: 0s