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.
|
|
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.
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.
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.)
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.
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 |
