Database Field Properties and Data Types

Essential Database Field Properties

• Validation Rule and Validation Text: This is a standard that all data in a field must meet. If data is entered that does not meet this standard, the tables will not be updated correctly. The Validation Text is the message that appears on the screen when you enter a figure that does not meet the validation rule; this message reports the specific error being committed.
• Required: This option lets you choose whether the field will be mandatory. If you select "Yes," you cannot leave this field blank, and the table is blocked until it is filled.
• Input Mask: This sets the default format that the data will take automatically. We use 0 for numbers and L for letters. Example: 91 881 14 000 00 00 79/00
• Indexed: This lets you create indexes within a table. An index is a field through which the table is sorted internally (not visible on screen) automatically every time you write a new record.
  • "NO" (default): The field is not indexed, and therefore your data will not be sorted.
  • "YES" (with duplicates): The field will be the table index and will allow duplicate data.
  • "YES" (without duplicates): The field will index the table, but the data may not be repeated.
• Unicode Compression: This is active only for text, memo, and hyperlink fields. It automatically serves to compress the data in these types of fields so they take up less space.

Common Database Data Types

• Hyperlink: Sets a link to any file hosted on your hard disk, a website, an email address, etc.
• Number: Allows you to store only numeric values with which we can perform mathematical operations or order the table.
• Date and Time: Allows you to store date and time data.
• Currency: This is similar to the number type; you can add the symbol of the currency with which you work, and it allows up to four decimal places.
• AutoNumber: Access automatically sets the data for this field.
• Yes/No: A type of field that accepts only two possible answers.
• Lookup Wizard: Allows you to create drop-down lists where you can choose the most convenient option.
• Text: You can enter any combination of characters (numbers, letters, signs, etc.). The maximum size of this field is 255 characters.

Tables, Queries, and Primary Keys

• Queries (Consultations): We can look for information that is housed in one or more tables in the database.
• Table: The main purpose of a database, which collects information on a particular topic.
• Key Field: The main field of the table through which Access will recognize the table within the database. Through the key field, Access can access the board and read all its information. It is mandatory to create a key field to relate the different tables with each other in the database.

Features a Key Field Must Meet

1. Your data cannot be repeated; it must be Indexed (No Duplicates).
2. It is mandatory to always fill the field (it must be Required).
3. The field data should occupy the necessary space efficiently.