Customer Table, Part 2

Lesson 5: Designing Customer Tables & IDs

S M L XL FS | Slo Reg Fast 2x | Bookmark

In Lesson 5, we continue designing the customer table by deciding on appropriate data types for each field, such as using short text for phone numbers and hyperlink for websites. We will discuss when to use text or number fields, explain the difference between long integers and doubles, and cover why and how to add an ID field using auto number. I will show you how to move fields around in the table, discuss naming conventions, saving the table, and introduce primary keys and indexing. We will also talk about other important fields like notes, active status, and structuring your customer table efficiently.

Navigation

Prev: Lesson 04
Next: Lesson 06

Keywords

Access Beginner, table design, customer table, field types, primary key, auto number, data types, long integer, double, text fields, phone number field, zip code as text, leading zeros, sorting records, naming conventions, index field, unique identifier,

Subscribe to Customer Table, Part 2
Get notifications when this page is updated

Intro

In lesson five, we continue designing the customer table by deciding on appropriate data types for each field, such as using short text for phone numbers and hyperlink for websites. We will discuss when to use text or number fields, explain the difference between long integers and doubles, and cover why and how to add an ID field using auto number. I will show you how to move fields around in the table, discuss naming conventions, saving the table, and introduce primary keys and indexing. We will also talk about other important fields like notes, active status, and structuring your customer table efficiently.

Transcript

In lesson five, we are continuing designing the customer table. We are going to decide whether or not certain fields need to be text or number, like phone number for example, or if we are going to store that as a text or as a number. We will see the difference between long integers and doubles, why we want to use an ID field, an auto number field, how to move fields around inside the table, saving your table of course, table naming conventions, and we will talk about primary keys and indexing.

Continuing on with our customer table, everything we have added so far has been a text field, specifically a short text field. Let's add some other stuff that's not text. I am going to scroll down a little bit here. You can add a whole bunch of stuff to one table. You can have lots and lots of fields.

Now I have seen people send me databases with 100 fields in them. It should not have to go that much. If you start getting over, like, 50 or 60 fields, then you might want to start thinking about breaking that down into multiple tables. We will cover that more when we get into future classes, but do not try to put too much stuff in one table.

Speaking from experience, most tables that I have built have somewhere between 10 and 20 fields in them. You start getting up over 100 fields and you really want to start considering a second table. We will talk a lot more about multiple tables working together with relationships in Access Expert Level 1. I will put a link to that down below if you want to learn how to do it now.

The upward limit, the maximum limit is 255 fields in a table, but you should never get anywhere near that close.

Let's put in this customer's website. That will be a hyperlink field.

Like I said before, if you want to capture their email, make that short text.

How about the person's phone number? You think you want to use a number for this, but I am going to go with short text. Remember the way you tell the difference: are you ever going to be adding up a column of phone numbers or calculating the average phone number? No, so store it in text.

Now, if you want to do home phone, work phone, cell phone, I will allow it. Generally, I say if you have got multiple bits of information, store them in a second table, but if it's just two, three at the most, you could do home phone, work phone, cell phone.

Billing address, shipping address, that is fine. For class, we are just going to store the one phone number. Since I actually made a slide for this when I did the Access 2013 version, I will put it here for you too.

If you need to find the sum or an average, use a number. Otherwise, use text.

Drive that point home. Ever going to add two phone numbers together? No. Store it as text.

Do you ever think you are going to want to find the customer with the greatest number of children? Yes, you might. Or the average number of children for all of your customers if you sell, let's say, kids' clothes. Those are fields that you might want to store as numbers.

Another thing to take into consideration is the sort order. Numbers obviously are sorted numerically: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, and so on. That is numerically. That is a numeric sort. Text values are sorted alphanumerically, and they are sorted by the character placement.

For example, all the text fields that start with one will come before all of the text fields that start with two. So it is going to go 1, 10, 11, 12, 13, and then after all those, then you are going to get 2, 20, 25. So it is a totally different sort, whether you have got it based on numbers or text.

