We will be undergoing scheduled maintenance on May 20th, 2013 at 02:00 GMT.
Crystal Reports: Advanced Tutorials
Advanced Database Concepts / Relationships and Joins
Subtitles of the Movie (Crystal Reports: Advanced / Advanced Database Concepts / Relationships and Joins)
We have just finished talking about verification. We understand the significance of looking for our errors. Another issue that we need to be aware of with our database, is the relationship between our fields. Just as an error can cause catastrophic results, well so to can a bad relationship. Without a relationship, we have no report. I am choosing the Northwind database in my Microsoft Access to show you this concept. We'll be utilizing Northwind throughout this course, so I'm choosing this database in the example. What you are seeing is a relationship screen, I can tell that my tables are actually related because I have a line that's actually connecting these together. Now in a well designed database, we try to use the same name in both the originating table, which we refer to as a primary table, and our secondary table, or a foreign table. We refer to it as foreign, because the data is not really coming from the tables, such as in this case, as products, but it's linking back to suppliers, to bring in that information. Normally we like to use the same name, because it is so easy to spot the relationship. I can see that supplier ID is coming from suppliers. Now on this screen, there are two ways I can tell a primary table. Number one it's normally the name of the table, followed by ID. To see where employee ID correlates to employees, order correlates to orders, customer correlates to customers, makes it very easy to spot your primary key. I also know the primary key because of the connection. The one means there is one occurrence of the item in the suppliers table, whereas on the opposite side, where you see the infinity symbol, means that there are multiple occurrences of the same item. One or primary, multiple, many times over. Think of it logically, in my orders I will sale the same product over and over and over again, multiple occurrences. In products however, I only keep one in the master list. Understanding these relationships is very, very important in putting Crystal Reports together. You should also be aware, that traditionally the ID is going to be a number, it's a code. We place that as a code, because it is faster on our database, so if I'm looking for a supplier, I bring in the supplier ID, as the connection between the two tables, but what I'm really interested in, is the suppliers name, or the company name. So I will actually bring in both the supplier ID and I will bring in the company name. I will bring in both of them on the report itself. When you have two tables that have no relationship, you do not see those lines connecting them. For instance, although orders has an employee ID, and employs, has an employee ID, there is no line connecting them, so Crystal will not treat that as being a relationship. These relationships, these types of joins, are established within our database program, we will see that Crystal will do it's best to bring these joins over, but remember we talked about things going wrong in our data. If that actual relationship doesn't come through correctly, we need to know the related tables, because we can create that join right in Crystal. Before we go back to Crystal, let's expand more about the type of joins, or the type of relationships there are.
Tutorial Information
| Course: | Crystal Reports: Advanced |
| Author: | Lauri Sowa-Matson |
| SKU: | 34268 |
| ISBN: | 978-1-61866-016-9 |
| Release Date: | 2011-09-30 |
| Duration: | 7.5 hrs / 100 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
