Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you assign the datatype number to a field you've several considerations to take into account. Understanding the different number field sizes can help keep your file size down. The first question is what is a number? Your phone number, or ZIP code are made up of numbers, but they're truly text fields. You don't add your ZIP code and my ZIP code together to create a new location. We use the Number datatype when you'll potentially perform some sort of mathematical calculation on the field. A good example would be the Quantity field where you would add or subtract your numbers or inventories.
It's important to choose the most appropriate number field size for your data, because each number option reserves space in the database and that space is set aside, even if it's not used. Let's take a look at our orders table in design view and see how this works. Click on the Quantity field and I can see that it's a Number datatype, and down in the Field Properties the Field Size is by default Long Integer, but if I click on this dropdown I have several different options and let's take a look at what each of these means.
A Byte is the smallest practical amount of space your computer can store any one piece of information. So if we make our number datatype byte, you're cutting down how much disk space is used for this one piece of information. The byte field size is used for integers that range from 0 to 255. The Integer field size is used for numbers that range from -32,000 to +32,000. A Long Integer is used for numbers that range from -2 billion to positive 2 billion.
If you need a decimal, the single decimal type is for decimals up to seven significant digits. Double is for decimals up to fifteen significant digits. The Decimal decimal type is for numbers up to thirty significant digits and there is also an option for something called a Replication ID, but that's actually for compatibility with enterprise databases and is not really used in Access. Long Integer and Double are the two most common Number types. If you're working with AutoNumbers and LookUp fields the datatype for your foreign key has to be a Long Integer.
We go into this in detail in the working with auto numbers as foreign Keys video in this course. Now, in our case, when a customer orders, it's possible they'll want more than 255 of a product. Now honestly, that's not likely, but it's possible. So a Byte is not big enough. So Integer is our next option and I don't think anyone's going to order more than 32,000 of one of our olive oils, so that's safe. And they can't order half a bottle, so I don't have to consider one of the options that allows for Decimals.
So in this example, Integer is the smallest number type that suits our data. By changing Long Integer to Integer, we've saved half of the disk space reserved in the database to hold this piece of data. It's small, but over time it can become significant. Understanding these number types separates the pros from the amateurs in Access database design.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97975 Viewers
80 Video lessons · 141248 Viewers
59 Video lessons · 59610 Viewers
52 Video lessons · 72866 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.