Ms access validation rules
![ms access validation rules ms access validation rules](https://somesa-assets.somesa.ug/golearn/access2007/content/collections/access2007/2870/img/ac07_validation_text_set.gif)
Rules that help to maintain accuracy between tables Parent-child relationship exists between linked tables First table is the parent Table the parent is linked to is the child Establishing referential integrity prevents the creation of an orphan Ensures related tables are consistent with one another
![ms access validation rules ms access validation rules](https://slidetodoc.com/presentation_image_h/f15f9f2db913fd0ba9cc8968f5e94d47/image-13.jpg)
In Customers table Join line Enforce Referential Integrity CustomerID is foreign key in Loans table
![ms access validation rules ms access validation rules](https://i.pinimg.com/originals/1b/e6/a4/1be6a49c72095a7563b5ec5e9034cda1.jpg)
Primary Key Most often the field chosen when defining a relationship Joined fields should be the same name, must be the same data type Once a primary key is selected, Access prevents any duplicate or Null values from being entered in the primary key fields Foreign Key One or more table fields that refer to the primary key field in the primary table Indicates how the tables are relatedġ8 Relationships Window CustomerID is primary key Join line Needs a new table especially created for the relationships, junction table May be thought of as par of one-to-many relationships between two tables Many records in a table have many records in another table One record in a table has one record in another table One-to-many One record in a table has many records in another table Many-to-one Also known as lookup table relationship Many records in a table have one record in another tableġ6 Relationships Many-to-many Most difficult of all relationships Parent In a one-to-many relationship the “parent” is the “one” in the relationship Referred to as the primary table Child In a one-to-many relationship the “child” is the “many” in the relationship These definitions also apply to query level relationshipsġ5 Relationships Four types of relationships One-to-one Rarely used The Relationships window used to define, view and edit table relationships When properly designed, every table in a database is related to at least one other table in the database A table may be related to more than one other table Important to know which will be the “parent” table and which will be the “child” table in the relationship Must enter digit 9 Must enter a digit or space L Must enter letter ? May enter letter A Must enter letter or text a May enter letter or text & Must enter any character or a space C May enter any character or a spaseġ1 Relationships and Referential Integrityġ2 Relationships Connecting fields with like information, or physically joining two (or more) tables In the Relationship window Relationship is at the table level When queries are created Relationship is at the query level Table level relationships take precedence over query level relationships Switch to Design View Format property: text character or space is required Example: displays as > : force upper case characters 0 And 50 Value entered must not be 50 Like Like “Jones” Value entered must be Jones And >5 and <15 Value entered must be greater than 5 and less than 15 Or “B” or “R” Value entered must be either B or R Not Not “Red” Value entered must not be Red * Like A* Value entered must begin with the letter A ? Like Mi?le Value must begin with Mi, end with le, and have two characters in the middle =10 Value entered must equal 10ġ0 Example of Input Mask Example Meaning Must enter digit 9 Validation Rule property Rejects a record when data is entered that does not agree to the rules as set out in this property Validation Text property Provides the user with a message that tells them how the data should be entered Validation Text is not required when using Validation Rule, however strongly recommended Property is an object characteristic that defines how that object will look and behave Validation properties maintain data integrity Reduce errors Simplify the entry process Two validation properties in Access Validation Rule Validation Text
![ms access validation rules ms access validation rules](https://i.ytimg.com/vi/5XZW6KNBlX8/hqdefault.jpg)
1 Microsoft Access Validation Rules, Table Relationships AndĢ Objectives Understand and use data validationĮstablish table level relationships Define and create an action query