Microsoft Access Tables For Beginners
Access tables are classified as the primary building block of an Access database. They’re 2-dimensional constructions which consist of rows (records) and columns (fields) and the theory inside of a relational database setting is that information is split up into discrete data structures and preserved in a number of related tables.
Relationships
In a database intended for a teaching institute rather than have one huge table that contains all the info about every student and every individual course which they might attend it is better to provide just one table for students and one for courses which has a relation relating to the two. There are two forms of relationships; One-To-Many and Many-To-Many. A One-To-Many relationship indicates that one record can exist many times in another table. As an example, a class is taught by just one teacher therefore we express that “one particular teacher teaches many classes”. A class, on the other hand, can easily consist of many students but a student may additionally be present at many classes. This is why we say that the students and classes require a “Many-to-Many” relationship. In this case a junction table is needed between the two tables.
Primary keys
Primary keys happen to be what Access utilizes to determine which field in a record specifies said record. By way of example, if a student called “John Smith” wished to join a course it would be confusing and pointless to inform Access to retrieve the information for John Smith belonging to the students table as there could well be two John Smiths. Incorporating the birth date wouldn’t help since there might yet end up being two students with the exact same birth dates as well as names. Relational databases deal with this by awarding every single record a primary key that’s unique to this record and can’t be duplicated. This is fundamentally the same as granting John Smith an ID number.
Data types
Access tables use data types to assist with data validation and also keep memory requirements very low. A data type is where the value associated with a field needs to match a particular kind of data. For instance, if a field were characterized to have the data type “number”, it would not recognize a text value similar to “Today” or “Seven”. The values will have to be numeric. Access has numerous data types. This is a list of the most popular:
Auto number: Designed to produce numbers automatically in a table. The numbers are in sequence (just like a counter) or random. The user only should add a record for this field to update itself with an auto number.
Text: Utilized to store characters and combinations involving characters. It is widely used to hold names, places and descriptive aspects. Numbers could be stored as data type text and it is recommended if they’re not numbers that calculations might be conducted on. They are able to store up to 255 characters.
Memo: Similar to text with the difference that many more characters could be stored (65,536).
Number: Used to store numbers for calculations. It is categorised into different kinds of numbering. Integers (whole numbers) can hold non-decimal numbers while it is typical to employ single and double when requiring working on fractions.
Yes/No: Binary data type that enables the user to store either a yes/no (true or false) value.
Currency: Used for holding currency values. Very beneficial as it removes a number of the complications with decimals associated with calculating currencies.
Data Validation
Access tables have got specific components to ensure that the data entered are as accurate and relevant as possible. Data validation is one such concept. It enables the user to enter expressions which control the data. For instance, you might have a check-in and check-out date for a hotel’s database. Clearly, the check-out date has to be after the check-in date therefore we can use data validation for the check-out field and insert a function like >[Check-In] to inform Access of our requirements. We can then combine this with a data validation message like “Please enter a check-out date which falls after the check-in date” to ensure the proper message is transferred to the user.
Other forms of data validation include:
Requiring a field to possess a value (leaving the field blank isn’t an option)
Unique records (not enabling repeating of records)
Indexing (permitting quicker searching of records)
Summary
Tables are the foundation of Access databases and are also easy to create and maintain. You will find quite a few integrated tools like data validation and data types that will help to maintain good quality data at the table level.
