New users often assume that just because two tables share a common field, that the tables can be linked in a report. But this is only true when the two tables can be successfully made into one composite table. And, there are only two logical ways that two tables can be combined into one: Linking them (horizontally) or appending them (vertically).

1) Linking is by far the most common. I describe it as horizontal because linking adds the columns of one table to the columns of the other. For instance you have an orders table with 4 fields, Customer, Date, Quantity and Product ID. You want to add a new column to show the product name field, but the name is stored in the product master table. So, you use the product field to link the Orders table and the Product table. this makes the columns of the Product table available to the report. Your available data just got ‘wider’.

2) Appending, which works vertically, is less common. It is also harder to do in Crystal. Say you have the same orders table described above and also have inventory adjustments table. You want your report to show both the Orders and the Adjustments for each Product. Both tables have the Product ID but this won’t provide a valid link for these tables. Your report should show both the Inventory Adjustment records and the Order Records as separate rows on the report. So, if there are 100 orders and 20 adjustments you need 120 rows. In other words, you need to append the 20 rows to the 100 and then sort them by product.

So, how can you tell if you can link or need to append? Usually by checking for a one-to-many (or lookup) relationship between the tables. Examine the linking fields of each table separately, before they are linked. At least one of the linking fields should not contain duplicates but should have all unique values. The field without duplicates is usually the “one” end of the one-to-many relationship. It allows you to “lookup” values related to that field; values that would have been redundant if stored in the other table. In our example above the Products table is the “one” end of the one-to-many relationship. There is one product to many sales.

If you find duplicates in BOTH of the linking fields, such as our second example, then you probably have an invalid link. The Product ID shows up multiple times in both tables so these tables do not have a one-to-many relationship. Trying to link these tables will generate redundant and typically useless data. They could be appended but, unfortunately, there isn’t a native Crystal feature for appending one table to another. To do this requires that you combine the tables in your database or create a SQL command that does a UNION of the two tables.

If you have questions about linking tables or creating a UNION please drop me a line.