Customer Table, Part 1

Lesson 4: Create Customer Table & Set Fields

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

In Lesson 4, we begin building our customer table in Microsoft Access. I will show you how to create a new table using Design View, discuss best practices for choosing field names and naming conventions, and explain the various data types available, such as Short Text, Long Text, Number, Date/Time, Currency, and others. We will walk through adding fields like FirstName, LastName, Address, City, State, and Zip, and I will explain when to use different data types and why. We will also discuss which data types and naming conventions to avoid.

Navigation

Prev: Lesson 03
Next: Lesson 05

Keywords

Access Beginner, field names, naming conventions, data types, table design, short text, long text, number fields, auto number, boolean field, currency field, date time field, reserved words, lookup wizard, calculated fields, address field, zip code, prima

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

Intro

In lesson 4, we begin building our customer table in Microsoft Access. I will show you how to create a new table using Design View, discuss best practices for choosing field names and naming conventions, and explain the various data types available, such as Short Text, Long Text, Number, Date/Time, Currency, and others. We will walk through adding fields like FirstName, LastName, Address, City, State, and Zip, and I will explain when to use different data types and why. We will also discuss which data types and naming conventions to avoid.

Transcript

In lesson 4, we are going to begin building our customer table. We will see how to create a new table. We will learn about field names and naming conventions. We will learn about the different data types that are available. And we will start building the customer table.

Now that we know our way around the Access interface, let's build our first table.

One thing I am going to mention, I just switched computers. My other computer was a brand new one that I wanted to use for starting up a blank brand new database to show you what it looks like on a new system. I just switched over to my other computer because this is the one I normally record my classes on. I do not want anybody freaking out if you see that the file name up here is different. Everything else is exactly the same. Do not worry about it.

Now, let's go back to our index cards. Find the card for your customer table. These are all the fields we are going to add to the customer table in our database: first name, last name, address, and so on.

Access gives you multiple ways to do pretty much everything. Just like in Word and Excel, there are five ways to do pretty much anything you can think of. There are multiple ways to create a new table.

Click on the Create tab and you will see Table, Table Design, and SharePoint Lists. Do not worry about SharePoint Lists. That is for working with online databases. I have got a whole seminar as in classes for SharePoint stuff. We will worry about that later. For now, there is Table and Table Design.

The first option here, Table, puts you into Datasheet View. This is where Access started us when we first started to create the database. Remember that? Datasheet View lets you type in the data first and then define the tables afterwards, kind of like a spreadsheet. You can type in Joe, Tab, Smith, Tab, New York, and so on, just like you are working with Excel.

Personally, I find this backwards when it comes to developing databases. I think one of the strong points of Access is you define the table first. You specify exactly what type of data each field is going to contain. Then you add data to it afterwards. So I do not like this method. I am going to close this table. And when Access says do you want to save changes, I am going to say no. Go away. We do not want that.

We are going to create a table the right way. Click on Create and then Table Design. This puts us right into Design Mode. Now, Design View lets you specify the field names, like first name, last name, and so on. The data types - text, number, currency, that kind of stuff. And then once you are done defining the table structure, then you can go back and add your data to it. It is better to build the structure first then add the data.

One of the problems with Excel, for example, is anybody can come in here and just type in anything they want, wherever they want. Put Joe there, put Smith over here. You can set up some kind of basic validation and some structure in here. But an Access database is what you want to use to make your users conform to the style and the types of data that you want them to be typing into each field. Sometimes you want a random notepad to be able to come over here and type stuff in. I get it. Excel has its purposes. But Access is what you want to use for structured data entry.

With Access, you force your users to type in the data types that you want. They can not type in text, for example, in a currency field. If you want a date, Access makes sure they give you a date.

When constructing a table, there are two types of information that you have to have, and an optional third one. You have to have the field name, the data type, and optionally a description. The name describes the kind of information that you are storing: first name, last name, address, city, state, all that. Those are field names.

Then the data type describes what kind of information that field is going to hold: text, number, currency, date, and lots more. If you make the data type a number field, then the user can not type something like blue in there. They have to put a number in. Later on, we will get to something called validation rules, where you can say it has got to be a number between 10 and 100.

Finally, you can type in an optional description if you want to. This is usually to explain what an ambiguous field might be to a user who does not know your database. For example, you might have a field called IsActive. You know that means it is an active customer. They buy from you regularly. They are on your mailing list, that kind of stuff. Someone else, a new employee, might look at that field and go, what does that mean? It is a guy who likes to exercise, he is active. That can tell the user in the description field what that means. If you type in something in the description field, it will show up down in the status bar when they are entering data in.

