Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

< 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: Recorded with Access 2013. Also use with 2007 and 2010. Access 2003 users should get Access 308 for the lessons on importing, and Access 302 for the lessons on the order entry system.
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: $26.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

Student Interaction: Microsoft Access Expert 22

Richard on 6/2/2014:  Microsoft Access Expert Level 22 is 2 hours, 3 minutes long. In this class 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 Click here for more information on Access Expert Level 22, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 21. The next class in the series is Expert Level 23.
B on 6/8/2014: Great class Rick. Here's a question (using the class' updating vendor pricing as an example)-what do you do if the vendor's product codes aren't unique enough for you? For example, you have two vendors, and they both have a product 003, and you don't want to manually tweak the spreadsheet to include your autonumber for the product id etc. To solve this, I've been using a concatenation for the product id (for example, their product code concatenated with the company id). Is there a better way?

Reply from Richard Rost:

This is why I recommend having a separate import for each vendor's price updates. They're likely going to have different fields or field names. Unless they're EXACTLY the same and using something universal like a UPC code, then this is probably the best solution for you. Perhaps eventually everyone will get onboard with the same XML-based data set (at least for your business) but until that day it's best to have separate imports, IMHO.

Roger Jeffrey on 6/9/2014: Expert 22
Great class.

Glad you added Update Products button with a macro on Main Menu.

Yes, I am interested in seeing a lesson on Appending new products from a Vendor to ProductT via import.

Reply from Richard Rost:

Got it. Thanks.

Landry L on 6/27/2014: Hi Rick,
Do you have a time frame for the release of the 2013 version of Access300 series?
Thanks, Jim

Reply from Richard Rost:

My goal is to try and release one new Access class a week. I'm behind schedule right now. Just had to spend the past week moving video files to a new server (still not done with that yet). I expect to have the next Expert class out by the end of the month (so, Monday). I've probably got about 4 or 5 more Expert classes, then I'm going to guess about 10 to 15 Advanced classes (macros and events), and THEN I'll start the Developer classes.

Wayne Ayotte on 7/4/2014: At time Index 21:28 in Expert 22 Lesson 3 you say you don't use the "Action Catalog" much. I have never used it because I don't know why I would want too. Just wondering why/how would I use it or what it's for.

Reply from Richard Rost:

It's just a list of all of the menu commands broken down into "type" categories. I honestly don't use macros enough to need this (I mostly program in VBA) and the macro commands I do need are easy enough to remember. I'll be spending a lot more time covering macros soon, so perhaps we'll start to use it more. Microsoft is trying to shift more towards macros again because they work better with Web Apps. They have a history of "now we're going to use macros... oh, forget macros, now it's all about VBA... oh, now we're back to macros..." LOL

Lynda Chase on 9/9/2014: Hi Richard,

When I add the GoToControl Macro - to point to controlName ProductID, I get an error message saying: There is no field named 'ProductID' in the current record. I've double checked spelling, and clicked on the ProductID box in the subform to verify its' spelled correctly. Can you advise where else I might look.


Lynda Chase on 9/9/2014: I have again had a 'blonde' moment, I put the ProductCombo box in the OrderF form footer, rather than the subforms form footer. I'm such a time waster, many apologies.


Reply from Richard Rost:

I'm glad you're able to figure these all out yourself. That's the best way to learn.

Sara Ahlers on 9/11/2014: I also would be interested in seeing a lesson on appending new products to your product list. I would also like to see a warning regarding a negative profit situation (where your cost is higher than what your sales price is).

Great series!

Sara Ahlers on 10/28/2014: I was wondering why it was necessary to delete all the existing orders (05:20). What would happen if you left them alone? Would the changes made to the database 'break' the existing orders? Thanks!
Richard R on 11/9/2014: It just made things easier to start over. The old table ONLY had the ProductID without the supporting details (price, etc.) That information was linked to the Product table. Now, that information gets copied into the record. You'd have to go back through ALL of the old records and update them with the current information if we chose to keep them all. I've tried to show the evolution of database design, and sometimes when you learn a new way of doing things, you have to radically redesign your database.
Betti Baldan on 3/21/2015: Richard,
This is not a class question. My son has a property management business and they use a program called Condo Manager. I see that it's an Access database. I'm just curious to know if you're familiar with it, or even may have worked on it.
Betti Baldan

Reply from Alex Hedley:

I've not come across it before but never had the need.
Anyone else?

Pramod on 4/1/2015: Need Learn MS Access.

Reply from Alex Hedley:

You can order any course using the order form.

