Customer Codes
By Richard Rost
6 years ago
Create Customer Codes that Don't Reveal Your Database Size
You don't always want your customers seeing their CustomerID. If someone gets assigned a low number, they'll know you don't have much business. In this Microsoft Access tutorial, I'll teach you how to create custom Customer Codes that are based on DateTime stamps and random numbers.
Bethany asks, "I’m using an AutoNumber for CustomerID, however I don’t want my clients knowing how many customers I have. If XYZ Corp sees that they have CustomerID 15 then they know I don’t have that many clients! How do I give them a Customer Code that isn’t dependent on an ID field?"
Members
There is no Extended Cut for this video.
 Links
Subscribe to Customer Codes
Get notifications when this page is updated
Intro In this video, I will show you how to create unique customer codes in Microsoft Access to keep your customer ID numbers hidden from clients. We will talk about why using auto number fields for customer-facing IDs is not recommended, set up a separate customer code field with a default value based on date, time, and a random number, and make sure it stays unique for each customer. I'll also walk you through updating your table and forms to use these codes in reports and anything your customers might see.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's lesson, I'm going to show you how to create customer codes. We're going to create customer codes to hide your IDs from your customers.
Bethany writes, she says, I'm using an auto number for customer ID. However, I don't want my clients knowing how many customers I have. If XYZ Corp sees that they have customer ID 15, then they know I don't have that many clients. Number 15, obviously. How do I give them a customer code that isn't dependent upon an ID field?
Auto numbers really should only be used by Access for internal use only for doing lookups and for making relationships between tables. You shouldn't rely on them for anything other than this. Yes, in my courses, I tell you to put an ID for every table and we put it on the customer form and on the order form, but that number really shouldn't be put on things that you give to your customers if you want to hide that information from them.
It's like if you have a guest check and you only have one book and you give a customer a guest check number 14, and he comes back three days later and he's up to 17. He knows you only had three orders since then.
It brings up something called the German Tank Problem. In World War II, the Allies captured German tanks and they could tell from the serial numbers how many tanks the Germans were producing. I talk about this in more detail in my other video, auto numbers good or bad. Look for that on YouTube. I'll put a link below.
Certain information like customer IDs and order IDs may be something you want to hide from your customers. So how do we do that?
Well, I've got a simple database here and I've got customer IDs in my table, but again, these shouldn't be shown to the customer if you care about hiding that information. Some people don't, and honestly, to keep my classes simple, I teach my beginner students just to use this as a customer ID on the invoices. But when you get into more advanced stuff, and if you're to the point where you're a developer building software for other people, then of course you want to know how to do this.
Now the trick is to keep the ID because we're going to use that inside the database. The auto number is the best type of field to use for making relationships and for doing lookups between tables, but it's only a number that Access cares about. You shouldn't care about it at all.
Let me reiterate that auto numbers are meaningless to the user. You can show them to them if you really want to, but they can give away business information.
So let's create a second field in here. We're going to keep the customer ID. Let's create a second field and we'll call it customer code. Let's go to our table, design view, insert a row right here, and call it customer code.
Let's leave this as text so we can have letters and numbers in it. Now I'm going to make it so that you can type in whatever you want. If you want to give them a customer code, that's fine, but let's set a default customer code so that when I create a new record, it's going to give them a value.
What's that default value going to be? Let's put a C in there for customer. I like to use date timestamps because if you put the date and time in there to the second, then chances are it's not going to come up with a duplicate. You'd have to literally add another customer at the exact same time.
So what I like to do is put a date timestamp in there, followed by a random number from 1 to 100. The chances of two customers being added at the exact second and getting the same exact random number are infinitesimal. We're going to say format, now, right now. Whatever date time format you want to use, I'm going to go YYMMDD. That's your month and day.
This all depends on how many customers you add. If you're adding dozens of customers every day, then you might want to go HHNNSS, which is hour, minute, and second. If you only add one or two customers a day, maybe go to the minute. Or if it's a customer every couple of days, just go to the day.
Now, does this give away business information? Yeah, but all it gives the customers is what day they became a customer. That's not really a big deal. So I'm going to go year and monthday, close parentheses, and then we're going to add on a random number, put in INT, and then RND times how big you want your number to be. I'm going to go 100. That'll give you a number from 0 to 99. Or you can add one if you want to go from 1 to 100.
So the chances of you getting two customers with that same code, with a random 100, random digit after it, random two digits after it is very slim. If you need more precision, then add hours and minutes in here.
Now, this is the default value. I'm going to set indexed to no duplicates. That way we can't have two customers in there with the same value. Required right now is set to no. We're going to change it to yes. You can't change it to yes if you have people in the table already that have missing values.
Let's save this and see how it works for us. Save it, close it, open it back up again. Now look down here. These guys we have to put records in for. But down here, that's C200806, that's today's date, 53. Let's put someone else in. There's 287776. If you do a bunch of these a day, then you might get two duplicates. So put as much precision on that as you want.
But that's a nice, quick, easy way to assign a fairly random, kind of random customer ID. Now we're not going to use this for any relationships. We're just going to use that to show on reports. If you want to print out an invoice, you can show them their customer ID. What's my customer ID? C2008, but that's your customer ID. It's nice and short. You can add seconds in there if you want to, or whatever.
You have to go back through and give these guys values. You could do it with an update query if you want to. That's a whole separate video though. I've got videos on update queries. I'll put a link below. You can run an update query and change these to the same formula you used below. For now, I'll just put random letters in here just to get through class. Oh, can't put D in there. These are already assigned. Those are random customer codes.
These are the ones we care about down here. Now I can go in and set required to yes. And that's it. That's how you set up data integrity rules of change. That just simply means that we've made it so that's required. So it's got to go through and analyze the table now.
Now you just add this field to your form, which is pretty simple to do. Go to Customers, Design View. Copy. Paste. I'm going to copy and paste the customer ID. Put it right over here. Customer code. Change this guy to Customer Code. Don't forget to change his name too. Use the Control Source and the Name. The Control Source is where it's bound - what field in the table it's bound to. The Name is the actual name of this box itself.
And there's my customer codes. Go to add a new one, and you can see there's a customer code in there already. If you want to make sure, see that one got a five and the one before it was 70. If you want to make sure that that's a uniform field size, you can go and do it. Take this number and make it one more digit, so it's 0 to 999.
I'm going to use format, and then go comma, 000. Put that in quotes too, quotes like that. That'll format that number so it always displays with three zeros, so it'll go 005 if you get a low number.
Let's go back one more time and take a look. New work. It might take me a minute to get a low value here. I'm getting all high numbers. Give me something down below 100. There we go, 015. This will make sure it's always the same length. So all of your customer codes are the same length, and you'll want to reassign these.
So Bethany, I hope that answers your question. Now, remember, don't show your customers their customer ID. You can show them that new customer code and use that in all of your reports and anything that the customer might possibly see.
When it comes to sequential numbering, I have another video on how to do sequential numbering. These are good for things like invoice numbers. I have another video on YouTube, look for the link below. For members, I show you how to do sequential numbering per customer, so every time a customer gets a new invoice it'll go 0001, 0002, 0003, for just that customer. That's a members only video.
How do you become a member? Click on that join button down below the video. You'll see a list of all my perks that are available for Silver members and above. You get access to the extended cut editions for my TechHelp videos.
I really didn't need to do a TechHelp extended video for this particular one because I showed you a lot of extra stuff in last night's video when I did the sequential numbering. We even did customer codes in that video in a slightly different way. We based them on the first name and the last name.
In last night's members only video, I showed how to make customer codes based on first name and last name.
Thank you for watching this free video. I'm going to keep making these free TechHelp videos until I can't make them anymore. I love making the videos and helping everybody for free.
Make sure you subscribe to my channel so you get notifications whenever I release a new video. Click on the little bell there, ring the bell, and pick all. You'll get an email notification for each class I release, for each video I put on YouTube.
Also, stop by my forums on my website. I've got lots of stuff up there too. If you want to see your question answered in a video like this, visit my TechHelp page and send it to me there.
Look for me on Facebook, Twitter, YouTube of course. If you haven't yet tried my free Access Level 1 class, it's free and it's three hours long, covers all the basics of Access. If you're still struggling, even if you've been using Access for a little while, I know a lot of people who say, well, I've been tinkering with Access for a few years and I finally took your class, and wow, there's a lot of stuff I didn't know. There's a lot of fundamentals, a lot of things I put in there from my 20 plus years of using Access that will help you if you're struggling, trying to figure out how things work.
If you like Level 1, Level 2 is just a dollar.
That's it. Thanks for watching and we'll see you next time.Quiz Q1. What is the main reason to avoid showing customers their internal auto number ID in Access?
A. It can reveal business information like the number of clients you have
B. Auto numbers are too complex for customers to understand
C. Auto numbers use too much storage space
D. Customers need different numbers for each order
Q2. According to the video, what is the proper use of the auto number field in a database?
A. For internal lookups and making table relationships
B. For generating customer codes for invoices
C. As a customer password
D. For sorting customer names alphabetically
Q3. Why should customer codes be created as separate fields rather than using the auto number ID?
A. To conceal business-sensitive information from customers
B. Because auto number fields cannot be indexed
C. Because customer codes are required to be numeric only
D. To allow auto numbers to be shown on reports
Q4. What field type does Richard suggest using for the customer code?
A. Text, so it can include letters and numbers
B. Number, so it can automatically increment
C. Yes/No, to indicate if code is assigned
D. Memo, to allow for long descriptions
Q5. What default value formula does Richard recommend for generating customer codes?
A. Date timestamp combined with a random number
B. Customer name and address
C. Auto number incremented by one
D. Just the current date in text format
Q6. What measure does Richard use to avoid duplicate customer codes?
A. Combining timestamps with random numbers and setting Indexed to No Duplicates
B. Using only the timestamp with no random component
C. Allowing duplicates and fixing them manually later
D. Using only the random number, no timestamp
Q7. If you want more uniqueness in customer codes, what field(s) should you include in the code format?
A. Add more time precision, like hour, minute, and second
B. Only use the current day
C. Use the customer's phone number
D. Add the word "Customer" at the end
Q8. Why might a customer code like "C20080653" still reveal some information?
A. It shows the date the customer joined
B. It includes their auto number
C. It contains a checksum for security
D. It displays their order total
Q9. How can you update existing customers without codes to have new codes assigned?
A. Use an update query with the code generation formula
B. Rename the table and re-enter all records manually
C. Export the data to Excel and add codes there
D. Delete all customers and re-add them
Q10. What does setting a field's "Required" property to Yes do?
A. Ensures every record must have a value in that field
B. Prevents users from editing that field
C. Automatically hides the field from reports
D. Deletes records with empty fields
Q11. When adding the customer code to your form, what should you do?
A. Set the Control Source to the customer code field and update the control's name
B. Bind the form only to the auto number field
C. Use the same field for both ID and code
D. Set the Control Source to the customer's phone number
Q12. How can you ensure the random number part of the customer code always displays as three digits (for example, 005)?
A. Use the Format function with a "000" format string
B. Use the Left function to shorten the string
C. Manually type extra zeros for low numbers
D. Add 1000 to the random number
Q13. What is the purpose of the customer code if not used for relationships?
A. To display to customers on reports and invoices without exposing internal IDs
B. To help Access link tables internally
C. To generate passwords for customer accounts
D. To sort customers alphabetically
Q14. In the video, what alternative method is mentioned for creating customer codes in a members-only tutorial?
A. Codes based on first name and last name
B. Codes based on zip code only
C. Codes using the order total
D. Codes based on the product category
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-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 video from Access Learning Zone shows how to create customer codes in Microsoft Access, especially when you want to hide internal auto number IDs from your customers.
This question came from someone looking to keep their client list size private. When you use auto numbers as customer IDs, customers could easily figure out how many clients you have by seeing their own ID number. For example, if a customer is assigned ID 15, they know they're the fifteenth in your system.
The general idea is that auto numbers should only be used internally within your Access database. Their main purpose is to establish relationships between tables and ensure each record is unique. While it's common to include auto numbers on forms and invoices in beginner tutorials, it's best to avoid exposing this information to customers if you want to keep your business data private.
This is very similar to the historical "German Tank Problem," where the Allies estimated German tank production during World War II based on captured serial numbers. For more on this topic, I recommend watching my other video, "Auto Numbers – Good or Bad," which covers the subject in more detail.
Now, suppose you want to give every customer a code to use externally, such as on invoices or receipts, without revealing your true customer count. The solution is to create a separate "Customer Code" field in your database.
Here's how to set that up: In your Customer table, create a new field called "Customer Code." Make it a text field so you can use both letters and numbers. By default, you can input whatever you want manually. But for consistency and ease, it's better to set a default value that generates a unique code for each new record automatically.
A good method is to combine the current date and time with a random number. For instance, start with a prefix like "C" for customer, then add a date stamp (using a format like YYMMDD), and finish with a random number between 1 and 100. This approach significantly reduces the risk of duplicate codes because it's unlikely you'll add multiple customers at the exact same second with the same random number.
Depending on how many new customers you add per day, you can adjust the timestamp to include more detail, like hours, minutes, or even seconds, to make duplicates even rarer. Using this technique, the only information customers could potentially figure out is the date they became a customer, which is usually harmless.
Once you've set the default value, make sure to mark the "Customer Code" field as required and set it to disallow duplicates. If you already have records without codes, you'll need to fill those in before setting the field as required. You can do this with an update query (and I have a separate video on creating update queries if you need guidance).
If you want the codes to always be a uniform length (which looks more professional), format the random number portion with leading zeros. For example, use a format that always displays the random number as a three-digit code, like 005 or 015.
After your table is set up, add the new "Customer Code" field to your customer form so new records use this code moving forward. After making these changes, use the customer code on reports, invoices, or anything your customers will see instead of the auto number ID.
Remember, the auto number ID remains in the table for internal use and relationships. Customers only see the customer code, which is much more anonymous.
If you're interested in assigning sequential numbers, such as invoice numbers, I have another video covering that process. For members, I even demonstrate how to create sequential numbers for each customer, so every customer can have their own series of invoices starting from 0001.
To access members-only content and extended video tutorials, you can join as a Silver member or above, which also unlocks additional perks. For sequential numbering and other advanced topics, membership is required, though this specific customer code video contains the essential information right here.
Thank you for watching and supporting these free TechHelp videos. I encourage you to subscribe to my channel so you never miss an update, and check out the forums on my website for more tips and to submit your own questions. If you have not yet taken my free Access Level 1 course, it's three hours of beginner Access training and new users often find it very helpful, even if they've been working with Access for a while. Level 2 is available for just a dollar if you want to continue your training.
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 Purpose and drawbacks of using AutoNumber as Customer IDs
Creating a separate customer code field in a table
Setting up the customer code as a Text field
Assigning a default value to the customer code using date and random numbers
Formatting customer codes with date/time stamps (YYMMDD, HHNNSS)
Appending a random number to customer codes to ensure uniqueness
Setting indexing to no duplicates on the customer code field
Making the customer code field required
Updating existing records with customer codes using update queries
Adding the customer code field to the customer form
Binding and naming controls for the customer code in forms
Formatting customer code numbers with leading zeros for uniform length
Using customer codes on reports and customer-facing documents |
