Multiple Phone Numbers
By Richard Rost
6 years ago
Properly Store Multiple Phone Numbers in your Access Database
In this video, I will show you how to store multiple phone numbers for each customer in your Microsoft Access databases using a one-to-many relationship and a subform. The same technique applies whether you have phone numbers, emails, addresses, etc.
Curtis from Denver CO (a Silver Member) asks: In your classes you show adding one phone number for each customer, but I have a situation where a customer may have anywhere from 1 to 10 or more phone numbers. Do I make Phone1, Phone2, etc.? What's the best way to handle this?
Members
I'll show you how to add a combo box to the subform so you can see what kind of a phone number it is: cell, home, work, fax, etc.
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
Relationships: https://599cd.com/relationships
Append Queries: https://599cd.com/append
Combo Boxes: https://599cd.com/combo
ABCD Database: https://599cd.com/ABCD
Intro
In this video, we will talk about how to store multiple phone numbers for each customer in Microsoft Access. I will explain why it is better to use a separate related table for phone numbers instead of adding multiple fields to your customer table. We will walk through creating the new phone number table, setting up the one-to-many relationship, and adding a subform to your customer form so you can easily manage and view all phone numbers for each customer. This technique can also be applied to other fields like email or physical addresses.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's class, we are going to talk about storing multiple phone numbers for each of your customers. Today's question comes from Curtis in Denver, Colorado, one of my Silver members.
Curtis asks, in your class, you show adding one phone number for each customer, but I have a situation where a customer may have anywhere from one to ten or more phone numbers. Do I make phone one, phone two, etc? What is the best way to handle this?
Well, Curtis, my rule of thumb generally is if it is going to be more than one of something, make another table and store related records. That way you can have one, two, three, twenty, two hundred, as many as you want, or zero.
The exception is, I might allow home phone and work phone, but then you get into the situation later where it is home phone, work phone, cell phone, and then cell two, or any number of other different types of numbers. If you are going to go through the trouble of creating a second field, you might as well just make it a related table.
The same technique applies for email addresses. I only have one email address in my customer database and I wish years ago I would have built it differently, because sometimes I have customers with three, four, five different email addresses. Or even street addresses, you have bill to, ship to, physical address, and so on.
Use this technique for any field where you think there is going to be more than one of any type of this particular piece of data.
Here is my basic customer form that I use for most of my classes. I have one phone number field right there. But what if we want to store home phone, work phone, cell phone, all kinds of different info? Let's make a second table to store phone numbers.
Let's close this stuff down. Go to Create and Table Design. Phone ID, I like to start all of my fields with an AutoNumber. Phone Number, and that's it. That's all you need. Save it. We'll call this the PhoneT. OK, primary key is fine.
Now, that is the information about each phone number. How do I know what customer it belongs to? Well, that is going to be in the CustomerID field. So, add a CustomerID field here. That will just be a Number of type Long Integer. This is a foreign key and it points back to the Customer table. This will be a one-to-many relationship. If you are unfamiliar with relationships, I have a whole separate video on relationships. I will put a link in the description below the video. Watch that first before continuing with this one.
I like to keep all my ID fields at the top, so I am just going to slide that one up. Save that.
I like to put some sample data in first. Here is my list of customers. Here is my phone number table now. We are going to take the phone number field out of here in a minute. For now, let us say I have customer, there is CustomerID and there is PhoneID.
Here is how this works. Number one gives me a phone number of this. Customer one also has this phone number. Customer one also has this one. Next customer, customer two, has this phone number. Customer two has another one. Customer three does not have any phone numbers so we leave it blank. Customer four just has one. That is all the phone numbers we have.
You can clearly see here by the relationship who belongs to each phone number.
Close that. Save changes, yes. Might as well get the phone numbers out of here. Of course, copy all this data first. Back up your database before you do anything like this. I keep this slide around for a reason. Back up your database before you do anything crazy. Once I have copied all the phone numbers out, we can take it out of this table. It says you are going to lose some data. Yeah, OK. Assuming you have already got your phone numbers out.
Is it possible to save those phone numbers and move them over to the other table? Sure, you could do that with an append query. That is a different video. I have videos on that too. If anyone wants to see me specifically do that, copy the phone numbers over to the phone number table, let me know. Post a comment below. I will make another video about it.
Now, let's make a little subform so we can put those phone numbers inside our customer form. Here is our customer form. Maybe put the phone numbers down in the bottom in a little subform. Let's get rid of this phone number field while we are in here because this field is gone. Delete that. I am going to slide this stuff up. You can do the same thing with address. You can do the same thing with email. We are going to put our phone subform down here.
Let's make that first. Save this and close it. Go to Create and then Form Design. I do not like using the wizards. If you have never watched any of my lessons, I hate the wizards. They add so much garbage. It is just so easy to make it from scratch.
We are only going to put one field on here. First, set the form's control source - excuse me, the record source - to our new phone number table. While we are in here, set Default View to Continuous Forms. We will get a whole list of them one after the other. Single Forms show you one record on the screen at a time; Continuous Forms let you see multiple records. Again, if you have never used Continuous Forms before, I have videos for that too. I will put a link down below.
One more thing while we are in here, I am going to turn off navigation buttons. The navigation buttons are right down here - those little buttons on the bottom that let you go from one to the next. If you open up the customer form, you will see these things down here. We do not need those in the form. We are just going to scroll up and down a little list.
So, we have that all set. Now we can go to Design and add existing fields. I only need one field in here. I just want to see the phone number. The PhoneID is an AutoNumber. We do not need to see it. The CustomerID will be set by the relationship between the parent form and the subform, so that is automatic too. We do not need to have that field in here.
Really, all we need is the phone number field. I want to actually delete that label too. Literally, all we are going to see is just the phone number. Make that as big as you think you need it to be. We are going to shrink up this section too, just like this. That form section is literally going to be just that field.
Save this. We will call it MyPhoneSubF, My Phone Subform. Close that. Let's take a look at what it looks like. That is all it is, literally. I do not like that alternating background color either. Let's get rid of that real quick. Go to Design View. I do not like that. Go to the detail section, open up its properties. Here is Back Color, Background 1, and then Background 1 slightly darker. I do not like these template colors either. Hit the dot-dot-dot button. Go to Standard Colors and pick white. Copy that. Paste it down there. Paste it there. That fixes that problem.
There are lots of little things, and I cover all this in my beginner class, by the way. It is a free three-hour beginner class. Watch it.
There are all the numbers for everybody in the database. The relationship we create as a form and parent form will fix that problem.
Now, it is just a matter of going into the customer form and dropping that in as a subform. Right-click, Design View. Open this up a little bit. We are going to drop it in here as a subform. How do you do it? Click and drag. Place your subform there. I am going to take this label and move it over here like this and put "Phone Numbers." Then slide this little guy right there and make this as big or as small as you want it because you will have a scroll bar. If you think you are going to usually have four or five phone numbers per customer, make it that big. If it is smaller, that is OK. They can scroll up and down.
Save it. Close it. Open it back up. There you go. There are the three phone numbers for customer one. Go to the next record. That is why I said we would not want a second one of these in there because that would be confusing. Next. There is James Kirk's two, none for customer three, and there is customer four.
Want to add more? Just click in here. You can put in as many phone numbers as you want, and those will all be tied to that customer. Go back and forth. You can see all their phone numbers. You can do the same technique with email addresses, physical addresses, whatever else you want to assign to this person.
The key is to put this information in a second table, a second related table. That is a one-to-many relationship. Again, I have lots of videos on relationships. I will put some links in the video description below. Go watch those.
Want to take this even further? In the extended cut for Silver members and up, I show you how to add another field where you can track what kind of phone number that is. You can specify whether it is the cell phone, fax, work, home phone, and so on.
So if you finally get Sue Jones to give you her number, what kind of number is that? Drop this down. That is her home phone.
That is it. I also have another database available that I just finished building called the ABCD, the Access Business and Contact Database, where you can use the same technique for email addresses, phone numbers, and addresses as well. I will put a link to this below if you are interested.
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 perks that are available: Silver, Gold, Platinum, and Diamond.
But do not worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making them, and they will always be free.
If you enjoyed this video, please give me a like and share. Click on the Subscribe button to subscribe to my channel and be notified of any new releases.
Check for additional resources below the video. Click the Show More button and you will see a list of other links to other videos, downloads, resources, lessons, and lots more.
If you have not yet tried my free Access Level 1 course, it is three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and that is free for my members.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can post your question there. Also be sure to stop by my Access Forum on my website. And also look for me on Facebook, Twitter, and of course, YouTube.
Once again, my name is Richard Rost with AccessLearningZone.com. Thanks for learning with me and I will see you next time.
Quiz
Q1. What is the recommended way to store multiple phone numbers for a single customer in an Access database?
A. Add multiple phone number fields to the customer table (phone1, phone2, etc)
B. Store all phone numbers in a single text field separated by commas
C. Create a separate related phone number table with a one-to-many relationship to the customer table
D. Allow only one phone number per customer
Q2. What type of relationship should be used between the customer table and the phone number table to allow storing multiple phone numbers for each customer?
A. One-to-one
B. Many-to-many
C. One-to-many
D. Self-join
Q3. What type of field is typically used as the primary key in the phone number table?
A. Text field containing the phone number
B. AutoNumber field
C. CustomerID field
D. Date/Time field
Q4. What is the role of the CustomerID field in the phone number table?
A. It is the table's primary key
B. It determines the phone number type
C. It acts as a foreign key to relate the phone number to a specific customer
D. It stores the customer's name
Q5. What is the purpose of creating a subform for phone numbers in the customer form?
A. To display only one phone number at a time
B. To display and allow entry of multiple phone numbers linked to the customer
C. To show unrelated phone numbers
D. To display a summary of all customers
Q6. What form view should you use for the phone number subform to display multiple records at once?
A. Datasheet View
B. Single Form
C. Continuous Forms
D. Pivot Table View
Q7. Why does the instructor prefer not to use wizards when creating forms?
A. Wizards are too slow
B. Wizards add unnecessary elements and complexity to forms
C. Wizards do not allow creating subforms
D. Wizards require advanced coding knowledge
Q8. Which of the following is NOT mentioned as another type of information that could benefit from being stored in a related table?
A. Email addresses
B. Street addresses
C. Customer names
D. Physical addresses
Q9. What is recommended before making significant changes to your database, such as removing fields?
A. Exporting all reports
B. Backing up your database
C. Deleting unused tables
D. Compacting and repairing the database
Q10. What benefit is there to storing information such as phone numbers in a related table rather than in multiple fields within the customer table?
A. It prevents duplicate records
B. It allows any number of phone numbers to be associated with each customer
C. It increases the file size
D. It limits customers to two phone numbers
Q11. What additional feature is demonstrated in the extended cut for Silver members and up?
A. How to hide all phone numbers
B. How to add a field tracking the type of phone number (home, work, cell, etc)
C. How to print all phone numbers
D. How to combine all phone numbers into one field
Q12. If you want to copy existing phone numbers from the customer table to the new phone number table, which tool or query will you likely use?
A. Update query
B. Delete query
C. Append query
D. Make-table query
Q13. The technique of splitting off information into a related table is useful for which situations?
A. Only when you have more than ten entries
B. Only for phone numbers
C. Anytime you expect more than one of a type of data associated with a record
D. Only for numeric data
Answers: 1-C; 2-C; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-B; 10-B; 11-B; 12-C; 13-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 the best way to store multiple phone numbers for each customer in your database. This is a common problem, often raised by students asking whether they should simply add extra fields like Phone1, Phone2, and so on, to accommodate more phone numbers. My usual advice is that if you need to store more than one of anything, it is much better to create a separate related table for these details. This not only allows you to add as many phone numbers as necessary for each customer, but also keeps your database design clean and efficient. Using a separate table makes it easy to handle any number of phone numbers, even if a customer happens to have none at all.
While some people might make an exception for scenarios like a home and a work phone, this approach tends to break down as the variety of phone numbers increases. Before you know it, you're adding fields for cell phones, fax numbers, and so on, which quickly gets unwieldy. It's much easier to set up a related table from the start.
This advice doesn't just apply to phone numbers. Email addresses and street addresses often run into the same challenges. If there's even a chance you might need more than one of any type of data for a person or company, you should use this related table approach.
Let me walk you through how I set this up using my standard customer form as an example. Normally, that form has just one phone number field for each customer. To support multiple phone numbers, start by creating a new table. This table will need a unique identifier, which I call PhoneID and set as an AutoNumber, plus a PhoneNumber field. That is all you need for the phone number itself. To link each phone number back to the correct customer, add a CustomerID field, set as a Number (type Long Integer). This serves as a foreign key, connecting each phone number to its corresponding record in the Customer table. The result is a one-to-many relationship between customers and their phone numbers.
I prefer to keep all ID fields together at the top of my tables, so feel free to arrange your fields accordingly. Once your table is ready, enter some sample data to test the design. For example, you might have Customer 1 with three phone numbers, Customer 2 with two, Customer 3 with none, and Customer 4 with just one. The data in this table will quickly show how each phone number is tied to its customer.
At this point, you should remove the single phone number field from your original Customer table. Be sure to back up your database first, as removing data can lead to loss of information if anything goes wrong. If you want to automatically copy old phone numbers into the new table, you can do this with an append query. I have a separate video that covers this process.
Next, create a subform to display these phone numbers along with your customer records. The subform will list all phone numbers associated with each customer. I recommend building this form from scratch instead of using the wizards, as they tend to add unnecessary clutter to your database. Set the record source of the subform to your new phone number table and adjust its default view to Continuous Forms. This will show a list of all phone numbers for the customer, rather than just one at a time.
You can remove navigation buttons from the subform since the parent form already handles navigation. The only field you really need to display in the subform is the PhoneNumber field. Hide unnecessary labels and resize the form section to focus on just the phone number itself.
Once the subform is created, you can insert it into your Customer form. Arrange the layout to suit your needs, placing the phone numbers wherever they make the most sense visually. The subform will update automatically to show the correct list of phone numbers as you move between customers, making it easy to add, view, or remove numbers as needed.
This same approach works equally well for email addresses, physical addresses, or any other information where multiple entries per customer might be needed. The core idea is to use a related table and establish a one-to-many relationship. If you would like to learn more about setting up relationships in Access, I have additional resources available, and links can be found on my website.
For those who want to take this even further, in the Extended Cut for Silver members and higher, I demonstrate how to add a field to specify the type of phone number, so you can indicate whether each number is a home, work, cell, or fax number, and much more.
If you're interested in a full database solution built around these principles, I have the Access Business and Contact Database (ABCD) available, which uses these same relationship strategies for phone numbers, emails, and addresses.
If you want to access extended cut content, live video sessions, and more, you can become a Silver member or higher. Full details on membership perks are available on my website. However, my TechHelp videos will always be free for everyone, so you can keep enjoying new tutorials as they are released.
If you found this information helpful, I encourage you to check out my free Access Level 1 course, which is three hours long and covers all the basics. Level 2 is available for just one dollar or free for members.
To have your question featured in a future TechHelp video, visit the TechHelp page on my website. You can also find helpful discussions in my Access Forum, and I invite you to follow along on Facebook, Twitter, and YouTube.
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 separate related table for multiple phone numbers
Setting up a one-to-many relationship between customers and phone numbers
Designing a PhoneT table with PhoneID and CustomerID fields
Populating the phone number table with sample data
Removing the obsolete phone number field from the customer table
Creating a subform for displaying phone numbers
Setting the subform's record source and format (continuous forms)
Adding the phone number field to the subform
Customizing subform appearance (removing alternating background color)
Embedding the phone number subform inside the customer form
Sizing and positioning the subform within the customer form
Demonstrating data entry and navigation for multiple phone numbers per customer
|