Let's start by putting in our first field. Come right over here, and in the field name column, type in FirstName. No spaces, capital F, capital N, and then press Tab.

Now remember, this is just my personal naming convention, but if you are going to be learning from me, I strongly suggest you follow it too. It is good. I have been doing this for almost 30 years now, and it works. I do not like to put spaces in my field names, my table names, my query names, my form names.

Trust me, when you get into more advanced stuff, macros and modules and VBA programming, you are going to thank me. This is what I usually do. I do capital F, no space, capital N name. Nice and easy, very popular with Visual Basic programmers.

If you already have been building Access databases for a little while and you have got some spaces in your field names, that is OK. You can still work with them. Access will allow it, but now you have got to remember to put square brackets around your field names if you use them in queries, Visual Basic, or SQL. Once you start getting into more advanced stuff, you are going to thank me and wish you did not have spaces in your field names or your table names. In fact, if you are just getting started and you have got some other databases that you have been working on that have spaces in them, you might want to go back now and get rid of those spaces. Trust me.

Access is not case sensitive. So all lower case first name, all upper case FIRSTNAME, Access does not care. All lower case or all upper case, and it is OK. It is a little more difficult to read, especially when you get into programming. And if you do decide to get into the more advanced stuff like the VB programming, if you have mixed cases in your field names and your variable names and stuff, if you type them in all lower case, Access will capitalize them for you, and that is a quick way that you can see the types of things in wrong. That is a trick that I will get into in my developer classes. So trust me, type in your variable names like this, your field names. You will thank me later.

You might see some programmers, some databases, use the underscore. That is perfectly fine too. I used to be a C programmer years ago, and in C, that is how you do all your variable declarations: first_name. That is OK. Again, I like my method better.

You can use a hyphen in your field name if you really want to, but again, I do not recommend it. I do not recommend using any non-letter or number characters in your table names or field names. That can be confused with subtraction sometimes. Again, stick to that. Trust me. Just try to keep it to letters and numbers. Do not use question marks. Do not use dashes.

You may see some other developers or book authors use this kind of notation. This is called Hungarian Notation. It is basically the first name field with a prefix on the front to tell you what kind of information first name is, if it is text, for example. I personally do not bother with this because most of my field names are self-explanatory. I usually know that first name, last name, those are text values. Credit limits, currency, amount, number of children, a numeric value. If you are working in large teams of developers, you might want to do that, but generally, as sole developers, I do not ever bother.

Like I said, I have been building Access databases for almost 30 years, and my naming convention works pretty well. The important thing is to maintain consistency. Stick with one style of naming your fields and your tables and such.

There are some reserved words that Access has that you should avoid using for your field names. For example: Name, Date, Left, Picture, those are all either property names or function names. Do not make a field called Name, for example; use FirstName or LastName. Do not have an OrderDate be just Date. Date has special meaning in Access. I have got a page on my website with all of the reserved words on it. I will put a link in the description down below the video if you want to check that out. There are a lot of them, so I am not going to put them here in the video, but go check that page out. You will see the big long list.

Usually if you try to keep your field names compound, like more than one word, like FirstName, OrderDate, you are safe. You are OK. Use ProfilePicture or LeftJunction or something like that. Try to be as descriptive as you can without going overboard. Usually two words works perfectly for a field name or a table name.

OK, so I put in my field name, FirstName. Now I am over in the data type column. The default option for the data type is Short Text. If you drop down this combo box, you will see the complete list of all of the data types available in Access. The first data type is Short Text - it is the most popular one. You are going to use it most of the time. Short Text includes pretty much all of the printable characters on the keyboard: A through Z, upper and lower case, 0 through 9, exclamation point, ampersand, all that stuff. These can be anywhere from 1 to 255 characters long.

You can use a Short Text field to store most types of data: names, addresses, all that stuff. If you happen to be using an older version of Access from 2010 or earlier, this used to be called just Text. They used to have Text and Memo, and they changed it in 2013 to Short Text and Long Text. So if you hear me refer to something as a Text field, I mean Short Text. And if I say Memo, I mean Long Text.

What is Long Text, you ask? Good question. Short Text fields can only be up to 255 characters long. Long Text fields can be 65,000 plus characters long. You can store a lot of information in a Long Text field. Long Text fields also support formatting, so you can have rich text with colors and bold, italics, all that stuff. In fact, in some of our later lessons, we are going to see how to make a letter writer where we can actually type up a letter just like you would in Microsoft Word, but it is all stored inside the database. Then you can hit print, print it out, and it looks just like a Word document. That will be using formatted Long Text.

