Access 2007-2019
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  
 
 
 

< 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
 

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
Change Email
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