Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
Access 22 Recorded
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   7 years ago

Access Expert 22All of the videos are recorded, but I haven't put it online yet. I'll get to that tomorrow morning. There are only three lessons, but it's over two hours long (yeah, I went with fewer long lessons instead of breaking them up, sorry). The major topics:

1. Fixing 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. I'll show you how to split it up and put customers in the customer table and orders in an order table.

2. Update Vendor Pricing. Take 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.)

3. Fix the Order Entry System. I wasn't initially planning on covering this TODAY, but it's been bothering me for many months now. 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.

Here's the full outline:

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)

Again, I'll get this posted up tomorrow. Waiting List orders for it will be processed tomorrow afternoon.

Access 22 Recorded Upload Images   Link  
Richard Rost 
7 years ago
UPDATE: The videos are all online and you can order the course now. I'm still working on the outline.
Add a Reply
Access 22 Recorded Upload Images   Link  
Deon R 
7 years ago
Hi Richard,

Sounds like just the right medicine for the coming week - a good dose of Access :)



Reply from Richard Rost:

Richard Rost Likes This. :)
Add a Reply

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

You may want to read these articles from the 599CD News:

12/3/2021Access Beginner 2
12/3/2021Access Beginner 2 Lessons
12/3/2021Access Beginner 2
11/30/2021Import Multiline Cells
11/30/2021Adam's Access Games
11/29/2021Prevent Close
11/22/2021Currency Symbols
11/13/2021Access Developer 36

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
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
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn