When you define a relationship in Access, you relate data from one table to another. By doing this, you are linking your tables together so you can perform queries and extract specific data from multiple tables all at once. Show
Access 2016 gives you three types of table relationships to use. Let's learn about these relationship types.
Introduction to JoinsSometimes in Access, you'll need to view information from two or more tables from different database tables. For this, you will need to create what's called a join. A join does as the name implies. It temporarily joins two tables together. Whenever you run a query to find data in tables that are related, Access will look for records on both sides of the relationship that have matching values. By learning what type of query join to use, you can determine what records will be displayed as the results. When you do this, you can find the exact data that you need without sorting through data that you won't. Let's delve a little deeper so that this makes sense to you. In this article, we're going to talk about the two types of joins:
Inner JoinsAn inner join is defined as a join that only selects records from tables that have matching values. You can select one or more fields to serve as the join fields. This is the default join type in Access. You don't even need to do anything most of the time to create an inner join if relationships are already established. Access creates an inner join between related tables when you add them. When you create primary keys and foreign keys, have the same field in more than one table, Access creates that relationship for you and creates the inner join. You can create an inner join by dragging a field from one table to another in query design view. There will be a line displayed to show that a join has been created. Outer JoinsAn outer join informs a query that the query needs to include all rows from one table, then rows from the other table too that share a value on both sides of the join. That said, outer joins can be left or right outer joins. Left outer joins include all rows from the first table, then rows from the other table that contains values that are the same in both tables. A right join is vice versa with all rows from the second table included and only those rows with matching values included from the first table. You can click any join to see the Join Properties dialog box to see if it was a right or left join. Creating a JoinTo create an inner or outer join, first create a relationship. Double-click on the line that illustrates the relationship in Design View. You can see the line below. The following dialogue box will appear: Now click Join Type.
Click OK. Create a Query on Multiple TablesNow that we have a join set up with two tables, we can create a query on these two tables. This is often done to get data from more than one table and display it in one place. In this example, we are going to show details of the Books and Orders table in the one query. Create a new Simple Query using the Query Wizard. In the drop-down box that appears on the field selection screen, select "Table: Books". Select the Author, Title, and Price fields, and add them to the Selected Fields section. Now, select the "Table: Orders" from the drop-down menu. Add the Order ID, Order Date and Destination Country fields to the Selected Fields section. Click Next, and Next again. Name your query and click Finish. The query will now show the results from both tables. If you go to Design view, you can see the fields listed, as well as the two tables and the relationship between them that was created. NormalizationNormalization sounds more difficult than it really is. It simply refers to the process of organizing data in your database efficiently. Normalization has two goals: to eliminate redundant data (storing the same data in more than one table) and ensuring data dependencies make sense (storing only related data in a table). By doing this, you reduce the amount of space a database takes up, and you insure that your data is logically stored. It's good to know this as it will help you understand why we create multiple tables and why we structure them in a certain way. With that said, there's a series of guidelines that you follow to make sure your databases are normalized. These guidelines are called normal forms. They're numbered from one (the lowest form that's called first normal form or 1NF) through five (fifth normal form of 5NF). You'll see 1NF, 2NF, and 3NF frequently. Sometimes you'll see a 4NF. However, 5NF's are rare. They're so rare, we're not even going to discuss them. Just remember that these are only guidelines. They're not requirements. You can use different variations to meet your own requirements. Now, let's discuss the normal forms. 1NF This first form sets basic rules for an organized database.
Reminder: The primary key of a relational table gives a unique identification to each record in the table. It is a normal attribute that is known to be unique, such as a social security number or account number. You will not be allowed to enter duplicate primary keys within a database table. 2NF This second form goes further in removing duplicated data.
Reminder: A foreign key is a field in a relational table that is a match for a primary key of another table. It can be used to cross reference tables. For example, perhaps the customer's account number is used at the primary key in Table 1. Perhaps their phone number is used as the primary key in Table 2, but their account number is also used in that table. It is a foreign key. 3NF
3.5NF
A candidate key is a column or set of columns in a table that identify the records (rows) without referring to any other data. It means it's a "candidate" to become your primary key, so it's a possibility. One of the candidate keys becomes your primary key. In a table, a determinant is an attribute that determines the values assigned to other attributes in the same row. For example, if you have the attributes employee ID, first name, last name, and job title, the attribute Employee ID would determine the values assigned to the other attributes. Your determinant may be your primary key. 4NF
A multi-valued dependency happens when one or more rows in a table imply the presence of one more other rows in the same table. If that doesn't make sense, think of it this way: if our example car dealership has a table that contains model name, color, and year of each car, there is a multi-valued dependency if you have a model name in blue, then another in red. The record (row) of the blue car implies the presence of other rows that contain red cars, etc. You may or may not want to complete the fourth normalization form. It depends on the data that you want to store. DenormalizationAfter normalization, you may want to speed up the data retrieval process and add back redundant data. Just don't confuse a denormalized database with a database that has been normalized. In a denormalized database, specific redundant data is added back in because it's needed to speed things up. Some redundant data will be left out. Calculated ExpressionsExpressions in Access 2016 are used to do mathematical calculations, combine or extract text, or validate the data in your database. Expressions use all or some of the following elements: functions, identifiers, operators, and constants. The plus and minus sign are operators, for example. However, before we move forward, let's talk briefly about the definitions of functions, identifiers, and constants before continuing. It's important that you understand what everything is before you begin to use it. Below is an example of an expression: =Sum([Retail Price])*.08 In this expression, Sum() is the function, [Retail Price] is the identifier, the asterisk is the operator, and .08 is the constant.
When you use expressions in Access 2016, you can use them to do any of the following:
Calculated Detail FieldsA calculated field is defined as a column that contains an expression. Whenever you enter a record into the table, Access will use the expression to calculate what data will appear in that field. To create a calculated field, open a table in your database. We've opened Books, as you can see in the snapshot below. Let's add a new field. We'll make it a calculated field. Click the arrow in the Click to Add field, then choose Calculated field from the dropdown menu, as shown below. Now you can choose what type of calculated field you want to add. It can be text, number, currency, Yes/No, or Date/Time. Since your table contains a lot of text and not many numbers, we've chosen text. The Expression Builder then opens up. You can use the Expression Builder to create calculated fields or expressions for records. The Expression BuilderBelow you'll see a snapshot of the Expression Builder that we opened in the last section of this lesson. Let's learn how to use it in creating a calculated field, although you can use the Expression Builder for any expression you want to create anywhere in table, query, or other area. Let's learn how to use it. The upper section of the Expression Builder is called the Expression box. This is where you put together your expression. The three columns below the Expression box are where you can go to construct the expression. If you want, you can type some or all of your expression directly into the Expression Box. If you go to the first of the three columns below, you can choose an expression element. If you'd like to see the month value for the publication date, you'd first expand the Functions group. Select a category, then select the value. We've selected Month. Double-click on the function to add it into the expression box. Now you can fill in values. Now you can click on <<date>> to highlight it, and type in the value. Type in [Publication Date]. Access may suggest the field for you as you're typing. Press Enter to add the selected field. Click OK and it will be inserted into your database. The field will be added, as shown below. Enter a name for the field, and the table will be updated. FunctionsAs we stated earlier, a function is a procedure that's used to determine a value. The concept is simple, but learning all the functions can be overwhelming and almost impossible. Unless you're a programmer or familiar with programming language, using the Expression Box is the quickest and simplest way to do functions in Access2013. In addition, MS Access 2016 explains what each function is at the bottom of the Expression Builder window. To see all functions for MS Access 2016 and what they do, as well as use them in your expressions, click on Functions in the first bottom column, then Built In Functions. Insert Expressions into Queries, Reports, and FormsTo use a calculated expression in a Query, open the query. Switch to Design view, then go to the Design tab and click Builder in the Query Setup Group. To create an expression in forms or reports, go to Design view. Select the field where you want to enter a calculated expression and select Properties. You'll see this window on the right hand side: Look for the three dots beside the Control Source item, which is on the Data tab. This represents the Expression Builder. We've pointed out in the snapshot below. Click on those three dots to open the Expression Builder and enter your expression. What is a relationship in MS Access List and explain three types of relationships?In a relational database (Access), the data in one table is related to the data in other tables. In general, tables can be related in one of three different ways: one-to-one, one-to-many or many-to-many. The relationship is used to cross reference information between tables.
How do you add relationships in Access?To create a relationship between two common table fields in the Relationships window in Access, click and drag the shared field from one table and drop it on top of the common field in the related table to establish a join between them based on the values in the common field.
What are the types of relationship in MS Access?There are three types of table relationships in Access.. A one-to-many relationship. Let's use an order tracking database that includes a Customers table and an Orders table as an example. ... . A many-to-many relationship. Now let's look at the relationship between a Products table and an Orders table. ... . A one-to-one relationship.. |