Introduction

Welcome! Fix Data, Vendor Prices & Orders

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 22. In this course we will learn how to fix non-relational data from spreadsheets by splitting and importing it into proper tables, update vendor pricing in the product table using data from Excel sheets, and address a problem with the order entry system where changing product information unintentionally affects previous orders. We will also discuss the prerequisites from earlier Access levels that are important for this class, go over resources available on the website, and review recommended ways to follow along with the lessons for the best learning experience.

Navigation

Prev: X22
Next: Lesson 01

Keywords

Access Expert, fix non-relational data, split spreadsheet data, import data into tables, update product pricing from vendor, action queries, match vendor price sheet, order entry system fix, macro for order form, store order item prices, AccessExpert Leve

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 22, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class only has three lessons, but there are three good long lessons that cover a lot of important topics.

First, we are going to learn how to fix non-relational data that someone else may give you. I am sure it has happened before. You get a spreadsheet with some data in it, and the data has customer data and order data all mixed together in the same spreadsheet. It is a list of orders, perhaps, and each order has customer information, and it is just not relational, it is a big mess.

So, in lesson one, we are going to learn how to fix that. We are going to split the spreadsheet up into order information and customer information. We are going to import that into our database and put all the stuff in proper tables.

Next, we are going to learn how to update the pricing in our product table based on an updated price sheet from our vendors. You have a vendor or two that you work with. Once a month they send you an Excel spreadsheet that has updated pricing. We are going to import that pricing into our database, match up each of those products with the products in our product table, and update our unit costs accordingly.

Finally, we are going to fix a problem with our order entry system. Way back in the first couple of Access Expert classes, we built the product table, product form, and an order form with order details. Now, the problem was when you picked a product and added it to an order, it was linked back to the product table. So if you made any changes to that product, including the price, it had the side effect of changing the price on all of the orders, which of course is bad.

Now, at the time, we did not know how to change values across multiple tables, and today I am going to teach you how to do that using a button macro. We are going to pick a product on the order form and then have a macro set the value of that product, the name, the ID, the unit price, whether it is taxable, into fields in the order detail form. This will fix that problem and give us the side benefit of being able to just type in any value we want if you want to be able to enter manual items into orders.

This class follows Access Expert Level 21. Over the last couple of classes, 20 and 21, we spent a lot of time with importing data. The lessons on fixing non-relational data and updating vendor pricing are going to rely heavily upon that information. So I strongly recommend you watch Access Level 20 and 21 before watching this class.

The order entry system was primarily built in Access Expert Level 8. So lesson 3 on fixing the order entry system is going to rely on Access Level 8. And of course, Level 8 relies on 1, 2, and 3 where we cover relational database design. More was built in Levels 9 and 10 as well. So really everything from Levels 1 through 10 is important for the order entry system.

Once again, my class is really designed to be followed one after the other. Skipping around is not recommended.

I will be using Access 2013 for this class. All of the lessons should work just fine with 2007 and 2010. If you are using Access 2003, I cover the importing section in Access 308. I cover this stuff on fixing the order entry system in Access 302, because 301 and 302 start teaching VBA. In the older system, we did not use macros for much.

Today I am going to use an embedded form macro which runs right inside of a button. Access 2003 did not really have good macros, so I almost never used them. We used VBA for that. I am going to teach the VBA for this in the Developer Series when we get to there.

But for now, the macros work just fine. However, if you want to learn the VBA and jump ahead of the class, you can pick up Access 301 and 302.

My courses are broken up into Beginner, Expert, Advanced, and Developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the Expert level classes, which you are in now. When you finish all the Expert level classes, the Advanced classes will cover event programming and macros, and the Developer classes will cover Visual Basic for Applications.

Each group of classes is broken down into multiple levels, Level 1, 2, 3, and so on. In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more.

You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student form for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning, or tell me what is wrong in the student forum and I will do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is real easy to use.

Now I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database.

One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson one, we are going to learn how to fix non-relational data. This is useful when someone, for example, gives you a spreadsheet full of data that may contain information that belongs on multiple sheets, but they put it all together in one spreadsheet. We will learn how to use some action queries to break that information up into two or more proper tables.