Long Text fields used to be called Memo fields in the older versions of Access. So if you hear me say a Memo field, I am talking about Long Text.

At this point, a lot of people ask me why not just use Long Text for everything if it is so much better. Short Text has some benefits. In fact, I made a 15 minute video that I put on my website and on YouTube that just goes over the differences between Short Text and Long Text and why you want to use each one. So if you are really curious, I will put a link to that video down below in the description field. See, this is why it is really nice that I have been doing this for so many years because I have got videos to explain all kinds of other stuff and I can point in that direction if you want to know right now. Instead of saying, we will cover it in a future class, I have already covered it. Go watch that video if you really want to know.

The bottom line is, if it is a normal bit of information like a name and address, a social security number, something like that, a small bit of information, use a Short Text field. If you do not know how much information you are going to be typing in, you could be typing in lots and lots of notes, use Long Text. Why specifically? Go see that video. If you are going to be typing in directions to get to a customer's house or information about a service call or, like I said before, the letter writer, use Long Text. Everything else, Short Text.

Short Text fields are much, much more efficient when it comes to searching and sorting. If you have a big, long list of customers, for example, you want to sort them by last name. Short Text is much more efficient.

Next, you will see there are number type fields. You have got your text, Short Text, Long Text, next we have Numbers. There is a whole bunch of different kinds of numbers, but there are two that we are going to focus on for beginners: long integers and doubles.

We are going to use long integers for any kind of counting number: 1, 2, 3, 0, negative 16, integer type numbers. Doubles are what we are going to use to store floating point numbers, anything with a decimal. How many gallons of gas did you put in your car? 10 and a half? That is 10.5. We are going to put it in a double. How many children do you have? 5? That is a long integer. The two types of numbers I want you to focus on. Yes, there is a whole bunch of other ones. There is byte, there is integer, there is single, there is decimal. Do not worry about all those for now. In future classes, I will go over all the little nuances between them, but for right now, long integers for counting numbers (integers, basically), doubles for floating points.

One thing to take into consideration when you are planning your database is should the field be a number or text? My rule of thumb is if you are ever going to perform calculations on it, use a number. If not, store it as text. Text is much easier to work with.

For example, Social Security Number, for us in the United States here - store it as text. Yes, it says number and yes, it is always numeric, but it is easier to work with as text. You are never going to be adding up a column of Social Security Numbers. You are never going to be finding the average of a bunch of Social Security Numbers. And sometimes they start with zero, so we do not want to deal with that zero falling off. Store it as text.

Zip codes - yes, they are all numbers. Store them as text. They are easier to work with. You are not going to be figuring out the largest zip code or the average of a bunch of zip codes. Store it as text.

Keep in mind that if you are working with old school systems, old databases, people using paper systems, they might have something called a part number, but it is not a number. The part number might be PT463G. That is not a number. That is text. Keep that in mind.

Access has something called a Date/Time field. A Date/Time field can store a date or a time or both. So you can store just a date, January 1, 1980, or just a time, 4:55 PM, or both at the same time, January 1, 1980 at 4:55 PM. You can be as specific as you want.

Access has a special kind of numeric value called Currency. Use currency fields for dealing with money. Currency is a special type of floating point number that is optimized for dealing with currency. Use currency values for money. I will explain the specifics a little bit later. One nice thing about currency is it will observe the regional settings on your computer. So if you are in a different country (I am here in the United States), if you are in Europe, it will use euros, for example, or whatever your country's currency is.

Next up, we have the Yes/No data type, also True/False, On/Off. These are called Boolean values.

Next, we have a very important data type called AutoNumber. I make sure there is an AutoNumber in pretty much every table that I build. AutoNumbers are going to start at one, and then every time you add another record, it is going to increment that number, so each record has a unique identifier: customer 1, customer 2, customer 3. No one's got the same AutoNumber. Later on, when we create relationships between our multiple tables, this is how we will track which customer belongs to an order, for example, because we will store the customer ID in the order table. Access maintains these numbers for you. You do not have to worry about them. You do not even have to see them if you do not want to. They are stored in the table and they are used internally for Microsoft Access to make relationships.

I sometimes get into arguments with other developers who do not like AutoNumbers. I put a whole huge video together on why I like AutoNumbers. If you care, I will put a link down in the description below the video. Go watch it if you want to. I have good reasons why I like to use AutoNumbers in almost every one of my tables.

