Free Lessons
Fast Tips
Topic Index
Home   Courses   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  

< 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: 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 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: $27.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
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


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: 2/24/2024 1:54:55 AM. PLT: 0s