In lesson two, we are going to cover updating vendor pricing in our database. Each month, our supplier gives us a spreadsheet with updated pricing. We are going to make a one-click routine to update our unit costs in our database based on their spreadsheet pricing.

In lesson three, we are going to fix a big problem that we have had with our database ever since we created the order entry system. The problem is that if we change the pricing of products in our product table, it affects all of the orders in the database. And of course, that is not good. So in this lesson, we are going to take some time and finally fix that problem.

Quiz

Q1. What is the main problem with non-relational data in a spreadsheet when importing into Access?
A. Customer and order data are mixed together without proper separation
B. Data is unavailable for import
C. All records always have missing fields
D. Only order information is present

Q2. In lesson one of this class, what do you learn to do with non-relational data?
A. Split data into appropriate tables for customers and orders
B. Delete redundant information
C. Automatically generate customer IDs for all records
D. Export the data back to Excel

Q3. What scenario is described for updating product prices in lesson two?
A. Vendor sends an Excel spreadsheet with new pricing monthly
B. You manually update each price in Access
C. Product prices are never updated
D. Access automatically downloads vendor updates

Q4. What is the main goal when updating vendor pricing in your product table?
A. Match products in the vendor sheet to your database and update unit costs
B. Delete all existing prices before importing
C. Create new tables for each price change
D. Notify users of price changes without modifying the data

Q5. What issue arises in the order entry system if you link order details directly to the product table?
A. Changing product prices alters all previous order prices
B. Orders cannot be placed for discontinued products
C. Orders are duplicated in the database
D. Product names are not displayed on orders

Q6. What is the solution implemented in lesson three to fix the order price changing problem?
A. Use a button macro to copy product info into order detail fields when selecting a product
B. Only display read-only product info on the order form
C. Prevent any price updates in the product table
D. Always ask the user for the correct price before saving

Q7. Which of the following is NOT recommended as an approach while learning from these video lessons?
A. Skip ahead to advanced material without reviewing previous classes
B. Watch a lesson completely through before trying it yourself
C. Build the sample database along with the video instructions
D. Use the student forum for questions if you get stuck

Q8. Which previous Access Expert levels are particularly important for understanding this lesson?
A. Levels 1 through 10, especially Levels 8, 20, and 21
B. Only Level 21
C. Only Levels 19 and below
D. Levels 15 and 16 only

Q9. Why does the instructor recommend using sample databases from the website?
A. So you can study and learn by analyzing a working example
B. There are no instructions in the video
C. It is required for certification
D. Access cannot build databases from scratch

Q10. What programming approach is used in this class for the button that copies order information?
A. Embedded macro in the form
B. External application code
C. SQL Server triggers
D. VBA module only

Q11. If you want to learn the VBA equivalent for macros shown in this class, which series should you take?
A. Developer series (Access 301 and 302)
B. Beginner series (Access 1 and 2)
C. Seminar on SQL
D. Advanced Reports series

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Microsoft Access Expert Level 22. I am your instructor, Richard Rost.

This class is made up of three main lessons that each focus on solving practical and often-requested database problems.

First, I will show you how to deal with non-relational data that you may have received from someone else. I am sure many of you have gotten spreadsheets where customer and order information are all jumbled together in a single sheet, instead of being separated into the logical tables that a relational database would use. In the first lesson, I will walk you through breaking apart that sort of combined data into the relevant categories - order information and customer information - and then importing both into separate, well-structured tables in your Access database.

Next, we will address how to update product pricing based on a new price list from your vendors. If you regularly receive pricing updates in Excel from suppliers, you know that it can be tedious to make sure your product tables always reflect those changes. I will demonstrate how to import the latest price sheet into Access, match each entry against your existing products, and then update the unit costs in your product table.