Next up are two data types that I try to recommend people do not use. OLE Object stands for Object Linking and Embedding. These are pictures, documents, videos, anything you can copy and paste, you can store in an OLE Object. You can copy a whole Word document or an Excel spreadsheet or a PDF file and you can paste it into a table inside your Access database.

Just like the Attachment data type, you could take whole files and save them inside your database. The problem with these two methods, with these two data types, is that they make your database big and bloated and slow and it does not work right if you have lots of attachments inside of it.

Yes, I am going to show you how to use both of these, because they do have their place. I am not saying they are 100 percent evil, but I am also going to show you better ways to manage working with files and pictures than using these two data types. People always ask me, that is why I am mentioning them, but we will get to these eventually.

Next up, you will see the Hyperlink data type. This is good for storing web pages and email addresses because if you click on it, it will load up your web browser or your email program. Personally, I like hyperlinks for storing web addresses. I do not like hyperlinks for storing email addresses. For web pages, it is nice because you can click on the web link and it loads up your web browser. For email addresses, it gets hard working with email addresses. I would rather store them in Short Text, and I will show you a way in the more advanced classes on how we can send an email from Access.

Do not use the Hyperlink data type for email addresses and we will talk about this when we get to email.

Next up is the Calculated field type. I do not like storing calculations in my tables. As a general rule of thumb, if you have to do a calculation, do it in a query. There are some exceptions and I will talk about this in a future class. Remember we had this conversation earlier. But if you see Calculated field types, do not use them. Trust me.

Microsoft added the Calculated field type to tables to make it easier for beginner users to do calculations like sale price minus unit cost equals profit. But that is more properly and better done in a query. Calculated fields can cause trouble later on.

Next up is the Lookup Wizard. Lookup Wizard is an evil wizard. Are you a good witch or a bad witch? Well, the Lookup Wizard is a bad witch. Do not use them.

What is the Lookup Wizard? Microsoft wanted to give beginner users the ability to look up a value from a list. You want to pick a state from a list of states - New York, Pennsylvania, Texas, whatever. Put it in the Lookup Wizard and that goes right in your table.

But it violates the normal database rules of normalization. You do not want to use them. Trust me. When you get into anything but the most basic of databases, Lookup Wizards are a nightmare to work with. If you do not use them, and if you have them in your existing databases, get rid of them. I will show you how to get rid of them when we get into the expert classes when we start working with relational combo boxes. If you are a new Access user, this is your first time using Access. Trust me. Do not use the Lookup Wizards.

Now that we have gotten a basic overview of all the different types of data types, which one do you think works best for FirstName? That is going to be text. Short Text or Long Text? I do not know too many first names that are longer than 255 characters, so Short Text. Then we will hit Tab.

If you want to type in a description, you can. Personally, I almost never use descriptions, very rarely. If I know I am building a database for someone who has no computer skill and the field name is confusing, then maybe I will put a description in there. But I almost never do, unless I am building a really complicated table for myself later on and I want to remind myself what this field is for, then sometimes I will use that, but generally no. So just hit Tab.

If you decide you want them later, you can always come back and put them in later. If you are working in a team with other developers or multiple people in your office that are designing the database, you may want to rely on the description fields a little more to explain to your teammates what some of these fields are. One of the most difficult things as a database consultant myself to work with is when I get a database from a client and they want me to fix it or add something to it and I have no idea what some of their field names are. So the description can help me sometimes to understand what they have done. But I find that most people do not use them anyway, so I have to call them up and say, what does this field mean?

Next field, LastName. Remember, no space, capital N, Short Text, Tab, Tab, done. Nice and quick, once you get the hang of it.

Do you want middle name, middle initial, prefix, suffix, title, all that stuff? That is completely up to you. That is one of the benefits of building your own database with Access - you can decide what is important and what is not important for your database. I have built databases for customers where they really only wanted one field for the customer's name because it was not important. And I have built databases for mailing list customers where they wanted everything: prefix, suffix, title, middle name, all that stuff.

You decide. One of the nice things about the Short Text field is that you can specify a limit on the size down here where you see field size 255. By default, they start off at 255 characters. That is the biggest possible. If you want to limit that to only one character or five characters for a zip code, for example, you can change that and make it smaller. We will talk more about field sizes a little bit later on. You can not do that with a Long Text field - people can type in as much as they want.

For the purposes of class, I am going to go with just FirstName and LastName, which honestly is all I use in my database. My company database, I have just got FirstName and LastName, I do not care about all the rest of it. Honestly, I really only care about FirstName and email address. It is an internet business. I call people either FirstName when I am addressing them in an email. LastName, I need it for credit card validation, but that is about it.

