Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Excellence can be obtained if you care more than others think is wise, risk more than others think is safe, dream more than others think is practical, expect more than others think is possible
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!


How Do I Combine Records from Two Separate Tables in Crystal Reports?

I was recently asked by a client "How do I combine records from two separate tables to make a single dataset in Crystal Reports?"


The most effective way of combining records from separate tables into one dataset is to create a recordset, based on an SQL command, using the SQL UNION function.

There are two UNION flavours:

UNION ALL - Returns all records including duplicates

UNION  - Only returns unique records

Using the Add Command option in the Database Expert dialog box, enter the following SQL code:

    SELECT Fieldname1, Fieldname2, Fieldname3 from Table1


    SELECT Fieldname1, Fieldname2, Fieldname3 from Table2


As the UNION ALL function includes duplicates, it tends to execute quicker than the UNION function, as it does not have to remove the duplicates from the dataset.

Some databases will only work with the UNION ALL function

If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us

Julia Emelogu

Hi Richard, which union statement are you using? UNION or UNION ALL? When you are adding the fields, are you adding the unique fields from each table? The other thing you may want to do is add a where statement to each union dataset, to bring back the exculede records. If you want me to have a look at the SQL statement let me know

Richard Vyse

I can get this to work but it only returns records which are the same in both databases. I need it to also list the records that are only in one of the 2 databases. Is it to do with the way the tables are joined in database expert? I note that the full outer join option is not available.

Lance Premus

Hello, Can you do the union all when the databases/tables are on different servers? I have a legacy database on a different server and new operations database a new server and We need a report to Union all from both legacy and new database for historical trends.


Hi Lance, Yes you can, you just need to reference the server as well as the database in the FROM clause


Name *

Email Address *

Comment *

To prove you are human what is:

50 + 18 =