Welcome! Please sit down quietly and find your bell ringer in the Mrs. Milnes folder
Computer Class
April 30th, 2019
Access Relationships
Today we are going to be talking about Access Relationships- how to make them, and why we need them!
Computer Two
What are they?
Ex: you might have a ProductID field in a Products table and in an OrderDetails table. Each record in the OrderDetails table has a ProductID that corresponds to a record in the Products table with the same ProductID.
What?
A relationship in Access helps you combine data from two different tables. Each relationship consists of fields in two tables with corresponding data. In other words, this is how Access can easily tie information together from multiple tables.
1.
Why do we need to do this?
well...Access IS a RELATIONAL Database! Relationships are a key part of Access. By setting relationships between tables, you are programming Access to see what we see- what information is related to each other. This is what sets Access apart from other data storage applications.
2.
Why?
One of the goals of good database design is to remove data redundancy (duplicate data)
3.
A relationship can also help prevent missing data, by keeping deleted data from getting out of synch, and this is called referential integrity.
Referential Integrity??
Referential What?
So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.
For example, if we delete record number 15 in a primary table, we need to be sure that there’s no foreign key in any related table with the value of 15. We should only be able to delete a primary key if there are no associated records. Otherwise, we would end up with an orphaned record.
We can't have data that doesn't exist! Referential Integrity tells Access to not allow let that happen.
There are different types of relationships...
There are many different types of Access relationships, but today we are only focusing on one-
the One to Many Relationship.
Types
Let's draw this out.
Important things to remember
HINT: The term Foreign Key in a Relational Database System refers to a field that in a table that stores values from a Primary Key field in another table, to relate the two records to each other.
Info
- You cannot create relationships when tables are open. You must close all tables before moving on.
- When creating a relationship, the two keys must be of the same data type (ex: Number and Number).
- When creating a relationship, it is between a Primary Key and a Foreign Key.
Microsoft Access- Field Validation
Any guesses as to what this is?
Computer One
Today we are going to be learning about FIELD VALIDATION
Taking a look at definitions...
With these definitions in mind, what do you think the purpose of Field Validation is?
Let's find out
Field- What is the definition?
Validation- the action of checking or proving the validity or accuracy of something.
Field Validation
Field Validation is when users can control what is entered in a table field
Closer Look
While you CAN validate by choosing Data Types, you can be more specific by writing a Validation Rule.
So...why do we need a Validation Rule?
There are several reasons why one would need to validate with a validation rule-
- Validating Specific Ages or Dates
- Validating Specific Prices
- Validating what Letter you would like each word to start with