What are factors for choosing data type in access table?

Answer 1

It depends on what you are using the data for. For things like names and addresses, they will naturally be text data. Code numbers and phone numbers should actually be text and not number as many people would think. There are a few reasons. One is that you never do calculations on phone numbers, like add them, multiply them, subtract them etc. Another is that often you will want spaces or other characters in them that a number type will not allow. The most important reason is that many phone numbers, when you include area codes, will begin with a zero. If you start anything with a zero in a number field, the zero will automatically be ignored. It is necessary in a phone number though, so a number field is no use. Code numbers often begin with zero too or have letters in them, so they should always be text.

There are various number formats, like currency and Autonumber, so it is important to choose the correct type for what you are doing. Hyperlink would obviously be the data type for a web address, although you could use text. E-mail addresses could also be in text. It depends on whether you want to be able to link to them.

The need for validation on some data would be another factor in choosing what data type you are going to use. You also may want to put input masks on them to reduce down the errors people can make when entering data. You can also set your data to be accessed through lookup values from a list or another table or query. If you have a very specific set of values, like the names of a department in a company, it is a good idea to use them. It reduces the possibilities for spelling mistakes by those entering in the data.

Compatibility with data in other tables or applications is important too. If you are setting up a relational database you need to be sure that fields in relationships are of the same types. If you are importing or exporting data to other formats, you'll need to be sure that they data types correspond or you may end up losing some of your data in the process. If you are updating an older system, you need to check what data types were used in it so that when you move the data to your new tables, that you don't lose anything. If you have decided that you do need to change a data type in an old system, but want to retain the data as it looked, like putting a numeric format to text if it is a code number, you need to be careful too. Making a few copies of the tables so that you can test them is a good idea. Then you won't lose anything if something doesn't work as planned.

These are just a few of the considerations to be taken into account. As stated at the beginning, it really depends on what you want to do with the data and how you are going to manipulate it. So a bit of planning before you start is what you should do first. Choosing the wrong data type can cause problems, and sometimes they won't be noticed initially. A common one is people putting dates into a text field. It looks fine until you try to sort or do some searches, or try to manipulate dates with the functions that are available. When you have finished your design, check everything carefully and run a few quick tests to see that things work. With experience you will learn what are the best types of data for your requirements.