That is why if you are doing a lot of mailings, for example, you might want to split out the house number from the street name. That way you can sort all the stuff on the same street together and then put them in order based on the street number, which should roughly put them in the order for delivery, for example.

If you store them together, one Main Street will come right next to one Park Avenue. So that's something to take into consideration when you are figuring out what fields to put in your database.

Here is another one: leading zeros. Some things that are called numbers, like social security number, even though it is a number, we are going to store it as text. That leading zero is important.

Yes, you could store it as a number and still display it with a leading zero, like the zip code, for example, which is a five-digit number. We want to always have that leading zero there. It is just easier to deal with it as text, trust me.

Plus, if you are dealing with Canada or some other countries that have a postal code that accepts letters as well, then you could still use your zip code field for those countries. As a preview, we are going to store only the characters, not those dashes, because you can format the number however you want to.

Another thing to consider, is it always a number? If you have got a phone number field stored as a number, and once in a while you might get something thrown at you that has got letters in it, like 1-800-Drew-IDD or 1-800-Flowers. I remember back in the day, my grandma used to have a phone number that they still used letters in the phone numbers. You do not really see that often anymore unless it is a business number, like an 800 number, but it could still happen.

Next, how about the company name? That will also be short text. Let's say PC Resale does a lot of business with companies, a lot of business-to-business business. Now, I would like to keep that company name up with the person's name.

How do I move these fields around? It does not really matter internally as far as Access is concerned, but you can move these fields up and down. Watch this. Click right there. That will select that row. Let your mouse go. Take your finger off the button. Right now, click on that same spot and then drag it up. See how that line is moving right there? Drag it wherever you want it and drop it, like, let's say, right there.

Zip code, for example, click on it, click, drag, drop. I do not want to do that though, so let's put it back where it was. See how easy that is to move these things around. First name, last name, company name.

Let's come back down here. Let's say at each company, I want to know how many employees they have for marketing purposes. How many people you got there? That way I know that if it is a bigger company, they are more likely to buy more computers.

Now, I like to start my number fields off with the word numb, with the prefix numb, n-u-m, so numb employee. I like to keep everything singular. I know it seems kind of weird, but that is just my own personal preference. In fact, if you watched the last version of this class that I did, I was calling this numb employees. But since then, that was 2013. So in the past eight years, I have really come to make sure that I keep all of my field names singular, because it bites me. It hurts me a lot.

Look on my website, I have got a table called CommentsT, plural, and every time I am coding for it, I am like, look up this from the comment T, comment table, and I am wrong because it is plural in the table. So I will try to keep everything singular.

Numb employee is going to be a number field. If you put in just employee, what is that? The employee ID, the employee's, I do not know. Numb employee tells me, oh, a number of employees this company has. It makes a little more sense. I do not want to type out 'number of' every time. So for me, again, this is just one of my naming conventions, numb employee. If you are making a database based on family information, you might have numb children, for example.

This is going to be a number field because I might want to run a query later that says, show me a list of all my customers with more than 50 employees, or sort this list of customers, everybody from New York with more than 20 employees, sorted by the employee number. I might say, add up all of the employees that my customers from Pennsylvania had. What is the total number?

On the number field down below here, it says field size long integer. Click in there and then drop this box down. You will see all the different types of numbers that are available.

Remember what I said earlier? For now, we are only going to worry about long integers and doubles. That is it. Long integer for counting numbers: 0, 1, 2, 3, 4, and the negatives. Doubles for anything that has a floating point, a decimal.

In my more advanced classes, I will talk about the differences between these number types. For example, integer and long integer, what is the difference? Integers are smaller: they only go up to 32,000 some odd, as far as their value goes. Long integers go up to the billions.

However, integers are also smaller as far as the amount of room they take up in the computer's memory. So if you have got lots and lots and lots and lots and lots of numbers, you might want to use an integer. If it's something like, for example, number of employees, you might not have a company that has a billion employees. Long integer works, but it takes up more space. These are considerations to worry about later on when you become a database expert.

For now, just think integers for counting numbers, doubles for decimals. That is all I want you to worry about at this point.