Jeffrey Ervin on 4/23/2015: Hi
Having Problems with the dlookup function, just cannot get it to work. At 13:00 Fixing non relational.

Reply from Alex Hedley:

Can you share your code please

Jeffrey Ervin on 4/24/2015: Address: DLookUp("Address","Sheet1","Customer=""" & [Customer] & """ AND [Order Date]=#" & [MaxOD] & "#")

I got this to work but only when messing around with order dates from the table sheet1, for eg. I had to make the date for Customer "XYZ Inc" at "1001 Starship' more recent then the one at "900 Box St". I followed the course to the letter or so I think. Twice.

Reply from Alex Hedley:

Does DLookUp("Address","Sheet1","Customer="'" & [Customer] & "'" work ok?

Are your Dates just Dates or do they include Times as well as this will change how you need to search for a date.
DLOOKUP can also be picky as to the format of the date, are you mm/dd or dd/mm

I usually use single quotes (') and not double quotes (") when writing this as double double quotes can get annoying.

Jeffrey Ervin on 4/25/2015: Hi Dlookup is not working to well, I have followed this part of the course to exactly as is in the video, the dates are just dates the format is d/mm/yyyy changed it to dd/mm/yyyy then back. I moved onto to lesson two, same problem, is possible the database has become corrupted?

Reply from Alex Hedley:

I've had major problems with DLOOKUP and the date format, I think it's due to Access expecting it in mm/dd format.
I format the date before passing it to the function and it seems to work well.

Jeffrey Ervin on 4/26/2015: I now have the Dlookup working in "Update Vendor Pricing", I done this by adding the "ID" Field From "VendorXProductT" to Query "ImportProductPricingXYZWholeSaleQ" Looking back on the video It seems I did miss the part where he added the ID field in, so I will go back on the the previuos lesson and its more then likely an error on my part, spent a lot of time on this part of the course, the upside is, I do now know the Dlookup Function inside out and a few off the problems that can come with it, this one needs to be obsolutly spot on.

Reply from Alex Hedley:

Yeah it's a pain but as you say great way to learn!

Wayne Markel on 7/17/2015: I used a different approach. I made sure the Excel sheet was sorted by order date before importing. Let Access assign primary key as ID on import. Used MAX(ID) instead of MAX(Order Date) to create the Customer list with latest data, then joined to Sheet 1 to get the most current address and phone. Does anyone see a downside to this?
Spiros Poulis on 9/21/2015: I am interested too, in seeing a lesson on Appending new products from a Vendor to ProductT via import. I see above the same inquiry of other students and I would like to inform me if there has already been created this lesson or it is going to be created in the future.
Brian Jensen on 1/15/2016: Tip for everyone-
At 24:13 there is an easy way to get around this small issue to have a vender name sorted by its ID.
If you were to add the VenderT to your query and link it to your import query with the IDs being related you can then
add in the Vender Name instead of the VenderID and then filter by the criteria of "XYZ computer Wholesale" in the venderName field.
This prevents any future changes to venderID messing up other queries down the line.

Reply from Alex Hedley:

It's unlikely that the VendorID changes if it's a Primary Key but filtering on Name might be acceptable but what if the name changes.

Ryan Weemhoff on 3/11/2016: Hi. Thanks for this lesson. Regarding the OrderDetailF: Is it possible to still have a ProductComboBox in the line item of the Detail section instead of the Form Footer with an button

Reply from Alex Hedley:

Yes you can have combos in the detail section

Ryan Weemhoff on 3/11/2016: Hi. Thanks for the lesson. Regarding the OrderDetailF: Is it possible to have a ProductCombo in the line item of the Detail section instead of in the Form Footer (with an "Add Product" button) and still have the same result, i.e., the product price is no longer directly tied to the ProductT. If yes, where/when do you teach this? Advance lesson? Thanks.
Ryan W on 3/14/2016: Hi Alex. Thanks for your reply. I actually submitted a comment/question twice by accident. On the first one I accidentally pressed enter instead of backspace, submitting it before my question was complete. My second comment/question was more detailed. Is it possible to possible to have a ProductCombo in the detail section that is not directly tied to (or live with) the ProductT such that price changed in the ProductT do not effect all the orders? And if the answer is yes, when/where is this taught? In advanced/VBA lessons?

Reply from Alex Hedley:

I think its 302
How to get a value from a Combo box and put it into another Field.
You would need to create another Field in the Table that is static, then have an AfterUpdate on the Product and put that value from the Product chosen into your Purchased Fields. You could do this with some Forms!FORMNAME!FIELDNAME = x etc


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


Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP