A few years ago I wrote a post about Linking vs Appending tables. I explained that CR doesn’t really provide a simple way to append the rows of one table to the rows of another. Normally it requires writing some SQL in a command object. But I recently heard from Gordon Portanier of Crystalize in Canada who had told this same thing to a pair of advanced students, who promptly showed him (and now me). How to accomplish a UNION of two tables without having to use a Command object or write SQL. It involves using an intentionally bad link and setting CR to use a FULL OUTER JOIN. Here is the scenario.

Say you have a table of charges and a table of payments and you want to combine them into one table. But you don’t want to use a normal link  because some charges can be payed in multiple payments and some payments can apply to multiple charges. I would normally append the charges to the payments using a UNION. Using this new method you would find a field in each table that is always filled in, but that will never have a matching value in the other table. For instance we could use the check number and the charge code. You link the tables together using these fields. With the default INNER JOIN there will be no data returned because there are no matches between the check number and the charge code. But if you change the join to a FULL OUTER JOIN. You will get all of the unmatched records on both sides. And because no record will have a match you will get all records from both sides.

One consideration before using this method is that not all database support FULL OUTER JOINS. SQL Server, MySQL And Oracle do, but MS Access does not. Another consideration is that a UNION (when done correctly) will align the corresponding fields into one column.  To accomplish the same thing using the FULL OUTER JOIN method you will have to write a formula for each column. For instance to get a single date column you would need a formula like this:

//@date
If IsNull ({Payment.Date}) the {Charge.Date} else {Payment.Date}
You can then use this formula to sort the records from both tables into one chronological list.

But probably the most important consideration will be performance, especially on large data sets.  It is tricky to filter a table that is considered the OUTER table, and both of the tables in a FULL OUTER JOIN are OUTER.  That might force you to filter the data within the report – something that can have a dramatic effect on the time it takes for the database to return records.

So with these considerations in mind it would be preferred in most cases to use a true UNION.  But who knows when this alternative method might come in handy.