Storing Duplicate Data
By Richard Rost
11 months ago
Store Historical Customer Addresses for Accurate Orders In this Microsoft Access tutorial, I will show you how to store duplicate data for customer addresses on order records to preserve historical address information when a customer's details change over time. We will cover why and when it makes sense to keep duplicate data, how to add address fields to your order table and form, and how to automatically copy the address from the customer form to new orders without using VBA. This is part 1. Grant from Ann Arbor, Michigan (a Platinum Member) asks: I followed your invoicing database setup, and it works great, but I realized it doesn't keep track of the customer's address at the time the order was placed. It just pulls the current address from the customer table. So if the customer moves or updates their info later, I lose the original shipping address for that order. I know you usually say it's not a good idea to store the same data in more than one place, but in this case, what's the best way to handle that? PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, store duplicate data, preserve historical address, shipping address at order time, billing address at order time, default value property, update customer address, order table address fields, copy address to order, normalized database exception, updating old orders, update query for orders, design view address fields, invoice address history, handling customer moves, copying address fields, synchronize address data, prevent invoice address changes, order form address, product price history, handling data changes over time, copying default values in forms, historical data in invoices, store customer info at order date, save shipping address on new order
Intro In this video, we'll talk about when it makes sense to store duplicate data in your Microsoft Access database, using the example of capturing a customer's address at the time an order is placed. I will show you how to add address fields to your order table and form, and use the Default Value property to automatically fill these fields with customer information for new orders. We'll also discuss the drawbacks of only storing current info, why sometimes normalization needs to be set aside, and how this approach helps keep an accurate history for each order.Transcript Today we're going to talk about when it's actually a good idea to store duplicate data in Microsoft Access.
Today's question comes from Grant in Ann Arbor, Michigan, one of my Platinum members. Grant says, I followed your invoicing database setup and it works great, but I realized it doesn't keep track of the customer's address at the time the order was placed. It just pulls the current address from the customer table. So if the customer moves or updates their info later, I lose the original shipping address for that order. I know you usually say it's not a good idea to store the same data in more than one place, but in this case, what's the best way to handle that?
Well, Grant, you are absolutely correct. The basic version of the invoicing database does not keep track of the customer's address at the time the order was placed, only the customer's main address.
So if you follow along with my invoicing database, you've got a customer form. The customer form has the address right here, but if you create an order for that customer, notice there's no address information here. So if you print an invoice when they purchase whatever, there's their address.
Now, let's say six months go by and they change their address to something else. Open them back up again. Oh, can you send me a copy of my invoice about my order six months ago? Sure, no problem. There's your invoice with the new address on it. And if you care about where this was shipped or any of that stuff, that could be a problem.
I generally tell people in the beginner classes, especially, you don't want to keep duplicated information in multiple tables in your database as you want to properly normalize everything, but there is an exception. That exception is time. If you care about that information at the time that whatever it was happened, whether you placed an order or a service call or whatever the information might happen to be, if you care about what the data was at that moment in time, then you want to store a duplicate copy of it.
Here we have the customer's address right now, but this can be changed. So what we're going to have to do is, when we create an order, we're going to have to copy that address to this order as well.
There are a couple of methods to do this. I'm going to show you one method today, and then I'll show you another method tomorrow in part two.
First, some prerequisites. If you have not yet watched my invoicing video, go watch this first so you understand what I'm doing. In the story, I show you how to build all this stuff.
Side note: I do cover this issue in the members video. That's one of the things I talk about. I talk about copying the customer's address to each order, so you know where the order was shipped or billed. I also teach you how to do a product list; you can pick a product and add it to the order because you'll have the same problem with products too if you've got a product table. If you've got prices today and you add them to an order, you don't want those prices changing in the future. So we do something similar in the extended cut for this, but I'm going to show you how to handle the address problem today.
Surprisingly, today we don't really need any VBA. We can use the method that we're going to cover today without VBA. Although, tomorrow in part two, the method I'm going to show you does require a little bit of VBA. So you might want to save yourself some time and go watch this first if you haven't watched it yet. If you want to learn some VBA, go watch this. It's about 20 minutes long and it'll cover everything you need to know to get started. These are free videos. They're on my website. They're on my YouTube channel. Go watch those and then come on back.
To get this address onto this order, when you place a new order, we can use the same technique we used to get the customer ID in here. This involves opening up the order form from the customer form. The thing that does that, when you do that, this guy can use the default value property.
The default value - I'm going to zoom in. Whoa, my zoom box is really big. Hang on, let me resize it. We can look at the Forms customer F, customer ID, to get that default value. We can do the same trick with the address fields.
Before we do that, we have to have some address fields first in order to put that data in. So we've got address information in our customer table. If we go to Design View here, we've got address, city, state, zip, and country. I'm going to select those fields. I'm going to just copy those. Control C.
Then we'll go to our order table, design that, and watch this: click down here and just paste those fields in. Now we've got a spot to hold those fields. I'm going to close those, save changes, yes.
If you look in here now, though, these should all be empty. They're brand new fields. So you're going to have to go through and update these old orders manually, or do an update query, or there are lots of other methods to do that. So this is only going to work with new orders moving forward. The method I'm going to show you tomorrow you can use to go back and do more easily with old orders.
While we're doing this, we're also going to steal these fields off of here. No sense in reinventing the wheel again. We're going to go to Design View. I'm going to copy all of these. Copy.
Let's go to the order form, which is down here, Design View. We need a spot to put these, just to drop them. I'm just going to delete the notes for now, and we'll just paste them in here. There we go. There's your address stuff for the order.
You might want the notes. Let's put the notes back. Here, I'm going to copy this notes box, click Copy, and then just paste that up here. They're named the same thing, so Access doesn't care. It just sees a box with the name notes. It doesn't matter. We'll just do that and stick the notes over here. There you go. As long as it works.
Now, how do we get that data from here to here? The same method we used here. Let's open this up again. I'm just going to copy this.
Let's go over here to the address field. Double click, go to Data, default value, where default value is right there. Paste that in, and this is instead going to be address. Then, we'll do the same thing over here with city. Default value: city. Move this over so we can get to it. There we go.
State: click state, zip code, whatever you call it in your country, and country. There we go. Save it. Close it. Close it. Close it. Open it.
Put a real address back in here, 101 Main Street, whatever. Now, going here, orders. On an existing order, it's still going to be blank. But if I go to a blank new order, look at that. It all defaults in there.
So, any new orders that you create, if I put an order date in here like today, now I've got the address at the time of the order. You can do this with two of these if you want, make a bill to and a ship to. In fact, I've got separate videos on that as well - there's a video on that if you want to check that out.
As I mentioned earlier, if you like to go in through the customer form first to place all of your orders, this will work just fine. But some people like to go in through the order form. So you maybe have a button on here and just open up the order form directly and have it open to a blank new one. Look at this, I'm getting all these pound name errors because that is an open. You don't see the pound name error here because the bound field, the first field, is actually hidden, so you don't see that.
In tomorrow's video, I will show you how to A) get rid of this pound name error, and B) I'll show you how you can pick from a list here, pick the customer, and it will fill in all this information for you. Not only get the customer here, but it will fill in the address. We'll cover that in tomorrow's video. So tune in tomorrow.
Wait, I'm going to announce in my best announcer voice: tune in tomorrow, same bat time, same bat channel. At least that's how I remember it when I was a kid. I haven't watched these in a long time.
Members, you can watch it right now because that's one of the benefits of being a member - you can watch stuff before it's released. I'm going to keep recording right at the moment here, so you should be able to watch it in just a few minutes.
That's going to do it for your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
TOPICS:
When to store duplicate data in Access databases
Tracking addresses at the time of an order
Problems with storing only current customer info
Adding address fields to the order table
Copying fields between tables in Design View
Updating existing records with new fields
Copying form controls between forms
Setting default values for address fields
Using the Default Value property for form fields
Auto-filling order address info from customer data
How the default value method applies to new records
Storing snapshot data like addresses in orders
Dealing with manual entry for old orders
Using both Bill To and Ship To addresses in orders
COMMERCIAL:
In today's video we're going to discuss when it's actually a good idea to store duplicate data in Microsoft Access, specifically how to capture a customer's address at the time an order is placed. I will show you the steps to add address fields to your order table and form, and how to use the Default Value property to automatically copy customer address info into new orders without any VBA. You'll learn why sometimes database normalization rules should be bent for real-world scenarios like tracking order history. In part two, we'll cover handling existing orders and fixing any errors that pop up. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. Why is it generally not recommended to store duplicate data in multiple tables in Microsoft Access?
A. It can lead to data inconsistency if the information needs to be updated
B. Microsoft Access does not allow duplicate fields
C. It increases database speed unnecessarily
D. It makes it harder to normalize the database
Q2. What is the main exception to the rule against duplicating data in a database, as discussed in the video?
A. To increase performance speed
B. When you need to store historical data that reflects the value at a moment in time
C. When you want to allow users to edit data more easily
D. If the database is very small
Q3. In the context of an invoicing database, why is it important to store the customer's address on the order itself?
A. To keep a record of the address as it was at the time of the order, even if the customer moves later
B. So that invoices can be printed more quickly
C. To make address updating easier for all past orders
D. To reduce the need for separate tables
Q4. If only the current address from the customer table is used on invoices, what problem could occur when customers update their addresses?
A. Old orders will all show the new address, losing track of the shipping location at the time of purchase
B. The invoices will fail to print at all
C. The database will generate duplicate invoice numbers
D. Old addresses will be merged with new ones
Q5. What is the method described in the video for copying address fields from the customer table to the order table?
A. Add address fields to the order table and set their default values to those from the customer form
B. Link the order table to the customer table using a relationship
C. Use an update query to synchronize all orders
D. Attach the customer address as a file in each order
Q6. What is necessary before you can set default values for address fields in the order form?
A. The address fields must exist in the order table
B. All orders must already be created
C. VBA code must be running
D. The customer table must be empty
Q7. What is noted as a possible downside to the "default value" method used in the video?
A. It only works for new orders going forward, not existing past orders
B. It causes Access to crash
C. It duplicates the entire customer record, not just the address
D. It prevents editing of orders
Q8. What is another scenario (besides addresses) in which duplicating data is valid in an invoicing database?
A. Keeping historical prices for products at the time of each order
B. Storing customer emails in multiple fields
C. Creating multiple IDs for each customer
D. Duplicating all notes fields
Q9. What alternative method is hinted at for dealing with past (already existing) orders?
A. Using VBA, as will be covered in the follow-up video
B. Manually re-entering data into each order
C. Printing all invoices and saving them as PDFs
D. Ignoring past data entirely
Q10. If you open the order form directly (not from the customer form), what potential display problem is mentioned in the video?
A. The address fields may show a pound name error
B. The form will always be blank
C. The notes will not be saved
D. The order will not be assignable to a customer
Q11. What does normalization in databases typically discourage?
A. Storing the same data in more than one place
B. Creating forms for each table
C. Using queries to retrieve information
D. Assigning primary keys to tables
Q12. Why might you want to create both "bill to" and "ship to" addresses on an order?
A. Orders might be billed and shipped to different locations
B. It improves database performance
C. It is required by Access templates
D. To satisfy all possible address formats
Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-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 TechHelp tutorial from Access Learning Zone focuses on an important exception to the general rule of database normalization: when it is appropriate to store duplicate data in a Microsoft Access database. This often comes up in scenarios involving historical data, such as capturing a customer's address at the time an order is placed.
The scenario begins with an invoicing database that works well for basic tracking but only stores each customer's current address. This poses a problem if you need to know the address at the time of a previous order, especially if the customer has since moved or updated their information. If you print an invoice for a past order, the address displayed will always be the most current one from the customer table, not the one valid when the order was originally created. For situations where knowing exactly where an item was shipped or billed in the past matters, this can be a significant issue.
Normally, in database design, I advise against storing redundant or duplicate data in multiple tables to keep the database normalized. However, the exception is when you care about the value of a field at the exact moment a record is created. In this context, to preserve historical accuracy, you absolutely should store a separate, duplicate copy of the relevant data, such as the address, at the time the order is placed.
To solve this, you need to add address fields directly to the order table. This allows each order to keep its own record of the address at the time it was created, even if the main customer address changes in the future. Here's how you can set this up using built-in Access features, without needing any VBA code.
First, make sure you have already set up the invoicing database as shown in my previous tutorials. If you haven't, I recommend watching that introductory video before proceeding, so you're familiar with the basic structure.
In the customer table, you likely already have fields like address, city, state, zip, and country. You want to create identical fields in the order table. This can be done by copying those field definitions from the customer table and pasting them into the order table design. When you do this, existing orders will have blank address fields, so you'll need to manually update older orders if necessary, but any new orders created from this point forward will have a place to save the customer's address at the time of the order.
Next, you also need to add these new address fields to your order form. To make things simple, copy the corresponding controls from the customer form and paste them onto the order form. You can move things around as needed, and even put any note fields back if you had to temporarily remove them during this process.
Now, the question is, how do you ensure that the customer's current address is automatically filled into the order at the moment of creation? One way to do this uses the Default Value property of the address fields on the order form. By setting the default value of each address-related control to pull from the customer form, when you open a new order tied to a customer, those fields will be filled automatically with the customer's address as it appears at that time.
This solution works best when you create orders by starting from the customer form. If you use the order form on its own, you might encounter some errors or blank fields, since the default value method depends on how the order is being created. Addressing issues like these and making the form more flexible is covered in further detail in part two of this series.
To summarize, storing duplicate data is generally something to avoid in Access, unless you need to preserve what that data looked like at a specific point in time for historical accuracy. Orders and invoices almost always present this scenario. This technique allows you to capture the customer's shipping and billing address as it was when the order was placed, ensuring your records are complete and accurate even if details change later.
For those interested, I also cover solutions for tracking product prices and similar situations in the extended cut of my members-only videos.
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 When to store duplicate data in Access databases
Tracking addresses at the time of an order
Problems with storing only current customer info
Adding address fields to the order table
Copying fields between tables in Design View
Updating existing records with new fields
Copying form controls between forms
Setting default values for address fields
Using the Default Value property for form fields
Auto-filling order address info from customer data
How the default value method applies to new records
Storing snapshot data like addresses in orders
Dealing with manual entry for old orders
Using both Bill To and Ship To addresses in orders |
