2. CREATING TABLES
2.1.1: Data Types
MS Access provides several data types – we will discuss Short Text, Long Text, Number, Large Number, Date/Time, Currency, AutoNumber, Yes/No, Calculated, and Lookup Wizard.
Short Text
If you specify that a field has the Short Text data type, then Access will permit any characters to be placed in that field in a row of the table. This is a common choice when the data will not be used in calculations. The Text data type provides for values that have fewer than 256 characters. If you know that a maximum length less than 255 would be appropriate, then you could use the Field Size property (discussed in the next section) to limit the maximum length of a text string.
Long Text
A designer selects Long Text if the field will have character data, but the length might be longer than 255. Long text allows for a maximum length of 63,999 characters. Consider the description field of the Course table: could these be longer than 255 characters?
Number
If a field is used for storing values that are used in numerical calculations and if the values will small enough, then Number is appropriate. Number values occupy 4 bytes of memory and have a range of -231 to 231-1.
Large Number
If a field is used for storing values that are used in numerical calculations and if the values can be huge, then Large Number is appropriate. Large number values occupy 8 bytes of memory and have a range of -263 to 263-1.
Date/Time
If a field contains date and or time values, then the Date/Time data type should be chosen. The Format property (discussed later) allows you to control how these values will appear to the user.
Currency
If a field will contain monetary values, then the Currency data type should be chosen. This data type provides for numeric calculations that are accurate to 15 digits to the left of the decimal and 4 digits to the right of the decimal.
AutoNumber
If you choose AutoNumber, then MS Access will generate a value for you when a row is inserted into the table. You can, via the New Values property, arrange the numbers to be generated sequentially or randomly. Often control numbers for things like orders, invoices, registrations, etc. are numeric and we can leave it to the system to generate a next value for us.
Yes/No
This data type restricts possible values to yes or no.
Calculated
A calculated field is one that has a derived value determined by some function.
Lookup Wizard
Sometimes you need to restrict values to a list of known values (e.g., a list of genders: Male, Female), or to values appearing as primary key values elsewhere in the database. Consider the creditHours field – a suitable list could be (1, 2, 3, 6). The Lookup Wizard is a suitable data type for these situations; when selected the system steps you through a series of windows where you can make the appropriate choices.
Exercises
These exercises refer to the Library database.
1) Consider the Book table. Add a field, paperback, that can be used to indicate if a book is a paperback. Choose the YES/NO datatype. Save the design and switch to datasheet view. Now you will see how to enter such values – MS Access provides a box that is to be checked, or not. You can select (a ‘Yes’) using the mouse or by using the space bar. You should experiment with this.
2) Consider the Member table. The id field was defined with the AutoNumber datatype. Experiment by adding new members and you will note that id values increase by 1. Now try deleting the last two members that you added. If you add those members back in, what id values do they get? Are id values reused?
3) Consider the Member table. Previously you added a gender field. Open the Member table in design view and change the datatype for gender to be Lookup Wizard. The wizard will automatically present 3 successive popup windows where you will:
- specify that you are providing the lookup values;
- enter the values (Male and Female);
- specify that values are to be limited to your list.
- Save the table and enter datasheet view so you can test out the datatype you have just created. You will notice the user sees a drop-down list containing Male and Female, and so the user cannot enter/select an inappropriate value.
