2 days ago: Power and Internet are out in my office due to Hurricane Ian. Click for details.
Free Lessons
Fast Tips
Topic Index
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
Access 22 Recorded
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   8 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.

Comments for Access 22 Recorded
Age Subject From
8 yearsAccess 22 RecordedRichard R
8 yearsAccess 22 RecordedDeon R


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.

Check out these other pages that may be of interest to you:

9/27/2022Report Border
9/26/2022First Day of Month
9/23/2022Before Update
9/22/2022Synchronize Subforms
9/20/2022Link Master Fields
9/18/2022Sequential Annual Coding
9/15/2022Resize Forms

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
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
PermaLink  Access 22 Recorded