|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Select Primary Customer By Richard Rost 
6 years ago
Indicate the Primary Person for an Account or Family In this Microsoft Access tutorial, I'm going to show you how to indicate which customer record is the Primary, and which are subordinates. This has a lot of uses. A school, for example, may want to have records for children and one for the responsible parent who gets mailings. A company may have multiple contacts but only one gets the invoices, and so on. Kristine asks: "Do you recommend creating a separate record for two customers who share the same address but whose other information, such as DOB etc is not shared? I ask because there should be only one mailing label and only one invoice for two or more customers at the same address but other reports may be individualized." MembersI'll show you how to create a combo box to select who the primary record is for each child. Double-click to switch between them. Also, we'll build a form with two list boxes in it to show your list of primary records, click on it and the 2nd list box will be filtered to show the child records! 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
IntroIn this video, I will show you how to set up a primary customer in Microsoft Access using a single customer table with an added Yes/No field to identify primary accounts. We will walk through modifying your customer table and form to mark individual records as primary or subordinate, making it easy to separate billing and mailing for multiple contacts within the same company or family. I will also demonstrate how to create a query to generate lists or mailing labels for primary customers only.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am Richard Rost, your instructor.In this video, I'm going to show you how to set up a primary customer. Now, you know you've had it happen before. You've got a customer table. You've got a list of customers, but you've got two, maybe three of them that belong to the same company. You want to have individual records in your customer table for them so you can send them individual emails or individual notifications of some kind. But as far as billing or invoicing goes, you only really want to set up one of them to be billed or to be on your printed mailing list. This can be for family members too if you want for school. Maybe you want to have a record set up for mom and then the children have their own records, but you're not going to mail stuff to them. So using one table, I'll show you how you can set up a primary customer and then have subordinate customers. Here's my basic customer template. You can download this off my website if you want to. I'll put a link in the description below the video. Basic customer table: first name, last name, address, city, state, zip, all this stuff in here. And I've got an IsActive field in here. IsActive is generally what I use to indicate that this customer is still around. If they die or move out of the area or something, you can mark them inactive and that takes them off all your lists. As I teach in my Access Beginner 1 class, you never really want to delete a customer record. If they die or are gone, you still want to keep them in there for historical purposes for billing and so on. But I'm going to add another one of these fields called IsPrimary. Let's go into design view. Right click, design view, IsPrimary. We'll make that a yes/no field, and I'm going to default that value to yes. As we enter records in, they'll be primaries by default. If you want to add a child record for them afterwards, you can. Let's save this, Ctrl+S. Let's close that down and open it up. Now everybody in here right now, you'll have to give a value to because these will all be null. So let's make these all primary. Let's open up our customer form now and we're going to add IsPrimary to the customer form as well. I'm going to get rid of this notes label. We don't need that there. I could copy and paste this check box here or just grab a new one and add it, or go to my Add Existing Fields box now because IsPrimary is going to be right there. It comes in from the table. Click and drag that, drop it right there. There's my IsPrimary. Slide it up right there. Let's close this, save it, and open it back up again. Now, there's me, primary. Everybody's primary right now. Let's say I want to add a secondary person from my account. Now I can go to a blank new record. I can put in, let's say, my daughter. I'm going to mark this IsPrimary as No. Let's close this down now. I don't have to bother putting in her address. You can if you want to, but if I go to her record now, you can see I'm just going to leave this blank. Let's say she loves me. She doesn't right now, but let's say she did and I don't want to have to mail her notices or anything. She's on my account. I can still put in her email address if I had an email address field in here. I can still put in her phone number so I can have her number to call her, but as far as billing goes, she'll just be on my account. Now, when it comes time to making orders or invoices or emailing lists, you want to be able to generate a list of just your primary people. That's what queries are good for. So, Create - Query Design - bring in my customer table. I cover this in my beginner classes. Let's say I'm making my mailing labels. So I want first name, last name, address, city, state, zip, country. I'm going to want IsPrimary. I'm going to bring this IsPrimary up to the front here because I'm going to put a criteria on it. The criteria is going to be True, just like that. So now when I run this query, I only see my primary users. I don't see all the subordinates. Let's save this, Ctrl+S, as my CustomerPrimaryOnlyQ. Now I can use this query in the rest of my database to do things like make mailing labels or any other object I create. Let's say "Create Mailing Labels." 5160s are fine, that's fine. Bring in first name, last name, enter, address, enter, city, state, zip, enter, country, next. How do you want to sort it? Last name and then first name, next. Then, looks good. You always get this "some data may not be displayed." That's just an error in the labels. Now I'm only getting labels for the people who are primaries. I don't see the subordinates. Close that, and we can rename this to "Customer Mailing Label Primary Only" or whatever you want to call it. Now you've got, in your form, the ability to mark people primary or not. This is just one way of doing this with a single table, having a primary and then subordinates. You can do it with two tables. If you want to relate two tables together, you can do it where you've got a family table or a company table that has all the main company information in it. Then you can have a person table where you link people to the company. The company gets the billing, and you can specify a primary person at the company. There are a lot of different ways to do this. In fact, that is exactly how I'm setting up my Access Developer 14 class right now. We're building a POS system. I've got a group table, and the group can be either a family, a business, or just simply a group of customers. Inside each group, you've got individual people listed. This is another way you could certainly do it. That's the beauty of Access. You can set up stuff however you want. Both ways have their pros and cons. I think for beginner users, this method is actually easier to use. Just set up a primary person. If you don't want invoices going to anybody else, then don't mark them as a primary. Now, what I've done is I've added some cool features, and this is for the members only. If you want to become a member, I'll give you information at the end of this video on how to become a member. Just join my channel. What I did was I added some extra stuff up here. For example, you can see now that I've got two children in this table. If I double-click on this, it brings me to my children. It filters the records based on my children. It hides the fields that aren't relevant too. For children, you don't want to have a separate address field in here for them or have stuff like "Customer Since" and all that. Turn that off to unfilter. All of these things only belong to the primary. If I make this person not a primary now, it hides that stuff. Also, if you mark someone as not a primary, you can pick who their primary is right here. So Walter Jones is a child of Sue Jones. Want to go back to Sue's record? Double-click, and it puts you on Sue's record. This is a little bit of code in here. A couple of lines of code will show you how many children that person has. It will filter or unfilter based on that record. See, Jim Kirk, no children. Actually, he does have one, and I mention that in the other video too. I'm a Star Trek nerd. So, a little bit of extra code in here to let you be able to jump back and forth. Also, what I did, I couldn't help myself, I made a customer list box. This customer list box lets you double-click and jump to a customer's record. Or you can click over here and see who their children are right here and jump to one of them as well. Isn't that cute? And these are for paid members. How do you become a paid member? Just go to my YouTube channel and click on that Join button. Once you do that, you'll get some options. There's Silver, Gold, Platinum. Silver members and up get access to all my paid videos, my paid TechHelp videos that I put on YouTube, the extended versions, the extra cut. This one happened to be about 30 minutes long, where I put all that extra stuff in this video. This is only my second one, so I'm getting there. I'm learning. Thanks for watching. Even if you don't want to become a paid member, that's okay. I'm still going to keep making these free videos. But make sure you subscribe to my channel. Subscribing is free. Click on the little bell icon and you'll get email notifications whenever I release a new video. Nothing's going to change. I'm going to keep making these free videos as long as I can. Make sure you also stop by my website and subscribe to my Access forum. Lots of cool stuff goes on there. If you have questions, post them on my tech support page. Or, of course, you can always email me. I get tons and tons of email, so you're better off making a comment on one of my videos or posting it on my TechHelp page because if I get a really long email, they tend to get shoved to the back until I've got a lot of time to answer them. There's all my cool stuff: Facebook, Twitter, YouTube, all that. If you haven't yet watched my Access Level 1 class, please do. It's free. It's on YouTube. It's on my website. It's three hours long and covers all the basics. If you like that, Level 2 is just one dollar and will get you started. It'll get you an account on my website so you can go through and post questions and stuff in my forums as well. That's only reserved for paid customers. Thanks for watching. I hope you learned something, and you have a great day. QuizQ1. What is the purpose of adding an IsPrimary field to the customer table?A. To track whether a customer is the main contact for billing and mailing B. To store the customer's email preferences C. To record the date when the customer joined D. To indicate if a customer is a company or an individual Q2. Why is it generally recommended not to delete customer records in Access databases? A. To avoid breaking a relationship between tables B. To keep historical information for billing and tracking C. Because Access does not allow record deletion D. To reduce the size of the database Q3. What is the default value set for the IsPrimary field when adding it to the customer table? A. No B. Null C. Yes D. False Q4. Which field helps indicate if a customer is still active and should appear on lists? A. CustomerType B. IsActive C. IsPrimary D. IsCurrent Q5. How would you generate mailing labels for only primary customers using Access queries? A. Filter on IsActive = True B. Filter on IsPrimary = True C. Sort by last name D. Filter on a custom field Q6. What happens if you mark a customer record as not primary in the system described? A. The record is deleted B. You mark which primary customer they belong to C. The record is archived D. The customer is automatically set to inactive Q7. If you want to relate customers to companies or families using two tables, what structure is suggested in the video? A. Customer and Product tables B. Company (or family) and Person tables, linked by a relationship C. Single flat customer table D. Person and Invoice tables Q8. What is one benefit of using a single table with IsPrimary and subordinate records, especially for beginners? A. It automatically sends emails to subordinates B. It is easier to implement than a multi-table structure C. It sorts records better by default D. It is the only way to manage customers in Access Q9. What query criteria does the instructor set to filter for primary customers? A. IsPrimary: False B. IsActive: False C. IsPrimary: True D. CustomerType: Primary Q10. What special feature is available for paid members as described at the end of the video? A. Ability to import Excel files directly B. Extra form code such as double-click navigation, child counting, and customer list box C. Access to all Microsoft Office products for free D. Automatic monthly database backups Q11. What should you do if you want to become a paid member and access extended content? A. Email the instructor directly B. Click the Join button on the YouTube channel C. Register on the AccessLearningZone.com forum D. Subscribe to the newsletter Q12. What is a recommended way to contact the instructor with Access questions? A. Send a long email B. Post a comment on a video or the TechHelp page C. Call the support hotline D. Visit his Facebook page Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-C; 10-B; 11-B; 12-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. SummaryToday's video from Access Learning Zone is all about setting up a primary customer in your database. This is a scenario you might be familiar with: you have multiple customers who belong to the same company or family, but you only want to treat one of them as the main contact for billing, mailing, or invoicing. The other records might represent additional employees or family members—important for emails or record-keeping, but not the ones getting invoices.Using a single table, I will show you how to identify and manage a primary customer, along with any subordinate entries connected to them. You might use this for a business where you have several contacts at the same company, or for a family with one parent and several children. Each gets an individual record, but only the main record is used for billing or mailing. The template we are working with is a basic customer table. It includes fields such as first name, last name, address, city, state, ZIP code, and a field called IsActive, which indicates whether a customer is still current. If a customer is no longer around, marked for any reason as inactive, they are simply excluded from lists but remain in the database for reference or billing history. Following the best practices I teach in my Access Beginner 1 class, I recommend never deleting customer records entirely when someone is no longer your customer. Instead, they should be marked inactive for historical and billing purposes. Now, to handle the primary customer setup, we are going to add a new field to the customer table called IsPrimary, a yes/no field that defaults to yes. This way, as you add new customers, they are considered primary by default. If you need to add, for example, a child or secondary contact, you simply add a new record and mark IsPrimary as no for that entry. After saving these changes and reopening the table, you should update existing records so they are appropriately marked as primary. Then, open your customer form and add the new IsPrimary field to it, making it easy to see and update directly from the form. Suppose you add a secondary customer, like a child linked to a parent account. You are not required to enter a separate address if the mail should go to the parent. Contact details like email or phone can still be entered for the child, but for billing and main contact purposes, only the primary record will be used. When it is time to generate order forms, invoices, or mailing lists, you do not want to include all individuals, just those marked as primary. To do this, you can create a query that filters for records where IsPrimary is true. This query becomes the source for your mailing labels or any other objects. When you generate mailing labels, for example, only those marked as primary will appear, excluding secondary contacts. You can rename your reports or label output as needed, such as "Customer Mailing Label Primary Only." This process allows you to easily manage who gets billed or mailed to, while still keeping records of all related individuals. While the single-table approach is simple and effective, especially for beginners, there are other ways to accomplish this with two related tables. For example, you can have one table for companies or families and a separate table for individuals linked to them, specifying a primary person within each group. This is the structure I am currently using in the Access Developer 14 class, which involves building a POS system with groups such as families or businesses, each with individual people assigned. Both methods have their strengths, but the single-table approach with an IsPrimary flag is easier to start with. For those interested in more advanced features, I have added some enhancements available for members only. These include filtering to display only child records, hiding irrelevant fields for non-primary records, and providing options to navigate between related contacts. For instance, you can now filter the customer form to show just children, or select the parent record for a subordinate directly from the form. I also created a customer list box, making it easy to jump to a particular customer or see their children with a double-click. These features are available to paid members, and you can sign up on my YouTube channel by joining as a Silver, Gold, or Platinum member. Silver members and above get access to all paid TechHelp videos, including extended versions with additional content. For this particular video, the extended cut runs about 30 minutes and covers all these advanced features. Regardless of membership, I appreciate everyone who watches and supports the channel. All the basic tutorials will always be free. If you want to stay up to date, subscribe to my YouTube channel, and consider joining the Access forums on my website to ask questions and interact with the community. If you are new to Access, I highly recommend starting with my Access Level 1 class, which is available for free both on YouTube and my website. It covers the basics in about three hours. Level 2 is just one dollar and creates an account for you on my site, giving you access to more resources. For more details and step-by-step instructions for everything covered in this video, you can find the complete video tutorial available on my website at the link below. Live long and prosper, my friends. Topic ListSetting up a primary customer in a customer tableAdding an IsPrimary yes/no field to the customer table Defaulting new records to primary in Access Updating existing records to set IsPrimary Adding the IsPrimary field to a customer form Using a checkbox to indicate primary or subordinate customers Entering subordinate customer records Deciding what information to enter for subordinate records Creating a query to show only primary customers Filtering mailing lists to include only primary customers Designing mailing labels using primary customers only Naming and saving queries for future use Comparing single-table and related-table approaches for customers Using queries to drive other Access database objects Beginners method for handling primary vs subordinate customers |
||||
|
| |||
| Keywords: TechHelp Access Primary Subordinate Parent Children PermaLink Select Primary Customer in Microsoft Access |