What is something you use a double for? How about discount rate? That will be a number of type double, because you can put 5.5 in there. In fact, if you look under format, you will find percent. We will talk about format in a future lesson, coming up pretty soon.

Next, I want to keep track of how long each customer has been a customer, and give rewards to people who have been with me for a year, two years, five years, whatever. I just recently started doing this on my website. I put badges up there. You get a five-year badge, a ten-year badge. How long you have been a customer?

So let's put a field here, customer since. That is going to be the date you first became a customer. So let's make that a date time field. Remember, a date time field stores date or time or both together.

Let's keep track of each customer's credit limit, how much money they can spend with us. It's a money value. What does that mean? Currency. Currency is a special type of number field that is optimized for storing currency values, and it will use whatever regional settings you have got going on. So if you are in the United States, you get dollars.

Earlier, I mentioned a field called is active. Do not put a question mark on the end of it. Remember, only letters and numbers go in here. This is going to be a yes no field. Yes or no value. That will show up in lists, like on a mailing list. You can explain what a yes no field means by putting something over here in the description field if you want; you do not have to.

Pretty much every table, I put a notes field in. Pretty much, almost every one. That is going to be long text, formerly known as memo. If you hear me talk about something being a memo field, it's a long text field.

Pretty much everything I can think of, like customers, orders, products, vendors, all that stuff, you might want to throw some notes in there to explain something about this particular customer. So I put a notes field in just about every table. They will not waste space. A notes field will not use any space unless you put data in there. So it does not hurt at all to have a notes field in every table.

Now, we are going to skip some of these data types for now, like date time extended, that is new. We are going to skip large number. We are going to skip OLE objects and attachments and calculations and lookup wizards and all these things. We will get to these later on. Some of these I do not use at all, but I am going to show you how to use them in case you want to. For now, I think we are just about good with the number of fields we have got in here.

There is still one bit of information, and it is one of the most important bits of information that we are missing from our table. Can anyone think of what that is? Raise your hand. I swear, I will see it. Just kidding.

We are missing a way to uniquely identify each customer. Remember, every customer should have their own ID so that we know that this John Smith is different from that John Smith. So we are going to add a customer ID field to this table. What are we going to make it? We are going to make it an auto number, right there. Auto number. Very important.

Personally, I like to keep my ID fields up at the top of the table. So I am going to select that row, let it go, then click and drag, and put it at the top. That is just a personal preference, a style, if you will. I keep all of my IDs at the tops of the tables.

Auto numbers will start at one, and Access will keep counting up for you. So the next customer is two, and three, and four, and so on. If you delete someone, like customer three, that number is gone. But you do not have to worry about those numbers.

Do not use those numbers for anything in real life. I have recorded whole videos on why you should not rely on auto numbers for real-world things. For example, if you are a new company starting out, and you make someone an invoice, and they see on the invoice that they are customer number seven, now they know you only have six other customers. Those are the numbers that we do not want our customers to see. Auto numbers are purely for Access to use internally. You do not use them for things in the real world.

If you want to have a separate customer code, like p3642, that is up to you. I have whole videos on how to set that kind of stuff up. I will put a link down below in the link section for my customer code video. It is a little more advanced, but it shows you how to set up a second field that you can use if you want to give the customer a customer ID, but do not show them the actual customer ID.

Pretty much every table that I build is going to have an auto number in it, and again, I have gotten into some arguments with some other developers. I recorded a whole video on why you should use auto numbers. Products will have a product ID, vendors will have a vendor ID, employees will have an employee ID. These auto numbers are used by Access internally to make sure that you have unique records and to create relationships. For our customers to orders, for example, which customer does this order belong to? You look up the customer ID.

I am going to keep repeating this information because I want your brain to start thinking about auto numbers to make relationships. It is going to be a little while before we get to relationships, but just be thinking about it.

The other fields in this table really are not good for uniquely identifying people. You might have 100 Johns, you might have six John Smiths, you could have three John Smiths from New York. I have seen some companies use phone number. You could have two people with the same phone number. It happens. Not only do you have people that share the same phone number at home, but you get phone numbers that get reassigned. Someone moves out of the area and someone else gets their phone number. So an ID field makes it so that internally in the database, you know that this customer is this customer. It cannot possibly be someone else.