How about the customer's address? That is a Short Text field as well. I personally have always been happy with just one address field. A lot of my clients like two: Address1, Address2. They like to put the suite number or apartment number in the Address2 field. That is fine. If that is what you want, build it that way. You can put line breaks inside of a Short Text field. So you can type in part of the address, hit the line break (Shift+Enter or Ctrl+Enter) and it will give you a second line in the address field if you want that.

I have had customers who have wanted to break the address field up into the number on the street, the street name, the type, whether it is a drive or avenue. Again, the benefit of building it in Access - you can make it as specific as you want to. For me, just Address.

Let's finish this up with City, State, and Zip. If you are in a different country, you might want to use PostalCode or whatever you have for your region. I am in the United States, so I am going to use US addresses here. If you do business internationally, you might want to add Country. That is fine. If you are in Canada, change State to Province, whatever you want to call it.

What I do for Country is most of my customers are here in the United States. But I have got customers in pretty much every country of the world. But I will leave Country blank for US customers. That is just how I do it. You obviously would probably want to make your Country blank for your home country.

So far, we have only used text data types. In the next lesson, we are going to add some additional fields to this table. And we are going to start talking about some of the other data types.

Quiz

Q1. What is the preferred way to create a table in Access according to the lesson?
A. Use Table Design to define fields before adding data
B. Use the Datasheet View and type data first
C. Use the SharePoint Lists option
D. Import tables from Excel directly

Q2. Why does the instructor advise against using spaces in field names?
A. It prevents sorting by those fields
B. Spaces make queries, macros, and code more complicated
C. Access does not allow spaces in field names
D. Field names with spaces cannot be used in reports

Q3. Which data type is MOST appropriate for storing a first name in Access?
A. Short Text
B. Long Text
C. Number
D. Currency

Q4. What is a valid reason to use an AutoNumber field in your tables?
A. To automatically generate unique identifiers for each record
B. To store the customer's credit limit
C. To allow users to choose their own unique numbers
D. To store multiple text values

Q5. When would you use a Long Text field instead of a Short Text field?
A. When storing a paragraph or large amount of notes
B. When storing a single letter code
C. When storing a phone number
D. When storing a zip code

Q6. According to the lesson, which should be stored as text instead of number data type?
A. Social Security Number
B. Amount of sales
C. Number of children
D. Credit balance

Q7. Which of the following is NOT recommended as a field name in Access?
A. OrderDate
B. Name
C. FirstName
D. CustomerAddress

Q8. What is an OLE Object data type used for?
A. Storing pictures or larger objects as embedded files
B. Storing short text like names
C. Storing only numbers and dates
D. Storing hyperlinks

Q9. Why does the instructor discourage using Lookup Wizards in tables?
A. They violate normalization and can cause problems in advanced databases
B. They make sorting impossible
C. They are not supported in Access
D. They only accept number fields

Q10. What advantage does storing addresses as Short Text provide?
A. Ability to control the maximum length of the field
B. Allows embedded formulas like Excel
C. Prevents duplicate entries automatically
D. Allows storage of pictures

Q11. What is the purpose of the Description field when designing tables?
A. Provides an optional explanation of the field's purpose
B. Required for table validation rules
C. Determines the data type of the field
D. Serves as the default value for new records

Q12. What is the instructor's rule of thumb for choosing between Number and Text field types?
A. Use Number only if calculations will be performed on the field
B. Always use Number for anything containing digits
C. Text fields are only used for long paragraphs
D. Use Text fields for all monetary values

Q13. Why should field names avoid using hyphens or non-alphanumeric characters?
A. They can be confused with calculation operators
B. Access will automatically rename those fields
C. They are required in older versions of Access
D. They help Access auto-fill data

Q14. Which field naming convention is most recommended by the instructor?
A. Capitalize each word, no spaces (e.g., FirstName)
B. Use all lowercase letters with spaces (e.g., first name)
C. Underscore between words (e.g., first_name)
D. Use hyphens between words (e.g., first-name)

Q15. What benefit does a Currency data type offer over Number for monetary fields?
A. It observes the regional settings and is optimized to handle money
B. It only stores whole numbers
C. It accepts text and formatting
D. It converts values to other currencies automatically

Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-B; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-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 is all about getting started with building your first customer table in Microsoft Access. In this lesson, we take the first steps in designing a new table. You'll learn about choosing field names and understanding naming conventions, exploring the various data types that Access offers, and we begin putting together the structure of our customer table.

