Manipulating data in MS Access
How to work with the data grid
When you have a large quantity of data to input, the data grid is not a bad way to go. You normally won't use this technique for day-to-day operations - we will create forms for that - but it is useful at startup.
You will have to modify some of the properties of the fields in the table to make the best use of all the functions. It is recommended to test the changes on a few entries at first.
You get the data grid when you click Open on a Table in the Database window.
The data grid acts like a spreadsheet. For people used to working with Excel it should look quite familiar. Even the tools used to manipulate the data are similar to those used for the spreadsheet.
Ensuring the accuracy of data
It is important to ensure that the information that gets into the tables is as exact as possible.
There are many ways to accomplish that. In many applications it is done through code - a programmer has to write validation rules that are checked whenever data is input into a table.
However, Access has built-in mechanisms that help with data validation. Those mechanisms are contained in the Properties of the tables and should be defined at the time the table is created. It's all part of database design.
Important table properties
Default values: If there is a field where you see you will enter the same data more than 50% of the time, use a Default. It will display automatically and you only have to tab over it to accept it. If you have to change it, you just enter a new value over the default.
A Validation rule is a check on the accuracy of the input. Sex can only be "M" or "F", for example. If a field had to be less tha 100 you would code the validation rule as: < 100.
If the operator makes a mistake on a validated field and there is no Validation text, chances are the system will display a weird error message ("You commited a fatal error! You will die!" can be somewhat intimidating.) The validation text is your way of telling the operator what he should have done.
Choosing the input from a list is a lot more exact than keying it.
The table containing the list should already have been created. Just select the table.
If the table for the list contains more than one column (Language, for example), Column count will be 2 and Bound column will be 1. Bound column is the position of the column that will be saved in "Movies". The list will show "Language number" and "Language" but it is column 1, Language number that is saved.
Next week: Creating a data entry Form. See you then!