If you have got an old paper system, and I know a lot of you do, you are upgrading from either an old database or you are using Excel spreadsheets or even your own index card still, you might have customer codes or some kind of a product ID that you are currently using. That is fine. You can just make another field for that, but still add the auto number for Access. Trust me.

Auto numbers will never get reused in the system. So if you get 100 customers and you delete customer 62, that 62 will never be reused again. That is a good thing because you would not want customer 62 to have related records in the database and then they get reassigned to the new 62. One of the things that I like to teach is we do not delete records. We just mark them inactive. That is one of the reasons why we have this is active field. If someone dies or moves out of the area, do not delete their record. Just mark them inactive and they will not show up on your list anymore. We will talk about this as we go along.

Again, if you want to learn more about that, I have got a whole video that I recorded about not deleting records. I will put a link to that down below too.

Nothing that we have done so far has actually been saved to our database. So let us save this table so we do not lose all of our work.

Now, right up here in the Quick Toolbar, you will see the little floppy disk icon. Remember floppy disks? Who is old enough to remember those. You can click on that floppy disk or you can see the little tooltip pops up, Control S. That is what I use. Control S for save. It is the same as in Excel and in Word and pretty much every other program you can think of, Control S to save.

It is going to ask you for a table name. What are we going to call this table? Well, what kind of stuff do we have in here? Customers. So, customer, singular, customer T. I like to end all of my tables in the letter T. All my queries will be Q. All my forms will be F. All my reports will be R.

Do not worry about macros and modules. I have good reasons and a lot of my reasons for things that I do in the beginner classes have to do with when you get to the advanced classes, when we start doing things like SQL and building advanced queries and some Visual Basic. If you know upfront that it is a table, you can do things differently versus if it is a query.

Just trust me. Benefit from my 27 plus years of experience teaching Access. End all your tables in a T. You might see some people do this: they might do TBL customer. That is fine too. I just like my way better. So, customer T is how I am going to be doing it. If you are going to be taking my classes, do it the way I do it. Things will go easier for you.

Click OK. Now, you get this thing up: there is no primary key defined. Although a primary key is not required, it is highly recommended. A table must have a primary key for you to define a relationship between this table and other tables in the database. Do you want to create a primary key now? Yes, No, or Cancel.

Primary keys are important. A primary key is that one field that uniquely identifies each record. Now, we did add an auto number. So, we created the field already that we need to use for our primary key. We just forgot to tell Access: Hey, this is my primary key. There is a button right there that you can click to make that your primary key.

The reason why I did not like this is because, personally, I have been using Access for almost 30 years. I always forget to do that. I never remember to click on that primary key button. So, I wanted to show you the way that I do it because you are going to do it the same way.

Then you get this thing popping up in your face and are like, What is a primary key? Oh, I forgot to click the button. Trust me. It will happen.

So, I just saved the table. Then when this pops up, I go, yeah. If Access sees an auto number, it will use the auto number as the primary key because you should make all of your tables have a primary key. That is an auto number. That is just my rule for good database design.

If Access does not see an auto number in your table, it will add one for you and call it ID. But try to get in the habit of remembering to add an ID field for each of your tables.

What exactly happens is when you make something a primary key, Access will index it, no duplicates. We will talk about what indexing is later on. It is very important. It speeds up searches and sorts a lot in your database. No duplicates means you cannot have two of the same thing. Now, you can index other fields. You can index something like phone number if you want to make sure that no two customers have the same phone number. That is certainly possible.

You would not want to index something like last name because then you could only have one person with the last name Smith. That is bad. That is where you can go indexed, yes, duplicates.

We will talk about indexing. I have a whole separate lesson on indexing. For now, just keep in mind that the primary key is unique.

Now that we have got everything saved, let's go ahead and close the customer table. Notice it is sitting right over here in our navigation pane. There is the customer T right there. If you want to make more design changes to it, editing the fields and the data types and stuff, you can right click on it and go to design view. That will open it back up in design view where we just worked.