First, to clarify, you might notice that the file name has changed during this session. That's just because I switched computers for recording purposes. Nothing else is different about the setup or the steps we'll follow, so you can proceed as usual.

We start off by referring to our planning material - those index cards where we've listed all the fields we'd like to include in our customer table, such as first name, last name, address, city, and so on.

One of the great things about Access is the flexibility it gives you to perform the same action in different ways. Just like with Word or Excel, there are usually several ways to get a job done. When it comes to creating tables, you have a couple of main options: you can use Datasheet View, or you can use Table Design View. Forget about SharePoint Lists for now, since that's for online database work.

Creating a table using Datasheet View is a bit like working with Excel - you just start entering your data, and then define the structure later. Personally, I don't find this method particularly helpful when building a database. The strength of Access is in planning your table structure up front: decide on your field names, choose the right data types, impose some rules, and then start adding your data. This approach helps keep your data consistent and much more reliable.

So, instead, I recommend starting in Design View. In Design View, you set up each field with a name and a data type right away. This ensures users enter only the sort of data you want in each column - for instance, only dates go into a date field, only numbers in a numeric field, and so on. Unlike Excel, which gives users total freedom to enter anything anywhere, Access lets you control the data entry process. Structured data entry is one of the biggest reasons to use Access.

When you are constructing a table, you need at least two pieces of information for each field: the field name and the data type. There is also an optional description, which can help clarify what a field is for, particularly if its purpose might not be obvious to others.

Field names describe what kind of information you want to store, such as FirstName, LastName, Address, and so on. The data type determines what sort of information each field will accept: short text, long text, number, currency, date, and so forth. If you choose a numeric data type, for example, users cannot enter words into that field. Later on, you'll also learn about validation rules to keep data even more controlled.

The description column is optional. It is useful for fields that might not be self-explanatory - like 'IsActive,' which could mean actively purchasing customers, mailing list members, or something else. Entering a description appears in the status bar and can help clarify things for other users.

Let's begin by entering our first field. In the Field Name column, type FirstName, with no spaces - capital F, capital N. This naming convention is what I recommend for all table names, field names, queries, and forms. It makes things much easier especially if you ever move into using macros or VBA. If you use spaces, Access allows it, but then you'll need to use square brackets in queries and code, which can be a headache later.

Access is not case sensitive - firstname, FirstName, and FIRSTNAME are all treated the same by the system. Still, for readability, especially if you get into any programming, I suggest you use capital letters for each new word. Some programmers use underscores - first_name - which is fine. Personally, I prefer my style without special characters. Avoid symbols like hyphens, question marks, or any punctuation in your names, as these can confuse Access or be misinterpreted in code.

You might see some developers use prefixes to indicate data types, like 'txtFirstName,' but I usually avoid this. Most field names, like FirstName or LastName, are obvious enough by themselves. The important thing is to choose a style you like and stay consistent.

Also, there are some reserved words in Access that you should not use as field names, such as Name, Date, Left, or Picture, because these have special meaning in Access or may conflict with built-in functions. To avoid this, use more descriptive names like FirstName or OrderDate. I have a resource with a complete list of reserved words, which is worth checking out.

Now, once you've entered FirstName as your field, the next column is the data type. The default is Short Text. Access provides a variety of data types. Short Text is the most common and suitable for names, addresses, and most standard bits of information. It stores up to 255 characters.

If you need to store large amounts of text, such as notes or letter contents, use Long Text. This can hold over 65,000 characters and can even include formatting for things like bold or italic text. In older Access versions, these were called Text and Memo fields.

You might wonder why not use Long Text for everything? Short Text is more efficient for searching and sorting, and is suitable whenever you know the amount of text will be small, like names or zip codes. Save Long Text for actual lengthy entries, like notes or directions.

Next, there are several types of numeric fields, but primarily you will use Long Integer for whole numbers and Double for floating-point numbers. For example, use Long Integer to count something like the number of children, and Double for numbers with decimals, like gallons of gas.

A key point when planning your tables is to decide whether to store information as numbers or text. If you will use the data for calculations, like totals or averages, use a numeric type. If not, use text. For example, Social Security Numbers and Zip Codes are best stored as Short Text, even though they are numerical, because you will never need to perform calculations on them, and leading zeros are important.

Part Numbers are another example. Even though they sometimes look like numbers, if they can include letters, store them as text.

Access also has a Date/Time data type, which can store dates, times, or both together. This provides a lot of flexibility.

