Import New, Changed Records
By Richard Rost
6 years ago
Import New and Changed Records. Update Pricing from Vendor.
In this lesson, we will take a product sheet that was sent by our vendor and import new products and price changes into our Access database. We'll learn how to create a permanent link to an Excel workbook. Review all the price changes and new products. Then create an update query to change our prices, and an append query to add new products.
Aaron from Brisbane, Australia (a Gold Member) asks: I get an updated product list in an Excel spreadsheet each month from my main vendor. I would like to import their data into my Access database, update unit cost for any existing products, and add new products to my table. Can this be automated? Right now it takes me an hour to type everything in each month.
Members
I'll show you how to also check for deleted products and automate the entire process (all three queries) with ONE CLICK. We will also learn how to work with product markups so if the vendor's price changes, your sales price will be updated accordingly.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
Linking to Excel Data: https://599cd.com/acx20
Outer Joins: https://599cd.com/OuterJoin
Update Query: https://599cd.com/UpdateQuery
Append Query: https://599cd.com/Append
Intro In this video, I will show you how to import new and changed records from an Excel spreadsheet into a Microsoft Access database. We'll cover how to link to your vendor's price sheet, review and update existing product prices, and identify and add new products to your product table using queries. I'll demonstrate how to set up your tables, create review and update queries for price changes, and build append queries to bring in new products, making monthly updates quick and efficient.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor.
In this video, I am going to show you how to import changes, import new and changed records from a spreadsheet. Let's say, for example, you sell a particular product, and once a month you get a spreadsheet emailed to you from one of your suppliers. You want to be able to instantly and easily go through, review price changes, review new products, and review discontinued products.
In this lesson, I'll show you how to do all that with a couple of clicks. Today's question comes from Aaron from Brisbane, Australia, one of my gold members. Aaron says, I get an updated product list in an Excel spreadsheet each month from my main vendor. I would like to import their data into my Access database, update unit cost for any existing products, and add new products to my table. Can this be automated? Right now, it takes me an hour to type everything in each month.
Of course, Aaron, that's one of the benefits of Access as you can automate data imports, and updates, and all that stuff. We can do this with just a couple of queries. Let me show you how.
Let's start off by opening up my blank customer template. This is a free download. You can grab it off my website. I'll put a link down in the description below. You don't have to use my database. Any simple database will do.
Now, let's start by creating our product tables. This will be the table in our Access database that we use to put all of our products in. So, create, and then table design. Of course, we'll have a product ID, which is an autonumber. We'll put in the stock number, which is what the vendor uses to call their product ID. It's alphanumeric, so we want to make sure this matches theirs, the type of data. If they are using just numbers, you can use numbers, but usually they use text.
We have, of course, the product name. We've got a unit cost, which is what we pay to buy this from the vendor. If you also want to store your unit price in here, your sale price, that's fine too. You are going to buy it from them for one price, and you are going to want to resell it to your customers for a higher price. It's also nice to track the date updated. That will be a date/time value, and we'll update that every time we perform an update for the products from their spreadsheet. That's how we know how fresh our product information is.
You will have whatever other information you want in here for your product database - your quantity on hand, your reorder level, your notes, pictures, whatever other information you want to have about your product. Let's save this as our product T, our product table. Let's just put one product in here to start. I am going to look at their product sheet. Let's take a look at their product sheet real quick.
This is what I get from the vendor. I made something up, of course, but let's pretend this is what they give us every month. Open it up. Here is their product sheet, the way that it comes in email from them.
Let's just put one of these guys in my database so we've got something in there. I am going to match this one. There's the stock numbers right there. I can copy that, put it in my stock number, the product name, copy that, drop it in there. Their unit cost, which they have listed as price, that's fine. Paste that in there. What do I sell it for? Maybe I sell it for 19.99. The update date: Ctrl+semicolon puts today's date in there. So just one. We will get the rest of them automatically in a minute. But that's how you want to match up the data. There's your product ID, their product ID, which they call stock number, or whatever.
It is best for the import to make it easier if you match your field name to theirs, but you don't have to. Product name, which you may or may not want to automate the import. I will automate this when I add new products, but then I am going to make it so I can change it. The only thing I really want to update is their cost, the unit cost, which they call price.
Now, what we want to do next is, I don't want to import their spreadsheet every month. What I want to do is create a link to it. I am going to save this on my computer. The first time I get it, right now, it's sitting on my desktop. Put it somewhere where you want to keep it full time because we are going to link to it from our database. When they send us a new copy, we are just going to replace that file.
Right now, I have got it sitting here on my desktop, price sheet.xls. You can put this wherever you want it. Just make sure it's going to be in the same spot. If you move it or rename it, you will have to update the link in Access. Just put it on your server somewhere where you know where it is going to be.
Let's create a link to it in our database. I cover creating links to Excel spreadsheet files in my Access Expert 20 and 21 classes. There is importing and linking all different kinds of sources, all different kinds of tricks. I'll put a link to that in the description down below too if you want to learn more.
We are going to go to External Data. Under Import and Link, we are going to go to New Data Source - From File - Excel. Now down here, we have a couple of choices. I want to link to the data source by creating a linked table. When you import it, you just pull in the data one time and create a brand new table. When I link to it, I am basically pointing a finger at that file and every time that file is updated, my Access database gets the updated copy. It is just looking at what is in the file right now every time you run it. That is the benefit of using a link.
You can import it if you want to. The rest of the steps in this example will work just fine if you don't link to it. But by creating the link, you don't have to keep doing the import. You just do it once and then you are done.
Pick the file, browse, it's going to be on my desktop and right up here, price sheet, and then hit open. The first row contains the column headings, which it does; if it doesn't, turn that off or it will assume that's a product then. What do you want to call it? I'll call it my price sheet, not product price sheet. My fingers are doing something different from my brain, and it happens all the time. Then hit finish.
Finish linking. Now, see it right there? A little Excel icon. Open it up. There it is. You can resize this and you can make a form around this if you want to preview their stuff. Close this. It will just save layout changes.
Now notice if you see here, for example, Trouble Hair Gel. It's 14.99. Let's say I go and get an updated product and then I open that up again. Open up the sheet. Let's change this to 15.99. Save it. You have to always save this in between. If you are making the edits yourself, make the changes in Excel, save it, and then I recommend closing it because sometimes the link does not work well if that file is open.
You are basically going to get this from your vendor and save it to your drive. Maybe open it up and take a peek at it. Make sure everything's okay. Make sure they have not made any major changes to the structure of it. Then go to your database. Now you can peek at it from in here. See, it changed to 15.99. Now we can use this linked table, this linked spreadsheet, just as if it was a table in our database.
The next thing I want to do is review changes to the existing product. I have got the product already in my product table, in this case, the Trouble Hair Gel. The first step is going to be review updates - review any price changes.
Let's create a query. Create - Query Design. I am going to start with my product table. Their spreadsheet will be under the links section here; even though it's a table, it's a linked table. Bring that in. Now we have to join these together. Access doesn't always make the join because this is an external table, so we are going to link my stock num to the stock num over there. That join type is just fine.
We are going to run this query first just to see all the products that the prices have changed. Bring in the information you want to see: my product ID, my stock number, the product name; maybe bring in their product name next to it so you can see if they have changed any product names. Then bring in your unit cost and their price. That's the same thing.
Let's save this as my product update review Q because we are just going to review the changes to the prices before we make any commitment, before we make any changes ourselves. Go ahead and run it. You will see all the products that are in your table already and then any differences in prices. At this point, you are going to see all of the prices for all of the matching products. We only want to see the ones where they are different.
Down here under unit cost put: Not Equal To (that is a greater than and less than symbol together) and then price. That means this has got to be different from that. Otherwise, you do not want to see all of them, if there are 5000 products in here, because you would have to go manually down the list and see which ones are different. This says just show me the different ones. Save it. Run it. You still see that one, of course, because of the difference in prices.
Now, why don't you see all of the products from both tables? Well, that's the join type. I just did another video a couple days ago explaining that. This says you have to have a matching record in both tables for that to appear down here. If you have got customers and orders, and you have a customer with no orders, he will not show up. You have to turn this into a left join, which we are actually going to do this with the next query. Go watch that if you do not know about the different join types. I have a video called Outer Joins. I'll put a link to it down below.
This is our review query to review the product changes. Save that. Now we've looked over the data. Now we want to actually commit the changes. Let's copy this guy. Copy, paste. We already got it kind of set up. Let's call this one the product update Q, the actual one that's going to do the updating. Design this guy.
Now we've already got the join set up here, so this is going to show us just the records where the unit cost is different. Let's change this now to an update query. If you've never done an update query, again, I've got videos on how update queries work. They basically change the data in a table. Go watch that if you have never done an update query before.
Now all we really need to keep down here is just this. We are going to update to price. Just like that. Show me all the records from both tables where the stock number matches and the unit cost is different from the price. What am I going to do? Update it to price. Save that. Close it. Now run it.
You won't be able to update these changes. Say yes. You may get another warning message that says you are about to update one row. Hit OK. I have my warnings turned off. How do you do that? It's under File, Options, Client Settings, and then right here it says Confirm Action Queries. I turn that off. I find those annoying, but you may want to leave them on. Cancel that for now.
Now let's run our review query again. We got nothing. What happened? We updated the price. If I look at my product table now, look at that. It updated. The unit cost is now $15.99. My unit price is what I charge; that hasn't changed. This was $14.99; now it's $15.99 because it saw there was a different one.
Let's test another change. Let me open up the spreadsheet again. Sometimes you will get this: Access database cannot open or write this because the database is still open and it's trying to get access to the spreadsheet. It is best to close this instead of minimizing it, but that's okay. It shouldn't matter.
Now come back in here and change it to $9.99. Save it. Close the spreadsheet. Open Access back up again. Let's take a look at the price sheet T. Look at that. It's got the updated price there. Let's run our review query. There it is. It's different. Now let's run our update query. You won't be able to undo this. Hit OK. Now check your product table. There you go. It's updated.
That's how you check for product changes with existing products. Now what do I do about the new stuff? It's going to be a little trickier. Again, we are going to take advantage of the join type in our query to show the stuff that doesn't have a matching record in the other table. Review that outer joins video if you have not yet.
Create - Query Design. This time I am going to start by pulling in the price sheet T. That's got all of the records in it. The next table is going to be the product table. Join these together, stock num to stock num. Let's bring in the stock num, the product name, the price. Let's bring in the product ID over here, just so you can see how they look when they're matched.
And let's save this as my product new review Q, the new stuff. We are going to review the new stuff. Now run it. I only get the one record, and it's already in there. Why am I getting this data set? Because this shows only the stuff where there is a matching record in both tables. If I want to see all of the products from the vendor spreadsheet, then I have to make this an outer join. Double click on this and go number two: Include all records - that's why it's capitalized - all records from the price sheet T and only those records from product T where the join fields are equal. In other words, show me all of these guys, and if it happens to match up to one of these, then go ahead and show that one too. That's what that little arrow is for. Save it and run it.
Now I see all of these guys, and if one of them happens to match up, there it is right there. There's the product ID. Now I can see which products are new, the ones that I don't have in my table. They have a null product ID, and we can use that by coming in here for product ID and saying criteria is null. Just like that.
Now when I run it, look at that. Those are all the products that do not exist in my table. Save this. That's the product new review. Now I can go through and review all the new stuff that's not in my database.
Now let's take this guy and make the actual append query. An append query is when you add records from one table to another. We are going to add the ones that are new, that show up here, into my product table. That will be a second query. Again, we will base it on this one. We have already got most of the work done. Copy and paste. Let's call this product new append Q.
Now let's change this guy into an append query. Again, if you haven't watched my append query video, go do that now. Come back here. The nice thing is it will save your spot. Let's design this one. Change this to an append query. It's going to ask us where do we want to append the data to. We are going to append it into my product table. Hit OK.
Notice the Append To fields appear down here. Stock number will go into my stock number field. The capitalization doesn't matter. Access doesn't care. Access is not case sensitive. Product name goes into my product name. That's one of the reasons why I said it's best if you match up the field names. If yours are different, that's okay. You can change it. For example, their price is my unit cost, so I have to manually come down here to Append To and put in here unit cost.
Now, product ID is not being appended. They don't have it. It's a new field for me, so I have to get rid of this Append To product ID. Take that out of there. Otherwise, it will generate an error message. They don't have a product ID. I still need to check and make sure I am appending only products that are null, any products that don't exist.
One more field we have to add, and that's going to be my date updated. I don't want to pull the date updated out of this field, so do this: type this in - x: Now(). Just like that. Or if you want it to the minute and second, type in Now(), it will tell you exactly when you updated it. What that does is it says create me a brand new field, a calculated value called x that is equal to Now(), which is right now, the now function. Put that in the Date Updated field. We should probably put that in our update over here too. I forgot about that. We will do that as well.
Save that. Close it. Now run it. Nothing appears to happen, or you might get the warning messages. Access is about to append five records to your table. Now let's take a look at our product table. Look at that. There they are. It's too small. There we go. Stock number, product name, unit cost came in, unit prices are all zero. You will have to go through and update those, or you can set that to a standard markup if you want to. I will show that in the extended cut. This video is getting kind of long already.
Members, I will show you how to set a markup value. You can automatically mark up new products, like a default markup of 10 percent or whatever. Let's go and put that date updated in our update query as well. Save changes. Yes. Go to the update Q design view. We are going to update date updated to Now(). Save it.
Let's go make a change. Minimize. Open this guy up. You get this warning message. The Trouble Hair Gel, back to 19.99. Let's add a new product. Let's put in 29.99 for that one. I should see an update for that product, and I should see a new product. Save this. Close it. Go back to the database.
First, we'll do an update review. There is one product that's changed in price. Let's run the update. Let's check the product table. Looks good. And it got the updated date and time - 26th. Yep. That's correct. It updated that.
Now let's check for new products. The new review first. Worst betleth competition video. That's betleth. There's probably an apostrophe in there. Close that. And now do the new append query. Now let's check the product table. There it is, right there. Now you have to go update your prices.
There you go, Aaron. There is how you import changes from your vendor's product table into your product table.
Want to learn more about this stuff? There is a 30 minute extended cut for members. I will show you how to put some buttons on your main menu - these guys right here. We will have buttons to review the price changes, review new products. I'll show you how to check for discontinued products, which products are in your database but are no longer showing up in the spreadsheet that the vendor sends us. Then I'll show you how to process all of these things with one click.
So you have done your reviews, everything looks good, now you're ready to make the changes. We will automatically make the price changes, pull in the new products, delete products out of your database. I don't recommend that step, but I will show you how to do it because you may still have the products in inventory that you don't want to just take out of your product list. But I will show you how to do it in case you do.
Then we'll make a product form. We will deal with markups so when their product price changes, their unit cost changes, it will look at your markup and adjust your price accordingly. I put a little function in there so that all of your prices will always end in 99 cents. So depending on what the markup is, it will always figure out the markup and then round it up to 99 cents. I will show you how to do that too.
With this form, you can also change the products in here manually. If you change the cost, it will update the price based on your markup. If you update the price, it will change what your markup is if you want to manually adjust your price. Or if you change your markup, it will then recalculate your price as well.
Lots to do. I will cover all of this in the extended cut for members. Silver members and up get access to all of my extended cut videos, and there are lots of them.
How do you become a member? Click on the join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. If you click the join button, you will see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they will always be free.Quiz Q1. What is the main benefit of linking to a vendor's Excel price sheet in Access, rather than importing the data each month?
A. It gives you a backup copy of the spreadsheet every time
B. It allows instant, dynamic access to the latest data every time the file is updated
C. It automatically creates forms and reports based on the Excel spreadsheet
D. It prevents accidental data changes in the Access tables
Q2. Which field should be used to join your product table to the vendor's price sheet when updating pricing information?
A. Product Name
B. Product ID (Autonumber)
C. Stock Number (the vendor's product ID)
D. Unit Cost
Q3. In the context of this tutorial, what is the purpose of a "review query" before running an update or append query?
A. To provide automated updates to all fields without user intervention
B. To preview changes and ensure you're only updating or adding intended records
C. To import all vendor records regardless of matching fields
D. To create a printable report for the vendor
Q4. What does an update query do in Access?
A. Adds new records to a target table
B. Removes records from a linked spreadsheet
C. Modifies data in an existing Access table based on specified criteria
D. Creates a backup copy of the table before making changes
Q5. How can you identify new products from the vendor's sheet that are not currently in your product table?
A. By matching products with identical names
B. By finding records where Product ID is null after joining tables
C. By checking for missing prices in the vendor sheet
D. By sorting by unit cost and comparing row by row
Q6. What is an append query used for in this import process?
A. Updating existing prices for products already in your table
B. Adding records from the price sheet that do not exist in your product table
C. Deleting discontinued products from your database
D. Creating new Excel sheets for export
Q7. Why is it helpful to match your Access table field names to the vendor's spreadsheet columns?
A. It prevents Access from crashing
B. It ensures that Access automatically recognizes and maps fields during linking or importing
C. It allows you to add more fields than necessary
D. It forces Access to make all fields required
Q8. What is the significance of using the "Now()" function in the append or update query?
A. It generates a random stock number for new records
B. It fills the Unit Cost with the latest price
C. It records the exact date and time when a product was updated or added
D. It formats the price fields to currency
Q9. If the linked Excel file is moved or renamed, what do you have to do in Access?
A. Nothing, Access always finds the file automatically
B. You must update the link in Access to point to the new location
C. Change the filename in the Product Table
D. Remove and re-install Access
Q10. Why would you use an outer join when reviewing or appending new records from the vendor's price sheet?
A. To display only the matching records in both tables
B. To include all records from your Access table only
C. To ensure all records from the price sheet are displayed, including those not in your table
D. To exclude records that have been updated
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-B; 7-B; 8-C; 9-B; 10-C
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 focuses on how to automatically import new and updated records from a vendor's product list spreadsheet into your Microsoft Access database.
Suppose you regularly receive a price sheet from your supplier in Excel format and want to efficiently review product changes, price adjustments, discontinued products, and add any new items to your own database. Manually entering this information each month can be very time-consuming. Fortunately, Access makes it possible to automate these updates with just a few queries.
I'll walk you through the general process, starting with how to set up your product table if you do not already have one. You don't have to use my sample template, but if you'd like, I offer a free blank customer database that you can download from my website.
Your product table in Access should include an AutoNumber ProductID, your vendor's StockNum (usually an alphanumeric identifier), the ProductName, UnitCost (this is your cost from the vendor), UnitPrice (what you plan to sell it for), and a DateUpdated field to track when the product info was last refreshed. You can also add any other information that's relevant to your products, such as quantity on hand, reorder level, or notes.
It's helpful if the field names in your Access database match those in your vendor's spreadsheet, but it's not necessary. The key is having a common field, like StockNum, that both systems can use to match products.
Rather than importing a new spreadsheet each time, a better approach is to create a linked table in Access that points directly to the vendor's Excel file. This way, whenever the file is updated or replaced, Access automatically sees the latest data. Make sure to keep this file in the same location each month; if the path changes, you'll need to update your link in Access.
To link the spreadsheet, use the External Data tab in Access and set up a new link to the Excel file. Choosing to link, rather than import, gives you a live connection to the vendor's changing data. You can then work with this linked table just like any other table in your database.
Start by setting up a simple product or two for testing purposes. Enter values from the vendor's spreadsheet to make sure your data matches up, especially the StockNum.
Once the link to the spreadsheet is in place, the first step is to check for updated prices. Create a query that joins your product table to the linked spreadsheet on StockNum. Include relevant fields from both tables, such as ProductID, StockNum, ProductName, UnitCost, and the vendor's Price. To review which products have changed in price, add a criterion so you only see records where your UnitCost does not equal their Price.
This review query allows you to look over any differences before committing changes. When you're ready to update the prices, copy this review query and switch it to an Update Query. Configure it so that, for products where UnitCost is different from the vendor's Price, the UnitCost will be updated to match the vendor's current Price. You may want to update the DateUpdated field at the same time so you know when each product was last refreshed. After running this update query, your prices for all current products will reflect the vendor's latest prices.
To identify new products that appear in the vendor's sheet but aren't yet in your table, create another query using an outer join. This will show all records from the vendor's spreadsheet and only the matching records from your product table. Filter the results for cases where the ProductID (from your table) is null; these are the new products your database does not yet contain.
Next, create an Append Query based on your new products review query. This will copy all new items from the vendor's sheet into your product table. Map the vendor's column names to your field names. For fields like UnitPrice that do not come from the vendor (because you resell at a markup), you can either leave them blank and update afterward or set a default value. For DateUpdated, use the Now function to mark when the product was added. Again, only append products where the ProductID is null to avoid duplicates.
At this point, your database is set up to quickly review and update existing product prices as well as import any new items with just a few clicks. Each month, you only need to replace the vendor's spreadsheet with their latest version and run your review and update queries.
In the extended cut for members, I also demonstrate how to add buttons to your main menu for a more user-friendly import process. I cover how to check for discontinued products by identifying any items in your database that are missing from the latest spreadsheet. There are also instructions on batching all these steps into a single automated process. Additionally, I show how to implement automatic price markups—setting pricing policies so your sales price always reflects the input cost plus your desired markup, and even how to round all prices to end in 99 cents for consistency. You can also adjust prices and markups directly from a product form, and the calculations will update automatically.
To learn more about updating, appending, and marking up your products in Access, you can find my complete video tutorial with detailed, step-by-step instructions on the Access Learning Zone website at the link below.
Live long and prosper, my friends.Topic List Creating a product table structure in Access
Linking an Excel spreadsheet as a table in Access
Reviewing and matching field names for import
Setting up joins between Access tables and linked spreadsheets
Creating a query to review price changes between tables
Filtering queries to show only records with changed prices
Converting a select query to an update query in Access
Running update queries to modify product prices
Automatically updating a date field with Now() in update queries
Reviewing outer joins to identify new records
Creating a query to find new products not in the Access table
Changing join types to perform left joins for new record detection
Filtering queries to show null values for new products
Creating append queries to add new products from spreadsheet
Appending calculated date values with Now() in append queries
Testing the import and update process with example data |