Let's go ahead and close it again.

When you are ready to actually add some records to it, put some data in it, you just double click on it. That opens it up in datasheet view. That is what we are going to do in the next lesson.

Quiz

Q1. Why should a phone number be stored as a text field instead of a number field?
A. Because phone numbers might contain letters or special formatting
B. Because you need to calculate the sum of all phone numbers
C. Because phone numbers should be stored using the double data type
D. Because Access does not allow numbers in phone numbers

Q2. What is the purpose of an auto number field in a customer table?
A. To store the customer's phone number automatically
B. To uniquely identify each record in the table
C. To store multiple addresses for a customer
D. To calculate the sum of all invoice totals

Q3. When should you consider splitting data into multiple tables instead of having one table with a large number of fields?
A. When you reach 255 fields
B. When you have more than 10 fields
C. When you have more than 50 or 60 fields
D. Only when you run out of storage space

Q4. What is a primary key in Microsoft Access used for?
A. To speed up network connections
B. To guarantee each record has a unique identifier
C. To store currency values
D. To store long notes about the customer

Q5. Which data type is best for storing values with decimal places, such as discount rates?
A. Long integer
B. Short text
C. Currency
D. Double

Q6. What is a recommended naming convention for tables in Microsoft Access based on the video?
A. Start table names with TBL or end with T
B. Always name tables in plural form
C. Name tables after their primary key field
D. Use numbers only for table names

Q7. Why is it important not to store fields like Social Security Numbers as numbers?
A. Social Security Numbers must be incremented
B. Numbers cannot be indexed
C. Leading zeros are important and may be lost if stored as numbers
D. Sorting will only work for text fields

Q8. Why should you avoid using real customer IDs (auto number) in customer-facing documents like invoices?
A. They are not unique
B. Customers might see they have a low number and learn you only have a few customers
C. Auto numbers are meant for text fields
D. Auto numbers are automatically reset

Q9. What is the difference between indexing a field as 'No duplicates' and 'Yes, duplicates'?
A. 'No duplicates' allows multiple identical values; 'Yes, duplicates' does not
B. 'No duplicates' means each value must be unique; 'Yes, duplicates' allows repeated values
C. Indexing does not affect duplicates at all
D. Both settings prevent duplicate values

Q10. What happens if you delete a record with an auto number in Access?
A. The auto number is reassigned to a new record automatically
B. The deleted auto number is reused for the next record
C. The deleted auto number is never reused again
D. The table must be compacted to reuse auto numbers

Q11. When should you use a long integer as a number field?
A. For storing sentences
B. For counting numbers like 1, 2, 3, 4
C. For currency values only
D. For hyperlinks

Q12. What is a good reason for adding a 'notes' field to nearly every table?
A. Notes fields waste a lot of space
B. Notes fields are required for indexing
C. Notes fields allow you to store optional descriptive information without using space unless needed
D. Notes fields are always displayed first

Q13. Why is it a good practice to keep all ID fields at the top of each table?
A. Access does not allow ID fields anywhere else
B. It is a personal or style preference for consistency and clarity
C. It prevents errors when saving tables
D. It changes the data type automatically

Q14. What must every table have in order to define relationships with other tables in Access?
A. A text field as primary key
B. At least 100 records
C. A memo field
D. A primary key

Q15. What shortcut is commonly used to save a table in Access?
A. Control P
B. Shift S
C. Control S
D. Alt S

Answers: 1-A; 2-B; 3-C; 4-B; 5-D; 6-A; 7-C; 8-B; 9-B; 10-C; 11-B; 12-C; 13-B; 14-D; 15-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 continues our work designing the customer table. In this lesson, I want to help you decide which fields should be set up as text, which as numbers, and what all that means in practical terms. For instance, we will look at why a phone number should typically be stored as text rather than as a numeric value. I will also introduce you to the differences between long integers and doubles, explain the importance of an ID field and auto number fields, show how to move fields around for better organization, discuss saving your table, provide some recommendations on naming conventions, and cover primary keys and indexing.