Currency is a special numeric type intended for money values. It is better than a standard number for handling financial calculations and automatically uses the regional currency format of your computer.

There is also a Yes/No field for storing Boolean values - simple true or false options.

Another very important data type is AutoNumber. Every table you create should have an AutoNumber field, often called ID or CustomerID. Access assigns these numbers automatically to each record, which makes it much easier to relate records between tables, such as matching a customer's orders to that customer. I always include this type of field and recommend you do as well.

There are a couple of data types I usually recommend avoiding: OLE Object and Attachment fields allow you to store pictures or files directly inside your database table. While these work, storing files this way makes your database much larger and slower. I cover alternatives and proper techniques for handling file storage in later lessons.

Hyperlink fields are handy for storing web addresses because they can launch your browser directly. However, I do not recommend using the Hyperlink type for email addresses - store those as Short Text fields instead, and there are more advanced ways to handle emails from within Access.

Access also offers Calculated fields, but as a rule, calculations should be done in queries, not stored in the table itself. Calculated fields can cause issues down the road, so avoid them.

Another feature to steer clear of is the Lookup Wizard. This tool is designed to help beginners create drop-down selections in tables. However, using it in the table structure can lead to normalization and design problems later. Instead, set up relational data with proper table design and use combo boxes in forms for lookup selection.

Now, based on all this, FirstName should be a Short Text field. Rarely does a first name exceed 255 characters, so Short Text is ideal. If you want to enter something into the description, you can, but most of the time I leave this column blank.

Repeat this process for all your fields - LastName, Address, City, State, and Zip - all should be Short Text fields. The naming conventions apply for whatever region you're in. If you're not in the US, use Province instead of State, or PostalCode instead of Zip, as appropriate, and add a Country field if necessary.

Remember, the benefit of building your own database is deciding which fields matter for your situation. If you need extra fields for middle names, titles, or multiple address lines, you can add those. If you want to limit the length of any field, such as Zip Code, you can adjust the field size in the properties.

In the next lesson, we will add more fields and discuss additional data types beyond just text.

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 new table in Microsoft Access
Field names and naming conventions
Overview of available data types in Access
Differences between Short Text and Long Text fields
Choosing appropriate data types for fields
Setting up field names without spaces
Using AutoNumber fields as unique identifiers
Description fields for table design
Best practices for table and field naming
Reserved words to avoid in field names
Short Text vs Long Text usage scenarios
Number data type and when to use it
Difference between long integers and doubles
Storing dates and times with Date/Time data type
Currency data type for money fields
Yes/No (Boolean) data type
Guidance on Zip Code and Social Security Number fields
Cautions about OLE Object and Attachment data types
Hyperlink data type use cases
Why not to use Calculated fields in tables
Why not to use Lookup Wizard fields
Building and organizing customer table structure

Article

In this lesson, we are going to begin building our customer table in Microsoft Access. You will learn how to create a new table, choose and name your fields, use proper naming conventions, pick the appropriate data types, and start putting together the structure for the customer table in your database.

Now that you are familiar with the Access interface, building your first table is straightforward. Before you start, it is helpful to prepare a simple list of all the information, or fields, you want to store for each customer such as first name, last name, address, city, state, and so on.

Access offers several ways to create a table, just like the other Microsoft Office applications do things in a few different ways. At the top of the screen, on the ribbon under the Create tab, you will see options for Table, Table Design, and SharePoint Lists. For now, we will not worry about SharePoint Lists, since those are for more advanced, online database scenarios.

If you use the Table button, you are taken right into what is called Datasheet View. This view lets you start typing your data right away, almost like working in Excel. You can type in information such as Joe, Tab, Smith, Tab, New York, and it organizes your entries in columns and rows. However, while Datasheet View can be convenient if you want to quickly type in sample data, it is not the best approach for designing your database. The reason is that you end up defining your table's structure after entering the data, rather than setting it up with the right rules from the start.

The better method is to use Table Design. When you click on Table Design, you enter Design View, which allows you to specify exactly which fields your table will contain, choose each field's data type, and set up other important properties. Defining your table's structure first is more organized and makes your database much easier to maintain. Instead of letting users type anything anywhere like in Excel, with Access you specify what kind of data can go into each column, or field, so users have to follow those rules. For example, if you want a field to be a date, then Access will make sure only dates can go into that field.

