Multiple Addresses
By Richard Rost
20 months ago
Storing Multiple Customer Addresses in Microsoft Access In this Microsoft Access tutorial, I will show you how to efficiently manage multiple addresses for a single customer, essential for businesses dealing with various shipping and billing locations. We'll explore different storage techniques, one-to-many and many-to-many relationships, and address sharing to prevent data redundancy and reduce mailing costs. Perfect for storefronts needing dynamic address management solutions. Jared from Wilton, Connecticut (a Gold Member) asks: I run a small store in a small town and I have some customers who want things sent to their home, or their office, or even sometimes their summer homes. How do you go about easily tracking multiple, and I'm talking sometimes six or seven, addresses per customer? Then, to make it even more difficult, sometimes I have multiple family members in my system separately. They have the same address, but now I'm sending multiple copies of my flyers to them, and it's wasting money. So, how do I handle this? MembersThere is no extended cut, but here is the database download: 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!
PrerequisitesUp Next
Keywords TechHelp Access, multiple addresses per customer, address management techniques, address table design, subform creation, one-to-many relationship Access, many-to-many relationship Access, shared address handling, address data entry efficiency, eliminating redundant mailings, relational combo boxes, address form setup, customer address tracking, advanced Access tutorials, configuring customer subforms, avoiding duplicate addresses, historical address tracking, zero programming database setup, Access database customization
Transcript
Today we're going to talk about multiple techniques for storing multiple addresses for a customer or multiple customers in your Microsoft Access database. We're going to talk about a couple of different techniques. Which one is best for you? Well, that all depends on your business.
Okay, here's a question that recently came in from Jared from Wilton, Connecticut, one of my gold members. But I get questions like this all the time and they're constantly coming up in the forums. In fact, there's a really good one right now in the forums. Here's a pretty good one from Donald. You can see the kind of conversations that we sometimes have in the forums. They go on for a bit. But, and I'll put a link to that one down below.
The questions usually go something like this: I run a small store in a small town and I have some customers who want things sent to their home or their office or even sometimes their summer homes. How do you go about easily tracking multiple, and I'm talking sometimes six or seven addresses per customer? Then to make it even more difficult, sometimes I have multiple family members in my system separately, they have the same address but now I'm sending multiple copies of my flyers to them and it's wasting money. So how do I handle this?
And yes, sometimes you will have different people that share an address and you don't want to send things to them separately. But me, for example, I had a case where I had several different businesses registered that I ran. You know, I had the PC business, then I had a consulting business, then I had a training business and they're all separate businesses, but they have the same address. And so I would sometimes get those free flyers that computer companies were always sending them out in the 90s. And they would jam my PO box full with multiple copies of the same thing that I'm sure this company was wasting money on. So having shared addresses in the system sometimes can be helpful.
So let's talk about the different things that might come up, the different scenarios that you might have with people and addresses, and how you might want to store those in your database and again, I'm going to make sure everyone knows upfront, this is all based on how it's best for your business. I'm going to show you multiple techniques; there is no one right technique. Okay, you pick the one that works best for you and go with that. In fact, in my beginner classes, I only show the simple techniques and then as we get a lot more advanced, we'll talk about different ways that you can store addresses but there's no one right way.
Alright, so the basic, most simple thing you might have is one person, one address. This is what I teach in my beginner level one class, right? You just got a customer table, you store the address in there. I like to break it up into multiple fields right? Address, city, state, zip, country. I've dealt with businesses where they need to have even more granularity than that. They've got address broken up into the number, the street name, and then the type like street, drive, avenue, that kind. So again, it's all completely based on your business. This is what I think works for most people. And a single address might work for, you've got one person and I say up to three addresses. Three is kind of like my rule of thumb. It's not a set limit. In fact, personally, I stick to no more than two but I'll allow three. That's what I tell people in my beginner classes. Because you might have bill-to and ship-to. That's the most common thing. Bill me at this address, ship everything to this address. And that will get you by. If you got two addresses in the customer table, not a big deal. You got a bill-to address, bill-to city, bill-to state, ship-to address, and so on. Honestly, this is what I have in my database because when I first started my current company, right, Computer Learning Zone, I used to ship a lot of CDs. So I had the billing address which is what the credit card was billed to and then I had the shipping address where the CDs go to. Now I don't need that ship-to address more; I don't ship products anymore right, all online now so I could get rid of it but it's still in my database and it's not hurting anything and again this will work for a lot of businesses just fine but now when you start talking about more than three addresses all right you might have a home address, office address, ship-to address, a PO box, or like Jared said, he's got customers with six or seven addresses in the system. Here's my summer home, that kind of stuff. Now it's time to break those addresses off and put them in a separate table with a one-to-many relationship. You're going to have your customer table, and you're going to make an address table. The address key will have all the address information in it, plus you'll add the customer ID, so you know which customer belongs to that address. Then you can make a subform, right, and you can say, okay, here's all the addresses for this particular customer. And again, this is great, this will work for a lot of different businesses. You can put an address type in there, and I'm going to show you how to do this. We're going to walk through building these in a minute. And you can put an address type in there. What is it? Is it the bill to? Is it the ship to? You could put dates in there, like when is it valid from and to. Lots of different options. Anything related to the address would go in the address table. And you'd find out what customer it is by linking back to the customer key.
Now here's where it starts to get tricky. It's when you have multiple people with different addresses, but some of those addresses might be shared. And you want to say, okay, if that address is already in the database, I want to use it. Okay, because the situation like for example where Jared mentioned, where he's got four or five customers, they're all separate customers in his database, but they all share the same address, multiple family members, let's say. Okay, and you want to know that, OK, this address is already in there. It makes it easier to do data entry, too, especially in a small area, because if you just start typing in the address, and oh, it's already in the list. You just pick it from a combo box, where it's easier to search from. So this is an option for some businesses. And again, this is the most advanced option. And I am going to walk you through building this. But you'll need a many-to-many relationship with three tables. You'll have a customer table, you'll have an address table, and all that's in the address table is that address. Address, city, state, that's it. It's got its own ID. Then you'll have a junction table to link the two together. This customer with this address. So, an address can have multiple customers at it, and a customer can have multiple addresses, and it can go back and forth. So you got our guy here who's got his different four addresses but his wife's also in the system and she's got the same home address and her separate office address. So why would you want to do this? Well, shared address utilization eliminates redundancy, makes it easier to find addresses. Cuts down on mailing costs, like I mentioned earlier. It's easier to track historical address data, whether you use a one-to-many or a many-to-many.
OK, that works great. Flexibility for different address types, what we talked about, error reduction in data entry. The address is already in the system, you just pick it from a list. OK, now, good news and bad news. Good news is this is an expert-level class, meaning it's not a developer-level class. We can do all of this with zero programming. But the bad news is, it is an expert-level class. The first couple of techniques I'm going to show you are beginners, but you're going to need to know relationships, specifically one-to-many, and many-to-many relationships, which are kind of difficult. So we've got a bunch of prerequisites I want you to watch first before you try tackling the more advanced methods I'm going to show you in this address video series. So we'll start off with the basics. If you haven't watched my blank template video, go watch this first. That's where I show you how I build my simple database, customer form, main menu, all that stuff and all my preferences. Of course, if you have the time and you haven't watched my TechHelp Access Beginner 1 class and you need to learn the basics, go watch that first too. That's absolutely free. It's on my website. It's on my YouTube channel, it's four hours long, it teaches you everything you need to know to get started. Make sure you have good working knowledge of the different types of relationships. This is an important video, go watch this one. You should know how to make a form with a subform. We're going to be using subforms to store the addresses and a subform on the customer form. Now here's the tricky one, many-to-many relationships. This one trips up a lot of people. Go watch this video first. The example that I give in this one is customers with courses, enrolling people in courses, because a customer could have multiple courses, and a course could have multiple customers or students in it. But this applies to lots of different things. Email addresses, phone numbers, you name it. There's lots of reasons why you'd want to have many-to-many relationships, and they are the trickiest one to implement. And since they are so tricky, and they do throw a lot of people for a loop, I'm going to have this is another good example of when you want a many-to-many relationship. And I'm probably going to make many more many-to-many relationships. Many, many, many-to-many relationships. Because it's a tricky concept. And I know I cover it in my full course like three or four different times. And you can never have too many many-to-many videos. But the good news is though, once you finally get it, once that light bulb turns on, then you got it. Like I got it years ago, so I'm trying to help you guys get it. All right, later on when we do get to the many-to-many relationship, this is going to be a couple of videos from now, make sure you do understand how to make relational combo boxes. That's where you can pick a value from a list from another table. And make sure you know how to use a list items edit format, so that you can edit that list or add items to it. We're going to do this one. We're going to add a new item to our many-to-many address table. This is a nice, easy way to add an item to a list without having to use programming. Because in the old days before they had this, we had to use the not-in-list event, which was really a pain in the butt. I still use it for a lot of things, but this is much easier for simple addition or modifying a list.
Alright, so let's get to the database. This is my TechHelp free template. This is a free database. You can download it off my website if you want to. And in this, my simple list of simple databases. Well, it's not that simple. It's pretty simple. It's a teaching database. It's a jump-off, starting point for you to build your own database. Alright, but here on the customer form, we've got one address. And in the customer table, there's one address if you want to learn how to build this watch those videos I gave you earlier to show you how to build this so let's do the next simplest option. I'm not going to waste time going over doing two addresses in the customer table right. You get that; that's how you do that right. You just come in here and you just take this and you copy these fields and I make it bill-to address right, bill-to city, and ship-to. That's simple.
Alright, so we're going to start with is this one. Alright, one person, multiple addresses, more than three so we're going to make a separate table to store the address information.
Alright so leave the addresses that you might have in here, in here. Alright if you have data in your tables already don't just delete these fields because you'll lose all that data. Okay, you want to at least be able to copy it over to the new thing. So let's create a table. Let's close this one. Right, create, table design. We'll need an address ID. That's our auto number. Now in a one-to-many solution like this is going to be, each address can only belong to one customer. All right, we're not to the point yet where we're sharing addresses. That will come later. So for now, this particular address will belong to one and only one customer. So a customer ID will go next. That's our foreign key, so it's just a number. Okay, and then the address fields and I'm just gonna borrow them from the other table. Where's the customer? Right-click, design and we'll just borrow those fields. No reason to type all these in again. Right address, city, state, zip, country, copy, ctrl C, and then we'll just paste them here. There you go. Now any other information that you want to put in here about this address you can and we're going to add some more stuff to it in a bit in future videos here but for now let's just save this as my address table address key-primary key y'all right there okay so now we've got an address T then we can link it back to our customer based on the customer now we need a form to be able to edit these addresses and some of our all the you continuous form that I've got here. some copy and paste that will call the c address f my address form right right click design view and this is my basic template right and I am going to let's get rid of these boxes here and let's bind this form to the record source address T. All right. Now I can go up to the add existing fields and grab these guys. Or if you want to, you can copy them off the customer form, whichever way works for you. In fact, I think I'm going to copy them off the customer form. As long as they have the same name, that's fine. And I don't need the address ID and I really don't need the customer ID on here either. I'll explain why in just a minute. Let's go to the customer form and let's just borrow these. Design. They got the same names, why not? Copy. Come over here, click Paste. Paste them right in there. Now, I kind of want this to look more like a tabular, like a spreadsheet kind of thing, so I'm going to get rid of these labels. I'm going to slide address over here. We're going to put city next to it, and then state, and then zip, and then country. County doesn't have to be quite that big. In fact, for US customers, or whatever country you're in, I always leave it blank. We'll do that. We'll do this. We'll just adjust these labels. And I'm going to do it the cheap way. Here's my cheap way. I just put an address, one label across the top. Do address. Whoops, I hit my caps lock accidentally. Come here. Come back here. Where'd you go? Oh, man. I accidentally deleted it. All right, address. I have my caps lock on. Come on. Address. Space. All the way over to here. City. It's easier than making multiple labels. That's the lazy method. We got one label across the top, there's that, let's save it, close it, open it back up again.
All right, now by itself it's kind of useless but we need to make this a subform in our customer form. And when we do that, it will maintain that relationship automatically. So let's come in here, design view. Now just for the purpose of the class, I'm going to get rid of this stuff. We don't need it. Goodbye. Okay, obviously, don't delete stuff off of your form.
All right, so I'm going to take my address form, click and drag and drop it over here. There it is. I'm going to get rid of that address F, and we're going to make this nice and big so it fills up as much space as we need it to. Save it, close it, open it, and there we go. Now I can put as many addresses in here as I want for each of these customers. You can just copy them off here if you want to. Copy, paste, copy, paste, copy, paste, copy, paste.
All right, and put a second one in here. All right, 123 Main Street, Buffalo, New York, 14222, whatever. And then if we go to the next customer put an address in here for him or her right blah blah blah blah and Go back to this one, and there's his addresses, and how does it know? Which customer belongs to this well? Design view, if you look in the subform Control object, there are two properties. There's link master fields and link child fields and it's linked automatically by customer ID because it saw customer ID in the table under this form and the table under that form. And if your forms are based on queries it might not make that relationship automatically, you might have to set it yourself. So keep that in mind and I talk about that in a lot more detail in my subform video that you are supposed to have watched at this point.
All right now how do I know which one of these is which? How do I know which of these addresses is billing, which is shipping, which is whatever? Well, we'll talk about that in part two. So tune in tomorrow, same bat time, same bat channel. And if you remember you can watch it right now because I'm going to record it in just a few minutes. But we got a lot more to cover. We're going to talk about the different types of addresses, physical billing, shipping. We're going to talk about setting up the many-to-many relationship, where addresses can be shared amongst different people. We'll talk about how do I get all these addresses that I already have in my database into this new table without having to manually copy and paste them all. We'll do some work with some action queries. And then maybe we'll do some work with a nice little custom search form.
Alright, lots more to cover. This is gonna be a multi-part series, so tune in for tomorrow to part two. I don't know how many it's gonna be. I got my outline listed, but I don't know how far I'm gonna get in each one, so we'll see, we'll get there. But that is going to be your TechHelp video for today. I
TOPICS:
Storing multiple addresses in Microsoft Access
Handling customers with multiple delivery addresses
Designing a database for multiple addresses per customer
Setting up a customer table with address fields
Creating an address table for additional addresses
Linking customer and address data via a one-to-many relationship
Implementing address types (bill-to, ship-to)
Using subforms to manage multiple addresses for a customer
Setting up a many-to-many relationship for shared addresses
Optimizing mailing costs through shared address utilization
Database design for shared addresses across different customers
Building relational combo boxes for address selection
Configuring list items edit format for address management
Transitioning from single to multiple address entries in databases
Error reduction in data entry through pre-existing address selection
Creating tables and fields for storing address data in Access
Database modeling for efficient handling of multiple and shared addresses
Quiz
Q1. What is the most basic level of storing addresses in a Microsoft Access database as taught in beginner classes?
A. One-to-many relationship with multiple address tables
B. Many-to-many relationship with a junction table
C. One person with one or more addresses stored directly in the customer table
D. Address data stored across unrelated tables
Q2. For a business dealing with multiple addresses for customers, what scenario necessitates a one-to-many relationship?
A. Each address is unique to one customer and is not shared
B. One customer with multiple unrelated addresses
C. Multiple customers sharing the same address
D. All addresses are temporary and change frequently
Q3. When should a many-to-many relationship be used according to the tutorial?
A. When only physical addresses are being recorded
B. When customers have unique addresses that never overlap
C. When multiple customers share the same address
D. When there is no need for historical data tracking
Q4. What is the recommended solution for cases where one person might have multiple addresses such as a home, office, or summer home?
A. Include all addresses in a single field separated by commas
B. Set up a single customer table with sub-tables for each address
C. Use a separate address table linked to the customer table by customer ID
D. Store all addresses in unrelated tables and manually manage relationships
Q5. Why is a many-to-many relationship considered difficult to implement?
A. It requires extensive programming knowledge
B. It needs thorough understanding and setup of junction tables
C. It can only be set up for billing addresses
D. It involves only two tables without a junction or link table
Q6. What tool is suggested to facilitate address management without programming?
A. Subforms that allow manipulation of data within a main form
B. Manual entry of all addresses into a single spreadsheet
C. Usage of singular, non-relational databases for each type of address
D. Creating multiple customer records for the same individual with different addresses
Q7. What is a potential benefit of using a many-to-many relationship for addresses in a database?
A. It increases the complexity of the database unnecessarily
B. It can reduce redundancy and mailing costs by sharing addresses
C. It makes it impossible to track historical data
D. It requires less understanding of database relationships
Q8. What prerequisite videos are recommended before tackling more advanced address storing methods?
A. Videos on how to start a business
B. Videos on managing financial records only
C. Videos that explain the basics of Access, creating forms, and understanding relationships
D. Videos on advanced programming and coding techniques
Answers: 1-C; 2-A; 3-C; 4-C; 5-B; 6-A; 7-B; 8-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.
|