Up to now, all the fields we have added to our customer table have been short text. But a real-world customer table often holds a variety of data types, not just text. You can add a significant number of fields to a table, but it is important not to overdo it. While Access technically allows up to 255 fields per table, that is far more than you should ever need for a well-designed database. If you find yourself approaching 50 or 60 fields, it is probably a sign that the table should be split into multiple related tables. I generally find somewhere between 10 and 20 fields per table is a good rule of thumb. The subject of splitting information into multiple tables and setting up relationships is something I cover in my advanced Access classes.

Let's add a few more non-text fields to our customer table as examples. For a customer's website, you would use a hyperlink data type. For email address, just stick with short text.

Now, let's discuss phone numbers. It might seem logical to store phone numbers as numbers, but I strongly recommend using short text. The guideline here is straightforward: only store a value as a number if you reasonably expect to do math with it, like adding or averaging. We are never going to add up two phone numbers or compute the average phone number, so text is the correct choice. This principle also applies to fields like Social Security Numbers and ZIP codes, which can include leading zeros and should be stored as text to preserve their integrity. Using text also helps avoid issues when alphanumeric formats are possible, like with Canadian postal codes or phone numbers with letters.

When assigning multiple phone number fields, such as home, work, and cell, it is acceptable to add a couple of separate fields, but for anything beyond two or three, consider a separate related table for those details.

The way fields are sorted can also be affected by their data type. Numbers are sorted numerically, so they follow a natural sequence: 1, 2, 3, and so on. Text fields are sorted alphanumerically, so you might see 1, 10, 11, 12, and only then 2, 20, and so forth. For fields such as house numbers, splitting the number and the street name into separate fields can help maintain more logical sort orders for things like mailings.

You should also be aware of the differences between the various number types. The two to be concerned with at this stage are long integer and double. Long integers are whole numbers, positive or negative, while doubles can have decimals. A field like "number of employees" at a company would be a long integer since you would not have, say, 6.5 employees. A field such as discount rate would make sense as a double, since it could include fractional values. In Access, the long integer type can count quite high, up to several billion, but is also designed for situations where you are just counting whole numbers.

Let's cover date and time as well. If you want to record when a customer first became a customer, a date/time type is what you need. Currency is another special type of number tailored to hold money values, respecting your region's symbol and format.

For fields that indicate a yes/no response, like "is active," use the Yes/No data type. This is ideal for things such as mailing lists or flagging a record as inactive rather than deleting it altogether.

I suggest adding a notes field to nearly every table you create. Long text (formerly called memo) is suitable for this. Don't worry about unused space; notes fields only take up room if you actually enter notes.

There are some more advanced data types in Access like OLE objects and attachments, but I recommend skipping those until you know you need them. Stick to the basic types for now to keep things simple and efficient.

One of the most crucial elements for any table is a unique identifier. While you could attempt to use names or phone numbers, these are not reliable due to possible duplicates and changing data. Instead, you should always add an ID field, typically as an auto number. This field allows Access to uniquely identify each record, manage relationships, and ensure database integrity. I personally place my ID fields at the top of the table for clarity. Do not use these auto numbers for anything a customer might see, such as an invoice or public code. If you want a public-facing customer code, create a separate field for it.

The auto number field is especially important when you start relating tables together, for example, connecting customers to orders. Every table in a well-designed Access database should have an auto number ID field as the primary key, which ensures uniqueness and helps Access build relationships between tables.

Saving your table is straightforward. In Access, use the familiar save shortcut (Ctrl+S) or the save toolbar button. When prompted for a table name, I use singular terms followed by a T for table, like customerT. This helps keep everything organized, especially as your database grows to include queries (Q), forms (F), and reports (R). It is a naming convention I have developed over the years, and it will serve you well in the long run.

If you forget to set your primary key before saving, Access will prompt you. The primary key must uniquely identify each record, which is why we use an auto number field for this purpose. When you designate a primary key, Access sets that field as indexed with no duplicates allowed, greatly improving search speed and ensuring record uniqueness. You can index other fields if needed, but not every field is appropriate for indexing.