The third lesson focuses on correcting a flaw in the order entry system we built earlier in the course. When you added a product to an order, the record was tied to the product table. This caused a major issue: if the product price in the product table changed at any point, all of your historical orders would also reflect the updated price, which is definitely not what you want. Previously, we did not cover how to update fields in multiple tables at once, but now that you are ready for more advanced features, I will show you how to use a button macro to solve this problem. From now on, when you select a product on an order, Access will copy the relevant details such as product name, ID, price, and taxable status into the order detail record, preserving those details for that order. This approach also allows you to add manual items to orders if needed.

It is important to note that this class builds on previous material. The lessons on importing data and fixing non-relational information rely on skills taught in Access Expert Levels 20 and 21, so be sure you have studied those before working through this class. The order entry system was originally created back in Expert Level 8, and relies on concepts from Levels 1 through 10, especially regarding database relationships and forms. My courses are designed to be completed in order, and skipping ahead can lead to confusion.

I am using Access 2013 for these examples, but the content is also compatible with Access 2007 and 2010. For those of you still using Access 2003, relevant lessons on importing data can be found in Access 308, and the order entry fix is in Access 302. In those versions, I relied more on VBA since Access 2003's macro system was fairly limited. I will be covering the VBA approach in detail in my Developer Series, but macros will work fine for now.

My classes are structured into four main groups: Beginner, Expert, Advanced, and Developer. You should already have a good handle on all Beginner-level topics before starting these Expert-level classes. Advanced classes dig deeper into event programming and macros, while Developer classes will introduce you to Visual Basic for Applications. Each group is divided into multiple levels so you can follow the learning path in a logical sequence. In addition to the standard course levels, I also offer seminars covering specialized topics, such as web database design, calendar forms and reports, database security, handling images and attachments, work orders, accounts payable, SQL, loan amortization, and more. Full details on these seminars are available on my website.

If you have questions about what we cover in these lessons, I encourage you to post them in my student forums. If you are watching this course through my website, you will find a form for each lesson where you can view and contribute questions, comments, and answers. Even if you are not watching from my site, you can still join in by visiting accesslearningzone.com/forums.

For best results, I suggest that you watch each lesson all the way through before trying anything on your own computer. Once you have seen the whole lesson, go back to the beginning and follow along with my sample database, step by step. Focus on mastering the class example before trying to adapt what you learn to your own projects. If you have trouble or get stuck, review the video once more, or post your question in the forums and I will try to help.

Approach the learning process with an open mind. Access can look overwhelming at first, but the more you practice, the easier it becomes.

I strongly suggest you build the database I use in the lessons as you watch, but if you would prefer, you can download a completed copy of my sample database from my website at accesslearningzone.com/databases. Sometimes the best way to learn is to explore how someone else's database works. That is actually how I learned Access years ago myself - by dissecting Microsoft's Northwind Traders database.

Here is a brief overview of what I will cover in the three lessons of this class:

First, we will tackle fixing non-relational data. If you receive a spreadsheet where information that belongs in several different tables is all combined into one, I will show you how to break it apart using action queries to create well-structured tables.

Second, we will address how to keep your product pricing current when receiving regular updates from vendors. I will show you how to automate updating your product table using the suppliers' monthly spreadsheets.

Third, we will finally resolve the lingering issue in our order entry system where changing product details retroactively affected all completed orders. I will teach you how to store relevant product information with each order so your historical data remains untouched by changes in the product list.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Fixing non-relational data from Excel spreadsheets
Breaking up mixed data into separate tables
Importing customer and order data into Access
Using action queries to split data
Updating product pricing from vendor spreadsheets
Importing updated pricing into product tables
Matching vendor products with internal products
Updating unit costs using action queries
Correcting order entry system pricing issues
Storing product details in order detail records
Using macros to set values in forms
Allowing manual entry of product details in orders

Article

Welcome to this Microsoft Access Expert tutorial, where we will tackle three crucial topics for working with data in Access: fixing non-relational data, updating vendor pricing, and solving issues with order entry systems. All of these are common challenges that anyone working with business databases will likely encounter. My goal is to guide you step by step through handling each of these situations effectively.

Let us start with the problem of non-relational data. Often, you might receive a spreadsheet from another department, a coworker, or even from a vendor you work with. This spreadsheet could contain multiple types of information all mixed together in one huge list, for example, customer data along with order details in the same table. This is what we call a flat or non-relational data structure. If you were to import this directly into your Access database and start using it, you would quickly run into problems like duplicated data, inconsistent records, and difficulty running reports or updating records.

