Part of the process of creating tables in an Access database is ensuring that each field has the appropriate data type. If the field type does not reflect the type of data entered into the field, then whenever the database is sorted, and when searches and queries are performed, erroneous results will be produced. Thus, for example, if you have a field called "date_of_birth" and you define it as a text field, you will still be able to input data into it. However, when you sort on this field, you will get an alphabetical sort, whereby "11/4/1969" will be placed before "12/7/1989". There are 9 different data types to choose from in Access 2007 and each time you create a field, you need to pause for a second and think about the data it will contain and the range that that data will fall into. The Access data types are Auto Number, Number, Currency, Text, Memo, Date/Time, Yes/No, Hyperlink, Attachment and OLE Object. Auto Number is a fairly specialized data type. It is used for primary key fields which need to contain a unique numerical value. As the name suggests, you cannot directly enter a value into a field defined with this data type; Access automatically assigns a new value each time a record is created. The Number field type is used for all fields which contain values which are definitely numeric in nature such as a person's salary. Having chosen this data type, you can then specify a Field Size value in the Field Properties section of the table design view. Thus, if you had a field called "width", you might choose integer which allows you to input values up to approximately 32,000. When defining fields which will store monetary values, you should consider using the Currency data type rather than the more general Number type. Currency is designed to perform accurate calculations on money-related figures. It allows 15 figures to the left of the decimal, and 4 to the right of, the decimal point. Access has two data types for holding text values, such as "description", "telephone" or "firstName": Text and Memo. The Text data type will accept a maximum of 255 characters while the Memo type can accept up to approximately 65,000 characters. The Date/Time data type is a specialized number field which is designed to accurately hold dates and times. Sorting, calculations and queries performed on this type of field will give the correct chronological results. Yes/No fields allow you to store Boolean values, i.e. values which may only be either true or false. Examples where this field might be useful include "internal", "approved", "inStock", "female", etc. Fields that contain emails or web URLs should be defined as the Hyperlink data type. The type of field is used to create a Microsoft style hyperlink, as found in Word and Excel. You can right-click on this field and choose Edit Hyperlink from the context menu to display the standard Microsoft dialog for editing hyperlink properties. Access allows you to store binary data, such as images, inside a database field. The Attachment and OLE Object data types can both be used for storing binary data such as images and other documents. Attachment is the more efficient of the two. As the name suggests, it works in a similar way to the attachments you add to an email. Access embeds the attached file in the table so that it becomes part of your database, increasing the file size accordingly. The OLE Object data type can be used in much the same way but is less efficient and exists principally for backward compatibility.

Find jobs in bay area and legal jobs san francisco. If you would like to learn more about Access training courses, visit Macresource Computer Training, an independent computer training company offering Access Classes in London and throughout the UK.

About The Author

Related Pages

Connect With Ubscure