As for deleting records, I recommend rather than deleting, simply marking them as inactive. This is why the "is active" field is useful. Deleting records can lead to confusion and possibly to losing important related data.

Once you have saved the table with all the necessary fields, including the ID field, you will see it in the Access navigation pane, ready for data entry or further changes. To edit the structure later, simply open the table in design view. To enter data, open it in datasheet view.

If you want to watch a complete video tutorial with step-by-step instructions on everything discussed here, you can find it on my website at the link below. Live long and prosper, my friends.

Topic List

Choosing field data types: text vs number
Storing phone numbers as text vs number
Number fields: long integer vs double
Adding a company website as a hyperlink field
Storing email addresses as short text
Storing numeric values only if calculations required
Numeric vs alphanumeric sort order in tables
Handling leading zeros in number-type fields
Naming conventions for fields (singular names, prefixes)
Using "numb" prefix for counting fields
Choosing between long integer and double for numbers
Adding date fields using date/time data type
Using currency fields for monetary values
Adding yes/no fields for status indicators
Adding long text fields for notes
Moving fields around in table design
Saving and naming your table in Access
Naming conventions for tables (ending in T)
Creating and using an auto number ID field
Setting the primary key in a table
Purpose and function of indexing fields
Importance of unique identifiers in tables
Switching between design view and datasheet view

Article

In this lesson, I am going to show you how to continue designing a customer table in Microsoft Access and make the right choices when it comes to each field's data type, arrangement, and naming. By the end of this tutorial, you will understand why certain fields like phone numbers should be stored as text, the difference between long integers and doubles, the purpose of using an auto number ID, how to move fields around in your table, and best practices for naming tables and fields. We will also talk about primary keys and indexing, which are essential for making your database efficient and reliable.

Let's begin by talking about the structure of our customer table. Up to this point, most of the fields you create will be short text fields. Common examples include first name, last name, and company name. Short text fields are ideal for information that does not need to be calculated or summed, such as names or addresses.

Now, let's think about adding other kinds of fields that do not fit the text category. For example, suppose you want to record the customer's website. This should be a hyperlink field. If you want to store their email address, keep that as short text. This works just like addresses and names, which usually do not require anything more than simple text.

A common question is what data type to use for phone numbers. Your first instinct might be to choose a number type, but in reality, phone numbers should be stored as short text. Ask yourself: Will you ever need to add two phone numbers together or calculate their average? The answer is no. You will not perform math on phone numbers, so treat them as text. This principle applies to most fields that look like numbers, such as social security numbers and zip codes. These often include leading zeros, and if you use a number type, you risk losing those important digits. Storing them as text preserves the formatting and makes it easier to adapt for international addresses, such as postal codes that use both letters and numbers.

Sorting behavior also depends on field type. Number fields are sorted numerically, which means 1, 2, 3, 10, 12, and so on. Text fields are sorted alphanumerically, so you might see records ordered as 1, 10, 11, 2, 20. This can cause confusion if you are not careful. For example, combining street numbers with street names in one field would result in all streets starting with 1 grouped together, regardless of the street itself. To avoid this, consider splitting the house number and street name into two separate fields, especially if you need to sort them for mailings.

You should also account for rare cases where numbers contain letters, such as custom phone numbers like 1-800-FLOWERS. Storing these as text ensures your database remains flexible in the future, and you will not run into formatting issues or accidentally strip out needed characters.

Company names should also be short text. If your company serves businesses and you want to keep track of which company your contacts belong to, consider placing the company name field near the name fields. You can rearrange fields in Access by clicking the row selector on the left side of a field, releasing the mouse button, then clicking and dragging the field to the desired position. This helps keep related information together for easy access.

Sometimes you might want to store additional numeric information, like the number of employees at a company. I recommend starting number fields with a "numb" prefix, such as numbEmployees, and keeping field names singular. For example, use numbEmployee for the number of employees and numbChildren for the number of children in a family. Naming fields consistently and clearly helps you and anyone else understand what the field represents.

