Microsoft Access database fields are created by entering a field name and a field data type in each row of the field entry area of the database table window. The field description is an option to identify the fields purpose; it appears in the status bar during data entry. After you enter each field's name and data type, you can specify how each field is used by entering properties in the property area. Before you enter any properties, however, you should enter all of you fields names and data types. Naming a Database FieldA field name identifies the field to both you and to Microsoft Access. For information regarding field names please see the article: Tables and Fields Specifying a Microsoft Access Data TypeAfter you name a field, you must decide what type of data the field will hold. Before you begin entering data, you should have a grasp of the data types that your system will use. Ten basic data types are shown in the table below; some data types (such as numbers) have several options:
For numeric data types, the field size enables you to further define the type of number, which in turn determines the storage size. The table below shows the seven possible settings in the Numeric Field Size property. You should make the field size the smallest one possible; Access runs faster with smaller field sizes. Note the first three settings don’t use decimal points, but allow increasingly larger positive or negative numbers. Single and Double permit even larger numbers: Single gives you 7 decimal places, and Double allows 15. Use the Double setting when you need many decimal places or very large numbers.
Caution: If you convert a large FieldSize setting to a smaller one in a field that already contains data, you might lose data. For example, if you change the FieldSize setting for a Text data type field from 255 to 50, data beyond the new 50-character setting will be discarded. If the data in a Number data type field doesn't fit in a new FieldSize setting, fractional numbers may be rounded or you might get a Null value. For example, if you change from a Single to an Integer field size, fractional values will be rounded to the nearest whole number and values greater than 32,767 or less than –32,768 will result in null fields. Tip: You can use the Currency data type if you plan to perform many calculations on a field that contains data with one to four decimal places. Single and Double data type fields require floating-point calculation. Currency data type fields use a faster fixed-point calculation. In relational databases like MS Access, you store any information in the form of records (rows) and fields (columns), which constitute a table. A table is created such that we can derive various relationships between the different data sets. Let’s take a real-life example to understand it more clearly. Say suppose you join an organization in the role of an HR manager. Now, the department of payroll asks you to provide the details of all the employees currently working in a particular department. So, you would most probably try to develop an MS Access database with all the employees’ information like their employee id, DOB, date of joining, number of days present, and also, and number of days taken leave or marked absent. So, whatever fields I just mentioned are all independent entities that are required to be entered and saved into a single place. This place is technically called a table. Before entering any particular data, you need to enter that field's data type. If you try to enter any data which is not of the mentioned data type, then Access would not take the entry. Among other advantages of creating a table in Access, one major advantage is that it prevents data redundancy. That is, it would help you prevent duplicate entries or repeated entries of particular data. Even if redundant data is not reduced to 0 levels, it can significantly lower the redundancies already present in the data. This is of great help because manually reducing or eliminating duplicate information can be a daunting task all by itself. So let’s see how a table is opened in a datasheet view and how helpful it can be in managing the information. Whenever you go to the navigation pane and double-click on a particular table, then the view in which the table opens is called a datasheet view. You can mark the important section of your table in this section. It is best suitable for viewing and editing the entries. At the bottom left corner of the datasheet view, you will be able to see the number of records in the table. An example of how a table looks like in datasheet view (image source: google) But if you want to set field names and their corresponding data types, you would need to go to the design view. In design view, it’s actually easy to manage view, and it controls the overall layout of the table that you are seeing. So when we open the design view, we can create the table by following these simple, easy steps- • In the Design view, go to the application ribbon, select the Create tab, and then the Table Design button. • Adding fields– You can add fields such as name, address, zip code, PAN card details, aadhar card, etc. After adding the fields, it should look like this. (image source: google) • Adjusting the data types– Now, you need to think about which field would contain what type of data type and select the data type accordingly. One thing to be noted here is if any particular data type doesn’t need any calculations, then that data type can be selected as text instead of a number. Say suppose, even though the zip code of a particular person’s address is not typically a text, we can still select the data type as text instead of a number since we usually don’t perform any calculations on someone’s zip code. But on the other hand, someone’s salary field should be set as a number because you may need to calculate salary and apply deductions to it; hence, it is always suggested to do it this way. • While you save a table, maybe you would be prompted by Access to create a primary key. It’s a good practice always to accept the suggestion and create a primary key, the importance of which we will explain in the later part of this tutorial. |