Multiple Addresses 3
By Richard Rost
2 years ago
Storing Multiple Customer Addresses in Access, Part 3 In this Microsoft Access tutorial, I'll show you how to manage multiple addresses for a single customer by setting up a many-to-many relationship. Learn to create and utilize a junction table, enabling shared customer addresses and maintaining address histories efficiently. Perfect for those prepping scalable database structures. This is part 3 of our series. 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!
PrerequisitesLinksUp Next
Keywords TechHelp Access, multiple addresses setup, customer address management, separate tables relationships, key relationships Access, address type differentiation, dynamic combo boxes, data interaction Access, managing customer information, many-to-many relationship Access, database design Access, junction table tutorial, foreign key implementation, address history tracking, primary address setup, combination address field query, Access tutorial part 2
Intro In this video, we will focus on setting up a many-to-many relationship for handling multiple shared addresses in Microsoft Access. I'll show you how to create and structure a junction table, remove unnecessary fields from your address table, and use foreign keys to link customers to shared addresses. We'll also discuss how to include additional information in your junction table, such as start and end dates, and how to concatenate address fields using a query for easier selection in a combo box on forms. This is part 3.Transcript It's part three time. We're doing multiple addresses. This is part three, so watch one, two, go watch one, two, come on back. We'll get back to it. Alright, so yesterday we added a little type here. Alright, we got multiple addresses for one customer, but what if these addresses are shared? What if Richard Rost and James Kirk share an office address? We both work on the Enterprise, for example. So you might want to be able to say, "Alright, start typing in that address, and it just shows up there." So now we have to make this a many-to-many relationship. So it's going to get a little more complicated. If you haven't watched the many-to-many video, go watch it now. Can't stress this enough. Go watch this for a detailed explanation of how many-to-many works. I'm not going to go over all the details again. I'm going to show you how to set it up.
Alright, so let's close this. Now again, this is going to assume you're building the database new. You don't have to keep the existing data in there, because you're going to lose some data at this point if you're converting from one to the other. Alright, if you've already got this address table set up with customer IDs in here, we're going to lose it now. Alright, so you'll have to do some fancy work with some queries to try to copy this stuff over. So that's why I recommend building your test database first, your sample database first, and then you can start feeding it with data.
And once again, I have to stress that this method is not for everyone. You might like what I'm building here. You might want to implement this in your database. But again, whatever works best for you is the best way to do it. I've shown you a couple of different methods. This isn't for everybody, but some people want to be able to do this, so I'm going to show you how to do it. People always say, "What's the best way to do this?" Well, the best way to do it is whatever works best for you. I can show you multiple techniques to do just about everything. I am programmed in multiple techniques.
So the first thing we're going to do is go into our address key, and we're going to remove the customer ID and the address type ID because this is no longer going to be tied to a customer or a specific address type. All right, it's just an address, okay. And as far as the address type goes, one person might use it as an office address, another person might be living there, like in the bathroom or in the, I don't know, like a cardboard box in the warehouse. You know what I'm saying. One person's billing address might be another person's shipping address, for example. So that information gets tied to the customer somehow.
Now, how do we tie the address to the customer? Well, that's where our junction table comes in. Create a table, and I'd like to name junction tables this way: it's going to be whatever the two tables are that tie together, so "CustomerXAddressID," and this will be the "CustomerXAddressID". Again, you can do it your way if you want to, or you can do it the wrong way. I'm just kidding. I do things the way I do because I've been doing it forever, not necessarily the best way; it's just how I do it. So if you're going to learn from me, if you're just learning how to do this stuff, it might be easier if you watch more of my videos, if you kind of do it my way. One of the things when people post stuff in my forums, for example, or they send me a question and they want me to look at it, I can definitely tell people who have not watched my beginner lessons. Because they've got spaces in their field names and they've got, I'm just saying.
So, alright. So our junction table is going to bind two things together. It's going to bind what? The customer ID. That is a foreign key in this table along with an address ID, another foreign key in this table. We also are going to put in here any information about this join, about this specific instance of a customer to an address, such as what type of address is it? Address type ID, another foreign key. You can have multiple, lots of foreign keys in a table. That table could bind a whole bunch of things together. It's like the force. It's like duct tape. It's got a light side and a dark side and it binds the universe together. I'm just full of puns today.
Alright. Now, you could also put other types of information in this junction table. Anything that has to do with this customer for this address, for example, start and end date if you want to keep track of a customer's address history. Right, when did they start using this address, when did they stop using it, put a start date and end date in here, and you'll have a string of all their address history. You could have a field in here indicating if this is their primary address. Right, if that here's where you put stuff like, do you want to send the newsletter to this address? All that kind of stuff goes in this junction table. Okay, and I cover this in a lot more detail in my many-to-many video and in my full courses. I'll put links to all this stuff down below.
Alright, so let's save this as our "CustomerXAddress" table. "CustomerXAddressKey". Primary key is the auto number. Okay, now let's put some sample data in here. Now, in order to see the sample data, it's often handy to be able to look at the other field. So I'm going to take this guy and make him nice and small, put him right there. Let's open up the "CustomerT". Here's my customers. Alright, ignore the addresses in here. We just care about the customer ID at this point. Alright, and then I want to see my list of addresses. Here's my addresses I've got, and pretend we've got more. We got those ones. Okay, so here's what the data in this table is going to look like. Alright, customer one, me, Richard Rost, belongs to address one, and that is his, what type? We need the type table too. Where's the types? Alright, and let's say that is my physical address, three. Okay, customer one belongs to address two, and that's my P.O. box. See how this works? Alright, Jean-Luc Picard, customer four belongs to address one, and that is also his physical address. See how two people can share the same address now because this table binds these all together. Make sense?
Now, what I need is a way where I can open up a customer's record and then add an address for them, and start off by either typing it in or picking an address from a list. It possibly could already be in the table. I want to see a list of addresses like this for each of my customers. Here's me, here's my addresses. And yes, for doing this, it's easier to just toss all those together into a single address field like that. Because now, if I go to a different customer, like Deanna Troy, I start typing in her address, 123, oh I'm looking for 123 Main Street, there it is right there, I just pick it from the list. See how this is going to work? And yes, you could do more advanced searches like searching for the street name or the city or all that stuff. We might do some of that in the extended cut. But let me show you how to do this. This is the easy part.
Alright, so close all these down. Okay, now we're going to make, save changes. Yeah, okay, fine. We're going to make a query that's going to basically have all of the addresses in one field. That way we can see them, all the address parts, I mean, in one field, that way we can see them together in a combo box when I open the whole thing up, just like I showed you in those previews a second ago. So let's create a query. We're just going to need the fields from the address table. Alright, I want address ID, and now I'm going to mesh together the rest of these fields into one. Alright, right here. In fact, I'm going to zoom in, so you can see it better. Oh, my Zoom box is messed up. There it is, okay. I'm going to be able to see all this stuff. Alright, here's my Zoom box. So we're going to be address and a space. And if you don't know how to use string concatenation or how to create calculated query fields, I've got videos on those. I assume most of you are probably past that point, but if you don't know what I'm doing right now, I'll put links down below. Alright? Address, and then a space, and then the city, and then space, and then the state, and then a space, and then the zip, and then, I know if you're in other countries, you might use postal code or whatever, and then country. Basically, I'm just smashing these all together with a space between, you don't put commas in there, fine, you put whatever you want it to look like. Okay, hit OK, there's your calculated query field. Now, if I run this, this is what it's gonna look like when I drop the box down. Okay, so you'll be able to pick that from a list or start typing in a new one. Let's save this as my "AddressQ". Now we've got something that we can come and put in here as a combo box, so I can pick from an existing address. We'll do that in tomorrow's video, part four. So stick around.
Tune in tomorrow, same bad time, same bad channel. Or members, yes, I'm gonna be recording it in just a few minutes, but yeah, what's it, we'll cover in the next part, so that's gonna be your "TechHelp" video for today. Hope you learned something! Wait, someone's beaming in... yeah, that's my hourly chime. It's time to go walk the dogs too. Live long and prosper, my friends. I'll see you tomorrow for part 4.
TOPICS:
Creating many-to-many relationships in databases
Setting up a junction table
Removing customer ID and address type ID from address table
Defining foreign keys in a junction table
Including additional data in a junction table (start/end date, primary address indicator)
Creating queries to copy data for database conversion
Best practices for naming junction tables
Embedding a combo box in forms to select existing addresses
Using calculated fields in queries for address concatenation
Handling shared addresses between multiple customers
Designing a user interface to add and manage addresses
Tracking address history for customersQuiz Q1. What type of relationship was created in the video tutorial to handle multiple addresses for one customer?
A. One-to-one
B. One-to-many
C. Many-to-one
D. Many-to-many
Q2. In the tutorial, why is the customer ID removed from the address key?
A. Because addresses are now only linked to address types.
B. Because it simplifies the database structure.
C. To allow multiple customers to share the same address.
D. To comply with data protection laws.
Q3. What is the purpose of a junction table in the video tutorial?
A. To store new customer information only.
B. To bind multiple addresses to a single customer.
C. To link customer IDs and address IDs for many-to-many relationships.
D. To track customer payments.
Q4. What information can be stored in the junction table according to the video tutorial?
A. Only the start and end dates of address usage.
B. Only the primary status of the address.
C. Details such as type of address, start and end dates of usage, and if it's a primary address.
D. Only customer preferences like newsletter subscription.
Q5. What kind of data loss does the tutor warn about during database transition?
A. Loss of customer emails.
B. Loss of existing address data linked to customer IDs.
C. Loss of financial records.
D. All personal data from the past year.
Q6. According to the video, what is required if you need to maintain existing data during the transition to a new database structure?
A. Deleting all previous records.
B. Running complex queries to migrate data.
C. Manually re-entering all data.
D. Upgrading to a newer database software.
Q7. What does the tutor show how to create in the video tutorial?
A. A query that combines several fields of address information.
B. A function that automatically deletes outdated addresses.
C. A script that encrypts customer data.
D. A formula that calculates shipping costs based on addresses.
Q8. How does the tutor recommend learning to properly set up databases?
A. By following her specific methods demonstrated in the video.
B. By experimenting with different relational models.
C. By using default table templates.
D. By outsourcing database management.
Q9. What was the tutor planning to cover in the next video, as mentioned in the tutorial?
A. How to delete entries from a database.
B. How to integrate a calendar with customer addresses.
C. How to use a combo box to choose from existing addresses.
D. How to configure automatic data backups.
Q10. What did the video tutor use as an analogy to describe the functionality of junction tables?
A. A library system.
B. Duct tape.
C. A bridge.
D. A lock.
Answers: 1-D; 2-C; 3-C; 4-C; 5-B; 6-B; 7-A; 8-A; 9-C; 10-B.
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 continues our series on handling multiple addresses for customers in your Access database. This is part three, so if you have not seen parts one and two, I recommend you go back and review those before proceeding. We have already covered setting up different address types and how to connect multiple addresses to a single customer. Now, let's move on to something a bit more advanced - allowing multiple customers to share one or more addresses. Think of a scenario where two people work at the same place and share an office address. Instead of entering the same address multiple times, it would be far more efficient to keep just one address record and link it to both customers.
To achieve this, we need a many-to-many relationship between customers and addresses. If you are not familiar with how many-to-many relationships work, I strongly suggest watching my dedicated tutorial on that topic. I will not repeat all of that foundational information here, but I will walk you through the process of setting it up for this scenario.
First, for this structure to work, we have to revisit the design of our tables. If you already have an address table that includes a customer ID, getting ready for this change will result in some data loss unless you take extra steps to migrate the data correctly with some queries. For learning, I always recommend building a sample database first, experimenting there, and then moving on to real data once you have everything working as you like.
It is important to stress that this particular approach does not suit every situation. There is no absolute best way to handle these relationships. You should pick whichever solution fits your needs best. I am showing you my method, but if another approach works better for you, feel free to use it.
In this case, we start by removing the customer ID and address type ID fields from the address table. An address should stand on its own. Those other pieces of information link a customer to an address, but they belong elsewhere. For example, one person might use an address as an office, while another may list it as a shipping or billing address. That context is specific to each customer's relationship to the address.
That is where a junction table comes in. I recommend naming it something that combines the two tables being joined, such as CustomerXAddress. This table will include a primary key, a customer ID foreign key, and an address ID foreign key. Additionally, you can add any information about the relationship in this table. For example, which type of address it is for that customer, start and end dates to track address history, a field to note if this is the customer's primary address, or even indicators for something like newsletter mailing preferences.
This design gives you lots of flexibility. By adding records to the junction table, you can tie one customer to multiple addresses and let multiple customers share the same address, all while keeping each link specific to how the customer uses that address.
Next, create the junction table as described and fill it with sample data so you can test the relationships. Keep open both the customers and addresses list while entering that sample data so you can easily choose which customer is linked to which address, and specify the address type by using your address type table as well.
For example, you could enter a record showing that one customer is linked to the same address as another customer, but each might use it in a different way. Customer one might call it a physical address, customer two might call it a billing address. This way, two customers can share one address, but use it for different purposes.
To make entering and managing this data easy and user-friendly, the next step is to design a form where you can pull up a customer and see all associated addresses, or easily add an address, either by typing in a new one or selecting from an existing list. For this, it helps to see all the address details together in one field. The best way to do that is to create a query that displays all the important address fields concatenated into a single output. This makes it easy for users to see the full address and pick the right one from a combo box when adding or updating records from the form.
The query should use string concatenation to combine address pieces like street, city, state, zip code, and country into one field. That way, when you build your form later, you can offer a dropdown so users can select an existing address or start typing to filter the list. If your database covers international data, be sure to adapt the query to use the correct postal code term for your country.
In our next session, I will demonstrate how to put this query to work in a combo box for address selection in your customer form. You will see exactly how to implement this and make it as seamless as possible for users.
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 Creating a many-to-many relationship between customers and addresses
Setting up a junction table to link customers and addresses
Removing customer ID and address type ID from the address table
Defining foreign keys in the junction table
Adding address type ID and other fields to the junction table
Storing additional data like start and end dates in the junction table
Indicating primary address in the junction table
Populating the junction table with sample data
Viewing address assignments for customers
Creating a query to concatenate address fields
Building a combo box to select or enter addresses in forms
Allowing multiple customers to share an address
Selecting existing addresses from a dropdown in customer forms |