When setting up each field in a table, there are two pieces of information you must provide and one that is optional. You have to define the Field Name, which describes what kind of information that column will hold, such as FirstName, LastName, or Address. You also need to choose the Data Type, which determines what kind of data is stored in that field, for example text, number, currency, date, and more. There is also an optional Description field, where you can briefly explain the purpose of a field for future reference. This is useful if you have a field that might be confusing to someone else working with your database. For example, if you have a field named IsActive, you might add a description like Indicates whether this customer is currently active so that it is clear to anyone using the table.

The naming convention you choose for your fields is important, especially as your database grows and you expand into queries, forms, and possibly even into VBA programming. My recommendation is to use names without spaces and to capitalize the first letter of each word, for example FirstName, LastName, and OrderDate. This style, known as camel case, is easy to read and will save you headaches later when you get into advanced features. Although Access allows spaces in field names, using them often causes trouble when you start writing queries or code, because you will need to remember to put square brackets around any name with spaces. Sticking to letters and numbers in your field names is the best practice; avoid dashes, question marks, and other special characters, and also avoid using reserved words like Name or Date as your field names, which can cause conflicts because these words have special meaning in Access.

Now, let us start building the table. In the Field Name column, enter FirstName with a capital F and N, with no space between them. Press Tab to move to the Data Type column. The default data type is Short Text. When you click the drop-down list, you will see a variety of options. Short Text is the most common data type for storing information like names and addresses. It holds up to 255 characters and can store any printable character, such as letters, numbers, spaces, punctuation, and so on. If you are using an older version of Access, Short Text used to be called simply Text, while what is now called Long Text was previously called Memo.

Long Text allows for much more information, more than 65,000 characters, and supports formatting like bold and italics. Use Long Text when you need to store large amounts of information, for example if you want to keep detailed notes about a particular customer. For smaller pieces of information such as names, addresses, and phone numbers, Short Text is faster and more efficient.

You will also see several numeric data types to choose from. For beginners, focus on two: Long Integer for whole numbers (like a count or quantity), and Double for numbers with decimals. For example, use Long Integer for the number of children a customer has, and Double for a measurement that might include a fraction, like 10.5 gallons. Only use a number type if you ever need to do calculations with that field. If you will never add up or average a column of data, and if the field might start with zero (like a zip code or Social Security Number), use Short Text instead. Even though values like Social Security Number and zip code are numbers, it is easier and safer to store them as text to prevent problems like dropping leading zeroes.

Access also has a Date/Time data type, which can store a date, a time, or both in the same field, such as January 1, 2024 or 4:55 PM, or January 1, 2024 4:55 PM.

If you need to track money, use the Currency data type. The Currency field is specifically designed to make calculations with money accurate and will automatically use your computer's regional settings, so the currency symbol matches your location.

The Yes/No data type is used for fields that have only two possible values, such as True/False or On/Off. These are sometimes called Boolean fields.

AutoNumber is a special data type that automatically generates a unique number for each record. This is ideal for giving every customer a unique ID, which is useful for connecting records in related tables later with relationships. Access takes care of assigning these numbers for you.

There are a few data types you should avoid in most situations. OLE Object and Attachment allow you to store pictures or files directly in the database, but they tend to make your database large and perform poorly when you have many records with attachments. The Hyperlink type is good for storing web addresses, but not the best option for email addresses. For emails, Short Text is better because it is easier to work with and more flexible.

Access also allows you to use a Calculated field to perform calculations within your tables, but in most cases, it is better to perform calculations in queries instead. Storing calculations in tables can cause problems over time.

Finally, the Lookup Wizard data type might seem helpful because it lets you pick values from a drop-down list directly in your table, but it can create hidden problems and make your database more difficult to manage as it grows. It is better practice to set up your lists properly with related tables, not use the Lookup Wizard.

Returning to your table, after entering FirstName as Short Text, proceed to enter LastName, also choosing Short Text as the data type. You may decide whether you want additional fields such as MiddleName, Prefix, or Suffix, depending on your needs. If your business requires detailed names, you can add those fields, but often just FirstName and LastName is sufficient.

For storing addresses, you can use a single field called Address, although many people like to use Address1 and Address2 to allow for an apartment or suite number on a separate line. City, State, and Zip should each have their own field. All of these are typically Short Text fields, even though things like zip codes are numeric, because you do not need to do calculations and you want to keep leading zeros.

If your customers are not all located in the same country, you may want to add a Country field. If most are from your home country, you can leave this field blank for those customers and fill it in only when needed for others.

So far, in building your customer table, most of your fields will use the Short Text data type. In future lessons, you will learn how to add additional types of fields and how to set more advanced properties for each field. For now, focus on laying out the basic structure with appropriate names and data types, which will give your database a solid foundation as you continue to build.