All 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
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)
Again, I'll get this posted up tomorrow. Waiting List orders for it will be processed tomorrow afternoon.
12 years ago