To fix this, our task is to separate the customer information and order details into their own tables. Begin by importing the spreadsheet into Access as a temporary table. Examine the data and determine where each field really belongs. For instance, columns like Customer Name, Address, and Phone Number should go into a Customers table. Fields such as Order Number, Order Date, and Product should go into an Orders or OrderDetails table. You will need to use action queries to create new tables from the original flat data. Make an append query to add distinct customers to the Customers table and ensure each customer gets a unique CustomerID. Then, use update queries to attach that CustomerID back to the corresponding orders in your temporary table. After you have your customers and orders separated and properly linked, you can delete the original imported spreadsheet table. At this point, your data is in a relational format, which makes it much easier to manage moving forward.

The second common problem is updating product pricing in your database based on the latest price sheet your vendors provide. Imagine you get a recent Excel sheet from your supplier every month with new prices for the items you are selling. You need a quick way to update only those products whose costs have changed, while leaving the rest untouched. To do this, first import the updated price sheet into Access as a temporary table. Next, create an update query that joins the product code or SKU from the imported sheet with the same field in your Products table. Set the query to update the UnitPrice or Cost field in your Products table, based on the new value in the imported data. You will typically use a query like this:

UPDATE Products
INNER JOIN TempPricing
ON Products.ProductCode = TempPricing.ProductCode
SET Products.UnitPrice = TempPricing.UnitPrice

This query matches up the products based on the product code and updates the prices accordingly. Be sure to back up your data first and spot-check a few records to make sure the updates worked as expected. Once finished, you can remove the temporary data table.

Now let us address an issue you may run into when building an order entry system with related tables. In the early stages of building a database, you might have a setup where your OrderDetails table references the ProductID, and pulls in the product name, price, and other details using relationships. The downside to this approach is that if you update the price or any other field in your Products table, all existing orders automatically reflect the new values, even for historical orders. This can create serious problems for your records and financial reporting.

What you really want is to preserve the original price and product details for each order, so that once an order is created, its details are locked in, regardless of future changes in the Products table. To solve this, you need to store a copy of the product details directly in the OrderDetails table at the time the order is placed. This way, each order keeps its own snapshot of the relevant details.

Here is how to implement this in Access. Open your OrderDetails form in Design View and add fields for ProductName, UnitPrice, Taxable, and any other product attributes you want to save for each order. Next, in your form, use a button or AfterUpdate event on the product selection combo box to run a macro or VBA code that copies values from the selected product record into these fields.

If you are using a macro, you can use the SetValue action to set each field. For example, after selecting a product, the macro would do:

SetValue [ProductName], [Products]![ProductName]
SetValue [UnitPrice], [Products]![UnitPrice]
SetValue [Taxable], [Products]![Taxable]

If you want to use VBA instead, you can put something like this in the AfterUpdate event of the product combo box:

Me.ProductName = Me.ProductCombo.Column(1)
Me.UnitPrice = Me.ProductCombo.Column(2)
Me.Taxable = Me.ProductCombo.Column(3)

This code takes values from the selected product in the combo box and writes them directly into fields in the OrderDetails record. You would adjust the column numbers based on the columns you are pulling from your combo box.

With this setup in place, once a product is selected for an order, all the relevant information is written directly into the order record, protecting it from future changes in your Products table. This not only fixes the pricing problem, but also allows you the flexibility to manually type in values for special cases, such as custom orders or unique services.

In summary, transforming non-relational data into a proper table structure ensures clean data management and ease of reporting. Automating vendor price updates streamlines keeping your inventory current. And copying product details into your order entry system at the moment of entry guarantees historical accuracy for all your transactions. By understanding and applying these techniques, you will make your Access databases more reliable, efficient, and professional. Be sure to practice these steps in a sample database before applying them to your live data. If you get stuck or need help, reviewing your work and experimenting with different queries will deepen your understanding and confidence in using Microsoft Access for all your data management needs.