Access 2007-2016
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 21

Next: Access Expert 23 >

Access Expert Level 22

Expert Microsoft Access Tutorial - 2 Hours, 3 Minutes
 
 
In Access Expert 22 we will learn how to fix non-relational spreadsheet data that we have to import into our database, creating multiple related tables. We will also learn how to import vendor price updates for our products, and we will fix our order-entry system so that product price changes no longer affect previously placed orders. Topics include:
 
  - Fixing Non-Relational Data
  - Split a Spreadsheet into Multiple Tables
  - Update Vendor Price Sheet Changes
  - Create Macro for One-Click Operation
  - Fix Our Order-Entry System
  - Product Changes No Longer Affect Orders
  - Build New Button Macro From Scratch
  - IF, GotoControl, GotoRecord Commands
  - Manually Add Products Not In Database

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 22
Description: Access Expert Level 22
Versions: Recorded with Access 2013. Also use with 2007 and 2010. Access 2003 users should get Access 308 for the lessons on importing, and Access 302 for the lessons on the order entry system.
Pre-Requisites: Access Expert Level 21 strongly recommended. This class builds upon the order entry system developed in the earlier classes. Access Expert 1 through Expert 8 are strongly recommended.
Running Time: 2 Hours, 3 Minutes
Cost: $26.99


Today's class only has three lessons, but they're good, long ones. We will begin by learning how to fix non-relational data. This happens when someone sends you a spreadsheet and you've got, for example, customers and order info all packed into the same flat-file sheet.

fix non-relational spreadsheet data

 

I'll show you how to split it up and put customers in the customer table and orders in an order table, as it should be.

customer order sheet split

 

Next, we'll learn how to update the prices of the products in our database based on spreadsheets that our vendors send us. Take the regular price updates that your vendors send you, import the data into your database, match up the products, and update your UnitCost fields accordingly... all with one click (once you build it, of course.)

update vendor price sheet info

 

Finally, we'll fix our order entry system. The problem is that our order entry system is currently tied into the product table, so any changes to the price of a product are reflected in your orders! This is very bad, and we've talked about this before. So in this class I'll show you how to make a button macro to copy the price information from the product table and set it in the order detail table so the orders are completely separate from the products. We'll build an embedded button macro from scratch for the first time.

fix order entry system

 

This is the 22nd class in the Access Expert series. There are a lot of good, practical examples of how to use the import techniques of the previous few classes in this one - plus a lot more. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 22

00. Intro (9:09)

01. Fixing Non-Relational Data (24:25)
Create Bad Spreadsheet
Import Sheet into Access
Aggregate Query for Unique Customer List
Customer Query with Most Recent Info
DLOOKUP Most Recent Address & Phone
Remove Customer Data from OrderT
Add CustomerID Foreign Key
Query to Change CustomerID
Delete Automatic CustomerID Join
Create New Join on Customer Name
Make Table Query to Create CustomerT


02. Update Vendor Pricing (41:41)
Price Sheet Changes from Vendor
Create Spreadsheet with Product Costs
Import Data into Access
Link Our Data with Vendor Data
Joined Query is Too Complex for Update
Make a Simpler Query
Discontinued Products have Null Price
Create Macro to Automate Import of Data
Make Button to Run Import
ImportExportSpreadsheet
RunSavedImportExport
03. Fix the Order Entry System (42:21)
Current DB alters orders on UnitPrice change
Add ProductName, UnitPrice to OrderDetailT
Fix the OrderDetailQ
Unbind Product Combo Box, Move to Footer
Add ProductID, ProductName
Lock ProductID
Unlock UnitPrice
Create Embedded Button Macro
Build Event > Macro Builder
IF Macro Command
MessageBox Macro Command
StopMacro Macro Command
GoToControl Macro Command
GoToRecord Macro Command
Show All Commands Button
Potentially Unsafe Macro Commands
SetValue Macro Command
Set SalesTaxRate with IF Statement


04. Review (5:28)

 


 
Keywords: Fixing Non-Relational Data, Update Vendor Pricing, Fix Order Entry System, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, Import new price sheet data, create macro to automate import process
 

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