For numeric fields where the value is something you might want to sum or take the average of, use a number data type. The specific number type you choose depends on what you need to store. In Access, choose between "long integer" and "double". Use long integer when the values you expect are whole numbers, like the number of employees. Long integers can hold very large whole numbers, but if you need to store very large amounts of tiny numbers, integers use less space in memory. Use "double" when you need to store decimal values, such as discount rates or percentages. For a field like discountRate, set it to double so you can use values like 5.5. You can also adjust the field's format to show percentages as needed.

To keep track of how long each customer has been with you, create a "customerSince" field and use the Date/Time data type. The Date/Time field can store any combination of date and time values. For a field like creditLimit, which stores a monetary value, use the Currency data type. Currency fields are optimized for storing money and will use your regional currency settings automatically.

If you want to indicate whether a customer is active or inactive, add an isActive field with a Yes/No data type. In Access, this field shows up as a simple checkbox. You can explain what the field means in the Description property, but it is optional.

It is also good practice to include a notes field in each table, using the Long Text data type (formerly known as a memo field). Long text fields allow you to store larger blocks of text, such as special instructions or miscellaneous observations about each customer. Having a notes field never hurts, as it will not take up space unless you enter something in it.

There are more data types in Access, such as date/time extended, large number, attachment, and calculated fields. For now, you can skip these until you are more experienced or have a specific need for them. Focus on the main data types like short text, number, date/time, currency, and yes/no.

One critical element we have not addressed yet is a way to uniquely identify each customer. Names, phone numbers, and even company names are not unique enough for a database, so we need to add a customer ID field. This will be the primary key for the customer table, and it should be set as an AutoNumber field. Access will automatically generate a sequential, unique number for each new record. Auto numbers start at 1 and increment by 1 for each new record. Even if you delete a record, that number will not be reused, which is good for database integrity.

It is best to keep the customer ID field at the very top of the table. To move it, select the row, then click and drag it to the top. This is just a personal preference, but it keeps fields organized, and makes it clear which field serves as the table's unique identifier.

Auto numbers are not intended for customers to see. For example, do not print the auto number on invoices or let customers know that they are customer 7, which might reveal how few customers you have. Use auto numbers strictly for internal purposes. If you want customers to have a code, create a separate field for that.

Every main table in your database should have an auto number field (customerID, productID, vendorID, and so on). This practice ensures every record is unique and makes it much easier to create relationships between tables, such as associating orders with customers using customerID.

Be cautious about using other information as a primary key. You might have multiple customers with the same name, or even the same phone number (for example, family members or people who move and their numbers get reassigned). Rely on the auto number to make sure every record is unique.

If you are importing records from a legacy system that already has its own codes or IDs, create an extra field for that information, but always include the Access auto number as the primary key.

Instead of deleting records, consider just marking them as inactive using the isActive field. This means you keep all your historical data, and avoid accidentally reassigning deleted IDs to new customers.

Once you have set up your fields, nothing is saved until you save the table. Click the floppy disk icon in the upper corner or use the shortcut Control+S to save. You will be prompted to give the table a name. I recommend naming tables with the type of record they contain, plus a T for table. For example, name this table customerT. This helps you keep tables, queries, forms, and reports organized - use Q, F, and R endings for those other object types.

If you forget to designate a primary key before saving, Access will warn you and offer to create one. If your table already has an auto number, Access will use it, which is what we want. The primary key is automatically indexed and marked as no duplicates allowed, which means you cannot have two of the same value. Indexing makes sorting and searching much faster, which becomes very important as your database grows. You can choose to index other fields if you need, such as a phone number, to ensure no two records have the same value, but be careful - indexing last names, for example, would restrict you to only one Smith, which is not practical.

Once you have saved your table, you can close it. The table now appears in the navigation pane on the left. To make further design changes to field names or data types, right-click the table and choose Design View. To enter data, just double-click the table to open it in datasheet view.

In the next lesson, we will start adding records to the customer table. For now, you have set up a solid, well-structured customer table with the right types of data for each field, a unique auto number as your primary key, and clean naming conventions to keep everything organized and easy to use.